How to restore a PostgreSQL backup

There are different types of database aside from the commonly used MySQL database. You might work on a project that uses another type of database like MongoDB or PostgreSQL

In this article, we will tackle how to import or restore a PostgreSQL backup using two different ways.

Table of Contents

Prerequisites

• PostgreSQL installed
• PostgreSQL user credentials
• A PostgreSQL backup file; if you don't already have one, SimpleBackups provides an automated PostgreSQL backup
• An existing PostgreSQL database

How to restore a PostgreSQL backup

There are two ways to restore a PostgreSQL database:

1. psql - for restoring from a plain SQL script file that is created using pg_dump
2. pg_restore for restoring from a .tar file, directory, or custom format created using pg_dump

Restore a database with psql

1. Create a new database where you will restore your backup, or use an existing database.

2. Run the following command in your terminal:

   psql -U db_user db_name < dump_name.sql

   where db_user is the database user, db_name is the database name, and dump_name.sql is the name of your backup file.

Restore a database with pg_restore

If you choose custom, directory, or archive format when creating a backup file, then you will need to use pg_restore in order to restore your database.

To restore your backup, run the following command in your terminal:

pg_restore -d db_name /path/to/your/file/dump_name.tar -c -U db_user

where db_user is the database user, db_name is the database name, and /path/to/your/file/dump_name.tar is the full path of your backup file.

Using pg_restore provides you various options, for example:

• -c to drop database objects before recreating them,
• -C to create a database before restoring into it,
• -e exit if an error has encountered,
• -F format to specify the format of the archive.

Use pg_restore -? if you want to get the full list of available options.