How to restore a PostgreSQL backup

SimpleBackups founder

Laurent Lemaire

Co-founder, SimpleBackups

October 23rd, 2020

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.



Back to blog

Stop worrying about your backups.
Focus on building amazing things!

Free 7-day trial. No credit card required.

Have a question? Need help getting started?
Get in touch via chat or at [email protected]

Customer support with experts
Security & privacy first
Service that you'll love using