How to use the command 'pg_restore' (with examples)

The pg_restore command is an essential utility for managing PostgreSQL databases. It enables users to restore a database from an archive file that was previously created using the pg_dump utility. This operation is crucial for database recovery, development processes, and data migration tasks. The command offers a plethora of options allowing customized restoration including user specification, host alteration, concurrent job processing, and more.

Restore an archive into an existing database

Code:

pg_restore -d db_name archive_file.dump

Motivation:

The most fundamental use of pg_restore is to restore a PostgreSQL database from an archive file. This is especially useful when you need to migrate a database to a different server or restore it after a failure. If your database setup has gone awry, restoring it from a reliable backup file using pg_restore can quickly get your database back to a working state.

Explanation:

• pg_restore: This command initiates the restoration process from the archive.
• -d db_name: The -d flag specifies the target database name where the data will be restored. The database must already exist.
• archive_file.dump: This is the file containing the archived database structure and data, which was created previously by pg_dump.

Example Output:

pg_restore: restoring data for table "users"
pg_restore: restoring data for table "orders"
... (additional restoration messages)
pg_restore: finished with exit code 0

Restore an archive into an existing database with a custom username

Code:

pg_restore -U username -d db_name archive_file.dump

Motivation:

In systems with multiple users, it may be necessary to perform database restoration operations under a specific user account. This use case allows you to specify a PostgreSQL username to connect as, which can be critical when different users have distinct permissions and roles within the database.

Explanation:

• -U username: The -U flag allows you to specify the username to connect to the database. This overrides the default user setting.
• -d db_name: Indicates the database to restore into.
• archive_file.dump: The archive file to be restored.

Example Output:

Password for user username:
pg_restore: connecting to database for restore
pg_restore: restoring data for table "sales"
pg_restore: finished with exit code 0

Restore an archive with custom host and port

Code:

pg_restore -h host -p port -d db_name archive_file.dump

Motivation:

In environments where databases are distributed across multiple servers or non-standard ports, you might need to specify a particular host and port. This functionality is indispensable for accessing databases located on different systems or cloud environments, ensuring you can restore data regardless of the physical location.

Explanation:

• -h host: This flag specifies the host name of the database server.
• -p port: The -p flag designates the port number the database server is listening on.
• -d db_name: The target database for the restore operation.
• archive_file.dump: The file that contains the database backup.

Example Output:

pg_restore: connecting to database on host example.com port 5432
pg_restore: restoring data for table "inventory"
pg_restore: finished with exit code 0

List database objects included in the archive

Code:

pg_restore --list archive_file.dump

Motivation:

Before performing a full restoration, it may be beneficial to review the contents of an archive file. This command allows you to list all database objects included in the archive, which is particularly useful for selective restoration or simply understanding the snapshot of the database at the time of backup.

Explanation:

• --list: This option lists all the objects contained in the archive.
• archive_file.dump: The archive file from which to list contents.

Example Output:

2615; 0 1663 DATABASE - my_database
2616; 0 1 SCHEMA public
2617; 0 0 TABLE public.categories
... (additional database objects)

Clean database objects before creating them

Code:

pg_restore --clean -d db_name archive_file.dump

Motivation:

This use case is valuable when you want to ensure a clean slate before restoration. Employing the --clean option instructs pg_restore to drop database objects before creating them, avoiding conflicts with existing objects. This is critical when the goal is to restore a database to a well-defined, clean state, ensuring consistency and removal of outdated or redundant data.

Explanation:

• --clean: Specifies that existing database objects should be dropped before restoring. This helps in avoiding duplicate data or object errors.
• -d db_name: The database being targeted for clean restoration.
• archive_file.dump: The archive file from which to restore.

Example Output:

pg_restore: dropping table "products"
pg_restore: dropping table "customer_orders"
pg_restore: restoring data for table "products"
pg_restore: finished with exit code 0

Use multiple jobs to do the restoring

Code:

pg_restore -j 2 -d db_name archive_file.dump

Motivation:

In cases where large databases need restoration, using multiple concurrent jobs can accelerate the process. The -j option allows parallel processing during restoration, making efficient use of available CPU resources and significantly reducing downtime during database recovery operations.

Explanation:

• -j 2: Specifies the number of parallel jobs to use. Here, two parallel processing jobs will be used to speed up the restoration.
• -d db_name: Indicates the database for the restoration.
• archive_file.dump: The data dump file to be restored.

Example Output:

pg_restore: using 2 parallel jobs
pg_restore: starting parallel restore
pg_restore: finished 2 of 8 items.
pg_restore: finished with exit code 0

Conclusion

The pg_restore command is a powerful and flexible tool for restoring PostgreSQL databases from backup files. Whether you are dealing with small-scale projects or managing enterprise-level systems, understanding its diverse capabilities allows you to tailor the restoration process to fit various technical and operational needs effectively. Exploring its options ensures you can manage database backups with confidence and precision.