Appearance

FAQ ​

Common errors and fixes for PostgreSQL backup operations.

Most PostgreSQL backup failures fall into three categories: insufficient privileges on the backup user, connection or resource errors during the dump, and version mismatches when restoring. The entries below cover the most common cases.

Backup failing due to insufficient privileges ​

If your backup fails with a permission denied error, your backup user likely lacks the privileges required for pg_dump to read all objects in the database. Grant the necessary access:

sql
GRANT CONNECT ON DATABASE "your_database_name" TO backup_user;
GRANT SELECT ON ALL TABLES IN SCHEMA your_schema_name TO backup_user;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA your_schema_name TO backup_user;

Replace your_database_name, your_schema_name, and backup_user with your actual values. Re-run the backup after applying the grants.

SSL SYSCALL error: EOF detected ​

You may see one or more of the following errors during a backup:

  • pg_dump: error: Error message from server: SSL SYSCALL error: EOF detected
  • pg_dump: error: Dumping the contents of table "my_table" failed: PQgetCopyData() failed
  • pg_dump: detail: Error message from server: could not receive data from server: Connection timed out

These errors typically occur when the database server has very limited RAM or CPU available during the dump. The connection is dropped mid-transfer because the server cannot sustain the query. To resolve it, check your database server's resource usage at the time of the backup and consider scheduling the backup during off-peak hours or upgrading the server plan.

pg_restore: unsupported file header version error ​

The error pg_restore: error: unsupported version (1.xx) in file header occurs when your local pg_restore binary is older than the PostgreSQL version that created the dump. You need to upgrade pg_restore to match or exceed the server version.

On macOS (Homebrew):

bash
brew update
brew install libpq
# or if already installed
brew upgrade libpq

Then add libpq to your PATH in ~/.zshrc or ~/.bash_profile:

bash
export PATH="/opt/homebrew/opt/libpq/bin:$PATH"

Reload your terminal and verify:

bash
pg_restore --version

On Ubuntu:

bash
sudo apt update
sudo apt install -y libpq-dev