pg_rman: PostgreSQL Backup and Recovery Guide

pg_rman​ is an open-source utility designed for physical backups and recovery of PostgreSQL clusters. This guide covers installation, configuration, and hands-on workflows for safeguarding PostgreSQL databases.

1. ​Setup Requirements​

Ensure PostgreSQL 14 is installed and configure WAL archiving:

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  

‍

2. ​Install pg_rman​

Clone the repository and compile:

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

‍

3. ​Backup Workflow​

Initialize backups and create sample data:

psql -d postgres -c "CREATE TABLE abc (ID INT);"  
psql -d postgres -c "INSERT INTO abc VALUES (1);"  

‍

Run a full backup with WAL archiving:

pg_rman backup --backup-mode=full --with-serverlog -B /backup -D pgdata_rman  

‍

Validate the backup integrity:

pg_rman validate -B /backup  

‍

4. ​Recovery Process​

To recover to a specific timestamp:

pg_ctl -D pgdata_rman stop  
pg_rman restore -B /backup --recovery-target-time="2022-05-27 13:05:32"  
pg_ctl -D pgdata_rman start  

‍

Verify restored data:

psql -d postgres -c "SELECT COUNT(*) FROM abc;"  -- Returns 2 records  

‍

5. ​Key Limitations​

While pg_rman excels in full-cluster backups, it currently lacks ​table-level recovery​ functionality—a feature often requested for targeted restores.

6. ​Conclusion​

pg_rman simplifies PostgreSQL disaster recovery with its command-line interface and validation checks. For advanced use cases, consider integrating it with ​pgBackRest​ or ​Barman​ for enhanced flexibility.

Explore further optimizations in our guides on ​PostgreSQL high availability​ and ​WAL management best practices.

‍