How to Backup & Restore PostgreSQL Database

Database administrators need to often backup & restore database. It is an essential task of website and system administrators. In this article, we will learn how to backup & restore PostgreSQL database.

How to Backup & Restore PostgreSQL Database

Here are the steps to backup & restore PostgreSQL database.

1. How to Backup PostgreSQL database

PostgreSQL provides pg_dump utility that allows you to easily backup your database. It generates a .sql file which contains SQL queries to create databases, tables and insert data into them, so that it can be easily restored in future. We will look at two use cases for backing up PostgreSQL database backup small database and large database.

Log into PostgreSQL and switch to administrator user such as postgres.

After that, run the following command to take backup of your database sales_db. Replace sales_db with name of your database and db_backup.sql with the filename you want to give to the backup.

$ pg_dump sales_db > db_backup.sql

If you want to backup all databases on your system, use pg_dumpall utility.

$ pg_dumpall > all_databases.sql

You can also specify the file format of backup using -F option. Here are the 3 commands to take backup as tar or even folder.

# custom backup format
$ pg_dump -F c sales_db > sales_db.dump

# tar file format
$ pg_dump -F t sales_db > sales_db.tar

# backup as directory
$ pg_dump -F d sales_db -f sales_dumpdir

PostgreSQL will support all the above formats during restoration.

How to Backup Large PostgreSQL database

If your database is large, then your backup will also be large. So it is advisable to pass the output of pg_dump to a compression tool like gzip to reduce its size.

$ pg_dump sales_db | gzip > sales_db.gz

Also, you can tell pg_dump to do a parallel dump of specific number of tables at a time. In the following example, we dump 5 tables in parallel at a time.

$ pg_dump -F d sales_db -j 5 -f salesdumpdir

Please note, parallel dump is faster but more resource intensive on database server.

How to Backup Remote PostgreSQL database

If you want to backup remote databases, you need to mention connection parameters as the different options for pg_dump command. In the following example, remote database name is sales_db, username is fedingo, host IP is 54.43.32.21 and port is 5432. You can replace them with your connection values.

$ pg_dump -U fedingo -h 54.43.32.21 -p 5432 sales_db > sales_db.sql

2. How to Restore PostgreSQL database

You can use pg_restore or psql utility to restore PostgreSQL database. psql allows you to restore database from .sql files whereas pg_restore allows you to restore database from other file formats such as archives and directories.

Here is the command to restore database from .sql file.

$ psql sales_db < db_backup.sql

Here are the commands to restore PostgreSQL database from custom file formats.

$ pg_restore -d sales_db sales_db.dump
OR
$ pg_restore -d sales_db sales_db.tar
OR
$ pg_restore -d sales_db sales_dumpdir

If you want to restore a remote database, provide remote connection parameters as options for psql command.

$ pqsl -U fedingo -h 54.43.32.21 sales_db < db_backup.sql

3. Automate DB Backup

You can also automate database backups using cronjobs. Open crontab for this purpose.

$ crontab -e

Add the following line to it to take a database backup daily at 10.a.m your server time. You can customize the command as per your requirement.

0 10 * * * pg_dump -U postgres sales_db > db_backup.sql 2>&1

Save and exit the file.

In this article, we have learnt many different use cases to take backup of PostgreSQL database, as well as how to restore PostgreSQL database. We have also learnt how to automate database backups using cronjobs. You can customize them as per your requirement.

How to Backup & Restore PostgreSQL Database