Troubleshooting Discourse PostgreSQL database migration locks on RHEL 9 during major version upgrades

Understanding PostgreSQL Lock Contention During Discourse Upgrades

Major version upgrades of PostgreSQL, especially when performed on a production Discourse instance, can introduce complex challenges. One of the most common and disruptive issues is database lock contention, which can halt the upgrade process or, worse, lead to data corruption. This often manifests during the `pg_upgrade` utility execution, particularly when migrating from older PostgreSQL versions (e.g., 12.x) to newer ones (e.g., 15.x or 16.x) on RHEL 9 systems. Understanding the root causes and having a systematic approach to diagnosis and resolution is paramount for enterprise infrastructure architects and DevOps engineers.

The `pg_upgrade` utility works by creating a new cluster and then copying or linking data files from the old cluster to the new one. During this process, it requires exclusive access to certain system catalogs and, in some scenarios, can be blocked by long-running transactions or active connections in the *source* PostgreSQL cluster. On RHEL 9, systemd services managing PostgreSQL, coupled with the default configurations, can sometimes contribute to these lingering connections if not managed carefully.

Pre-Upgrade Checklist: Minimizing Lock Risks

Before even initiating the `pg_upgrade` process, a rigorous checklist can significantly reduce the likelihood of encountering lock issues. This involves ensuring the Discourse application is in a quiescent state and that no unexpected processes are holding connections.

• Application Downtime: Ensure all Discourse web and background worker processes are stopped. This is the most critical step to prevent new connections.
• Database Backups: Perform a full, verified backup of the PostgreSQL database immediately before starting the upgrade.
• Disk Space: Verify sufficient free disk space for the new PostgreSQL data directory. `pg_upgrade` can be space-intensive.
• PostgreSQL Configuration: Review `postgresql.conf` and `pg_hba.conf` for any non-standard settings that might influence connection handling or performance.
• System Resources: Monitor CPU, memory, and I/O on the database server. Resource exhaustion can exacerbate lock issues.

Identifying the Blocking Process

When `pg_upgrade` hangs or reports lock errors, the immediate next step is to identify which process is holding the lock. This requires direct access to the *source* PostgreSQL cluster.

Querying for Active Locks

Connect to the source PostgreSQL instance using `psql` and execute the following query to list all active backend processes and their current activity. Pay close attention to queries that are in a `state` of ‘active’ and have a `wait_event_type` other than ‘Lock’ or ‘Client’ (which might indicate `pg_upgrade` itself waiting).

SELECT
    pid,
    datname,
    usename,
    client_addr,
    client_port,
    backend_start,
    query_start,
    state,
    wait_event_type,
    wait_event,
    query
FROM
    pg_stat_activity
WHERE
    state = 'active' AND pid NOT IN (SELECT pg_backend_pid());

If `pg_upgrade` is running, its PID will likely appear in `pg_stat_activity` with a `wait_event_type` of ‘Lock’ and `wait_event` of ‘relation’. The goal is to find *other* PIDs that are active and not related to the upgrade process itself.

Analyzing `pg_locks`

To understand what specific resources are locked, query the `pg_locks` catalog. This view shows all currently active lock requests and granted locks.

SELECT
    locktype,
    relation::regclass,
    mode,
    granted,
    pid
FROM
    pg_locks
WHERE
    NOT granted;

This query will show locks that are being requested but not yet granted. If `pg_upgrade` is blocked, you’ll likely see entries where `granted` is false, and the `pid` column will correspond to the `pg_upgrade` process. The `relation` column will indicate which table or object is causing the block.

Common Causes and Resolution Strategies

Stale Application Connections

The most frequent culprit is a connection that was not properly terminated before `pg_upgrade` was initiated. This can happen if the Discourse application services (web, sidekiq) were not fully stopped, or if a connection was dropped uncleanly.

Diagnosis

Use the `pg_stat_activity` query from above. Look for PIDs that are in an ‘idle’ or ‘idle in transaction’ state but have been running for an unusually long time, or PIDs that are not associated with any known Discourse process.

Resolution

Once a blocking PID is identified, and you’ve confirmed it’s not the `pg_upgrade` process itself, you can terminate it. Caution: This should only be done if you are certain the process is stale and not actively processing critical data. Terminating an active transaction can lead to data inconsistency.

SELECT pg_terminate_backend(PID_TO_TERMINATE);

Replace `PID_TO_TERMINATE` with the actual process ID found. After terminating, re-run the `pg_upgrade` command. If the issue persists, it might indicate a deeper problem or multiple blocking processes.

Long-Running Transactions (Idle in Transaction)

Sometimes, a transaction might be open but not actively executing a query. These ‘idle in transaction’ states can hold locks on tables, preventing `pg_upgrade` from proceeding.

Diagnosis

The `pg_stat_activity` query will show these with `state = ‘idle in transaction’`. The `query_start` or `backend_start` timestamps will indicate how long the transaction has been open.

Resolution

Similar to stale connections, these can be terminated using `pg_terminate_backend()`. However, it’s crucial to understand *why* a transaction is left open. This often points to an application bug where a transaction is not explicitly committed or rolled back. If you encounter this repeatedly, investigate the Discourse application code or its interaction with the database.

`pg_upgrade` Itself Waiting on Locks

It’s possible that `pg_upgrade` is correctly identifying a legitimate lock held by another process (perhaps a background maintenance task that wasn’t stopped, or even a lingering connection from a previous failed attempt).

Diagnosis

In `pg_stat_activity`, the `pg_upgrade` process will show `state = ‘active’` and `wait_event_type = ‘Lock’`. The `wait_event` will often be `relation` or `transactionid`. The `query` column might show something like `VACUUM (INCREMENTAL, WRAP)` or similar maintenance operations.

Resolution

If `pg_upgrade` is waiting for a lock, you need to identify the process *holding* that lock. Use the `pg_locks` query to see which `pid` is holding the lock that `pg_upgrade` is waiting for. Then, investigate that holding PID. If it’s a legitimate, long-running operation that should have completed, you might need to wait. If it’s a stale process, terminate it as described previously.

RHEL 9 Specific Considerations

RHEL 9 uses `systemd` to manage PostgreSQL services. Understanding how `systemd` interacts with PostgreSQL can be crucial.

Service Management

Ensure you are using the correct `systemctl` commands to stop and start PostgreSQL. For PostgreSQL 15 (a common target for upgrades), the service name is typically `postgresql-15.service`. Always verify the exact service name on your system.

# Stop the source PostgreSQL service
sudo systemctl stop postgresql-<source_version>.service

# Verify it's stopped
sudo systemctl status postgresql-<source_version>.service

# After pg_upgrade completes, start the new PostgreSQL service
sudo systemctl start postgresql-<target_version>.service

If you encounter issues where PostgreSQL seems to restart unexpectedly or connections linger, check the `systemd` journal for relevant error messages:

sudo journalctl -u postgresql-<source_version>.service -f

Advanced Troubleshooting: `pg_upgrade` Logs and Verbosity

The `pg_upgrade` utility itself provides logs that can be invaluable. When running `pg_upgrade`, use the `-v` (verbose) flag and redirect output to a file.

# Example command structure
pg_upgrade \
    --old-datadir=/var/lib/pgsql/<source_version>/data \
    --new-datadir=/var/lib/pgsql/<target_version>/data \
    --old-bindir=/usr/pgsql-<source_version>/bin \
    --new-bindir=/usr/pgsql-<target_version>/bin \
    --verbose \
    > pg_upgrade_run.log 2>&1

Review `pg_upgrade_run.log` for specific error messages, especially those indicating which object or table `pg_upgrade` was trying to access when it encountered a lock. This can help pinpoint the exact resource causing the contention.

Preventative Measures for Future Upgrades

To minimize the risk of lock contention during future Discourse or PostgreSQL upgrades:

• Automate Downtime Procedures: Implement robust scripts that ensure all Discourse application components are stopped cleanly and in the correct order before `pg_upgrade` begins.
• Monitor `idle in transaction` States: Regularly monitor your PostgreSQL instance for long-running ‘idle in transaction’ states. Address the root cause in the application if found.
• Scheduled Maintenance: If possible, schedule major upgrades during periods of extremely low activity, and ensure any background maintenance tasks (like `VACUUM`) are completed beforehand or are configured to be non-blocking.
• Test Upgrades: Always perform a dry run of the upgrade process on a staging environment that mirrors production as closely as possible. This is the best way to uncover potential lock issues before impacting production.

By systematically diagnosing lock contention using PostgreSQL’s built-in tools and understanding the lifecycle of your application’s database connections, you can navigate major version upgrades with greater confidence and minimize costly downtime.