How to fix pg_dump: error: connection to server was lost in PostgreSQL

PostgreSQLINTERMEDIATEHIGH

The pg_dump utility lost its database connection mid-backup, usually due to network issues, server restarts, or timeout settings. Check server status, verify network connectivity, and adjust timeout parameters.

What this error means

This error occurs when pg_dump establishes a connection to the PostgreSQL server but the connection is dropped during the backup operation. Unlike connection failures at startup, this error indicates the server was initially reachable but became unavailable or unresponsive while transferring data. This typically signals transient network problems, the database server terminating abnormally, or timeout limits being exceeded during large data transfers.

How to fix "pg_dump: error: connection to server was lost"

1Verify PostgreSQL server is running and stable

Connect to the database using psql to ensure the server is responsive and not restarting:

bash

psql -h localhost -U postgres -d postgres -c "SELECT version();";

If this fails, start PostgreSQL:

bash

sudo systemctl start postgresql

Check the PostgreSQL logs for errors:

bash

sudo tail -f /var/log/postgresql/postgresql.log

2Test basic connectivity from the pg_dump client

Run a simple connection test before attempting the full dump:

bash

pg_isready -h localhost -p 5432 -U postgres

For remote servers, verify you can reach the host:

bash

ping <server_host>
telnet <server_host> 5432

3Increase or disable statement_timeout

A too-aggressive statement_timeout can kill pg_dump mid-operation. Increase it for the dump:

bash

pg_dump -h localhost -U postgres -d mydb --statement-timeout=0 > backup.sql

Or set it on the server (PostgreSQL config):

bash

statement_timeout = 300000  # 5 minutes in milliseconds

Restart PostgreSQL after changing the config:

bash

sudo systemctl restart postgresql

4Add lock_timeout to prevent acquisition delays

If pg_dump hangs waiting for table locks, set a lock timeout:

bash

pg_dump -h localhost -U postgres -d mydb --lock-wait-timeout=30s > backup.sql

5Reduce parallel jobs or increase max_connections

Parallel dumps open multiple connections. If you're using -j (jobs), reduce it or increase the server limit:

Check current max_connections:

bash

psql -h localhost -U postgres -d postgres -c "SHOW max_connections;"

Reduce parallel jobs in pg_dump:

bash

pg_dump -h localhost -U postgres -d mydb -j 2 > backup.sql

Or increase max_connections in postgresql.conf:

bash

max_connections = 300

Restart PostgreSQL:

bash

sudo systemctl restart postgresql

6Use a .pgpass file for reliable authentication

Avoid password prompts that could timeout:

bash

cat > ~/.pgpass <<EOF
localhost:5432:mydb:postgres:mypassword
EOF

chmod 600 ~/.pgpass

Then run pg_dump without password arguments:

bash

pg_dump -h localhost -U postgres -d mydb > backup.sql

7Try dumping smaller tables first

If a specific large table is causing timeouts, dump selectively:

bash

# Dump schema only
pg_dump -h localhost -U postgres -d mydb -s > schema.sql

# Dump specific large table with verbose output
pg_dump -h localhost -U postgres -d mydb -t large_table -v > large_table.sql

# Dump all others
pg_dump -h localhost -U postgres -d mydb --exclude-table=large_table > data.sql

8Match pg_dump version with server version

Version mismatches can cause unexpected disconnections. Verify versions match:

bash

# Check pg_dump version
pg_dump --version

# Check server version
psql -h localhost -U postgres -d postgres -c "SELECT version();"

If they differ significantly (e.g., pg_dump from PostgreSQL 12 vs server 16), upgrade pg_dump to match the server.

How to fix pg_dump: error: connection to server was lost in PostgreSQL

What this error means

Typical symptoms

Common causes

How to fix "pg_dump: error: connection to server was lost"

Advanced notes

Related errors

Official resources & further reading