PostgreSQL Restore Database

 Summary: in this tutorial, you will learn how to restore a database by using PostgreSQL restore tools including  pg_restore and psql.

Before restoring a database, you need to terminate all connections to that database and prepare the backup file. In PostgreSQL, you can restore a database in two ways:

• Using psqlto restore plain SQL script file generated by  pg_dump and  pg_dumpall tools.
• Using  pg_restore to restore tar file and directory format created by the  pg_dump tool

How to restore databases using psql

The psqlutility allows you to restore SQL script file generated by the pg_dump,  pg_dumpall or any other tools that generates compatible backed up files. By using the psql tool, you have to execute the entire script.

To restore a full backup and ignore any error occurred during the restoration process, you use the following command:

If you want to stop restoring database in case an error occurs, you use the following command:

Notice that we have added an additional option --set ON_ERROR_STOP=on

If you backup specific database objects in a particular database, you can restore them using the following command:

How to restore databases using pg_restore

Besides psqltool, you can use  pg_restore program to restore databases backed up by the  pg_dump or pg_dumpalltools. With  pg_restore program, you have various options for restoration databases, for example:

• The  pg_restore allows you to perform parallel restores using the  -j option to specify the number of threads for restoration. Each thread restores a separate table simultaneously, which speeds up the process dramatically. Currently, the  pg_restore support this option for the only custom file format.
• The  pg_restore enables you to restore specific database objects in a backup file that contains the full database.
• The  pg_restore can take a database backed up in the older version and restore it in the newer version.

Let’s create a new database named newdvdrentalfor practicing with the pg_restore tool.

You can restore the dvdrentaldatabase in tarfile format generated by the  pg_dump tool in the PostgreSQL backup database tutorial using the following command:

If you restore the database, which is the same as the one that you backed up, you can use the following command:

As PostgreSQL 9.2, you can use the  --section option to restore table structure only. This allows you to use the new database as the template for creating other databases.

First, you can create a new database named dvdrental_tpl.

Second, we can restore table structure only from the dvdrental.tar backup file by using the following command:

PostgreSQL restore databases using pgAdmin tool

If you want to run the  pg_restore via an intuitive user interface instead of the command line, you can use the  pgAdmin restore tool.

The following example demonstrates how to restore the dvdrentaldatabase from the  dvdrental.tar file

First, drop the existing dvdrentaldatabase:

Second, create an empty dvdrentaldatabase:

Third, choose the dvdrentaldatabase, right mouse click and choose the  Restore... menu item. A dialog that provides restore options displays.

Fourth, choose appropriate options such as backed up file, user, restore options, and click the Restorebutton to start restoring the database.

pgAdmin displays log messages in the Messagestab. If the restoration completed successfully, click the Done button to finish.

You can verify the restoration by checking the dvdrentaldatabase:

In this tutorial, we have shown you practical ways to restore databases by using PostgreSQL restore tools.

Further Reading

• http://www.postgresql.org/docs/9.2/static/app-pgrestore.html – pg_restore tool documentation