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
1 | 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.
1 | 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
1 | pg_restore [connection] [options] [filename] |
Postgres pg_restore example
1 | 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 | 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”.
Step 2: In the dialog box that comes up choose a format and filename.
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