How to Backup & Restore PostgreSQL Database

Protecting the integrity of databases is essential for organizations of all kinds in the data-driven world of today. An open-source, dependable relational database management system called PostgreSQL is widely used in many different sectors. Knowing how to backup and restore a PostgreSQL database is a key skill that protects us from data loosing whether we are a novice or an expert database administrator.

Let us discuss about how to restore and back up the PostgreSQL databases. We will explain the key principles, approaches, and best practices to ensure the security and availability of our data.

PostgreSQL Installation on Ubuntu

$ sudo apt update

Install the most recent PostgreSQL version from the Ubuntu default repository.

$ sudo apt install postgresql

The configuration of PostgreSQL is stored in the “/etc/postgresql/12/main” directory, while the default data location is “/var/lib/postgresql/12/main”.

$ sudo systemctl is-active postgresql

$ sudo systemctl is-enabled postgresql

$ sudo systemctl status postgresql

Also, validate that the PostgreSQL server is ready to accept the client connections by doing the following:

$ sudo pg_isready

After that, we can create the database and configure the database.

Creating a Backup of a Single PostgreSQL Database

The “pg_dump” program from PostgreSQL makes it easy to back up the databases. With the use of SQL commands, it creates a database file that can later be readily recovered.

Next, log in as a Postgres user and execute “pg_dump” as shown in the following:

$ pg_dump souvikdb > souvikdb.sql

Other output formats are also supported by “pg_dump”:

-F: Output format

“c”: Custom format archive file

“t”: Tar format archive file

All formats are compatible with “pg_restore”.

Let us see an example:

$ pg_dump -F c souvikdb > souvikdb.dump

$ pg_dump -F t souvikdb > souvikdb.tar

The “-f” options help to save the output.

$ pg_dump -F d souvikdb -f souvikdumpdir

We may restore the dump using “psql” as demonstrated in the following:

$ psql -f all_pg_dbs.sql postgres

Recovering or Restoring a PostgreSQL Database

To restore a PostgreSQL database, we can use the “psql” or “pg_restore” commands. The “psql” command recovers the text files created by “pg_dump”, but “pg_restore” recovers a PostgreSQL database from a non-plain-text archive prepared by “pg_dump” (custom, tar, or directory).

The following is an illustration on how to recover a plain text file dump:

$ psql souvikdb < souvikdb.sql

A custom-format dump must be restored using “pg_restore” as demonstrated because, as already mentioned, it is not a script for “psql”. Here, “d” means “directory” format archive:

$ pg_restore -d souvikdb souvikdb.dump

Taking Backup of Large PostgreSQL Database

We can perform a compressed dump by filtering the output of “pg_dump” through a compression tool like gzip or any of our favourites if the database which we are backing up is huge and we want to create a somewhat smaller output file. So, use the following format:

$ pg_dump souvikdb | gzip > souvikdb.gz

Remote PostgreSQL Database Backup

The “pg_dump” is a standard PostgreSQL client tool that allows us to perform the operations on remote database servers. In addition, use the -U parameter to indicate the database role to connect as:

Replace the 30.10.20.10 hostip or hostname.

Replace 5232 with database port.

Replace the “souvikdb” database name.

$ pg_dump -U souvik -h 30.10.20.10 -p 5232 souvikdb > souvikdb.sql

Utilizing the “pg_dump” and “psql” utilities as demonstrated. It is also feasible to directly dump a database from one server to another.

$ pg_dump -U souvik -h 30.10.20.10 souvikdb | pqsl -U souvik -h 30.10.20.30 souvikdb

Using a Cron Job to Auto Backup a PostgreSQL Database

Using cron tasks, we can schedule the backups at regular intervals. Cron jobs are a popular method to schedule various types of tasks to execute on a server.

The following cron job can be used to automate the PostgreSQL database backup. It should be noted that the commands that follow must be run as the PostgreSQL superuser:

$ mkdir -p /srv/backups/databases

$ crontab -e

This new service is started automatically by the cron service without requiring a restart.

It automatically takes the backup of our important files.

Conclusion

Integrity protection for PostgreSQL databases is essential. A reliable backup and restore plan are necessary. We can successfully handle the backup and restore the difficulties with the help of this guide. Data availability and recoverability are ensured through routine backups, strategic planning, and adherence to best practices to investigate the PostgreSQL backups, rely on tools and methods, and improve our data management.