How to restore a PostgreSQL database

Topic: Databases core

Restore from a pg_dump custom-format file with pg_restore, or from a plain SQL dump with psql. Drop or create the target database first if replacing; restore globals with psql if you have pg_dumpall output. Use this when recovering from a failure or when cloning a database.

Intent: How-to

Quick answer

  • Custom format: pg_restore -d mydb -Fc mydb.dump. If the database exists and has objects, use --clean (drops before restore) or restore to an empty database. Use -j N for parallel restore.
  • Plain SQL: psql -d mydb -f mydb.sql. Create the database first (CREATE DATABASE mydb;) and ensure the user has rights. Restore globals first if you have pg_dumpall -g output.
  • Physical restore: stop Postgres, replace the data directory with the base backup, restore recovery.signal or standby.signal if needed, replay WAL, then start. Use for full cluster recovery or standby.

Prerequisites

Steps

  1. Prepare target database

    For full replace: DROP DATABASE mydb; CREATE DATABASE mydb OWNER myuser; or create an empty database. Ensure the restore user has CREATEDB or is superuser if creating.

  2. Restore custom dump

    pg_restore -U postgres -d mydb -Fc -j 4 /backup/mydb.dump. Use -j for parallel jobs. If errors (e.g. existing objects), use --clean or fix conflicts. Restore roles/globals separately if needed.

  3. Restore plain SQL

    psql -U postgres -d mydb -f /backup/mydb.sql. Redirect errors to a file if needed: psql ... 2> restore_errors.log. Run globals (pg_dumpall -g output) before database dumps.

  4. Verify and re-grant

    Connect and run SELECT count(*) on key tables; check application connectivity. Re-grant permissions if restore did not include them or if roles differ. Document restore time and source backup.

Summary

Restore with pg_restore for custom dumps or psql for SQL dumps; prepare the target database first. For physical restore, replace the data directory and replay WAL. Use this for recovery or cloning.

Prerequisites

Steps

Step 1: Prepare target database

Create or drop-and-recreate the target database as needed; ensure the restore user has required privileges.

Step 2: Restore custom dump

Run pg_restore with -d and -Fc; use -j for parallel restore; handle existing objects with —clean or manual drop.

Step 3: Restore plain SQL

Run psql -d mydb -f dump.sql; restore globals first if you have them.

Step 4: Verify and re-grant

Check row counts and application access; re-grant permissions if needed; document the restore.

Verification

  • Database is consistent; application can connect and run queries; permissions are correct.

Troubleshooting

Object already exists — Use —clean with pg_restore or drop objects first; ensure restore order (globals then DB). Wrong version — Restore from dump to same or newer major version; use pg_upgrade for major upgrade.

Next steps

Continue to