How to Restore a PostgreSQL Database Snapshot
5 min read
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.
- Restores a PostgreSQL database from a snapshot created by
pg_dumpin one of the non-plaintext formats (specified by the
- 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.
pg_restore -d "<url>/postgres" -j 8 <snapshot_directory_name> --create
-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 -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.
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:
|DROP all objects that will be restored. Suppress ignorable error messages if object doesn't exist with
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
- CREATE DATABASE
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.
|Table data, large objects, and sequence values are restored, if present in the archive.
|includes views, materialized views, sequences, and foreign tables. Multiple tables with multiple
-t flags. Could error if table dependencies not met.
|Restore the named index. Use multiple
-I flags for multiple indices.
|Restore the named trigger, Use multiple
-T flags for multiple triggers.
|Restore the named section, can be one of [
post-data]. Use multiple
--section flags for multiple sections.
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
- Execute the restore as a single transaction with
- Instead of the connection string URL, you can use the usual connection parameters of
-Ufor username, and
-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
The DBSnapper app integrates with the PostgreSQL
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.