PostgreSQL backup and restore with pg_dump and pg_restore

For a PostgreSQL logical backup, export one database with pg_dump and restore a custom-format archive with pg_restore into a separate database. Check the exit status, review errors, and test the restored data before relying on the backup. A completed export alone does not establish that your application can recover.

This guide is for developers and administrators who need a repeatable PostgreSQL backup and restore exercise. It covers a whole-database logical archive, the database objects it leaves out, and a practical verification sequence. It does not configure a production backup service or point-in-time recovery.

Choose the backup method around the recovery need

PostgreSQL documents three approaches: logical dumps, file-system backups, and continuous archiving. A logical dump is useful for a database transfer or a controlled restore exercise. Large or frequently changing production systems often need a different primary backup design.

Point-in-time recovery requires a suitable base backup and the necessary sequence of archived write-ahead log, or WAL, records. A daily pg_dump archive cannot replay transactions to an arbitrary time between exports. Use the continuous archiving documentation when your recovery requirement calls for that capability.

Do not copy a running PostgreSQL data directory with an ordinary file-copy command and call it a consistent backup. File-system backups have consistency requirements, including coordinated snapshots or shutdown for a simple file copy. pg_basebackup provides another route for a physical base backup, with its own prerequisites.

Check PostgreSQL versions and the database server

Record the source and target server versions, installed extensions, locale settings, database size, and the client utilities you will use. Start with these client checks:

pg_dump --version
pg_restore --version
psql --version

A pg_dump client cannot export from a server with a newer major version than itself. Loading into an older PostgreSQL major version is not a supported assumption. For this exercise, use current patched clients from the same major release as the source and target; handle version upgrades as a separate tested change. The pg_dump reference explains compatibility and archive formats.

The examples use placeholder hosts, a source database named appdb, and a separate restore server. Replace every placeholder deliberately. Confirm the target cannot send production emails, consume live queues, or trigger external jobs. Run commands one at a time and stop after any failure.

Prepare credentials and a protected destination

The backup_reader role must be able to read all required data. Row-level security, restricted tables, or extension-specific objects can require additional planning; do not respond to an error by granting blanket superuser privileges. The restore_owner role must be able to create the test database and its objects on the isolated server.

Configure approved authentication before running the commands. With password authentication, a protected pgpass file can support unattended work. On Unix, it must exclude group and other-user access; Windows relies on a securely protected directory. Keep passwords out of command text and committed scripts. The --no-password option means fail if a password is needed but unavailable. It does not disable authentication. For an interactive password prompt, omit that option.

Use a new restricted directory with enough free space, a unique output filename, and approved transport settings. An archive may contain the same sensitive records as the source. Compression is not encryption. Plan encrypted storage, access controls, retention, and a separate copy; our cloud backup guide covers that wider storage plan.

Create and inspect a custom-format backup file

Export the single database to a custom-format archive:

pg_dump --host=source-db.example.invalid --port=5432 --username=backup_reader --dbname=appdb --format=custom --file=appdb-2026-10-30.dump --verbose --no-password

Capture standard error and the process exit code in your job record. In a shell, inspect the status immediately, such as $? in Bash or $LASTEXITCODE in PowerShell. Do not advance after a nonzero exit code or unexplained warnings. A file can exist even when a job failed.

List the archive contents without connecting to a database:

pg_restore --list --file=appdb-2026-10-30.contents.txt appdb-2026-10-30.dump

Check that the expected schemas, tables, sequences, and other objects appear. A readable contents list only establishes that the archive header and catalog can be read. It does not prove that every data block will restore.

A logical dump represents a consistent database snapshot, not changes committed throughout the export. If the source keeps changing, later row counts will not necessarily match the restored copy. For the first exercise, use a small fixture dataset with writes paused and record expected results before exporting.

Account for objects outside a single database

One pg_dump archive does not capture the entire cluster. Shared roles and tablespace definitions need separate treatment. pg_dumpall --globals-only can export those global definitions, subject to the executing role's privileges. Review that SQL before applying it elsewhere and protect any authentication-related material it contains.

Also record extensions and their installable versions, server configuration, certificates, scheduled jobs, and external files. A restored database that references a missing extension or unavailable service may be unusable even when its ordinary tables are present.

Restore into a new, isolated target database

Use a trusted archive. Restoring can execute code defined by source database superusers; selecting only a table does not remove that trust issue. Consult the warning in the pg_restore documentation before restoring an unfamiliar dump.

Create a new target from template0, then restore. The createdb utility lets you set the target host and template explicitly. If this database name already exists, stop and choose another name rather than deleting it.

createdb --host=restore-db.example.invalid --port=5432 --username=restore_owner --template=template0 --no-password appdb_restore_20261030
pg_restore --host=restore-db.example.invalid --port=5432 --username=restore_owner --dbname=appdb_restore_20261030 --no-owner --no-privileges --no-tablespaces --single-transaction --exit-on-error --verbose --no-password appdb-2026-10-30.dump

These options deliberately adapt the restore for a test environment: --no-owner assigns restored objects to the restoring role; --no-privileges omits grants; --no-tablespaces avoids the original placement. This does not reproduce production access controls. Role names embedded in policies or functions may still require attention.

--single-transaction makes this restore all-or-nothing and implies exit-on-error. It cannot be combined with parallel jobs, and a large restore may exceed practical transaction or lock limits. Do not add --clean or --create to this exercise: they change the destination behavior and can affect existing objects or the database named in the archive.

Verify the restored database beyond command success

Save this starting check as verify-restore.sql. It identifies the connection and lists visible application tables:

SELECT current_database(), current_user, version();

SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
  AND table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY table_schema, table_name;

Run it against the new database:

psql --host=restore-db.example.invalid --port=5432 --username=restore_owner --dbname=appdb_restore_20261030 --no-psqlrc --set=ON_ERROR_STOP=on --no-password --file=verify-restore.sql

psql's error-stop setting stops script processing on an error, while --no-psqlrc avoids local startup customizations. Neither turns query results into assertions. Extend the script with checks specific to your data and explicitly fail the test when an expected result differs.

• Compare known row counts and selected record values from the fixture, including empty tables and non-ASCII text.
• Check primary keys, foreign keys, indexes, sequences, views, functions, and required extensions.
• Test a new insert to detect sequence or default-value problems, using only disposable test data.
• Test application reads and writes with intended application roles after preparing the appropriate grants.
• Measure the full recovery time, including obtaining the archive, preparing dependencies, restoring, and checking the application.

Refresh query-planner statistics where needed with ANALYZE before drawing performance conclusions. A table list, row count, or matching file checksum answers a narrower question than a working application restore. Our recoverability testing guide helps define acceptance criteria.

Plain SQL files use psql instead

For a plain SQL script, choose --format=plain and load the file with psql. The following is an alternative workflow, not another step to run against the custom-format restore. First create a fresh database named appdb_sql_restore_20261030 on the isolated server, with compatible settings and the roles the script needs:

pg_dump --host=source-db.example.invalid --port=5432 --username=backup_reader --dbname=appdb --format=plain --file=appdb-2026-10-30.sql --no-password
psql --host=restore-db.example.invalid --port=5432 --username=restore_owner --dbname=appdb_sql_restore_20261030 --no-psqlrc --set=ON_ERROR_STOP=on --no-password --file=appdb-2026-10-30.sql

Using --file keeps the output path explicit and avoids shell-dependent redirection behavior. This plain SQL example does not strip original ownership or grants, and ON_ERROR_STOP does not roll back statements already committed. Keep the destination disposable and read the first error before retrying.

Turn the exercise into a maintained recovery procedure

Record the archive identifier, source version, export start and finish times, exit codes, restore target, checks performed, and unresolved differences. Schedule future runs around the amount of data loss the application can tolerate, then verify that actual backup completion meets that requirement.

Alert on missed or failed exports, failed transfers, retention errors, and failed restore tests. Keep an independently protected recovery copy and document who can retrieve it. Rehearse again after schema, extension, role, or PostgreSQL version changes.

The command options in this guide were checked against PostgreSQL documentation. They were not executed against a PostgreSQL server here. Treat them as a starting procedure to adapt and test, with production recovery approval kept separate from the exercise.