How to Backup and Restore a PostgreSQL Database in Linux

Backup a Single PostgreSQL Database

In a production environment, no matter how large or small your PostgreSQL database may be, regular backup is an essential aspect of database management. In this article, we will learn how to take a backup and restore a PostgreSQL database.
Backing up and restoring a PostgreSQL database in Linux is a straightforward process using the command-line tools pg_dump for backups and psql for restoration.

pg_dump -U [username] [database_name] > [backup_file].sql

PostgreSQL allows you to create backups in a custom format, which can be useful for more complex backup and restore scenarios. Use the -F option to specify the format (c for custom):
Replace [username] with your PostgreSQL username, [database_name] with the name of the database you want to back up, and [backup_file] with the desired name for your backup file.

pg_dump -U [username] -F c [database_name] -f [backup_file].dump

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.

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

PostgreSQL allows you to create backups in a custom format, which can be useful for more complex backup and restore scenarios. Use the -F option to specify the format (c for custom):

$ pg_dump -F d tecmintdb -f tecmintdumpdir

$ pg_dumpall > all_pg_dbs.sql

For Example:

$ psql -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 -U postgres tecmintdb < tecmintdb.sql

As mentioned above, a custom-format dump is not a script for psql, 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

Compressed PostgreSQL Database Backup

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
$ pg_dump -F d -j 5 -f tecmintdumpdir

Backup Remote PostgreSQL Databases

Use the pg_dump command with the -h flag to specify the remote host, -p to specify the port, and -U to specify the username. You’ll also need to provide the name of the database you want to back up.
Replace [remote_host] with the hostname or IP address of the remote server, [port] with the port number of the PostgreSQL server (usually 5432), [username] with the username used to connect to the database, [database_name] with the name of the database you want to back up, and [backup_file] with the desired name for your backup file.

pg_dump -h [remote_host] -p [port] -U [username] [database_name] > [backup_file].sql

pg_dump -h example.com -p 5432 -U postgres mydatabase > mydatabase_backup.sql

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

$ 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.

How to Backup and Restore a PostgreSQL Database in Linux​