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.
- Read the exact error in the pg_dump output and the server log.
out of shared memoryplus the hint points to the lock table. A messagecould 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. - Check the current setting.
SHOW max_locks_per_transaction;andSHOW max_connections;. Note the documentation default of 64. - Count what the dump will lock. Count the tables (including partitions) in the schemas you are dumping. If the dump uses
-t,-T,-nor-N, count only what those patterns select. - Watch locks while a dump runs. In
pg_locks,locktypeidentifies the lock type,modethe lock mode,grantedis false for a waiter, andpidis the process. The pg_locks page says to usepg_blocking_pids()to find which processes a waiting process is blocked behind, because joiningpg_locksto itself is hard to get right. - 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 VIEWwithoutCONCURRENTLY, and many forms ofALTER TABLE. That page also says only an ACCESS EXCLUSIVE lock blocks a plainSELECT(one withoutFOR 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 ofSET 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_connectionsand memory together. - Recurrence signal: the same error some weeks later means the object count grew past your headroom.
Read next
Related articles you may want next.
- Backup & RecoveryA PostgreSQL Dump Restored with Missing Roles or Extensions: Check the Recovery SetInventory database content, cluster-wide objects and extension packages needed for a usable logical restore.Read article
- PerformancePostgreSQL Tables Keep Growing: Diagnose Autovacuum and Transaction AgeDistinguish reusable table space, retained row versions and transaction-ID risk before selecting maintenance.Read article
- Backup & Recoverypg_dump: aborting because of server version mismatch, and pg_restore 'unsupported version in file header'pg_dump server version mismatch or pg_restore unsupported version in file header? Use tools of the same or newer major version, and psql for text dumps.Read article
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 ofmax_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-timeoutis applied as aSET 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
This article was originally published on .