Skip to content
Home » All Posts » A simple way to backup PostgreSQL with pg_rman

A simple way to backup PostgreSQL with pg_rman

in-memory postgresql database

Introduction


PostgreSQL, a powerful open-source relational database, is known for its robustness and extensibility. One critical aspect of database management is ensuring reliable backup and recovery processes. While PostgreSQL provides tools like pg_basebackup and WAL archiving for this purpose, there’s a powerful external tool called pg_rman that takes this process to the next level. In this blog post, we’ll explore what pg_rman is, how to perform a simple backup with pg_rman.

What is pg_rman?

pg_rman is an advanced backup and recovery manager designed specifically for PostgreSQL databases. Developed by the open source community, it provides a range of features to simplify the backup and restore process.

Key features:

backup with pg_rman in full and incremental mode
  1. Full backup
    pg_rman supports full backups, allowing you to capture the entire database at a specific point in time. This is crucial for disaster recovery and cloning databases for testing. For example,
$ pg_rman backup --backup-mode=full -B /path/to/backupdir -D /path/to/pgdatadir -d postgres --progress


Where,

-B /path/to/backupdir: Specifies the location where the backup will be stored.
-D /path/to/pgdatadir: Specifies the location where the original pgdata cluster need to be backed up.
–backup-mode=full: Indicates that this is a full backup.
-d postgres: Specifies the user to perform this backup.
–progress: Displays progress during the backup process.

  1. Incremental backup
    In addition to full backups, pg_rman offers the ability to perform incremental backups. This means you can save disk space and time by only backing up the changes since the last full or incremental backup. For example,
$ pg_rman backup --backup-mode=incremental -B /path/to/backupdir -D /path/to/pgdatadir -d postgres --progress


Where,

–backup-mode=incremental: Indicates that this is an incremental backup. pg_rman will only backup changes since the last full or incremental backup.

  1. Archive Log backup
    Managing WAL (Write-Ahead Logging) files is vital for point-in-time recovery. pg_rman seamlessly handles the archiving of WAL files, ensuring data integrity. For example,
$ pg_rman backup --backup-mode=archive -B /tmp/rman/backup -D /tmp/rman/pgdata  -d postgres --progress


Where,

–backup-mode=archive: pg_rman will backup archive WAL files only.

  1. Compression backup
    To optimize storage usage, pg_rman supports both compression and deduplication of backup data. This can be a game-changer for environments with limited storage resources. For example,
$ pg_rman backup --backup-mode=full -B /tmp/rman/backup -D /tmp/rman/pgdata -d postgres --progress --compress-data


Where,

–compress-data: Enables data compression during the backup process, helping to reduce storage requirements.

Getting started with pg_rman

Postgres preparation

To compile pg_rman from source code, it requires pg_config to identify the current Postgres installed on the same Server machine. In this blog, let’s use the latest stable Postgres release 16.1.

$ git clone https://github.com/postgres/postgres.git
$ cd postgres
$ git checkout REL_16_1 -b REL_16_1
$ ./configure --prefix=/media/david/disk1/pg161 --enable-tap-tests --enable-debug CFLAGS="-g3 -O0" CC="gcc -std=gnu99"
$ make -j && make install

To allow run pg_rman against the local Postgres Server, we need to change some minimum configuration parameters on Postgres Server before starting it up.

$ mkdir -p /tmp/rman/pglog/ /tmp/rman/archive
$ initdb -D /tmp/rman/pgdata
$ echo "archive_mode = on" >> /tmp/rman/pgdata/postgresql.conf
$ echo "archive_command = 'cp %p /tmp/rman/archive/%f'" >> /tmp/rman/pgdata/postgresql.conf
$ echo "log_directory = '/tmp/rman/pglog'" >> /tmp/rman/pgdata/postgresql.conf
$ pg_ctl -D /tmp/rman/pgdata -l /tmp/rman/pglog/logfile start

pg_rman installation

Installing pg_rman is straightforward. Here is a simple way to build it from source code. Detailed instructions can be found in the official documentation.

$ wget https://github.com/ossc-db/pg_rman/archive/refs/tags/V1.3.15.tar.gz
$ tar xzvf V1.3.15.tar.gz 
$ cd pg_rman-1.3.15/
$ make && make install

Initial backup

Before backup a Postgres database cluster, pg_rman requires to initiate the backup folder first with a command like below.

$ pg_rman init -B /tmp/rman/backup -D /tmp/rman/pgdata 
INFO: ARCLOG_PATH is set to '/tmp/rman/archive'
INFO: SRVLOG_PATH is set to '/tmp/rman/pglog'
$ pg_rman show -B /tmp/rman/backup
=====================================================================
 StartTime           EndTime              Mode    Size   TLI  Status 
=====================================================================

Backup and validate

Once we have created the initial backup, then we can run a full backup on the same backup folder.

$ pg_rman backup --backup-mode=full --with-serverlog -B /tmp/rman/backup -D /tmp/rman/pgdata -A /tmp/rman/archive -S /tmp/rman/pglog -p 5432 -d postgres
INFO: copying database files
INFO: copying archived WAL files
INFO: copying server log files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.

$ pg_rman show -B /tmp/rman/backup
=====================================================================
 StartTime           EndTime              Mode    Size   TLI  Status 
=====================================================================
2023-11-13 13:34:22  2023-11-13 13:34:24  FULL    49MB     1  DONE

$ pg_rman validate -B /tmp/rman/backup
INFO: validate: "2023-11-13 13:34:22" backup, archive log files and server log files by CRC
INFO: backup "2023-11-13 13:34:22" is valid

$ pg_rman show -B /tmp/rman/backup
=====================================================================
 StartTime           EndTime              Mode    Size   TLI  Status 
=====================================================================
2023-11-13 13:34:22  2023-11-13 13:34:24  FULL    49MB     1  OK

Incremental backup

After the full backup, we can create tables and insert some data, and then try the incremental backup with commands like below,

$ pg_rman backup --backup-mode=incremental --with-serverlog -B /tmp/rman/backup -D /tmp/rman/pgdata -A /tmp/rman/archive -S /tmp/rman/pglog -p 5432 -d postgres
INFO: copying database files
INFO: copying archived WAL files
INFO: copying server log files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.

$ pg_rman validate -B /tmp/rman/backup
INFO: validate: "2023-11-13 13:40:32" backup, archive log files and server log files by CRC
INFO: backup "2023-11-13 13:40:32" is valid

$ pg_rman show -B /tmp/rman/backup
=====================================================================
 StartTime           EndTime              Mode    Size   TLI  Status 
=====================================================================
2023-11-13 13:40:32  2023-11-13 13:40:34  INCR    34MB     1  OK
2023-11-13 13:34:22  2023-11-13 13:34:24  FULL    49MB     1  OK

Archive backup

Sometimes, you may want to backup WAL files only, then we can perform an archive backup.

$ pg_rman backup --backup-mode=archive --with-serverlog -B /tmp/rman/backup -D /tmp/rman/pgdata -A /tmp/rman/archive -S /tmp/rman/pglog -p 5432 -d postgres
INFO: copying archived WAL files
INFO: copying server log files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.

$ pg_rman validate -B /tmp/rman/backup
INFO: validate: "2023-11-13 13:43:13" archive log files and server log files by CRC
INFO: backup "2023-11-13 13:43:13" is valid

$ pg_rman show -B /tmp/rman/backup
=====================================================================
 StartTime           EndTime              Mode    Size   TLI  Status 
=====================================================================
2023-11-13 13:43:13  2023-11-13 13:43:14  ARCH   654MB     1  OK
2023-11-13 13:40:32  2023-11-13 13:40:34  INCR    34MB     1  OK
2023-11-13 13:34:22  2023-11-13 13:34:24  FULL    49MB     1  OK

Compression backup

If you want to reduce the backup size, then we can insert the same amount of data again and then backup with compression enabled. From the results below, we can see for the same amount of data, the backup size reduced from 654MB to 132MB.

$ pg_rman backup --backup-mode=archive --with-serverlog -B /tmp/rman/backup -D /tmp/rman/pgdata -A /tmp/rman/archive -S /tmp/rman/pglog -p 5432 -d postgres --compress-data
INFO: copying archived WAL files
INFO: copying server log files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.

$ pg_rman validate -B /tmp/rman/backup
INFO: validate: "2023-11-13 13:48:53" archive log files and server log files by CRC
INFO: backup "2023-11-13 13:48:53" is valid

$ pg_rman show -B /tmp/rman/backup
=====================================================================
 StartTime           EndTime              Mode    Size   TLI  Status 
=====================================================================
2023-11-13 13:48:53  2023-11-13 13:49:04  ARCH   132MB     1  OK
2023-11-13 13:43:13  2023-11-13 13:43:14  ARCH   654MB     1  OK
2023-11-13 13:40:32  2023-11-13 13:40:34  INCR    34MB     1  OK
2023-11-13 13:34:22  2023-11-13 13:34:24  FULL    49MB     1  OK

Conclusion

Ensuring the safety and availability of your PostgreSQL database is a top priority. pg_rman is a powerful tool that streamlines the backup process with more advanced features. I will come up with another simple blog to help go through the recovery process using pg_rman.

Tags:

Join the conversation

Your email address will not be published. Required fields are marked *