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:
- 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.
- 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.
- 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.
- 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.
Hello, this is David, one of the authors at techbuddies.io. Currently working as a software architect at Highgo Software Canada, I enjoy tinkering with software and have huge curiosity for all things tech. With over 15 years experience in software technology, please allow me to be your go-to guide for navigating this digital universe. Find more blogs from me at highgo.ca
Pingback: Restore PostgreSQL with pg_rman - techbuddies.io
Pingback: Understanding Incremental Data Backup in pg_rman - techbuddies.io