How to fix pg_dump: error: query failed: server closed the connection in PostgreSQL

Examine the PostgreSQL server logs to understand why the connection was closed. The server logs are typically located at:

# Linux/macOS
sudo tail -f /var/log/postgresql/postgresql.log

# Or query from psql (if accessible)
SELECT pg_current_logfile();
SELECT * FROM pg_read_file('/path/to/log/file');

Look for messages like:
- "terminating connection due to idle-in-transaction timeout"
- "canceling statement due to statement timeout"
- "could not send data to client: Connection reset by peer"
- "could not send data to client: Connection timed out"
- "terminating connection due to administrator command"

These messages indicate which limit was exceeded.

If server logs show 'idle-in-transaction timeout', the idle_in_transaction_session_timeout parameter is terminating idle backup sessions. This commonly happens with parallel pg_dump (-j flag) when worker sessions wait idle while another worker is processing a large table.

Disable the timeout for the backup user:

-- Option 1: Disable for specific user (most secure)
ALTER USER backup_user SET idle_in_transaction_session_timeout = '0';

-- Option 2: Disable at database level
ALTER DATABASE your_database SET idle_in_transaction_session_timeout = '0';

-- Verify the setting
SHOW idle_in_transaction_session_timeout;

Or set it temporarily via environment variable when running pg_dump:

PGOPTIONS="-c idle_in_transaction_session_timeout=0" pg_dump -h localhost -U backup_user -d your_database > backup.sql

If the server logs show 'canceling statement due to statement timeout', adjust the statement_timeout parameter:

-- Option 1: Disable statement timeout for the backup user
ALTER USER backup_user SET statement_timeout = '0';

-- Option 2: Disable at database level
ALTER DATABASE your_database SET statement_timeout = '0';

-- Option 3: Increase to a large value (e.g., 1 hour)
ALTER USER backup_user SET statement_timeout = '3600000';

Or set it via environment variable:

PGOPTIONS="-c statement_timeout=0" pg_dump -h localhost -U backup_user -d your_database > backup.sql

Note: statement_timeout = '0' disables the timeout entirely, suitable for administrative tasks like backups.

The --lock-wait-timeout option tells pg_dump to fail fast if it cannot acquire table locks, rather than waiting indefinitely:

pg_dump -h localhost -U backup_user -d your_database --lock-wait-timeout=10s > backup.sql

This is helpful if pg_dump is being blocked by long-running transactions. Acceptable formats for the timeout are:
- "10s" (seconds)
- "1min" (minute)
- "2min" (minutes)

If pg_dump still fails due to locks, you may need to identify and terminate blocking queries:

SELECT pid, usename, query, state FROM pg_stat_activity WHERE state != 'idle';
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE query ILIKE '%some_long_query%';

Parallel pg_dump (using -j flag) opens multiple worker connections that may idle while waiting for table locks. If you're seeing timeout errors with parallel dumps, try sequential mode:

# Parallel dump (more prone to timeouts) - requires directory format
pg_dump -h localhost -U backup_user -d your_database -j 4 -F d -f ./backup_dir

# Sequential dump (less prone to timeouts)
pg_dump -h localhost -U backup_user -d your_database > backup.sql

Note: parallel dumps (-j) require the directory format (-F d) and cannot be written to a plain SQL file or stdout. Sequential dumps are slower but more reliable on systems with aggressive timeout settings. For very large databases, consider:
- Using pg_dump --format=custom which produces a compact, restorable archive
- Dumping individual schemas or tables instead of the entire database
- Scheduling dumps during low-activity periods to reduce lock contention

If server logs show 'could not send data to client: Connection reset by peer' or 'Connection timed out', the issue is network-related:

# Test TCP connectivity to PostgreSQL
nc -zv localhost 5432

# If dumping from a remote machine, verify hostname/port reachability
ping <postgres_host>
nc -zv <postgres_host> 5432

Network issues to investigate:
- Firewall drops idle connections after a timeout period (common in cloud environments)
- Network switch or gateway has aggressive connection timeout
- NAT router resets long-lived connections
- Corporate proxy drops idle TCP streams

To keep idle connections alive across firewalls/NAT, enable libpq TCP keepalives in the connection string (there is no --no-reconnect option in pg_dump). Pass keepalive parameters through -d as a connection URI or keyword string:

# Connection URI form
pg_dump -d "postgresql://backup_user@db_host:5432/your_database?keepalives=1&keepalives_idle=30&keepalives_interval=10&keepalives_count=5" -v > backup.sql

# Keyword/value form
pg_dump -d "host=db_host user=backup_user dbname=your_database keepalives=1 keepalives_idle=30 keepalives_interval=10 keepalives_count=5" -v > backup.sql

These settings make the OS send TCP keepalive probes every 30 seconds while idle, preventing stateful firewalls and NAT devices from silently dropping the connection during long backups.

Resource exhaustion (memory, file descriptors, connections) can force connection termination. Check server health:

# Check available memory and file descriptors
free -h
ulimit -n

# On Linux, check PostgreSQL process resource limits
cat /proc/$(pidof postgres)/limits

# Check current connection count
psql -c "SELECT count(*) as connections FROM pg_stat_activity;"
psql -c "SHOW max_connections;"

If nearing connection limits:

-- Increase max_connections (postmaster-level parameter: requires a full PostgreSQL RESTART, not just a reload)
ALTER SYSTEM SET max_connections = 500;
-- Then restart the PostgreSQL service for the change to take effect

-- Or use a connection pooler like PgBouncer to reduce backend connections

For memory issues, check if PostgreSQL is swapping. Use swapon to inspect swap usage (note: swapoff disables swap and is not a status command):

ps aux | grep postgres
free -h
swapon --show   # or: swapon -s

Use -v (verbose) or -vv (extra verbose) to see exactly which table is being dumped when the error occurs:

pg_dump -h localhost -U backup_user -d your_database -vv > backup.sql 2>dump.log
cat dump.log

Verbose output shows:
- "dumping contents of table TABLE_NAME"
- The exact point where the connection was lost

If a specific table consistently causes the error, that table may be:
- Very large (exceeds statement_timeout)
- Locked by another process
- Corrupted (uncommon but possible)

You can dump that problematic table separately or exclude it:

# Dump all except problematic_table
pg_dump -h localhost -U backup_user -d your_database -T problematic_table > backup.sql

# Dump only a specific table
pg_dump -h localhost -U backup_user -d your_database -t specific_table > table.sql

The plain SQL text format (-F p, default) streams output as raw SQL text. Try the custom archive format, which is more compact and supports compression and selective restore:

# Plain text format (default)
pg_dump -h localhost -U backup_user -d your_database -F p > backup.sql

# Custom format (compressed archive, supports selective restore)
pg_dump -h localhost -U backup_user -d your_database -F c > backup.custom

# Directory format (allows parallel dump and restore)
pg_dump -h localhost -U backup_user -d your_database -F d -f ./backup_dir

Custom format benefits:
- Compressed by default (control with -Z), reducing the data written
- Supports selective restore of individual objects
- Works with pg_restore parallelism (-j)

Restore custom format dumps:

pg_restore -d target_database backup.custom

Note: the format itself does not change PostgreSQL's timeout behavior; if timeouts are the root cause, combine this with the timeout fixes in the earlier steps.