Australian cloud for business software — built and run in Sydney since 2002

NetLeverage
NetLeverage

pg_dump fails with 'out of shared memory: you might need to increase max_locks_per_transaction'

Technical article
Backup & Recovery

About this article: This article is reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.

pg_dump takes a lock on every table it dumps, and PostgreSQL keeps those locks until the dump's transaction ends. On a database with many tables or partitions, the dump alone can use up the server's shared lock table, and the server answers with out of shared memory and a hint naming max_locks_per_transaction. The fix is to size that setting for the number of objects, which requires a server restart. A different failure, where the dump waits or aborts because someone holds an exclusive lock, looks similar in a backup log but has a different cause and fix.

What the error means

PostgreSQL has a fixed-size shared memory table for locks. The documentation says it has space for max_locks_per_transaction objects per server process or prepared transaction, so no more than that many distinct objects can be locked at one time. The setting is an average: an individual transaction may lock more objects as long as all transactions' locks fit. The default is 64, and the page says you may need to raise it for queries that touch many tables in one transaction, "e.g., query of a parent table with many children". It can only be set at server start.

In the REL_18_STABLE source, lock.c raises the error with the out-of-memory error code, the message out of shared memory and the hint You might need to increase "max_locks_per_transaction". It computes the lock table size as max_locks_per_xact multiplied by (MaxBackends plus max_prepared_xacts). The number of backend slots is not exactly max_connections (the explicit locking page refers to max_locks_per_transaction and max_connections), so treat the product as an estimate and leave headroom.

Why pg_dump triggers it: it starts a transaction (the source uses BEGIN and sets REPEATABLE READ, READ ONLY, commented as starting a transaction-snapshot-mode transaction to dump consistent data) and, in getTables, issues LOCK TABLE ... IN ACCESS SHARE MODE statements for the target tables before anything else, with the comment that locking first is to minimise the window in which a table could disappear (be dropped) during the dump. The documentation says a lock is normally held until the end of the transaction. The locks therefore pile up for the whole dump. The tables are locked in batches, but they all stay locked.

Parallel dumps do not reduce this. The pg_dump page says the leader requests shared locks on the objects the workers will dump later, and that each worker then requests another shared lock with NOWAIT.

Likely causes

Cause What you see How to confirm
Very many tables or partitions in the dump out of shared memory with the max_locks_per_transaction hint, early in the dump Count the objects dumped; SHOW max_locks_per_transaction;
Default max_locks_per_transaction (64) never sized for the schema Worked until a partitioning scheme or new tenants added tables Compare the object count with the estimated lock table size
Other sessions already holding many locks Failure only when the dump runs alongside batch jobs SELECT pid, count(*) FROM pg_locks WHERE locktype = 'relation' GROUP BY pid ORDER BY 2 DESC; during the run
Lock wait on an ACCESS EXCLUSIVE lock Dump hangs at the start, or fails with could not obtain lock on relation in a parallel dump Look for waiting rows in pg_locks (granted false)
Standby with a lower setting than the primary Queries refused on the standby Docs require the standby's value to be the same or higher

Prove the cause before changing anything

These are read-only checks taken from the documentation.

  1. Read the exact error in the pg_dump output and the server log. out of shared memory plus the hint points to the lock table. A message could not obtain lock on relation "..." (from the parallel-dump code) points to lock conflict, and its hint says "This usually means" that someone requested an ACCESS EXCLUSIVE lock on the table after the parent process had taken its ACCESS SHARE lock.
  2. Check the current setting. SHOW max_locks_per_transaction; and SHOW max_connections;. Note the documentation default of 64.
  3. Count what the dump will lock. Count the tables (including partitions) in the schemas you are dumping. If the dump uses -t, -T, -n or -N, count only what those patterns select.
  4. Watch locks while a dump runs. In pg_locks, locktype identifies the lock type, mode the lock mode, granted is false for a waiter, and pid is the process. The pg_locks page says to use pg_blocking_pids() to find which processes a waiting process is blocked behind, because joining pg_locks to itself is hard to get right.
  5. Find the exclusive-lock holder, if any. Statements that take ACCESS EXCLUSIVE, per the locking documentation, include DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL, REFRESH MATERIALIZED VIEW without CONCURRENTLY, and many forms of ALTER TABLE. That page also says only an ACCESS EXCLUSIVE lock blocks a plain SELECT (one without FOR UPDATE/SHARE), and ACCESS SHARE conflicts only with ACCESS EXCLUSIVE.

Example (illustrative): on PG03, max_connections is 100, max_prepared_transactions is 0 and max_locks_per_transaction is 64, giving an estimated lock table of 64 x 100 = 6,400 object locks (using max_connections as an approximation of the backend count). A schema partitioned by day has 20,000 partitions. The nightly pg_dump wants about 20,000 table locks and fails with out of shared memory.

Fix

Cause 1: the lock table is too small

Raise max_locks_per_transaction in postgresql.conf. This needs a restart (the documentation says it can only be set at server start), which interrupts every connection. Plan a maintenance window.

Sizing from the example: the dump needs about 20,000 locks and the table has max_connections (100) slots multiplied by the setting, so the setting must be at least 20,000 / 100 = 200 for the dump alone. Other sessions need room as well, so a value such as 256 gives 25,600 in the estimate. That is an illustration of the arithmetic, not a recommendation for your server. Memory use rises with the setting, and the documentation does not give a figure; check shared memory use after the change.

If the server has standbys, the documentation requires each standby's value to be the same or higher than the primary's, or queries are not allowed on the standby. Which to change first is a judgement the documentation does not make: raising the standbys before the primary keeps the rule satisfied throughout.

Cause 2: you cannot restart yet

Split the dump with -n, -N, -t or -T into several runs, so each takes fewer locks. Trade-off: pg_dump gives a consistent export per run, so separate runs are consistent with themselves but not with each other. Foreign keys and cross-schema data may not line up. The documentation also warns that -t and -n dumps do not include objects the selection depends on, so a partial dump may not restore alone. Treat this as a stopgap.

Parallel dumping (-j) does not reduce the lock count and adds connections: pg_dump opens njobs + 1 connections, so max_connections must allow for them.

Cause 3: lock conflict (wait or abort)

  • Move the dump away from maintenance windows, rebuilds and anything that takes ACCESS EXCLUSIVE locks.
  • Use --lock-wait-timeout=timeout, which the documentation describes as failing if pg_dump cannot lock a table within the timeout, instead of waiting forever. It accepts the formats of SET statement_timeout. It makes a stuck dump fail visibly; it does not remove the blocker.
  • Identify the blocking session with pg_blocking_pids() and ask its owner. Ending someone else's session is a decision for the application owner; do not do it for the sake of a dump.

Confirm the fix

  • SHOW max_locks_per_transaction; shows the new value after the restart.
  • The full pg_dump completes with no error and exits successfully, and the output size is in line with earlier dumps.
  • The real proof is a restore of the dump into a scratch database, followed by a table count and row-count comparison on key tables.
  • If standbys exist, confirm they are accepting queries.

What to watch afterwards

  • Track the number of tables and partitions. A partitioning change or a new tenant model can make this error return.
  • Schedule the dump away from batch windows, and alert on dump failures, not only on missing files.
  • After raising the setting, review max_connections and memory together.
  • Recurrence signal: the same error some weeks later means the object count grew past your headroom.

Risks and limits

  • Commands and settings are taken from the PostgreSQL 18 documentation and source and have not been run or lab-tested.
  • The lock table size in the source uses MaxBackends, which the documentation's page expresses in terms of max_connections. The estimate here is approximate.
  • The error and hint wording are from the 18 source. Earlier releases may differ.
  • In the 18 source, --lock-wait-timeout is applied as a SET statement_timeout, so a table that cannot be locked in time ends in a statement-timeout error rather than a dedicated message.
  • Restarting PostgreSQL drops all connections.
  • Splitting a dump trades consistency across runs for lower lock use.
  • This article does not cover SSI predicate locks, which have their own setting, max_pred_locks_per_transaction.

Sources

  • 19.12 Lock Management — PostgreSQL Global Development Group, PostgreSQL 18.7 documentation, date not shown, accessed 16 November 2026
  • 13.3 Explicit Locking — PostgreSQL Global Development Group, PostgreSQL 18.7 documentation, date not shown, accessed 16 November 2026
  • pg_dump — PostgreSQL Global Development Group, PostgreSQL 18.7 documentation, date not shown, accessed 16 November 2026
  • 53.13 pg_locks — PostgreSQL Global Development Group, PostgreSQL 18.7 documentation, date not shown, accessed 16 November 2026
  • lock.c, REL_18_STABLE — PostgreSQL source repository, source file, accessed 16 November 2026
  • pg_dump.c, REL_18_STABLE — PostgreSQL source repository, source file (copyright to 2025), accessed 16 November 2026
  • parallel.c, REL_18_STABLE — PostgreSQL source repository, source file, accessed 16 November 2026