How to back up and restore PostgreSQL with pg_dump and pg_restore

Before you start

pg_dump connects like any other client. On a server where PostgreSQL runs locally, the simplest account is the postgres system user, so the commands below start with sudo -u postgres. That user needs a directory it can write to:

sudo install -d -o postgres -g postgres -m 700 /var/backups/postgresql

Replace mydb with your database name. pg_dump does not block other readers or writers. It does hold a light lock on each table, so schema changes such as ALTER TABLE wait until the dump finishes.

Pick a format

The -F flag sets the output format. -f sets the output file or directory.

sudo -u postgres pg_dump -Fc -f /var/backups/postgresql/mydb.dump mydb

For a large database, use the directory format with -j, the number of tables to dump at once. pg_dump opens one connection per job plus one, so check max_connections first. With -Fc, -j fails with parallel backup only supported by the directory format.

sudo -u postgres pg_dump -Fd -j 4 -f /var/backups/postgresql/mydb_dir mydb

Plain SQL is the default, so no -F is needed:

sudo -u postgres pg_dump -f /var/backups/postgresql/mydb.sql mydb

Compress the dump

Custom and directory dumps are gzip-compressed by default. On pg_dump 16 or newer, --compress also accepts lz4 and zstd. On our 21,000-row test database, plain SQL was 940 KB, the default custom dump 98 KB, and zstd:3 30 KB.

sudo -u postgres pg_dump -Fc --compress=zstd:3 -f /var/backups/postgresql/mydb.dump mydb

For plain SQL, pipe it through gzip:

sudo -u postgres pg_dump mydb | gzip > /var/backups/postgresql/mydb.sql.gz

Connect to a remote server without exposing the password

-h is the host, -p the port, -U the database user and -d the database:

pg_dump -h db.example.com -p 5432 -U backup -d mydb -Fc -f mydb.dump

Put the password in ~/.pgpass, one line per connection: host:port:database:user:password. A * matches anything in the first four fields. libpq ignores the file unless only its owner can read it.

db.example.com:5432:mydb:backup:your-password-here

chmod 600 ~/.pgpass

Avoid PGPASSWORD. The PostgreSQL docs advise against it because some systems let other users see a process's environment through ps. In scripts, add -w so pg_dump fails at once instead of waiting for a password prompt nobody will answer.

Dump one table or one schema

-t dumps matching tables, -n matching schemas, and --exclude-table-data keeps a table's structure but skips its rows, which suits large log tables. Patterns accept * wildcards.

sudo -u postgres pg_dump -Fc -t public.orders -f /var/backups/postgresql/orders.dump mydb

Back up roles with pg_dumpall

Roles and tablespaces belong to the whole server, not to one database, so pg_dump never includes them. --globals-only writes just those as SQL:

sudo -u postgres pg_dumpall --globals-only -f /var/backups/postgresql/globals.sql

The file contains password hashes, so keep it as private as the dumps. --no-role-passwords leaves them out. Restore it with sudo -u postgres psql -f globals.sql postgres before restoring databases.

Restore into a new database

Restore into a fresh database first, check it, then switch over. That way a bad dump never touches production.

sudo -u postgres createdb mydb_restored

sudo -u postgres pg_restore -j 4 --no-owner -d mydb_restored /var/backups/postgresql/mydb.dump

• -d is the database to restore into.
• -j 4 loads data and builds indexes in four parallel sessions. It works with custom and directory archives, not with --single-transaction.
• --no-owner skips ALTER OWNER commands, so the restoring user owns everything. Use it when the original roles do not exist on the target.

By default pg_restore keeps going after errors and ends with pg_restore: warning: errors ignored on restore: N. Add --exit-on-error to stop at the first one. Plain SQL dumps go through psql instead; ON_ERROR_STOP makes it stop on the first error:

gunzip -c mydb.sql.gz | sudo -u postgres psql -v ON_ERROR_STOP=1 -d mydb_restored

Then refresh the planner statistics, or the first queries may be slow:

sudo -u postgres vacuumdb --analyze-only -d mydb_restored

To overwrite an existing database instead, add --clean --if-exists. --clean drops each object before recreating it, and --if-exists hides the does not exist errors for objects that are missing. Tables created after the dump are left alone.

To restore one table from a full dump, use pg_restore -n public -t orders. It restores the table and its rows but not its indexes, constraints or sequence. For full control, write the table of contents with pg_restore -l mydb.dump > toc.list, delete the lines you do not want, and restore with -L toc.list.

Verify the dump

pg_restore --list prints the archive's header and table of contents. The header from our test database:

sudo -u postgres pg_restore --list /var/backups/postgresql/mydb.dump | head -12

;
; Archive created at 2026-10-03 16:22:51 UTC
;     dbname: learn_scratch_pg
;     TOC Entries: 25
;     Compression: gzip
;     Dump Version: 1.15-0
;     Format: CUSTOM
;     Integer: 4 bytes
;     Offset: 8 bytes
;     Dumped from database version: 16.15 (Ubuntu 16.15-0ubuntu0.24.04.1)
;     Dumped by pg_dump version: 16.15 (Ubuntu 16.15-0ubuntu0.24.04.1)
;

The real test is a restore. Load the dump into a scratch database as above, then compare row counts for your important tables:

sudo -u postgres psql -d mydb_restored -Atc "select count(*) from orders"

For plain dumps, gzip -t mydb.sql.gz checks the compressed file. Store a sha256sum next to each dump so you can confirm a copy was not damaged in transit.

Version rules

Run it every night with cron

This script writes to a temporary name, checks the archive, then renames it, so a failed run never leaves a file that looks like a good backup. It then deletes dumps older than seven days.

#!/usr/bin/env bash
set -euo pipefail

DB="mydb"
BACKUP_DIR="/var/backups/postgresql"
KEEP_DAYS=7
OUT="$BACKUP_DIR/$DB-$(date +%Y-%m-%d_%H%M).dump"

trap 'rm -f "$OUT.partial"' EXIT

pg_dump -Fc -f "$OUT.partial" "$DB"
pg_restore -f /dev/null "$OUT.partial"
mv "$OUT.partial" "$OUT"

find "$BACKUP_DIR" -name "$DB-*.dump" -type f -mtime +"$KEEP_DAYS" -delete

sudo chmod 755 /usr/local/bin/pg-backup.sh

30 2 * * * postgres /usr/local/bin/pg-backup.sh >> /var/backups/postgresql/backup.log 2>&1

-mtime +7 matches files more than seven full days old, so you keep about eight nightly dumps. A copy on the same disk does not survive losing the server, so send one off-site too.

Common errors

Frequently asked questions

Does pg_dump lock the database?

No. It reads a consistent snapshot and does not block reads or writes. It does make schema changes such as ALTER TABLE or DROP TABLE wait until it finishes with that table.

What is the difference between pg_dump and pg_dumpall?

pg_dump backs up one database in any format. pg_dumpall writes every database plus roles and tablespaces as one plain SQL script. A common setup is pg_dump per database plus pg_dumpall --globals-only.

Can I restore a pg_dump backup on a newer PostgreSQL version?

Yes. Dumps load into newer major versions, and that is the standard way to upgrade. Loading into an older major version is not supported.

Which pg_dump format should I use?

Custom (-Fc) for most databases. Directory (-Fd) with -j for large ones. Plain SQL only when you need to read or edit the SQL.

Is pg_dump enough on its own?

It gives you the database as of the moment the dump started. To recover to any point in between, you also need WAL archiving with a base backup.