To back up a PostgreSQL database, run pg_dump -F c -f mydb.dump mydb. To restore it, create an empty database and run pg_restore -d newdb mydb.dump. Then automate it, keep copies off the server, and test your restores.
A database holds the part of an application you can’t rebuild: customer records, orders, content, and history. Servers fail, people run the wrong DELETE, updates go wrong, and ransomware encrypts disks. A tested backup is what turns any of those into an inconvenience instead of a disaster.
This guide goes beyond the basics covered in PostgreSQL for Beginners. It explains the backup types and formats, how to restore a full database or a single table, how to back up PostgreSQL running in Docker, how to automate and verify backups with a script, and when you need physical backups instead.
Every command in this guide was tested on PostgreSQL 17, using the same bookstore database (with authors and books tables) from the beginner guide.
Logical vs. Physical Backups
PostgreSQL supports two kinds of backup:
| Logical backup | Physical backup | |
|---|---|---|
| Tools | pg_dump, pg_dumpall | pg_basebackup, WAL archiving, pgBackRest, Barman |
| What it copies | SQL definitions and data | The server’s data files, byte for byte |
| Scope | One database (or all, with pg_dumpall) | The entire server (cluster) |
| Restore to another version | Yes (same or newer) | No, same major version only |
| Restore a single table | Yes | No |
| Point-in-time recovery | No | Yes, with WAL archiving |
| Best for | Small to medium databases, migrations, most projects | Large databases, minimal data loss |
For most websites, apps, and small business databases, logical backups with pg_dump are the right starting point. They’re simple, portable, and easy to restore. The rest of this guide focuses on them, with a section on physical backups at the end.
Before You Start: Connecting Without Typing a Password
pg_dump, pg_restore, and psql accept the same connection options:
| Option | Meaning | Default |
|---|---|---|
-h | Host | Local socket |
-p | Port | 5432 |
-U | User | Your operating system username |
-d | Database | Same as the user name |
On Ubuntu, the easiest way to run these tools locally is as the postgres system user: sudo -u postgres pg_dump ....
For scripts that connect with a password, store it in a .pgpass file in the home folder of the user that runs the backup, instead of putting it in the script:
localhost:5432:bookstore:backup_user:your-password
The format is host:port:database:user:password. PostgreSQL ignores the file unless only its owner can read it, so run chmod 600 ~/.pgpass.
Backing Up One Database with pg_dump
pg_dump creates a consistent snapshot of one database, even while people are using it. It doesn’t lock out readers or writers.
Plain SQL Format
pg_dump -U postgres -d bookstore -f bookstore.sql
This creates a readable text file full of SQL commands (CREATE TABLE, COPY, and so on). You can open it in any editor, which makes it handy for small databases, code reviews, and moving data between systems.
Custom Format (Recommended)
pg_dump -U postgres -F c -f bookstore.dump bookstore
-F c creates a compressed archive that only pg_restore can read. It’s the best default because it lets you:
- restore everything or only selected tables,
- restore with several parallel jobs,
- list the contents before restoring,
- and use
--cleanto replace existing objects.
All Four Formats
| Format | Option | Output | Restore with | Parallel backup |
|---|---|---|---|---|
| Plain | -F p (default) | .sql text file | psql | No |
| Custom | -F c | Single compressed file | pg_restore | No |
| Directory | -F d | A folder of files | pg_restore | Yes (-j) |
| Tar | -F t | .tar file | pg_restore | No |
For large databases, the directory format can dump several tables at once:
pg_dump -U postgres -F d -j 4 -f bookstore_dir bookstore
-j 4 uses four parallel jobs. Each table’s data goes into its own compressed file inside bookstore_dir.
On PostgreSQL 16 and later, you can also choose the compression method, for example --compress=zstd which often gives smaller files and faster compression than the default.
What pg_dump Doesn’t Include
pg_dump saves one database, including its tables, data, indexes, and the permissions on those objects. It does not save roles (users) and their passwords or tablespaces, because those belong to the whole server. Back them up separately:
pg_dumpall -U postgres --globals-only -f roles.sql
To back up every database on the server plus the roles in a single plain SQL file, run pg_dumpall -U postgres -f all-databases.sql. It’s convenient for small servers, but it only produces plain SQL, so you lose the flexibility of the custom format.
Restoring a Database
Restore a Plain SQL Backup
Create an empty database, then run the file with psql:
createdb -U postgres bookstore_restore
psql -U postgres -X -v ON_ERROR_STOP=1 -d bookstore_restore -f bookstore.sql
ON_ERROR_STOP=1 makes psql stop at the first error instead of continuing and leaving you with a half-restored database. -X skips your personal psql settings so they can’t interfere.
Restore a Custom or Directory Backup
createdb -U postgres bookstore_restore
pg_restore -U postgres -d bookstore_restore bookstore.dump
For a directory backup, add parallel jobs to speed up the restore:
pg_restore -U postgres -j 4 -d bookstore_restore bookstore_dir
Useful pg_restore Options
| Option | What it does |
|---|---|
-C | Create the database with its original name before restoring (connect with -d postgres) |
--clean --if-exists | Drop existing objects before recreating them |
--no-owner | Don’t try to set the original owners (useful on a different server) |
--no-privileges | Skip GRANT and REVOKE commands |
-j N | Restore with N parallel jobs |
--list | Show the archive’s contents without restoring anything |
-t table | Restore only one table |
A few common situations:
Recreate the database with its original name, for example on a new server:
pg_restore -U postgres -C -d postgres bookstore.dump
If a database named bookstore already exists, this fails with database "bookstore" already exists. Drop it first or restore into a different name.
Overwrite an existing database with the backup’s contents:
pg_restore -U postgres --clean --if-exists -d bookstore bookstore.dump
This drops and recreates every object in the backup, so anything changed since the backup is lost. Make a fresh backup first if you’re not sure.
Move a database to another server or hosting provider, where the original users don’t exist:
pg_restore -U postgres --no-owner --no-privileges -d bookstore bookstore.dump
All objects will belong to the user running the restore. Grant permissions again afterward.
Restore a Single Table
Someone deleted the wrong rows from books, and you only want that table back. First look at what’s in the backup:
pg_restore --list bookstore.dump
220; 1259 16395 TABLE public books postgres
3434; 0 16395 TABLE DATA public books postgres
3284; 2606 16402 CONSTRAINT public books books_pkey postgres
3285; 2606 16403 FK CONSTRAINT public books books_author_id_fkey postgres
(Output shortened.) You can restore just that table into an empty database with -t books, but be aware that -t restores only the table definition and its rows, not its sequence, primary key, or foreign keys.
The safest approach is to restore the whole backup into a scratch database and copy back only what you need:
createdb -U postgres bookstore_scratch
pg_restore -U postgres -d bookstore_scratch bookstore.dump
Then compare the two databases, copy the missing rows back with SQL, and drop bookstore_scratch when you’re done. The live database is never touched until you’re sure.
Backing Up PostgreSQL in Docker
If PostgreSQL runs in a container (as in MySQL with Docker, but with the postgres image), run the tools inside the container and save the output on the host:
docker exec my-postgres pg_dump -U postgres -F c bookstore > bookstore.dump
To restore, pipe the file back in. Use -i (not -t) so the binary file passes through correctly:
docker exec my-postgres createdb -U postgres bookstore_restore
docker exec -i my-postgres pg_restore -U postgres -d bookstore_restore < bookstore.dump
Replace my-postgres with your container’s name (docker ps shows it). Backing up the container’s data volume while the database is running is not a safe substitute, because the files can be copied in an inconsistent state.
Automating Backups with a Script
A backup you have to remember to run is a backup you’ll eventually forget. This script creates a backup, checks that the file can be read, and deletes backups older than 14 days:
#!/usr/bin/env bash
# Nightly PostgreSQL backup with verification and cleanup.
set -euo pipefail
DB_NAME="bookstore"
BACKUP_DIR="/var/backups/postgres"
KEEP_DAYS=14
STAMP=$(date +%F_%H%M)
FILE="$BACKUP_DIR/${DB_NAME}_${STAMP}.dump"
mkdir -p "$BACKUP_DIR"
# 1. Create the backup in custom format.
pg_dump -F c -f "$FILE" "$DB_NAME"
# 2. Make sure the file can be read back.
pg_restore --list "$FILE" > /dev/null
# 3. Delete backups older than KEEP_DAYS days.
find "$BACKUP_DIR" -name "${DB_NAME}_*.dump" -mtime +"$KEEP_DAYS" -delete
echo "Backup complete: $FILE ($(du -h "$FILE" | cut -f1))"
set -euo pipefail stops the script at the first failure, so a failed pg_dump never deletes your older backups. Save it as /usr/local/bin/pg-backup.sh, make it executable, and give the postgres user a folder it can write to:
sudo chmod +x /usr/local/bin/pg-backup.sh
sudo mkdir -p /var/backups/postgres
sudo chown postgres:postgres /var/backups/postgres
A test run prints something like:
Backup complete: /var/backups/postgres/bookstore_2026-11-14_0200.dump (8.0K)
Schedule it every night at 2 a.m. in the postgres user’s crontab (sudo crontab -u postgres -e):
0 2 * * * /usr/local/bin/pg-backup.sh >> /var/log/pg-backup.log 2>&1
Create the log file first and give it to the postgres user (sudo touch /var/log/pg-backup.log && sudo chown postgres /var/log/pg-backup.log). Check the log now and then, or have a monitoring tool alert you when it stops updating. If you’re new to the command line, the Linux terminal guide covers chmod, chown, and sudo.
Testing Your Restores
pg_restore --list proves the file isn’t corrupted, but not that it contains what you need. The only real test is a restore. This script restores the newest backup into a temporary database, checks a row count, and cleans up:
#!/usr/bin/env bash
# Restore the newest backup into a scratch database and check it.
set -euo pipefail
BACKUP_DIR="/var/backups/postgres"
TEST_DB="restore_test"
LATEST=$(ls -t "$BACKUP_DIR"/bookstore_*.dump | head -n 1)
dropdb --if-exists "$TEST_DB"
createdb "$TEST_DB"
pg_restore -d "$TEST_DB" "$LATEST"
BOOKS=$(psql -d "$TEST_DB" -tAc "SELECT count(*) FROM books")
echo "Restored $LATEST: $BOOKS books"
dropdb "$TEST_DB"
Restored /var/backups/postgres/bookstore_2026-11-14_0200.dump: 4 books
Run it weekly, ideally on a different server, and compare the count with what you expect. Also time it: the restore time is roughly how long your application would be down after a failure.
Where to Store Backups: The 3-2-1 Rule
A backup on the same disk as the database disappears with it. Follow the 3-2-1 rule:
- 3 copies of your data (the live database plus two backups),
- on 2 different types of storage,
- with 1 copy off-site, such as cloud object storage or another location.
Also:
- Encrypt backups that leave the server. A dump contains every row in plain form, including personal data. Use encrypted storage or encrypt the file before uploading it. See Database Security and Privacy.
- Restrict access. Only the backup user and administrators should be able to read backup files.
- Keep at least one copy that can’t be changed or deleted from the server, such as storage with versioning or object lock, so ransomware or an attacker can’t wipe your backups too.
Physical Backups and Point-in-Time Recovery
Logical backups capture the database at one moment. If you back up every night at 2 a.m. and the server fails at 5 p.m., you lose 15 hours of changes. If that’s too much, you need physical backups.
pg_basebackup copies the entire server’s data files while it runs:
pg_basebackup -U postgres -D /var/backups/postgres/base -F t -z -X stream -c fast -P
| Option | Meaning |
|---|---|
-D | Where to write the backup |
-F t -z | Tar format, compressed |
-X stream | Include the WAL needed to make the backup consistent |
-c fast | Start immediately instead of waiting for the next scheduled checkpoint |
-P | Show progress |
Without -c fast, pg_basebackup can appear to hang for several minutes at the start while it waits for a checkpoint.
Combined with WAL archiving (continuously saving PostgreSQL’s write-ahead log files), a base backup lets you do point-in-time recovery (PITR): restore the server to any moment, such as one minute before someone ran a bad DELETE. Setting this up involves server configuration (archive_mode and archive_command) and a careful recovery procedure, so most teams use a tool like pgBackRest or Barman, which handle archiving, retention, and restores for you.
If you use a managed database (such as Amazon RDS, Google Cloud SQL, Azure, or DigitalOcean), automated snapshots and PITR are usually built in. Check how long backups are kept, and still take your own pg_dump copies so you have one outside the provider.
How Often Should You Back Up?
Two numbers help you decide:
- RPO (Recovery Point Objective): how much data you can afford to lose. A nightly backup means up to 24 hours.
- RTO (Recovery Time Objective): how long you can afford to be down while restoring.
A personal project may be fine with a nightly pg_dump. An online store taking orders all day may need hourly dumps or PITR. These decisions are part of a wider disaster recovery plan.
Common Problems and Fixes
| Problem | Cause and fix |
|---|---|
pg_dump: error: aborting because of server version mismatch | Your pg_dump is older than the server. Use the pg_dump from the same or a newer PostgreSQL version. |
pg_restore: error: input file appears to be a text format dump | It’s a plain SQL file. Restore it with psql -f, not pg_restore. |
role "app_user" does not exist during restore | Roles aren’t in pg_dump backups. Restore roles.sql first, or use --no-owner --no-privileges. |
database "bookstore" already exists with -C | Drop the database first, or restore into a new database without -C. |
Peer authentication failed for user "postgres" | On Ubuntu, run the command as that system user: sudo -u postgres pg_dump .... |
| Backup files fill the disk | Add cleanup to your script (find ... -mtime +14 -delete) and monitor free space with df -h. |
Backup Checklist
- Back up with
pg_dump -F cat least daily - Back up roles with
pg_dumpall --globals-only - Automate it with a script and cron
- Verify each file with
pg_restore --list - Test a full restore regularly
- Keep an encrypted copy off-site
- Delete old backups on a schedule
- Back up before every risky change or upgrade
- Consider physical backups and PITR if you can’t lose a day of data
Conclusion
PostgreSQL makes backups straightforward: pg_dump -F c creates a consistent, compressed backup of a database, pg_dumpall --globals-only saves the users, and pg_restore brings everything back, whole or in parts.
The tools are the easy part. What actually protects your data is the routine around them: automate backups, check each one, keep copies somewhere else, and restore them regularly. The day you need a backup is the worst day to find out it doesn’t work.
Frequently Asked Questions
Does pg_dump lock the database?
No. It reads a consistent snapshot while users keep reading and writing. It only blocks operations that change table structure, such as ALTER TABLE, on tables it’s currently dumping.
What’s the best format for pg_dump?
Use the custom format (-F c) for most backups. Use the directory format (-F d -j N) for large databases, and plain SQL when you need a readable file.
Can I restore a backup to a newer PostgreSQL version?
Yes. pg_dump backups restore into the same or newer major versions, which makes them a common way to upgrade. Restoring into an older version often fails.
How do I back up all databases at once?
Run pg_dumpall -f all-databases.sql, or loop over databases in a script and run pg_dump -F c for each one, plus pg_dumpall --globals-only for the roles.
Is copying the data folder a valid backup?
Only if PostgreSQL is stopped, or if you use pg_basebackup or a tool designed for it. Copying the files of a running server can produce a backup that won’t start.
How long should I keep backups?
It depends on your needs and any legal requirements. A common pattern is daily backups for two weeks, weekly for a few months, and monthly for a year. Old backups still contain personal data, so delete them when you no longer need them.
