PostgreSQL is an open-source and one of the most widely used relational database system. Backing up and restoring a PostgreSQL database is an essential part of any system administrator. PostgreSQL provides a pg_dump and pg_restore utility to backup 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 instruction of how to backup and restore PostgreSQL databases on Linux machines.
- 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 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 option is shown below:
- -U : Specify the PostgreSQL username.
- -W : Force 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, 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 very large and wants to generate a small backup file then 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 : Also keep in mind that the above command will reduce the time of the backup but it also increases the load on the server.
# Backup All PostgreSQL Databases
PostgreSQL provides a simple tool (pg_dumpall) to back up your all 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. You will need superuser privileges to perform to dump all databases.
The basic syntax of 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
In order to perform the database backup on the remote PostgreSQL server. You will need to configure your PostgreSQL server to allow 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 name remote_db1 in the tar format, run the following command:
pg_dump -h 192.168.0.100 -U postgres -F c remote_db1 > remote_db1.tar
# Restore a Single PostgreSQL Database
If you choose custom, directory, or archive format when taking a database backup. Then, you will need to use 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 into 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:
- If the database already exists.
- 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 is not exists, 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 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 the specified intervals on your server.
You can edit the cron jobs by running the following command:
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 every day at 8:30 PM and create a backup file at /root/backup_db.sql.
In the above guide, you learned how to backup and restore 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.