PostgreSQL Backup and Restore Step by Step

If you are using PostgreSQL in a production environment, it is recommended to frequently take backups to ensure that your important data is not lost. With the backups, you will be able to quickly restore if your database is lost or corrupted. The good thing is, PostgreSQL includes tools to make database backup simple and easy to restore.

This tutorial will show you how to perform backups and restore procedures for your PostgreSQL databases.

Prerequisites

This tutorial assumes that you have a working installation of PostgreSQL on your system. The steps in this guide require root privileges so be sure to perform these steps as root or with the sudo prefix.

Backup Single Database

PostgreSQL comes with the builtin (pg_dump) utility that simplifies backing up a single database. Log in into your database server with a user that has read permissions to the database you intend to back up:

Create a backup directory and dump the contents of a database to a file in that directory by running the following command.

mkdir -p ~/backup_db
pg_dump exampleDB > /backup_db/exampleDB.bak

In the above example, we created a directory (backup_db) to store the (exampleDB.bak) locally. However, it is recommended to store your critical backup remotely over the network in a safe and secure place for later use.

There are several options for the backup format:

*.bak: compressed binary format
*.sql: plaintext dump
*.tar: tarball

Restore Single Database

The following section demonstrates restoring the lost data from the backup. For this guide, we will delete exampleDB, create an empty database called testDB, then restore database contents from the backup file:

dropdb exampleDB
createdb testDB

Restore the database using psql:

psql testDB < /backup_db/exampleDB.bak

Backup Database Remotely

PostgreSQL client utility (psql) allows you to run (pg_dump) from a client computer to take the database back up on a remote server like below:

pg_dump -h database_server_ip -p 5432 exampleDB > /backup_db/exampleDB.bak

Clustered Database Backup

The pg_dump utility only creates a backup of one database at a time, it does not store information about database roles or cluster-wide configuration. To backup such information including all of your databases simultaneously, you can use pg_dumpall. 

The following example will backup all databases at once:

pg_dumpall > /backup_db/all_databases.bak

Restore all databases at once from the backup:

psql -f /backup_db/all_databases.bak postgres

Automating Database Backup

If you wish, you can set up a cron job to automatically backup your database at regular intervals. The steps in this section will set up a cron task that will run pg_dump once every week. Make sure you perform these task as the postgres user:

su - postgres

Create a directory to store the automatic backups:

mkdir -p ~/postgres/backup_db

Edit the crontab to create the new cron task:

crontab -e

Add the following line to the end of the crontab:

0 0 * * 0 pg_dump -U postgres exampleDB > ~/postgres/backup_db/exampleDB.bak

Save and close the editor.

This cron job will automatically back up your database at midnight every Sunday.

Wrapping up

PostgreSQL offers more advanced procedures to back up your critical databases. This official documentation will help you to set up continuous archiving and point-in-time recovery. This method is quite complex, but in the end it will maintain a constant archive of your critical database and make it possible to recover the state of the database at any point in the past.