PostgreSQL Restore

Restore Methods

psql dbname < file.sql

pg_restore -d dbname file.dump

pg_restore -j 4 -d dbname dir/

pg_restore -t table -d db file

🔹 Restore SQL Dump

Restore plain text SQL dumps using the psql command-line tool. This method works with backups created by pg_dump in plain SQL format. It executes SQL commands sequentially to recreate database objects and insert data.

-- Restore to existing database
psql mydb < mydb_backup.sql

-- Create database and restore
createdb mydb
psql mydb < mydb_backup.sql

-- Restore with specific user
psql -U postgres mydb < backup.sql

-- Restore compressed SQL
gunzip -c backup.sql.gz | psql mydb

🔹 Restore Custom Format

Use pg_restore for custom format backups created with pg_dump -Fc. Custom format provides flexibility with selective restoration and parallel processing. It's the preferred method for large databases requiring faster restore times.

-- Basic restore
pg_restore -d mydb mydb.dump

-- Restore with clean (drop existing objects)
pg_restore -d mydb --clean mydb.dump

-- Create database and restore
pg_restore -C -d postgres mydb.dump

-- Restore with verbose output
pg_restore -d mydb -v mydb.dump

🔹 Parallel Restore

Speed up restoration with parallel processing using multiple CPU cores. Directory format backups support parallel restore, significantly reducing time for large databases. Each job processes different tables simultaneously for maximum efficiency.

-- Restore with 4 parallel jobs
pg_restore -d mydb -j 4 backup_dir/

-- Fast restore with 8 jobs
pg_restore -d large_db -j 8 large_backup/

-- Parallel restore with clean
pg_restore -d mydb -j 4 --clean backup_dir/

-- Maximum parallelism
pg_restore -d mydb -j 16 backup_dir/

🔹 Selective Restore

Restore specific tables, schemas, or objects instead of entire database. Selective restoration is useful for recovering individual tables or testing specific data. This approach saves time when you only need portions of a backup.

-- Restore specific table
pg_restore -d mydb -t employees mydb.dump

-- Restore multiple tables
pg_restore -d mydb -t employees -t departments mydb.dump

-- Restore specific schema
pg_restore -d mydb -n public mydb.dump

-- Restore only data (no schema)
pg_restore -d mydb --data-only mydb.dump

-- Restore only schema (no data)
pg_restore -d mydb --schema-only mydb.dump

🔹 Restore All Databases

Restore complete PostgreSQL cluster from pg_dumpall backup including all databases and global objects. This method recreates the entire server state including roles, tablespaces, and all databases. Essential for complete disaster recovery scenarios.

-- Restore all databases
psql -f all_databases.sql postgres

-- Restore compressed cluster backup
gunzip -c cluster_backup.sql.gz | psql postgres

-- Restore only global objects
psql -f globals.sql postgres

-- Restore with specific superuser
psql -U postgres -f all_databases.sql

🔹 Restore with Options

Control restore behavior with various options for different scenarios. Options handle conflicts, errors, and ownership issues during restoration. Understanding these options ensures successful restores in complex environments.

-- Ignore errors and continue
pg_restore -d mydb --exit-on-error mydb.dump

-- Don't restore ownership
pg_restore -d mydb --no-owner mydb.dump

-- Don't restore privileges
pg_restore -d mydb --no-acl mydb.dump

-- Single transaction (all or nothing)
pg_restore -d mydb --single-transaction mydb.dump

-- If exists, drop before creating
pg_restore -d mydb --clean --if-exists mydb.dump

🔹 Restore Best Practices

Follow these guidelines for safe and successful database restoration. Proper restore procedures minimize downtime and prevent data loss. Always test restore processes before emergencies occur to ensure reliability.