How to Backup and Restore a PostgreSQL Database on Linux

2023-06-28 08:30:08

In a production environment, no matter how big or small your database is PostgreSQL, regular backup is an essential aspect of database administration. 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, please read our following articles to install PostgreSQL in your Linux distribution.

Backup a single database PostgreSQL

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

To back up a database PostgreSQLlogin to your database server, then switch to user account from Postgres and run pg_dump as follows (replace linuxpartydb with the name of the database you want to back up). By default, the output format is a plain text SQL script file.

$pg_dump linuxpartydb > linuxpartydb.sql

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

For example:

$ pg_dump -F c linuxpartydb > linuxpartydb.dump
O
$ pg_dump -F t linuxpartydb > linuxpartydb.tar

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

$ pg_dump -F d linuxpartydb -f linuxpartydumpdir

To back up all databases de PostgreSQLuse the tool pg_dumpall as shown.

$ pg_dumpall > all_pg_dbs.sql

You can restore the dump using psql as shown.

$ psql -f all_pg_dbs.sql postgres

Restoring a PostgreSQL database

To restore a database PostgreSQLyou can use the utilities psql o pg_restore. psql used to restore text files created by pg_dump while pg_restore used to restore a PostgreSQL database from a file 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 linuxpartydb < linuxpartydb.sql

As mentioned above, a custom format dump is not a script to psqlso it must be restored with pg_restore as shown.

$ pg_restore -d linuxpartydb linuxpartydb.dump
O
$ pg_restore -d linuxpartydb linuxpartydb.tar
O
$ pg_restore -d linuxpartydb linuxpartydumpdir

Compressed PostgreSQL database backup

If the database you are backing up is large and you want to produce a much smaller output file, you can run a compressed dump where you have to filter the output of pg_dump through a tool compression such as gzip or any of your favorites:

$ pg_dump linuxpartydb | gzip > linuxpartydb.gz

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

$ pg_dump -F d -j 5 -f linuxpartydumpdir

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

Backup of remote PostgreSQL databases

pg_dump is a regular PostgreSQL client tool, it supports operations on remote database servers. To specify the remote database server to contact pg_dump, use the -h command line options to specify the remote host and -p specify the remote port on which the database server listens. Also, use the -U to specify the name of the database role to connect to.

remember to replace 10.10.20.10 y 5432 y linuxpartydb with the IP address or hostname of your remote host, the database port, and the database name, respectively.

$ pg_dump -U linuxparty -h 10.10.20.10 -p 5432 linuxpartydb > linuxpartydb.sql

Make sure that the user connecting remotely has the necessary privileges to access the database and that the proper database authentication method is configured on the database server; otherwise, you will get an error like the following screenshot.

PostgreSQL database connection error

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

$ pg_dump -U linuxparty -h 10.10.20.10 linuxpartydb | pqsl -U linuxparty -h 10.10.20.30 linuxpartydb

Automatic backup of PostgreSQL database using a cron job

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

You can set up a cron job to automate database backup PostgreSQL in the following way. Note that you must run the following commands as the PostgreSQL superuser:

$ mkdir -p /srv/backups/databases

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

$crontab -e

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

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

Save the file and close.

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 minimal solution for backup task.

For more information on how to schedule cron jobs, see: How to create and manage cron jobs on Linux

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