How to Backup and Restore a PostgreSQL Database

In a production environment, no matter how large or small your PostgreSQL database may be, regular back is an essential aspect of database management. In this article, you will learn how to backup and restore a PostgreSQL database.

We assume that you already have a working installation of the PostgreSQL database system. If not, read our following articles to install PostgreSQL on your Linux distribution.

Let’s get started…

Backup a Single PostgreSQL Database

PostgreSQL provides the pg_dump utility to help you back up databases. It generates a database file with SQL commands in a format that can be easily restored in the future.

To back up, a PostgreSQL database, start by logging into your database server, then switch to the Postgres user account, and run pg_dump as follows (replace tecmintdb with the name of the database you want to backup). By default, the output format is a plain-text SQL script file.

$ pg_dump tecmintdb > tecmintdb.sql

The pg_dump supports other output formats as well. You can specify the output format using the -F option, where c means custom format archive file, d means directory format archive, and t means tar format archive file: all formats are suitable for input into pg_restore.

For example:

$ pg_dump -F c tecmintdb > tecmintdb.dump
OR
$ pg_dump -F t tecmintdb > tecmintdb.tar

To dump output in the directory output format, use the -f flag (which is used to specify the output file) to specify the target directory instead of a file. The directory which will be created by pg_dump must not exist.

$ pg_dump -F d tecmintdb -f tecmintdumpdir

To back up all PostgreSQL databases, use the pg_dumpall tool as shown.

$ pg_dumpall > all_pg_dbs.sql

You can restore the dump using psql as shown.

$ pgsql -f all_pg_dbs.sql postgres

Restoring a PostgreSQL Database

To restore a PostgreSQL database, you can use the psql or pg_restore utilities. psql is used to restore text files created by pg_dump whereas pg_restore is used to restore a PostgreSQL database from an archive created by pg_dump in one of the non-plain-text formats (custom, tar, or directory).

Here is an example of how to restore a plain text file dump:

$ psql tecmintdb < tecmintdb.sql

As mentioned above, a custom-format dump is not a script for pgsql, so it must be restored with pg_restore as shown.

$ pg_restore -d tecmintdb tecmintdb.dump
OR
$ pg_restore -d tecmintdb tecmintdb.tar
OR
$ pg_restore -d tecmintdb tecmintdumpdir

Backup Large PostgreSQL Databases

If the database you are backing up is large and you want to generate a fairly smaller output file, then you can run a compressed dump where you have to filter the output of pg_dump via a compression tool such as gzip or any of your favorite:

$ pg_dump tecmintdb | gzip > tecmintdb.gz

If the database is extremely large, you can dump in parallel by dumping number_of_jobs tables simultaneously using the -j flag, as shown.

$ pg_dump -F d -j 5 -f tecmintdumpdir

It is important to note that the parallel dump option reduces the time of the dump, but on the other hand, it also increases the load on the database server.

Backup Remote PostgreSQL Databases

pg_dump is a regular PostgreSQL client tool, it supports operations on remote database servers. To specify the remote database server pg_dump should contact, use the command-line options -h to specify the remote host and -p specifies the remote port the database server is listening on. Besides, use the -U flag to specify the database role name to connect as.

Remember to replace 10.10.20.10 and 5432 and tecmintdb with your remote host IP address or hostname, database port, and database name respectively.

$ pg_dump -U tecmint -h 10.10.20.10 -p 5432 tecmintdb > tecmintdb.sql

Ensure that the user connecting remotely has the required privileges to access the database, and the appropriate database authentication method is configured on the database server, otherwise, you will get an error like the one shown in the following screenshot.

It is also possible to dump a database directly from one server to another, use the pg_dump and psql utilities as shown.

$ pg_dump -U tecmint -h 10.10.20.10 tecmintdb | pqsl -U tecmint -h 10.10.20.30 tecmintdb

Auto Backup PostgreSQL Database Using a Cron Job

You can perform backups at regular intervals using cron jobs. Cron jobs are a commonly used means for scheduling various kinds of tasks to run on a server.

You can configure a cron job to automate PostgreSQL database backup as follows. Note that you need to run the following commands as the PostgreSQL superuser:

$ mkdir -p /srv/backups/databases

Next, run the following command to edit the crontab to add a new cron job.

$ crontab -e

Copy and paste the following line at the end of the crontab. You can use any of the dump formats explained above.

0 0 * * *  pg_dump  -U postgres tecmintdb > /srv/backups/postgres/tecmintdb.sql

Save the file and exit.

The cron service will automatically start running this new job without a restart. And this cron job will run every day at midnight, it is a minimum solution to the backup task.

For more information on how to schedule cron jobs, see: How to Create and Manage Cron Jobs on Linux

That’s it for now! It’s a good idea to make backing up data a part of your database management routine. To reach us for any questions or comments, use the feedback form below. For more information, see the pg_dump and pg_restore reference pages.

Tags: PostgreSQL Tips