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)
- "1m" (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:

# Bad: Parallel dump (more prone to timeouts)
pg_dump -h localhost -U backup_user -d your_database -j 4 > backup.sql

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

Sequential dumps are slower but more reliable on systems with aggressive timeout settings. For very large databases, consider:
- Using pg_dump --format=custom which is faster than plain SQL
- 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
telnet localhost 5432

# Or using nc (netcat)
nc -zv localhost 5432

# If dumping from remote machine, verify hostname/port
ping <postgres_host>
telnet <postgres_host> 5432

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

To help network devices stay alive, configure TCP keep-alive:

pg_dump -h localhost -U backup_user -d your_database -v --no-reconnect > backup.sql

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 (requires PostgreSQL restart)
ALTER SYSTEM SET max_connections = 500;
-- Then restart PostgreSQL

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

For memory issues, check if PostgreSQL is swapping:

ps aux | grep postgres
free -h
swapoff -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 table schema for TABLE_NAME"
- "dumping contents of table TABLE_NAME"
- Exact point where 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 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. If network issues occur, try the custom binary format which is more resilient:

# Plain text format (default, most prone to connection issues)
pg_dump -h localhost -U backup_user -d your_database -F p > backup.sql

# Custom format (binary, more robust, smaller file size)
pg_dump -h localhost -U backup_user -d your_database -F c > backup.custom

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

Custom format benefits:
- More efficient binary encoding reduces data transfer
- Supports compression with -Z flag
- More resistant to network timeouts
- Restores individual objects selectively

Restore custom format dumps:

pg_restore -d target_database backup.custom