Make a PostgreSQL Backup You Can Verify and Restore

You will make a consistent backup of one PostgreSQL database, check that it contains what you expect, and choose the matching restore command. The examples use pg_dump 16.15, installed here as PostgreSQL 16.15-0ubuntu0.24.04.1. Allow 15 to 30 minutes for a first backup, longer if the database is large or the connection is remote.

You need the PostgreSQL client tools, a database account that can read the objects you intend to dump, enough local disk space, and a destination that is not exposed to untrusted users. A dump covers one database only. Use pg_dumpall separately when you need cluster-wide roles or tablespaces.

1. Confirm the client and connection

Check the installed version, then test the connection with a harmless metadata query. Replace the placeholders with the database, host and account used by your installation:

$ pg_dump --version
pg_dump (PostgreSQL) 16.15
$ psql --host=DB_HOST --username=DB_USER --dbname=DB_NAME --command='SELECT current_database(), current_user;'
 current_database | current_user
------------------+--------------
 DB_NAME          | DB_USER
(1 row)

Use --host, --port, --username and --dbname when the defaults are not suitable. Without a database name, pg_dump uses PGDATABASE, then the connection user name. PGHOST, PGPORT, PGUSER and the other libpq settings can also supply defaults. A password prompt is normal when the server requires one. For unattended work, arrange an appropriate .pgpass entry with restrictive permissions rather than putting a password in the command line.

2. Make a plain SQL dump

Plain format is the default. Write it to a new file, and capture errors separately so a warning is not mistaken for a successful backup:

$ pg_dump --host=DB_HOST --username=DB_USER --dbname=DB_NAME \
    > db-name.sql 2>db-name.dump.log
$ status=$?
$ printf 'pg_dump exit status: %s\n' "$status"
pg_dump exit status: 0

The SQL script can be read by psql. The dump sees a consistent database state while the database remains available to other readers and writers. Read the log even when the exit status is zero, because pg_dump tells you to examine warnings on standard error.

Redirection with > truncates an existing destination before the command runs. To protect an existing backup, write a temporary file and replace the old file only after success:

$ pg_dump --dbname=DB_NAME > db-name.sql.new 2>db-name.dump.log
$ status=$?
$ if [ "$status" -eq 0 ]; then mv -- db-name.sql.new db-name.sql; else rm -- db-name.sql.new; fi
$ exit "$status"

The rm above removes only the failed temporary output. Do not use this pattern with a placeholder that names a valuable existing file.

3. Prefer an archive when you need selective restore

Custom format is a compressed archive for pg_restore. It lets you inspect, select and reorder objects during recovery:

$ pg_dump --format=custom --file=db-name.dump --dbname=DB_NAME
$ pg_restore --list db-name.dump | sed -n '1,12p'

A directory archive is the format that supports parallel dumps. The target directory must not already exist; pg_dump creates it:

$ pg_dump --format=directory --file=db-name.dumpdir --dbname=DB_NAME
$ pg_dump --format=directory --jobs=4 --file=db-name-parallel.dumpdir --dbname=DB_NAME
$ pg_restore --list db-name.dumpdir | sed -n '1,12p'

With four jobs, the client opens five database connections, so check the server's connection limit and expect extra load. Parallel mode is available only for directory output. A tar archive is also accepted by pg_restore, but it is not compressed by pg_dump and cannot be written in parallel.

4. Verify the backup before relying on it

For a plain script, check its size and look for recognisable SQL without treating a text search as a complete test:

$ test -s db-name.sql && echo 'plain dump is non-empty'
plain dump is non-empty
$ rg -n '^(CREATE|COPY|INSERT|ALTER TABLE)' db-name.sql | head

For an archive, ask pg_restore for its table of contents and check the file type:

$ test -s db-name.dump && echo 'custom archive is non-empty'
custom archive is non-empty
$ pg_restore --list db-name.dump | head

These checks prove that output was written and can be read. They do not prove that every expected table or row is present. Compare the table of contents with your scope, retain the standard-error log, and perform a test restore into an isolated database when the backup matters.

5. Restore into a fresh test database

Create a destination that is safe to throw away, then restore the matching format. Creating a database changes server state and usually needs a role with the CREATEDB privilege; use an administrator-approved account if your normal account cannot do it.

$ createdb --host=DB_HOST --username=DB_USER DB_NAME_TEST
$ psql --host=DB_HOST --username=DB_USER --dbname=DB_NAME_TEST \
    --command='SELECT current_database();'
 current_database
------------------
 DB_NAME_TEST
(1 row)
$ psql --host=DB_HOST --username=DB_USER --dbname=DB_NAME_TEST \
    --file=db-name.sql --echo-errors

For an archive, use:

$ pg_restore --host=DB_HOST --username=DB_USER \
    --dbname=DB_NAME_TEST --exit-on-error db-name.dump

The --exit-on-error choice makes a test restore stop at the first restore error. After either restore, check representative objects and row counts with read-only SQL. Run ANALYZE after a real restore so the optimiser has current statistics; statistics are not included in the dump.

To undo this test database after checking it, confirm the name first and then drop only that disposable database:

$ psql --host=DB_HOST --username=DB_USER --dbname=postgres \
    --command='DROP DATABASE DB_NAME_TEST;'

This is destructive and normally requires ownership or elevated database privileges. Do not substitute a production database name.

6. Narrow a dump only when the dependency boundary is clear

--schema-only writes definitions without table data. --data-only writes data, large objects and sequence values without definitions. A section can be selected with --section=pre-data, --section=data or --section=post-data.

$ pg_dump --schema-only --dbname=DB_NAME > db-name-schema.sql
$ pg_dump --data-only --dbname=DB_NAME > db-name-data.sql
$ pg_dump --table='app.orders*' --exclude-table='app.orders_log' \
    --dbname=DB_NAME > orders.sql

Quote table and schema patterns so the shell does not expand the asterisk. A filtered dump is not a self-contained backup: selecting a schema or table does not pull in every object it depends on. Use --strict-names when a requested inclusion pattern must match something, and review the result before restoring it.

--no-owner and --no-privileges can make a plain script easier to restore under a different account. They do not grant missing access, and the archive equivalents are restore-time choices. Keep ownership and privilege statements when they are part of the recovery requirement.

7. Handle version and security boundaries

This installed client can dump older PostgreSQL servers, but it refuses to dump a server newer than its own major version. Output is intended to load into newer server versions, while loading into an older major version is not guaranteed. For cross-version work, --quote-all-identifiers can avoid reserved-word differences, at the cost of a less readable script.

Never restore an untrusted dump without inspection. A restore causes the destination to execute SQL and object definitions chosen by source superusers, even when the dump is partial. For non-plain archives, inspect the SQL with pg_restore --file before applying it. Restore as a deliberately chosen account, not automatically as a superuser.