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

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.

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

Postgres pg_restore example

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:

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.