How to Restore a PostgreSQL Database Snapshot

The PostgreSQL pg_restore utility is used to restore a point-in-time database backup, also known as a database snapshot created by the pg_dump utility (previously discussed here). In this guide, we will dive into how pg_restore works and the different ways it can be used, depending on your needs.

pg_restore highlights

• Restores a PostgreSQL database from a snapshot created by pg_dump in one of the non-plaintext formats (specified by the pg_dump -f format flag)
• The snapshot format is automatically determined by pg_restore so you do not need to specify it on the command line.
• pg_restore has two main ways of working, which depend on whether you include a database name or not. We'll go over these two methods in the next sections.
• pg_restore has the ability to selectively restore parts of a database snapshot, allowing you to backup everything, and restore only what you need.

The `pg_restore`` command with recommended flags

To restore a database snapshot created by the , you can use the following command:

pg_restore -d "<url>/postgres" -j 8 <snapshot_directory_name> --create

Description of pg_restorecommand-line flags

-d "<url>" Specifies the destination database as a connection string in URL format. This is a convenient way to specify all connection information in a single parameter. Note: we're telling pg_restore to connect to the default postgres database, which will be needed for the --clean flag to work properly.

-j 8 Indicates that we want to use parallelization to speed up the restore of the database snapshot. In this case, we are using eight (8) processes/connections to the server. This command-line flag only supports custom and directory snapshot formats.

--create As detailed in the Database existence section below, this flag will establish a connection to the database specified with the -d flag and only use it to issue DROP DATABASE and CREATE DATABASE commands to create the database specified in the snapshot archive.

pg_restore Example:

pg_restore -d "postgres://user:pass@host:port/postgres?sslmode=[require | disable]" -j 8 snapshot_directory --create

This example will connect to the default postgres database and use that connection to --create the database specified in the snapshot archive.

Database existence

pg_restore is versatile and doesn't always require an existing database to function. It can be used to inject data into an already established database or to create a new database from a snapshot. The following table discusses some of these options:

Option	Flags	Notes
Clean	-c --clean	DROP all objects that will be restored. Suppress ignorable error messages if object doesn't exist with --if-exists
Create	-C --create	Create the snapshot_database specified in the snapshot prior to restoring. Also specify --clean to overwrite any existing objects. (See note below)
Database name provided		Restores snapshot directly into the specified database name
Database name omitted		Creates a SQL script with the commands needed to restore the database. Can be written to a file or stdout

Note: The --create flag does the following:

1. Connects to database specified with -d flag (This database must exist!) in order to issue the following SQL commands:
2. DROP DATABASE <snapshot_database>
3. CREATE DATABASE <snapshot_database>

Selective restores

With pg_restore you have the ability to selectively restore parts of the database snapshot. For this reason, it is often recommended to snapshot the entire database with pg_dump and then use pg_restore to selectively restore the parts you're interested in.

Part	Flags	Notes
Schema	-s --schema-only
Data	-a --data-only	Table data, large objects, and sequence values are restored, if present in the archive.
Table	-t table --table=table	includes views, materialized views, sequences, and foreign tables. Multiple tables with multiple -t flags. Could error if table dependencies not met.
Index	-I index --index=index	Restore the named index. Use multiple -I flags for multiple indices.
Trigger	-T trigger --trigger= trigger	Restore the named trigger, Use multiple -T flags for multiple triggers.
Section	--section= section_name	Restore the named section, can be one of [pre-data, data, or post-data]. Use multiple --section flags for multiple sections.

Other pg_restore flags

There are a number of other flags supported by pg_restore which will allow you to have more control over the restore process. A quick mention of some of these include:

• Managing the restore of privileges with --no-privileges, --no-owner, and --enable-row-security.
• Execute the restore as a single transaction with --single-transaction.
• Instead of the connection string URL, you can use the usual connection parameters of -h for host, -p for port, -U for username, and -W or -w for password/no-password, respectively.
• Enable verbose mode with --verbose to have progress messages output to standard error. Repeat the flag to have additional debug-level messages reported as well.
• Prevent duplicate or obsolete data from being loaded into a direct-to-database restore with --no-data-for-failed-tables

DBSnapper simplifies database snapshot management

The DBSnapper app integrates with the PostgreSQL pg_dump and pg_restore commands and simplifies the process of creating, sanitizing, and restoring database snapshots. When combined with the DBSnapper Cloud, management of your snapshots is much easier with private cloud storage profiles and team sharing options. Install the DBSnapper Agent to get started, and Sign up for the DBSnapper Cloud today!

If you have any questions or feedback on this article, please get in touch on our contact page.

---

References

• pg_restore command - PostgreSQL Documentation
• pg_dump command - PostgreSQL Documentation