How to Backup and Restore a PostgreSQL Database

Introduction

Before we begin talking about how to backup and Restore a PostgreSQL database, let's briefly understand - What is PostgreSQL?

PostgreSQL is also known as Postgres is an open-source and free relational database, it is mainly used as a data warehouse of many web, mobile, and geospatial and analytics applications.

PostgreSQL supports multiple platforms and has gained popularity in various industries due to its robustness and ability to handle complex workloads. With its advanced capabilities, PostgreSQL remains a top choice for organizations looking for a flexible and reliable database solution.

In this tutorial, you will backup and restore a Postgres database. We will also address a few FAQs on how to backup and restore a Postgres database.

Backup a Single PostgreSQL Database

Postgres has a backup utility that can take the backup even if the database is being used currently. It doesn't block other users (readers or writers) from accessing the database. It generates a database file with which consists of SQL command in a format that can be restored easily.

1) Firstly, log in to your Postgres database server and then switch to a PostgreSQL account and run pg_dump. You will get a plain-text SQL script file.

pg_dump your_database_name > file_name.sql

2) You can get the output in many other ways. For that, you need to use -F option. Refer to the below example to get an idea:

pg_dump -F c your_database_name > file_name.dump
OR
pg_dump -F t your_database_name > file_name.tar

Here, -c mean custom format archive file, -d means directory archive file, and -t means tar format archive file. All these formats are compatible with the input into pg_restore.

3) -f flag is used to dump output in the directory output format. Make sure that the directory created by pg_dump must not exist.

pg_dump -F d your_database_name -f vegadumpdir

4) Use the pg_dumpall tool to back up all PostgreSQL databases:

pg_dumpall > all_pg_dbs.sql

5) Now, restore the dump using psql as shown below:

psql -f all_pg_dbs.sql postgres

Restoring a PostgreSQL Database

There are 2 utilities (psql or pg_restore) that can be used for restoring the PostgreSQL database. If you want to restore the text files created by pg_dump, use psql on the other hand pg_restore is used when you need to restore Postgres data from the archive created by pg_dump.

1) Follow the below example to see how can you restore a plain text file dump:

psql your_database_name < file_name.sql

2) As you are already aware that the custom-format dump is not the script for pgsql, you can restore it with pg_restore as shown below:

pg_restore -d your_database_name file_name.dump
OR
pg_restore -d your_database_name file_name.tar
OR
pg_restore -d your_database_name vegadumpdir

Backup Large PostgreSQL Databases

1) Many times when you want to restore the database which is very large, and you want a small output file in return, then you have to run a compress dump where you can filter the output of pg_dump via a compression tool like gzip:

pg_dump your_database_name | gzip > file_name.gz

2) In case the database is extremely large, you can dump the number_of_jobs in parallel using the -h flag:

pg_dump -F d -j 5 -f vegadumpdir

Please note that parallel dumping reduces the time of dump, but on the other hand it also increases the load on the database server.

Backup Remote PostgreSQL Databases

If you want to take the backup of the remote Postgres database, then you need to use -h to specify the remote host, -p for specifying the host and -U to specify the database role name to connect as.

1) Make sure to replace 10.10.20.10, 5432, and your_database_name with your remote host IP address or hostname, database port, and database name respectively:

pg_dump -U vegastack -h 10.10.20.10 -p 5432 your_database_name > file_name.sql

2) Make sure that the user who is connecting remotely has access to the database and there is an appropriate database authentic method in place, otherwise you will get a PostgreSQL Database Connection error.

3) If you want to dump the database directly from one server to another, you can use the pg_dump and psql utilities as shown below:

pg_dump -U vegastack -h 10.10.20.10 your_database_name | pqsl -U vegastack -h 10.10.20.30 your_database_name

Using a Cron Job for Auto Backup of PostgreSQL

If you want to take backups at a regular interval, then you can use corn jobs. You can schedule various tasks on a server using a cron job.

1) Run the below command as a PostgreSQL superuser to configure a cron job to automate the backup:

mkdir -p /srv/backups/databases

2) After that, run the below command to edit the crontab and a new cron job:

crontab -e

3) You can use the dump format of your choice and run the below command:

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

4) Save and exit.

5) Now that the cron job is set up, it will automatically run the job without any restart. Now, this cron job will run every day at midnight.

It is very important to back up your data from time to time without fail and make it a part of your database management routine.

FAQs to Backup and Restore a PostgreSQL Database

Can I backup a database while it is being used?

Yes, you can backup a database while it is being used. However, it's recommended to use the pg_dump command with the --lock-wait-timeout option to avoid potential locking issues.

How do I restore a PostgreSQL database from a backup?

To restore a PostgreSQL database from a backup file created with pg_dump, you can use the pg_restore command. For example: pg_restore -U username -h hostname -p port -d database_name backup.sql.

Is it possible to restore a database with a different name?

Yes, you can restore a PostgreSQL database with a different name. Simply specify the new database name using the -C or --create option when running the pg_restore command.

Can I restore a database to a different PostgreSQL version?

It's recommended to restore a database backup to the same or a compatible PostgreSQL version. Restoring to a different major version may cause compatibility issues or data loss. Always check the PostgreSQL documentation for compatibility guidelines.

How often should I backup my PostgreSQL database?

The frequency of backups depends on your specific needs and the rate of data changes. However, it's good practice to schedule regular backups, preferably daily or hourly for critical systems, ensuring minimal data loss in case of failures.

Can I automate the backup process?

Certainly! Automating backups is highly recommended. You can create scripts or use tools like pg_dump, pgBackRest, or Barman to schedule and automate the backup process efficiently.

How long should I retain my database backups?

The retention period for database backups depends on your organization's policies and requirements. Consider factors like data sensitivity, compliance regulations, and the resources available for storage. Retaining backups for at least a few days or weeks is generally recommended.