Restore Postgres Backup

Introduction

In this tutorial we learn how to restore a Postgres backup, whether the backup is of schemas, roles, or data. We’ll look at the command line utility called pg_restore and the web interface method provided by PGadmin to restore PostgreSQL databases, whether you want to backup only the schema, only the data, or both.

Prerequisites

  • A running install of the PostgreSQL relational database system.
  • Optional: visual database admin tools that provide functions for backup. In this article we show how to use the free pgAdmin tool, currently in version 4.x.
  • Not required, but useful for testing and practice: Your PostgreSQL database has some tables with data.

Why Backup Postgres?

  • Safety. Whether considering hardware failures, viruses, hacking, or bugs, no system is completely fault-free. We’ll assume you don’t want to lose your time spent designing, building, and filling a database. Consistent backups will save you in the event one of these things happens.
  • Rollbacks. Have you ever received an email from a user saying ooops they deleted or otherwise lost some version of the data and want to know if you can restore it? This is where you will be glad you made backups of your Postgres database. This is where the pg_restore function comes in handy.
  • Migrate your PostgreSQL database. When you want to migrate your Postgres database to another server, whether it is local or on a different host, one of the easiest ways to do this is to use pg_dump to export and then pg_restore to import the database to the new database location.

Postgres pg_dump

Postgres includes a utility to back up your database schema and/or data. There are quite a few parameter choices that allow you to define exactly what you want to back up, how, etc. The pg_dump command makes perfect backups even when Postgres is under heavy load. There is also no chance that the backup process will interrupt any of the typical read/write operations from users accessing the database.

Since pg_dump is so closely related to pg_restore, we’ll go briefly over the basics of pg_dump.

Postgres pg_dump example

There are quite a few ways to use pg_dump. We’ll focus here on two of the most common forms of schema backup and data backup. If you are doing a migration, you may want to do them separately, starting with a schema dump.

Postgres schema backup

pg_dump -s -d name_of_database > \mybackups\2019-12-19-schema.sql

Analysis

  • -s: The “-s” tells Postgres’ pg_dump to backup only the schema, meaning the tables, etc. but NOT the data. The “s” here is case sensitive. If you capitalize this to “-S”, it means “username is next parameter”.
  • -d: The “-d” tells pg_dump to use the next string as the name of the PostgreSQL database to be backed up. The “d” here is case sensitive.

Postgres data backup

Here’s how you back up the data (as opposed to the schema) from your Postgres database using the command line, since it’s pretty much self-explanatory how to do this using PGadmin.

pg_dump -d name_of_database -F t > \mybackups\2019-12-19-schema.tar

Analysis

  • -d: The “-d” tells pg_dump to use the next string as the name of the PostgreSQL database to be backed up. The “d” here is case sensitive.
  • -F: “-F” means format, “t” means use tar compression.

Postgres pg_restore

Postgres pg_restore syntax

pg_restore [connection] [options] [filename]

Postgres pg_restore example

pg_restore -d name_of_target_database \mybackups\2019-12-19-my-backup-file.tar -c

Analysis

  • -d: The “-d” tells Postgres to drop the existing objects of the target database before recreating from the backup file.
  • -c: The “-c” is for creating the database before restoring to it.

Here is a list of the options available for the current Postgres version 12 pg_restore utility:

C:\Program Files\PostgreSQL\12\bin>pg_restore -?
pg_restore restores a PostgreSQL database from an archive created by pg_dump.

Usage:
  pg_restore [OPTION]... [FILE]

General options:
  -d, --dbname=NAME        connect to database name
  -f, --file=FILENAME      output file name (- for stdout)
  -F, --format=c|d|t       backup file format (should be automatic)
  -l, --list               print summarized TOC of the archive
  -v, --verbose            verbose mode
  -V, --version            output version information, then exit
  -?, --help               show this help, then exit

Options controlling the restore:
  -a, --data-only              restore only the data, no schema
  -c, --clean                  clean (drop) database objects before recreating
  -C, --create                 create the target database
  -e, --exit-on-error          exit on error, default is to continue
  -I, --index=NAME             restore named index
  -j, --jobs=NUM               use this many parallel jobs to restore
  -L, --use-list=FILENAME      use table of contents from this file for
                               selecting/ordering output
  -n, --schema=NAME            restore only objects in this schema
  -N, --exclude-schema=NAME    do not restore objects in this schema
  -O, --no-owner               skip restoration of object ownership
  -P, --function=NAME(args)    restore named function
  -s, --schema-only            restore only the schema, no data
  -S, --superuser=NAME         superuser user name to use for disabling triggers
  -t, --table=NAME             restore named relation (table, view, etc.)
  -T, --trigger=NAME           restore named trigger
  -x, --no-privileges          skip restoration of access privileges (grant/revoke)
  -1, --single-transaction     restore as a single transaction
  --disable-triggers           disable triggers during data-only restore
  --enable-row-security        enable row security
  --if-exists                  use IF EXISTS when dropping objects
  --no-comments                do not restore comments
  --no-data-for-failed-tables  do not restore data of tables that could not be
                               created
  --no-publications            do not restore publications
  --no-security-labels         do not restore security labels
  --no-subscriptions           do not restore subscriptions
  --no-tablespaces             do not restore tablespace assignments
  --section=SECTION            restore named section (pre-data, data, or post-data)
  --strict-names               require table and/or schema include patterns to
                               match at least one entity each
  --use-set-session-authorization
                               use SET SESSION AUTHORIZATION commands instead of
                               ALTER OWNER commands to set ownership

Connection options:
  -h, --host=HOSTNAME      database server host or socket directory
  -p, --port=PORT          database server port number
  -U, --username=NAME      connect as specified database user
  -w, --no-password        never prompt for password
  -W, --password           force password prompt (should happen automatically)
  --role=ROLENAME          do SET ROLE before restore

The options -I, -n, -N, -P, -t, -T, and --section can be combined and specified
multiple times to select multiple objects.

If no input file name is supplied, then standard input is used.

Report bugs to <pgsql-bugs@lists.postgresql.org>.

Backup restore with PGadmin

If you want to use the free PGadmin tool for PostgreSQL to restore a backup, here’s how:

Step 1: Right click on your database. In this case, in the image below, you can see our database is called “practice”. From the context menu that comes up, choose “Restore”.

Restore Postgres Backup

Step 2: In the dialog box that comes up choose a format and filename.

Restore PostgreSQL Backup

Conclusion

In this tutorial we learned how to restore a Postgres backup, whether the backup is schemas, roles, or data. We’ll used the command line utility called pg_restore and the visual interface provided by PGadmin to restore PostgreSQL databases, whether you want to restore only the schema, only the data, or both. Backup and restore in Postgres are useful for many reasons, including safety, data rollback, and database migration.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.