How to restore a PostgreSQL backup

Introduction

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.

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_useris 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_useris 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.



Back to blog

Ready to automate your backups?

Sign up for FREE. Get started in less than one minute.

Secure your backups

No credit card required. Free 7-day trial.