How to use pg_rman do backup and restore for PostgreSQL

1. Overview

PostgreSQL is a very popular open-source relational database management system, and it is widely used in many different production environments. To maintain the production environment always functioning, you need to a lot tools, and one of the tools must to have been backup and restore. This blog is going to introduce one backup and restore tools designed for Postgres, i.e. pg_rman.

2. What is pg_rman

pg_ramn is a free utility program designed to backup and restore PostgreSQL database. It takes a physical online backup of whole database cluster, archive WALs, and server logs, and restore a specific backup when the Postgres is offline. pg_rman not only supports backup a Primary Postgres server, but also can get backup from a standby server. If you want to learn more about pg_rman, you can [check it out at] (https://github.com/ossc-db/pg_rman.git).

3.1. How to setup

Depends on the Postgres version you are running, in this blog, we will use the latest pg_rman tagged with V1.3.14 to demonstrate the backup and restore on PostgreSQL 14.

Now, assume you have the PostgreSQL 14 installed properly, then you can follow below steps to set up the Postgres server.

mkdir pgdata_rman
initdb  -D pgdata_rman

echo "archive_mode = on" >> pgdata_rman/postgresql.conf
echo "archive_command = 'cp %p /media/david/disk1/archive/%f'" >> pgdata_rman/postgresql.conf
echo "log_directory = '/media/david/disk1/pglog'" >> pgdata_rman/postgresql.conf
pg_ctl -D pgdata_rman -l pglog/logfile start

Then, we can check out the source code from github

1
2
3

git clone https://github.com/ossc-db/pg_rman.git
git checkout V1.3.14 -b local
make clean && make && make install

If no errors, then you have the pg_rman ready for the rest of the tests.

3.2. Backup and restore

Before running any backup and restore test, we need to initialize the backup for pg_rman to set the backup folder properly.

1 2	# Initialize backup pg_rman init -B /media/david/disk1/backup_rman -D /media/david/disk1/pgdata_rman

Once the backup folder has been initialized, then we can try to create a few tables and insert some data like below.

psql -d postgres -c "CREATE TABLE abc (ID INT);"
psql -d postgres -c "CREATE TABLE xyz (ID INT);"
psql -d postgres -c "INSERT INTO abc VALUES (1);"
psql -d postgres -c "INSERT INTO xyz VALUES (1);"
psql -d postgres -c "SELECT count(*) from abc;"
psql -d postgres -c "SELECT count(*) from xyz;"

Now, we can run our first backup with below commands,

1
2

#Do a backup
pg_rman backup --backup-mode=full --with-serverlog -B /media/david/disk1/backup_rman -D /media/david/disk1/pgdata_rman -A /media/david/disk1/archive -S /media/david/disk1/pglog -p 5432 -d postgres

here, we do a full backup to include everything, and with all the basic information for this full backup. pg_rman will ask you to valid the backup after each backup is done. So, to verify the backup we can simple run the command,

1 2	#Validate pg_rman validate -B /media/david/disk1/backup_rman

If the backup is valid, then we can insert some more data and run another backup. You can follow the steps below.

psql -d postgres -c "INSERT INTO abc VALUES (2);"
psql -d postgres -c "INSERT INTO xyz VALUES (2);"
psql -d postgres -c "SELECT count(*) from abc;"
psql -d postgres -c "SELECT count(*) from xyz;"

pg_rman backup --backup-mode=full --with-serverlog -B /media/david/disk1/backup_rman -D /media/david/disk1/pgdata_rman -A /media/david/disk1/archive -S /media/david/disk1/pglog -p 5432 -d postgres 

#Validate
pg_rman validate -B /media/david/disk1/backup_rman


#### Insert more data
psql -d postgres -c "INSERT INTO abc VALUES (3);"
psql -d postgres -c "INSERT INTO xyz VALUES (3);"


$ pg_rman show -B /media/david/disk1/backup_rman
=====================================================================
 StartTime           EndTime              Mode    Size   TLI  Status 
=====================================================================
2022-05-27 13:05:30  2022-05-27 13:05:32  FULL    51MB     1  OK
2022-05-27 13:05:28  2022-05-27 13:05:30  FULL    51MB     1  OK

Now, we have two full backups first one has one record in each table, the second one has two records in each table, and the second backup we have inserted the third record to each table. If you query these two tables now, you can see below results.

$ psql -d postgres -c "SELECT count(*) from abc;"
 count 
-------
     3
(1 row)

$ psql -d postgres -c "SELECT count(*) from xyz;"
 count 
-------
     3
(1 row)

Assume we made a mistake on the third operation, and let’s stop the PostgreSQL server and try to restore back to the second backup stage.

# Stop
pg_ctl -D pgdata_rman -l pglog/logfile stop
 
#Restore to target time
pg_rman restore -B /media/david/disk1/backup_rman -D /media/david/disk1/pgdata_rman --recovery-target-time="2022-05-27 13:05:32"


# Restart
pg_ctl -D pgdata_rman -l pglog/logfile start
$ psql -d postgres -c "SELECT count(*) from abc;"
 count 
-------
     2
(1 row)

$ psql -d postgres -c "SELECT count(*) from xyz;"
 count 
-------
     2
(1 row)

As you can see we back to the second backup stage, and each table has only two records.

3.3. Anything is missing

The pg_rman is a great free open-source tool for PostgreSQL users to backup and restore the database, however, there is one feature missed at this moment, i.e. targeted table restore. as a database administrator, one specific table restore can help reduce the risk on a production enrolment if only a single table need to be restored is a clear action.

4. Summary

In this blog, we discussed the basic of PostgreSQL backup restore using a free open-source tool, i.e. pg_rman, and hope this piece information can help if you are looking for a free backup and restore solution for PostgreSQL.