PostgreSQL pg_dump Backup and pg_restore Restore Guide

PostgreSQL is an open-source and one of the most widely used relational database systems. Backing up and restoring a PostgreSQL database is essential to any system administrator. PostgreSQL provides a pg_dump and pg_restore commands to back up and restores databases easily. This utility helps you to create a full, incremental and continuous backup locally or remotely.

This tutorial will show you step-by-step instructions for backup and restore PostgreSQL databases on Linux machines.

Table Of Contents

What is the pg_dump command?

PostgreSQL pg_dump is a database utility that helps you make automatic, consistent backups. For example, you can back up offline and online databases. The utility creates a set of SQL statements and processes them against the database instance to create a dump file that can use to restore the database later.

You can use the pg_dump command to backup your PostgreSQL database. Even if others are accessing the database, pg_dump will still back it up, and it will not block others from reading or writing to it.

The output of the pg_dump command will generate in the form of an archive file which allows the user to select the areas to be restored. Another format of pg_dump output is the script that contains SQL commands.

What is the pg_restore command?

The pg_restore command is utilized to restore a database from an archive generated by pg_dump (which produces and saves the data snapshot). This function will issue appropriate commands to reconstruct the contained database back into its initial state at the backup time.

Like other backup utilities, pg_restore connects the database to restore the data by taking many different arguments, which allow you to specify how you’d like it to conduct the restoration process. The main difference is that, rather than restoring everything from scratch, it will apply any necessary modifications to set up your database just as it was when you saved it.

pg_restore is a database utility for restoring a backup in an access-friendly format created by pg_dump in one of the non-plain text formats. The pg_restore create database will reconstruct the data according to how it was originally saved and allow access to that information.

How to do PostgreSQL pg_dump Backup

Requirements

  • A server running Linux operating system with PostgreSQL installed.

  • A root password is setup on your server.

Backup a Single PostgreSQL Database

You will need to use the pg_dump tool to backup a PostgreSQL database. This tool will dump all content of a database into a single file.

The basic syntax to backup a PostgreSQL database is shown below:

pg_dump -U [option] [database_name] > [backup_name]

A brief explanation of all available options is shown below:

  • -U : Specify the PostgreSQL username.

  • -W : Force the pg_dump command to ask for a password.

  • -F : Specify the format of the output file.

  • -f : Specify the output file.

  • p : Plain text SQL script.

  • c : Specify the custom formate.

  • d : Specify the directory format.

  • t : Specify tar format archive file.

For example, create a backup of the PostgreSQL database named db1 in the tar format, and run the following command:

pg_dump -U postgres -F c db1 > db1.tar

If you want to save the backup in a directory format, run the following command:

pg_dump -U postgres -F d db1 > db1_backup

If your database is extensive and wants to generate a small backup file, you can use pg_dump with a compression tool such as gzip to compress the database backup.

pg_dump -U postgres db1 | gzip > db1.gz

You can also reduce the database backup time by dumping number_of_jobs tables simultaneously using the -j flag.

pg_dump -U postgres -F d -j 5 db1 -f db1_backup

Note: Remember that the above command will reduce the backup time and increase the server's load.

Backup All PostgreSQL Databases

PostgreSQL provides a simple tool (pg_dumpall) to back up all your databases using a single command. This tool will dump all PostgreSQL databases of a cluster into one script file. It also dumps global objects that are common to all databases. Therefore, you will need superuser privileges to perform to dump all databases.

The basic syntax of the pgdump_all command is shown below:

pg_dumpall -f backupfile_name.sql

The above command will dump all databases to a single file named backupfile_name.sql.

Backup a Remote PostgreSQL Database

To perform the database backup on the remote PostgreSQL server, you must configure your PostgreSQL server to allow a remote connection.

The basic syntax to backup a remote PostgreSQL database is shown below:

pg_dump -h [remote-postgres-server-ip] -U [option] [database_name] > [backup_name]

For example, create a backup of the PostgreSQL database on the remote server ( 192.168.0.100 ) with the name remote_db1 in the tar format, and run the following command:

pg_dump -h 192.168.0.100 -U postgres -F c remote_db1 > remote_db1.tar

How to do PostgreSQL pg_restore

Restore a Single PostgreSQL Database

If you choose custom, directory, or archive format when taking a database backup, you need to use the pg_restore command to restore your database.

The basic syntax to restore a database with pg_restore is shown below:

pg_restore -U [option] [db_name] [db_backup]

A brief explanation of each option is shown below:

  • -c : Used to drop database objects before recreating them.

  • -C : Used to create a database before restoring it.

  • -e : Exit if an error has been encountered.

  • -F format: Used to specify the format of the archive.

For example, restore a backup from the file db1.tar, you will need to consider two options:

  1. If the database already exists.

  2. The format of your backup.

If your database already exists, you can restore it with the following command:

pg_restore -U postgres -Ft -d db1 < db1.tar

If your database does not exist, you can restore it with the following command:

pg_restore -U postgres -Ft -C -d db1 < db1.tar

Restore All PostgreSQL Databases

You can use psql command to restore all PostgreSQL databases.

The basic syntax to restore all databases is shown below:

psql -f [db_backup.sql]

For example, restore a backup from the backupfile_name.sql file, run the following command:

psql -f backupfile_name.sql

Restore a Remote PostgreSQL Database

The basic syntax to restore a remote PostgreSQL database is shown below:

pg_restore -h [remote-postgres-server-ip] -U [option] [database_name] < [backup_name]

For example, restore a database from the file remote_db1.tar on the remote server ( 192.168.0.100 ), run the following command:

pg_dump -h 192.168.0.100 -U postgres -Ft remote_db1 < remote_db1.tar

Schedule PostgreSQL Database Backup Automatically

You can also use cron jobs to perform backups at regular intervals. Cron jobs are used to schedule tasks at specified intervals on your server.

You can edit the cron jobs by running the following command:

crontab -e

Add the following lines at the end of the as per your requirements:

30 20 * * * pg_dump -U postgres your_db > /root/backup_db.sql

Save and close the file when you are finished.

The above jobs will run daily at 8:30 PM and create a backup file at /root/backup_db.sql.

Conclusion

The above guide taught you how to backup and restore the PostgreSQL database. I hope this will help you to perform day-to-day database operations. For more information, you can see the pg_dump and pg_restore reference pages.

What is the difference between pg_dump and Pg_dumpall?

Roles and other database objects, like tablespaces, are not dumped by pg_dump. Instead, pg_dumpall is preferable for backing up your PostgreSQL cluster as a whole. For example, you can handle the entire group with pg dumpall, which can backup data on roles, tablespaces, users, permissions, etc.

Does Pg_restore overwrite?

The pg_restore clean option will delete the old tables before creating the new ones. The table already exists error will appear if the clean option is not used, but pg_restore will proceed unless the exit-on-error option is used.

How do I check my Postgres backup status?

Check the arcserve PostgreSQL backup $DATE. Log file to find out the status of the PostgreSQL backup. This log file is created in the directory that the user specifies. Consult the PostgreSQL settings file for more details on configuring the directory.

Scheduled PostgreSQL Backup Service Backups SnapShooter

Backup one server, database, or application for free forever.

Learn more about PostgreSQL Backup Service Backups

Get started for free
No credit card required. Cancel anytime!

Was this page helpful?

Thank you for helping us improve!