How to fix pg_dump: could not obtain lock on table in PostgreSQL

PostgreSQLINTERMEDIATEHIGHUpdated Dec 29, 2025· 296 views

pg_dump fails with a lock timeout when it cannot acquire AccessShareLock on tables because other transactions hold conflicting locks. This typically happens when active DDL operations or long-running transactions block the backup process.

Quick fix

Fixes pg_dump: could not obtain lock on table

SELECT pid, usename, query, state, query_start, wait_event_type
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;
  1. Check for active transactions and locks
  2. Set lock-wait-timeout for pg_dump
  3. Terminate blocking transactions
  4. Schedule dumps during low-activity windows
postgresql — error
SELECT pid, usename, query, state, query_start, wait_event_type
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;
pg_dump: could not obtain lock on table

What this error means

pg_dump acquires an AccessShareLock on each table during the backup startup phase to ensure no one drops tables while the dump is in progress. If another transaction holds an exclusive lock (from operations like ALTER TABLE, DROP TABLE, TRUNCATE, or REINDEX), or if there are uncommitted transactions, pg_dump will block waiting for the lock. If the wait exceeds the lock timeout (default is indefinite), pg_dump fails with this error. The error can also occur in managed services like AWS RDS when the dump user lacks proper permissions on tables owned by other users.

Typical symptoms

  • pg_dump hangs or fails immediately with 'could not obtain lock on table' error
  • Backup process never completes or times out
  • Error occurs even though the specific table is not actively being used
  • Long-running queries or transactions are blocking the dump
  • DDL operations (ALTER TABLE, CREATE INDEX) are in progress on the database
  • On AWS RDS, error includes 'permission denied' variants

Common causes

  1. Long-running transactions or queries holding locks on tables
  2. Concurrent DDL operations (ALTER TABLE, DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL)
  3. Exclusive locks from other backup or maintenance processes
  4. High database activity preventing lock acquisition
  5. Insufficient max_locks_per_transaction setting when dumping many tables
  6. On AWS RDS: dump user lacks SELECT permissions on tables owned by other users

How to fix "pg_dump: could not obtain lock on table"

1Check for active transactions and locks

Connect to the database and identify what is holding locks:

sql
SELECT pid, usename, query, state, query_start, wait_event_type
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;

This shows all active queries. Look for long-running transactions (old query_start times) or DDL operations that might be blocking the dump.

To see explicit locks:

sql
SELECT database, relation::regclass, mode, granted
FROM pg_locks
WHERE NOT granted
ORDER BY relation;
2Set lock-wait-timeout for pg_dump

Use the --lock-wait-timeout option to prevent indefinite waiting:

bash
pg_dump --lock-wait-timeout=60000 dbname > backup.sql

The timeout is specified in milliseconds. The example above waits up to 60 seconds before failing. This prevents pg_dump from blocking production traffic for hours while waiting for locks.

Alternatively, use a shorter timeout to fail faster:

bash
pg_dump --lock-wait-timeout=10000 dbname > backup.sql
3Terminate blocking transactions

If you identify a blocking process, you can safely terminate it (use caution in production):

sql
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid = 12345;  -- Replace with the PID from step 1

Verify the query is actually finished before retrying pg_dump:

bash
pg_dump --lock-wait-timeout=60000 dbname > backup.sql
4Schedule dumps during low-activity windows

Run pg_dump when minimal database activity is occurring. Check your application logs to identify quiet periods (early mornings, weekends, etc.).

For recurring backups, add to cron:

bash
# Run backup daily at 2 AM
0 2 * * * /usr/bin/pg_dump --lock-wait-timeout=60000 dbname > /backups/db-$(date +\%Y\%m\%d).sql
5Increase max_locks_per_transaction if needed

If you're dumping a database with many tables and getting lock exhaustion errors, increase the setting:

sql
-- Show current value
SHOW max_locks_per_transaction;

-- Set in postgresql.conf
max_locks_per_transaction = 256

-- Or set per-session
SET max_locks_per_transaction = 256;

Note: Increasing this requires a server restart when set in postgresql.conf. The default is 64, but large databases may need 256 or more.

6Fix permission issues on AWS RDS

On AWS RDS, if the error includes 'permission denied', the dump user lacks access to tables owned by other users:

Have the table owner grant permissions:

sql
GRANT CONNECT ON DATABASE mydb TO rds_master_user;
GRANT USAGE ON SCHEMA public TO rds_master_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO rds_master_user;

-- Make it default for future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO rds_master_user;

Then retry pg_dump as the master user.

Advanced notes

Lock Modes: pg_dump uses AccessShareLock (not the default exclusive lock), which is compatible with SELECT, INSERT, UPDATE, DELETE, and most operations. It only conflicts with DDL operations (DROP TABLE, TRUNCATE, ALTER TABLE, etc.) that require AccessExclusiveLock.

Parallel Dumps: When using pg_dump -j N for parallel dumps, the leader process acquires shared locks on all tables before workers begin. If another process requests an exclusive lock during parallel dump, it creates a deadlock situation, and the dump may fail with a NOWAIT error.

RDS Specifics: On AWS RDS, the rds_superuser role is not a true superuser and cannot access tables owned by other users unless permissions are explicitly granted. This is fixed in PostgreSQL 10+.

Snapshot Isolation: For PostgreSQL 9.4+, you can use explicit snapshot isolation:

bash
pg_dump --snapshot='"00000003-FFFFFFFF:2"' dbname > backup.sql

Create the snapshot with another connection to ensure consistency.

Frequently asked questions

What does "pg_dump: could not obtain lock on table" mean?
pg_dump acquires an AccessShareLock on each table during the backup startup phase to ensure no one drops tables while the dump is in progress. If another transaction holds an exclusive lock (from operations like ALTER TABLE, DROP TABLE, TRUNCATE, or REINDEX), or if there are uncommitted transactions, pg_dump will block waiting for the lock. If the wait exceeds the lock timeout (default is indefinite), pg_dump fails with this error.
How do I fix "pg_dump: could not obtain lock on table"?
pg_dump fails with a lock timeout when it cannot acquire AccessShareLock on tables because other transactions hold conflicting locks. This typically happens when active DDL operations or long-running transactions block the backup process.
What causes "pg_dump: could not obtain lock on table"?
Long-running transactions or queries holding locks on tables Concurrent DDL operations (ALTER TABLE, DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL) Exclusive locks from other backup or maintenance processes
Is the "pg_dump: could not obtain lock on table" error serious?
Yes. This is a high-severity error that usually blocks work until it is fixed, but the steps above resolve it.

Official resources & further reading