Online College Courses for Credit

Restore from Backup

Restore from Backup

Author: Sophia Tutorial

Identify a correctly composed command to restore a database from a backed up file using the command line.

See More

what's covered
This tutorial explores using pgsql or pg_restore to restore data from a backup in the command line in two parts:
  1. Getting Started
  2. Running A Query

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 be selective to identify what items are restored as well as the items to be restored.

First, we can explore the psql tool as it does allow you to restore from SQL scripts. By using the psql tool, you can execute the entire 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. There are other options similar to the pg_dump tool 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 like -d mydb to connect to.

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 specific 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 as well. Say we had the backup.tar file that has been created and it is in the same folder, we can restore the database by doing:

pg_restore -d mydb -f backup.tar

We do have a lot more options with the pg_restore that includes the following options. 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.

The psql and pg_restore commands will be able to restore the database.

Source: Authored by Vincent Tran