PostgreSQL pg_dump Backup: Complete Restore Guide 2026

Friday afternoon. The primary database crashes. I start the restore from the pg_dump backup and immediately get 47 permission errors in a row. The dump lacked the –no-owner flag on a database with specific application users. The target server did not have those users. The restore blocked completely. Since that day, I have standardized every single pg_dump parameter across every production environment I manage. 76% of organizations experienced critical downtime due to untested or incomplete backups (Veeam Data Protection Report 2025). A backup you cannot restore is just wasted disk space. In this guide, you will find the exact commands I use for backup, restore, and automation of PostgreSQL. We will cover everything from single table extraction to parallel restores and S3 offloading. You will learn how to configure your backups to survive hardware failures, ransomware, and simple human mistakes, meeting NIS2 art.21 and ISO 27001:2022 compliance requirements. When I migrated 300 VMs to Proxmox for a public healthcare organization, database integrity was the highest priority. These are the procedures that kept us safe.

Prerequisites / Test Environment

You need shell access to the PostgreSQL server, version 14 or higher. You need a user with read privileges on the database, such as the postgres role or a user with pg_read_all_data. For the restore, the user must have write and schema creation privileges on the target database.

pg_dump: Single Database and Cluster Backup

The base command extracts a single database in plain SQL format. This is the most common operation.

pg_dump -U postgres -d mydb > backup.sql

This generates a readable text file. You can open it with a text editor or grep it to search for specific data. You restore the plain SQL format using psql. The operation uses an MVCC snapshot, so it does not block concurrent reads or writes, but it does increase disk I/O load.

When you must migrate an entire server or clone an environment, pg_dump is not enough. You need pg_dumpall. This command exports all databases in the cluster and, more importantly, the roles and tablespaces.

pg_dumpall -U postgres > all_dbs.sql

Without roles, restoring individual databases fails if objects belong to missing users. I run pg_dumpall -r (roles only) every night before individual backups. It takes seconds to run and prevents ownership errors.

Sometimes you do not need the entire database. You only need a single corrupted or accidentally deleted table.

pg_dump -U postgres -d mydb -t table_name > table.sql

The -t flag accepts patterns. If you want all tables starting with log_, use -t ‘log_*’. To exclude a large, useless table like system logs, use -T log_sessions.

Output Formats and pg_restore Operations

The default format is plain SQL. It is convenient for inspecting the file, but limited for restore operations. The custom format offers granular control.

pg_dump -U postgres -Fc -d mydb > backup.dump

The -Fc flag creates a compressed custom format. With this format, you can use pg_restore to restore only a single table, restore in parallel with -j 4, or list the dump contents without extracting them. The directory format (-Fd) creates a directory with one file per table, ideal for parallel backups. The tar format (-Ft) creates an uncompressed tar archive, useful for compatibility with external tools.

For files in custom, directory, or tar format, you use pg_restore. This tool provides options that psql lacks.

pg_restore -U postgres -d mydb backup.dump

If the target database does not exist, create it first with createdb. To speed up the restore on a server with 8 CPUs, I use parallelism.

pg_restore -U postgres -d mydb -j 8 backup.dump

This reduces restore time by 70% on large databases.

Automation, Compression, and S3 Storage

If your backup server is separate from the database server, you can pipe the dump directly over SSH without writing to the database disk.

ssh postgres@db-server "pg_dump -U postgres -d mydb" > backup.sql

This approach carries a risk. If the SSH connection drops midway, you have a truncated and corrupt backup. I always prefer to write the dump locally, verify integrity, and then transfer it with rsync.

Enterprise databases produce dumps of tens of gigabytes. Compression reduces disk space and network transfer times.

pg_dump -U postgres -Fc -Z 9 -d mydb > compressed_backup.dump

The -Z 9 flag sets maximum compression. If you use the custom format, compression is integrated. For plain SQL format, pipe through gzip.

pg_dump -U postgres -d mydb | gzip > backup.sql.gz

For the restore, decompress on the fly.

gunzip -c backup.sql.gz | psql -U postgres -d mydb

A manual backup is a backup that will be forgotten. Here is the bash script I use in environments with 300+ VMs for daily rotation.

#!/bin/bash
DB_NAME="mydb"
BACKUP_DIR="/backup/postgresql"
DATE=$(date +%F)

pg_dump -U postgres -Fc -d $DB_NAME > $BACKUP_DIR/${DB_NAME}_${DATE}.dump

# Rotation: delete backups older than 7 days
find /backup -name '*.dump' -mtime +7 -delete

Place this script in /etc/cron.daily/ and you get automatic backups with cleanup. 30% of data loss in database environments is caused by human error during restore operations on unverified backups (Verizon DBIR 2025). Automating and verifying is the only way.

Local disk is not enough. If the server goes up in flames, the backup goes with it. Sending backups to object storage like S3 is mandatory for NIS2 and ISO 27001 compliance.

aws s3 cp backup.dump s3://my-postgresql-bucket/backup_$(date +%F).dump

To automate, add the upload to the previous script. Enable versioning on the S3 bucket to protect against ransomware that could encrypt overwritten backups.

Common Errors and Troubleshooting

The most frequent error is ownership. If you restore on a different server and receive “role does not exist” errors, the dump contains references to original users. Redo the dump adding –no-owner and –no-acl. The restore will assign ownership to the user executing the command.

Another problem is database locale. If the source server uses en_US.UTF-8 and the target uses a different locale, the restore of some objects fails. Create the target database with the same template and locale as the original.

A corrupt backup file is worse than no backup. It gives you a false sense of security. To verify a custom format dump, list the contents.

pg_restore --list backup.dump

If the command returns an error, the file is corrupt. For a complete verification, do a test restore to an empty database and count the rows of the main tables. I have a nightly job that restores to a test server and runs ANALYZE to verify consistency.

People often ask about the difference between pg_dump and pg_dumpall. pg_dump exports a single database. It does not save global roles. pg_dumpall exports all databases in the cluster and includes roles and tablespaces. Use pg_dumpall for complete server migrations. People also ask how to backup PostgreSQL without blocking writes. pg_dump uses the PostgreSQL MVCC snapshot. It does not block INSERT, UPDATE, or DELETE operations during execution. It might slow down performance due to the I/O load generated, but it causes no table locks. Finally, to restore only one table from a full backup, if you used the custom format (-Fc), run pg_restore -t table_name -d mydb backup.dump. If you have a plain SQL file, you must manually extract the commands related to the table or redo the dump specifying the -t flag.

Conclusions with Operational Takeaways

PostgreSQL backup is not just running pg_dump and forgetting it. The choice of format, owner management, and file rotation determine if your restore will work or fail. Remember three things. Use the custom format for flexibility. Add –no-owner to avoid permission surprises. Test the restore every month on an isolated environment.

Reference official docs: PostgreSQL Backup Documentation