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

Connect to the database and identify what is holding locks:

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:

SELECT database, relation::regclass, mode, granted
FROM pg_locks
WHERE NOT granted
ORDER BY relation;

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

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:

pg_dump --lock-wait-timeout=10000 dbname > backup.sql

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

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:

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

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:

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

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

-- 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.

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:

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.