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 backupPhysical backup
Toolspg_dump, pg_dumpallpg_basebackup, WAL archiving, pgBackRest, Barman
What it copiesSQL definitions and dataThe server’s data files, byte for byte
ScopeOne database (or all, with pg_dumpall)The entire server (cluster)
Restore to another versionYes (same or newer)No, same major version only
Restore a single tableYesNo
Point-in-time recoveryNoYes, with WAL archiving
Best forSmall to medium databases, migrations, most projectsLarge 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:

OptionMeaningDefault
-hHostLocal socket
-pPort5432
-UUserYour operating system username
-dDatabaseSame 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.

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 --clean to replace existing objects.

All Four Formats

FormatOptionOutputRestore withParallel backup
Plain-F p (default).sql text filepsqlNo
Custom-F cSingle compressed filepg_restoreNo
Directory-F dA folder of filespg_restoreYes (-j)
Tar-F t.tar filepg_restoreNo

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

OptionWhat it does
-CCreate the database with its original name before restoring (connect with -d postgres)
--clean --if-existsDrop existing objects before recreating them
--no-ownerDon’t try to set the original owners (useful on a different server)
--no-privilegesSkip GRANT and REVOKE commands
-j NRestore with N parallel jobs
--listShow the archive’s contents without restoring anything
-t tableRestore 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
OptionMeaning
-DWhere to write the backup
-F t -zTar format, compressed
-X streamInclude the WAL needed to make the backup consistent
-c fastStart immediately instead of waiting for the next scheduled checkpoint
-PShow 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

ProblemCause and fix
pg_dump: error: aborting because of server version mismatchYour 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 dumpIt’s a plain SQL file. Restore it with psql -f, not pg_restore.
role "app_user" does not exist during restoreRoles aren’t in pg_dump backups. Restore roles.sql first, or use --no-owner --no-privileges.
database "bookstore" already exists with -CDrop 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 diskAdd cleanup to your script (find ... -mtime +14 -delete) and monitor free space with df -h.

Backup Checklist

  • Back up with pg_dump -F c at 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.