Logical backups hang waiting for locks when VACUUM FULL is running on large PostgreSQL tables

Environment

PostgreSQL 13 on Debian 11. Production primary database hosts several large tables (>200GB) with occasional manual VACUUM FULL and regular logical backups using pg_dumpall run by a nightly maintenance script. autovacuum is tuned conservatively.

Symptoms

Nightly logical backups using pg_dumpall start but then hang indefinitely with the backup process shown in pg_stat_activity as 'waiting for lock'. VACUUM FULL has been scheduled by DBAs earlier in the maintenance window and appears to be running concurrently with the logical dump, causing the dump to block.

Observations

pg_stat_activity shows the dump process in a transaction waiting for a relation lock held by the VACUUM FULL backend. Server logs indicate heavy lock contention. Attempts to cancel the backup leave long-running transactions; killing VACUUM FULL releases the lock and allows the dump to proceed but causes long blocking time for other queries. The VACUUM FULL is required to reclaim large amounts of bloat but runs for many hours and requires exclusive access to tables.

Already attempted

Moved backups to later window after VACUUM FULL in a test run but operational needs require backups at night. Tested pg_basebackup as a physical backup alternative which completed without blocking, but restore requirements and offsite snapshot workflow are tied to logical dumps. Tried running VACUUM (not FULL), which did not free as much space and left bloat unresolved.

Question

How can I perform logical backups without being blocked by long VACUUM FULL operations, or how can I restructure maintenance to avoid long exclusive locks while still reclaiming bloat? Which changes are safe to try in production with low risk to data integrity?

Post timeline

COMMUNITY RESPONSES

Verified community troubleshooting

0 replies

No answers yet..

Showing 0 of 0 community responses

AI DIAGNOSTICS

Incident analysis

Updated 1w ago• Trouble ID

Most likely causes

VACUUM FULL demands exclusive locks, conflicting with long-lived logical backup transactions. 98%

Logical backup method chosen (pg_dumpall) requires a consistent snapshot that can't be created or progressed while exclusive locks are held. 92%

Large bloat forcing use of VACUUM FULL instead of online maintenance tools causing long maintenance windows and blocking. 88%

Summary

VACUUM FULL takes an exclusive lock on each table, which prevents other transactions (including pg_dump which needs consistent snapshots and may access the same tables) from proceeding. Logical backups like pg_dump require long-lived snapshots which conflict with VACUUM FULL. Using pg_basebackup (physical backup) avoids these logical lock conflicts because it operates at a different level, but many organizations depend on logical dumps for portability or text-based schema backups. The underlying issue is performing exclusive operations concurrently with logical snapshot backups.

Suggested Fixes

Diagnostic step 1

Prefer physical backups for nightly backups if restore requirements permit: use pg_basebackup or filesystem-level snapshotting of a consistent data directory while WAL archiving is enabled. Test restores on a non-production host before switching.

Diagnostic step 2

Avoid VACUUM FULL on production during backup windows. Use alternatives such as VACUUM (non-FULL) combined with REINDEX CONCURRENTLY and partitioning large tables to reduce bloat without exclusive locks.

Diagnostic step 3

Consider pg_repack to reclaim bloat online without requiring exclusive locks; test on staging first as it requires appropriate extensions and disk capacity for copies.