Restore from Backup

1. Getting Started

There are two commands that can be used to restore from database backups. The psql command will restore plain SQL script files that have been created by pg_dump and pg_dump tools. The pg_restore command is a utility that allows us to restore a PostgreSQL database from an archive that has been created using the pg_dump command using one of the non-plain text formats such as a tar. This command will execute the commands to reconstruct the database to the time in which the database backup was created. Within the archive file, the pg_restore has the ability to identify what items are restored as well as the items to be restored.

First, we will look at the psql tool. By using the psql tool, you can execute the entire SQL script at once. The command will look like the following:

psql -U adminrole -f backupfile.sql

This will log in using the admin role, although you will need to enter in the password and run the backupfile.sql to restore the data. Similar to the pg_dump tool, there are other options that can be passed in:

• The -a option will output all of the input lines to the standard output so you will be able to visually see the progress of the restore.
• The -d option will allow you to specify the database name to connect to, like -d mydb.
• The -W will force psql to prompt for a password if needed.

2. Running A Query

The pg_restore focuses on restoring databases that are in a non-text format created from the pg_dump or pg_dumpall tools. Using this command, you can specify database objects from a database file that contains full databases or individual databases. This tool can also take a backed-up database from an older version of a database and restore it in a new version.

For example, say we had a backup.tar file that had been created in the same folder. We can restore the database by doing:

pg_restore -d mydb -f backup.tar

We also have options with the pg_restore:

• The -a option will only restore the data, but not create the schema. This would assume that the schema has already been created.
• The -c option will clean/drop the database objects before they are recreated.
• The -C with an uppercase C will create the entire database before restoring it. If the -d database is used, it will drop the current database and recreate it before the restoring is done.
• The -f can pass the filename if we include the file name.
• The -s will only create the schema but not restore the data into the database.
• With the -t option, we can specify the table name to restore.