Summary: in this tutorial, you will learn how to restore a database by using PostgreSQL restore tools including
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:
psqlto restore plain SQL script file generated by
pg_restoreto restore tar file and directory format created by the
How to restore databases using psql
psqlutility allows you to restore SQL script file generated by the
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:
>psql -U username -f backupfile.sql
If you want to stop restoring database in case an error occurs, you use the following command:
>psql -U username --set ON_ERROR_STOP=on -f backupfile
Notice that we have added an additional option
If you backup specific database objects in a particular database, you can restore them using the following command:
>psql -U username -d database_name -f objects.sql
How to restore databases using pg_restore
psqltool, you can use
pg_restore program to restore databases backed up by the
pg_restore program, you have various options for restoration databases, for example:
pg_restoreallows you to perform parallel restores using the
-joption to specify the number of threads for restoration. Each thread restores a separate table simultaneously, which speeds up the process dramatically. Currently, the
pg_restoresupport this option for the only custom file format.
pg_restoreenables you to restore specific database objects in a backup file that contains the full database.
pg_restorecan 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
CREATE DATABASE newdvdrental;
You can restore the
tarfile format generated by the
pg_dump tool in the PostgreSQL backup database tutorial using the following command:
>pg_restore --dbname=newdvdrental --verbose c:\pgbackup\dvdrental.tar
If you restore the database, which is the same as the one that you backed up, you can use the following command:
>pg_restore --dbname=dvdrental --create --verbose c:\pgbackup\dvdrental.tar
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
CREATE DATABASE dvdrental_tpl;
Second, we can restore table structure only from the dvdrental.tar backup file by using the following command:
>pg_restore --dbname=dvdrental_tpl --section=pre-data c:\pgbackup\dvdrental.tar
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
First, drop the existing
DROP DATABASE dvdrental;
Second, create an empty
CREATE DATABASE dvdrental;
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
In this tutorial, we have shown you practical ways to restore databases by using PostgreSQL restore tools.
- http://www.postgresql.org/docs/9.2/static/app-pgrestore.html – pg_restore tool documentation