Restore a PostgreSQL Archive Safely with pg_restore

You will finish with a repeatable way to inspect a PostgreSQL archive, restore it into an empty database, and verify that the destination contains the expected objects. The examples use pg_restore 16.15 from Ubuntu package build 16.15-0ubuntu0.24.04.1. Allow 15 to 30 minutes for a small dump, plus the time needed to check the data.

You need the archive produced by pg_dump in custom, directory or tar format, a PostgreSQL server you are allowed to use, and a database role with the permissions needed by the dump. These examples use the unprivileged shell account that owns the PostgreSQL client. Database creation, dropping and role changes may require a PostgreSQL administrator, but Linux sudo is not automatically required.

1. Confirm the client and choose the destination

Check the installed client before you begin:

$ pg_restore --version
pg_restore (PostgreSQL) 16.15 (Ubuntu 16.15-0ubuntu0.24.04.1)

Decide whether the archive should replace an existing database or populate a new one. This guide uses a new database called restored_app, so the original database remains available for comparison. Substitute a name and archive path that you have deliberately checked.

archive=/srv/backups/app.dump
destination=restored_app

Checkpoint: verify the archive path without opening it as a database operation:

test -r "$archive" && printf '%s\n' "Readable: $archive"

2. Inspect the table of contents

List the archive before restoring it. The format is detected automatically, so do not add --format unless you have a specific reason to override detection.

pg_restore --list "$archive" > /tmp/app-restore.list
sed -n '1,25p' /tmp/app-restore.list

The list contains comments followed by archive items such as schemas, tables, data, indexes and access-control entries. It is a planning file, not SQL. Look for unexpected schemas, extensions, owners, large objects and subscriptions before allowing the archive to change a server.

If you need a smaller restore, copy the list to a working file and edit that copy. Lines beginning with ; are excluded, while remaining lines can be moved to change the order:

cp /tmp/app-restore.list /tmp/app-restore-selected.list
$EDITOR /tmp/app-restore-selected.list

Do not edit an archive in place. Use the selected file with --use-list only after reviewing the resulting file. Filters such as --schema and --table restrict the list further.

3. Create an empty destination

Create the destination from template0, not template1. Local additions in template1 can create duplicate objects during a restore.

$ createdb -T template0 restored_app
$ psql -d restored_app -c '\dt'
Did not find any relations.

The exact empty-database message can vary with the psql version and output settings. The useful check is that no application relations appear. If the database already exists, stop and decide whether it is genuinely disposable before using any destructive option.

Destructive boundary: --clean issues DROP commands for objects that the archive will restore. Combining it with --create can drop and recreate the target database. Do not add either option to a command aimed at a shared or production database unless you have a tested backup and an explicit change window.

4. Restore into the new database

Restore directly into the database with --dbname. Add --exit-on-error so the command stops at the first SQL error instead of continuing and reporting a count at the end. Add --verbose when you need progress messages.

pg_restore \
  --dbname=restored_app \
  --exit-on-error \
  --verbose \
  "$archive"

A successful run exits with status 0 and prints object progress when verbose mode is enabled. Check it immediately if the command is part of a script:

status=$?
printf 'pg_restore exit status: %s\n' "$status"
test "$status" -eq 0

If the server is remote, supply connection options such as --host=database.example, --port=5432 and --username=restore_user. PGHOST, PGPORT, PGUSER and other libpq settings can also provide defaults. The PGDATABASE environment variable is not used when no database name is supplied, so make the destination explicit with --dbname.

When authentication needs a password, pg_restore can prompt automatically. Use --password to prompt before the connection attempt, or --no-password in unattended jobs where a missing credential should fail rather than wait. Avoid putting passwords in the command line, where they may be visible to other processes.

5. Verify objects and data

Start with the database connection and relation list:

psql -d restored_app -c 'select current_database(), current_user;'
psql -d restored_app -c '\dt *.*'

Check a known table with a query suited to the application. A row count alone is not proof that the restore is correct, but it quickly catches an empty or wrong destination:

psql -d restored_app -c 'select count(*) from public.example_table;'

Run ANALYZE on restored tables when the database will serve queries immediately. This refreshes planner statistics; it does not validate business meaning or repair inconsistent data.

psql -d restored_app -c 'analyze;'

6. Apply the common variations carefully

For a schema-only rehearsal, use --schema-only. For data into an already prepared schema, use --data-only. These options cannot restore content that was not present in the archive.

pg_restore --dbname=restored_app --schema-only --exit-on-error "$archive"
pg_restore --dbname=restored_app --data-only --exit-on-error "$archive"

To restore only one schema or table, use repeated --schema or --table options. A table-only restore does not bring along every object that the table depends on, and --table has no wildcard matching in PostgreSQL 16.15. Test this mode in a clean database before relying on it.

pg_restore --dbname=restored_app --schema=public --table=example_table --exit-on-error "$archive"

For a large custom or directory archive, --jobs=4 can use four concurrent sessions for data loading, index creation and constraints. It is ignored for SQL script output, requires a regular file or directory, supports custom and directory formats, and cannot be combined with --single-transaction. Start with a modest value and watch server CPU, storage and connection limits.

pg_restore --dbname=restored_app --jobs=4 --exit-on-error "$archive"

Use --single-transaction when an all-or-nothing restore is more useful than parallelism. It also implies --exit-on-error, but it does not make an untrusted archive safe.

7. Generate SQL when the archive needs review

Omit --dbname and write the generated SQL to a file for inspection. This is the safer workflow for an archive whose source roles are not fully trusted:

pg_restore --file=/tmp/app-restore.sql "$archive"
less /tmp/app-restore.sql

The output can be inspected, searched and tested before psql is allowed to execute it. Do not treat a partial selection, --table filter or --schema filter as a security boundary: the PostgreSQL documentation warns that partial dumps and partial restores do not remove the source superuser's ability to place executable content in the dump.