How to Restore a PostgreSQL Database Snapshot
Joe Scharf
Updated
5 min read
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_dumpin one of the non-plaintext formats (specified by thepg_dump-fformat flag) - The snapshot format is automatically determined by
pg_restoreso you do not need to specify it on the command line. pg_restorehas 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_restorehas 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:
- Connects to database specified with
-dflag (This database must exist!) in order to issue the following SQL commands: - DROP DATABASE
<snapshot_database> - 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
-hfor host,-pfor port,-Ufor username, and-Wor-wfor password/no-password, respectively. - Enable verbose mode with
--verboseto 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.