Restore a database from a backup file
Destructive
What do risk levels mean?
- Read-only
- Inspects state without changing anything.
- Low risk
- Reversible with a routine follow-up command.
- Medium risk
- Changes state; undo path documented.
- Destructive
- Deletes or overwrites; confirmation required.
Loads a PostgreSQL dump file into a database, reconstructing schema and data from a previous backup.
postgresqlrestorebackuppg-restore
Confirmation required
This recipe is destructive and requires confirmation — confirm you understand the impact before running any command below.
Parameters
Name of the target database.
Path to the backup file to restore from.
Commands
Inspect the current state of the target database before overwriting it
psql -d <database_name> -c "SELECT count(*) AS table_count FROM information_schema.tables WHERE table_schema = 'public';"Restore the database from the compressed SQL dump
gunzip -c <backup_file> | psql -d <database_name>Verification
psql -d <database_name> -c "\dt"Tables from the backup are listed; the count should match expectations for the restored data.
Undo
Not undoable
Restoring overwrites existing objects with no way back unless you created a backup of the target database before restoring.
Pitfalls
- The target database must exist and be empty, or the restore will fail on conflicts; drop and recreate it first, or use --clean in pg_dump.
- Backups created with pg_dump --format=custom require pg_restore, not psql.
- Active connections may block the restore; terminate them before starting.
- On Windows, gunzip may not be available; decompress the backup first or use a custom-format backup with pg_restore.