How to use the command 'pg_restore' (with examples)
The ‘pg_restore’ command is used to restore a PostgreSQL database from an archive file created by the ‘pg_dump’ command. It allows users to easily restore databases, either into an existing database or by creating a new one.
Use case 1: Restore an archive into an existing database
Code:
pg_restore -d db_name archive_file.dump
Motivation: This use case is useful when you have an existing database and want to restore it to a previous state using an archive file. It allows you to easily revert any changes made to the database and restore it to a specific point in time.
Explanation:
- -d db_name: Specifies the name of the target database where the restore will be performed.
- archive_file.dump: The path to the archive file that contains the database dump.
Example output:
pg_restore: connecting to database for restore
pg_restore: creating SCHEMA public
pg_restore: creating COMMENT SCHEMA public
...
pg_restore: restoring data for table "employees"
pg_restore: restoring data for table "departments"
pg_restore: applying PL/pgSQL function xyz()
pg_restore: restoring data for table "products"
...
pg_restore: process successfully completed
Use case 2: Same as above, customize username
Code:
pg_restore -U username -d db_name archive_file.dump
Motivation: When the database requires authentication, customizing the username allows you to provide the correct credentials to access the database. This is especially useful in multi-user environments or when connecting to a remote database.
Explanation:
- -U username: Specifies the username to use for authentication.
- -d db_name: Specifies the name of the target database where the restore will be performed.
- archive_file.dump: The path to the archive file that contains the database dump.
Example output:
pg_restore: connecting to database for restore
pg_restore: creating SCHEMA public
pg_restore: creating COMMENT SCHEMA public
...
pg_restore: restoring data for table "employees"
pg_restore: restoring data for table "departments"
pg_restore: applying PL/pgSQL function xyz()
pg_restore: restoring data for table "products"
...
pg_restore: process successfully completed
Use case 3: Same as above, customize host and port
Code:
pg_restore -h host -p port -d db_name archive_file.dump
Motivation: In situations where the PostgreSQL server is running on a specific host and port, customizing these options allows you to connect to the correct server and restore the database.
Explanation:
- -h host: Specifies the host where the PostgreSQL server is running.
- -p port: Specifies the port on which the PostgreSQL server is listening.
- -d db_name: Specifies the name of the target database where the restore will be performed.
- archive_file.dump: The path to the archive file that contains the database dump.
Example output:
pg_restore: connecting to database for restore
pg_restore: creating SCHEMA public
pg_restore: creating COMMENT SCHEMA public
...
pg_restore: restoring data for table "employees"
pg_restore: restoring data for table "departments"
pg_restore: applying PL/pgSQL function xyz()
pg_restore: restoring data for table "products"
...
pg_restore: process successfully completed
Use case 4: List database objects included in the archive
Code:
pg_restore --list archive_file.dump
Motivation: When restoring a database, it can be helpful to know which objects are included in the archive file. This use case allows you to preview the database objects that will be restored without actually performing the restore operation.
Explanation:
- –list: Lists the database objects included in the archive file.
- archive_file.dump: The path to the archive file that contains the database dump.
Example output:
Table "public.employees"
Table "public.departments"
Function "public.xyz()"
Table "public.products"
...
Use case 5: Clean database objects before creating them
Code:
pg_restore --clean -d db_name archive_file.dump
Motivation: When restoring a database, there may be existing objects with conflicting names. Adding the ‘–clean’ option ensures that any existing objects with the same names are dropped before restoring the database objects from the archive file.
Explanation:
- –clean: Drops existing objects before restoring the database.
- -d db_name: Specifies the name of the target database where the restore will be performed.
- archive_file.dump: The path to the archive file that contains the database dump.
Example output:
pg_restore: connecting to database for restore
pg_restore: dropping existing objects
pg_restore: creating SCHEMA public
pg_restore: creating COMMENT SCHEMA public
...
pg_restore: restoring data for table "employees"
pg_restore: restoring data for table "departments"
pg_restore: applying PL/pgSQL function xyz()
pg_restore: restoring data for table "products"
...
pg_restore: process successfully completed
Use case 6: Use multiple jobs to do the restoring
Code:
pg_restore -j 2 -d db_name archive_file.dump
Motivation: When restoring a large database, using multiple parallel jobs can significantly reduce the restoration time. This use case specifies the number of concurrent jobs to be used in the restore process.
Explanation:
- -j 2: Specifies the number of parallel jobs to be used for restoring.
- -d db_name: Specifies the name of the target database where the restore will be performed.
- archive_file.dump: The path to the archive file that contains the database dump.
Example output:
pg_restore: connecting to database for restore
pg_restore: creating SCHEMA public
pg_restore: creating COMMENT SCHEMA public
...
pg_restore: restoring data for table "employees" (1 of 2 jobs)
pg_restore: restoring data for table "departments" (2 of 2 jobs)
pg_restore: applying PL/pgSQL function xyz() (1 of 2 jobs)
pg_restore: restoring data for table "products" (2 of 2 jobs)
...
pg_restore: process successfully completed
Conclusion:
The ‘pg_restore’ command provides a convenient way to restore PostgreSQL databases from archive files created with ‘pg_dump’. It allows users to easily customize various options such as the target database, username, host, port, and the number of parallel jobs. Whether you need to restore a database into an existing one, clean existing objects, or preview the objects in an archive, ‘pg_restore’ offers the flexibility to achieve these tasks efficiently.
