Fix

pg_dump hangs, and the application hangs with it

The backup job has been sitting there for an hour with no output. Then the alerts start: the API is timing out on queries that normally take a millisecond. The dump is not slow — it is stuck behind a lock, and everything else is stuck behind the dump.

What it looks like

There is no error. pg_dump simply produces nothing and does not exit. If you left it in the foreground, the file it writes stops growing, or never starts. Meanwhile, plain SELECTs from the application begin queueing on one specific table, and a deploy that was running a migration is also waiting.

All three are the same event, seen from three places.

Why a read-only dump can block anything

pg_dump is read-only, but it is not lock-free. Before reading anything, it opens one transaction and takes an ACCESS SHARE lock on every table it is about to dump, and holds all of them until the dump finishes. That lock is the weakest one Postgres has: it conflicts with exactly one mode, ACCESS EXCLUSIVE.

ACCESS EXCLUSIVE is what these take:

• most forms of ALTER TABLE — which means most schema migrations
• DROP TABLE, TRUNCATE, CLUSTER
• VACUUM FULL and a non-concurrent REINDEX
• a bare LOCK TABLE, whose default mode is ACCESS EXCLUSIVE

So far this is just two statements taking turns. The outage comes from the third ingredient: Postgres queues lock requests, and the queue is ordered. A request that cannot be granted immediately waits, and every later request that conflicts with the waiting one waits behind it — even if it would not have conflicted with the lock currently held.

That produces the pile-up:

1. The dump starts and holds ACCESS SHARE on orders. Reads and writes carry on normally; nothing conflicts.
2. A migration issues ALTER TABLE orders. It needs ACCESS EXCLUSIVE, which conflicts, so it waits.
3. Every SELECT on orders that arrives from now on needs ACCESS SHARE, which conflicts with the queued ALTER TABLE. They all wait.

The table is now effectively offline for the remaining duration of the dump, and the culprit in your monitoring is a migration that looks instant in staging. The dump running the other way round — pg_dump arriving second, behind an ALTER TABLE that is itself stuck behind a long-running report — hangs for the same reason, and by default it will wait forever.

Diagnose it: who is blocking whom

One query answers it. pg_blocking_pids() returns the sessions a given backend is waiting on:

SELECT pid,
       pg_blocking_pids(pid) AS blocked_by,
       state,
       wait_event_type,
       now() - query_start AS running_for,
       left(query, 60) AS query
  FROM pg_stat_activity
 WHERE backend_type = 'client backend'
   AND state <> 'idle'
 ORDER BY running_for DESC;

Read it from the bottom: the session that has been running longest and has an empty blocked_by is the root cause. Everything with wait_event_type = 'Lock' is a victim, including, usually, the migration in the middle.

To unblock immediately, cancel the head of the queue — the ALTER TABLE, not the dump. Cancelling the dump alone leaves the migration to grab its exclusive lock and keep the table shut:

# ask politely: cancels the current statement
SELECT pg_cancel_backend(12345);

# if that does not take: kills the connection
SELECT pg_terminate_backend(12345);

Fix 1 — never let a dump wait forever

pg_dump has an option for precisely this, and it is off by default:

pg_dump --lock-wait-timeout=10s -Fc -d "$SOURCE_URL" -f appdb.dump

If it cannot acquire its table locks within the timeout, it fails instead of waiting. That turns a silent, open-ended outage into a failed backup job with a message — which you can retry an hour later. A backup that did not run and told you is strictly better than one that ran and took the site with it.

Fix 2 — put a lock_timeout on your migrations

This is the fix that addresses the actual cause, and it belongs in the deploy path rather than the backup:

SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN shipped_at timestamptz;

Now the ALTER TABLE gives up after three seconds instead of sitting in the queue and gathering every subsequent read behind it. The migration fails, you rerun it, and no application query ever waits. Most migration tools can set this per session or per transaction; it is worth finding where.

Note that statement_timeout does not substitute for it: a statement blocked on a lock is not yet running, and you generally want a much shorter patience for acquiring a lock than for executing a query.

Fix 3 — separate the windows, then the machines

Backups and migrations competing for the same tables is a scheduling problem before it is a Postgres problem. Keep them out of the same hour. If that is not enough because the dump takes longer than the gap, move the dump to a read replica — the locks then live on the standby and cannot reach production.

That move has its own failure mode, and it is better to know it in advance than at 3 a.m. A long query on a standby can be cancelled by replay of conflicting changes from the primary:

ERROR:  canceling statement due to conflict with recovery
DETAIL:  User query might have needed to see row versions that must be removed.

The two settings that govern it are max_standby_streaming_delay on the standby (how long replay will be held up for a query) and hot_standby_feedback (which asks the primary not to vacuum away rows the standby still needs, at the cost of bloat on the primary). A dump on a replica with neither tuned will fail on large tables, intermittently.

The other cost of a long dump: bloat

Even when nothing blocks, a dump that runs for hours is not free. pg_dump holds a single repeatable-read transaction for its whole duration so the output is consistent, and that transaction pins a snapshot. VACUUM cannot remove dead row versions newer than the oldest snapshot in use, so on a heavily updated table the dead tuples accumulate for as long as the dump runs, and the table stays bloated afterwards.

You can watch the horizon move — or fail to:

SELECT pid, backend_type, state,
       age(backend_xmin) AS xmin_age,
       now() - xact_start AS xact_duration
  FROM pg_stat_activity
 WHERE backend_xmin IS NOT NULL
 ORDER BY xmin_age DESC;

The dump will be at the top of that list, by design. The point is not to eliminate it but to know its real duration, because that duration is also the length of your bloat window. idle_in_transaction_session_timeout will not save you here, incidentally: the dump's transaction is busy, not idle.

If a full logical dump no longer fits in the window your workload can absorb, that is the signal to add a physical method rather than to optimise the dump. pg_basebackup and WAL archiving copy files rather than query tables, so they take no table-level locks and pin no snapshot on the primary.

One more, if you dump in parallel

Parallel dump (-j, directory format only) opens one connection per worker plus the leader's, and each worker needs its own lock on the tables it handles. The leader takes the locks up front so a conflicting request cannot slip into the queue between workers, but two consequences remain: the dump consumes several connection slots, and --lock-wait-timeout matters more, not less, because there are more lock requests that can end up waiting. Size -j against max_connections and against what the source can actually sustain.

The part worth keeping

"Read-only" and "harmless" are not the same thing. A dump takes weak locks, but it takes them on every table and holds them for its whole run, so it is a long-lived participant in a queue that anything exclusive can jam. Two flags cover most of it — --lock-wait-timeout on the dump, lock_timeout on the migrations — and both fail the job rather than the site.

Related fixes: pg_dump: permission denied for table pg_dump vs pg_basebackup vs PITR pg_dump formats: custom, plain, directory or tar? pg_dump fails through the Transaction Pooler How to actually test a Postgres backup