Restore a PostgreSQL archive with pg_restore

pg_restore restores archives created by pg_dump in a non-plain-text format, including the custom format used in our backup guide. Plain SQL scripts use a different restoration workflow. Consult the official pg_restore documentation.

Four checks before restoring

1. Version compatibility: review the source server, export tool and destination versions. The original article favours the same or a later PostgreSQL version over a downgrade; that is not a guarantee that every combination will work.
2. Archive and source: review the backup log and confirm that the archive comes from a trusted source. A restore can execute SQL from the source database. A clean-looking log alone does not prove that the archive is safe or complete.
3. WAL and recovery: plan for the volume of generated logs. Do not routinely disable archiving on a production server just to make a restore faster.
4. Space and destination: allow enough space for restored data, indexes, temporary work and logs. Verify the destination and protect existing data.

The original article recommends disabling archive mode. This English edition qualifies that recommendation: changes affecting WAL, archiving or replication can invalidate recovery assumptions and require a new backup plan. Review PostgreSQL's bulk-loading guidance with the database administrator. An isolated test restore is a safer starting point.

Parameters

Locate the installed pg_restore.exe. The original article lists C:\Program Files\PostgreSQL\13\bin and C:\Program Files (x86)\PostgreSQL\13\bin. Use the actual compatible version on your machine rather than copying a historical path blindly.

• --host=: destination server name or IP address.
• --username=: role carrying out the restore.
• --port=: destination server port.
• --verbose: detailed progress output.
• --dbname=: destination database, not a schema.
• --exit-on-error: stop when a SQL error occurs; this alone does not undo earlier changes.
• The archive filename and a separate restoration log path.

Adapted original example

The source scenario uses 64-bit Windows Server 2022, server 10.0.0.2, port 5433, role pgcarlos, archive D:\Respaldo\Backup.bak and original backup log Backup.log. It calls DB2 a schema even though the command uses it as the database.

First read the original backup log. For the template below, replace TEST_DATABASE with an existing, empty database reserved for the restore, and replace YOUR_VERSION and the other example details. Confirm permissions with the administrator. The new log is Restauracion.log, without an accent in the filename.

"C:\Program Files\PostgreSQL\YOUR_VERSION\bin\pg_restore.exe" --host=10.0.0.2 --username=pgcarlos --port=5433 --verbose --exit-on-error --dbname=TEST_DATABASE "D:\Respaldo\Backup.bak" > "D:\Respaldo\Restauracion.log" 2>&1

This command can create objects and load data into the named database. Do not point it at production as a test. Supply a password at the expected prompt if requested.

After the process

Review the exit status and full restoration log, including warnings and errors. Check the restored objects and application behaviour in the test environment before considering a production change.

The English example corrects path quoting and the database/schema distinction and adds an explicit stop-on-error option. It is a documented template, not a restore tested against your databases.