Taking Backups Using pgBackRest on a Replication Server

Backup and disaster recovery are essential components of any database management strategy. With PostgreSQL databases running on replication servers, ensuring data integrity and availability becomes even more critical. One of the tools that can help in achieving this is pgBackRest. This article will guide you through setting up and using pgBackRest for backups on a PostgreSQL replication server.

Installation of pgBackRest

Firstly, install the necessary repositories and pgBackRest package:

yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum -y install https://dl.fedoraproject.org/pub/epel/epel-release-latest-9.noarch.rpm
yum install -y pgbackrest

Configuring PostgreSQL for pgBackRest

Edit the postgresql.conf file to enable WAL (Write-Ahead Logging) archiving:

vi /var/lib/pgsql/12/data/postgresql.conf

Add or modify the following lines:

archive_mode = on
archive_command = 'pgbackrest -stanza=12_data archive-push %p'

Restart PostgreSQL:

systemctl restart postgresql-12

Configuring pgBackRest

On both the Master and Replica servers, edit the pgbackrest.conf file:

vi /etc/pgbackrest.conf

Add the following configurations:

[global]
repo1-host=Backup_Server_IP
repo1-host-user=postgres

[12_data]
pg1-path=/var/lib/pgsql/12/data/

Create backup directories and set permissions:

mkdir -p /pg_backup/12/data/pgbackrest
chown postgres:postgres /pg_backup/ /pg_backup/12 /pg_backup/12/data /pg_backup/12/data/pgbackrest

On the Backup Server, configure pgBackRest:

vi /etc/pgbackrest.conf

Add the following configurations:

[global]
repo1-path=/pg_backup/12/data/pgbackrest
repo1-retention-full=3
repo1-retention-diff=2
process-max=2
backup-standby=y

[12_data]
pg1-host=Master_Server_IP
pg1-path=/var/lib/pgsql/12/data/
pg1-user=postgres
pg2-host=Replica_Server_IP
pg2-path=/var/lib/pgsql/12/data/
pg2-user=postgres

Set up password-less SSH connections between servers:

su postgres
ssh-keygen -t rsa
ssh-copy-id -i /var/lib/pgsql/.ssh/id_rsa.pub postgres@Backup_Server_IP
ssh postgres@Backup_Server_IP

Starting Backups

Verify the pgBackRest configurations:

pgbackrest --stanza=12_data stanza-create --log-level-console=info
pgbackrest --stanza=12_data check --log-level-console=info

Begin the backup process:

pgbackrest --stanza=12_data backup --log-level-console=info

Additional Backup Types

Full Backup:

pgbackrest --stanza=12_data --type=full backup

Differential Incremental Backup:

pgbackrest --stanza=12_data --type=diff backup

Cumulative Incremental Backup:

pgbackrest --stanza=12_data --type=incr backup

Restoring Data

Restore entire database:

pgbackrest --stanza=12_data --type=restore

Point-in-Time Recovery:

pgbackrest --stanza=12_data --type=time --target='2023-04-29 12:45:00' restore

Restore to the same machine:

pgbackrest --stanza=12_data --type=restore

Restore to a different machine:

pgbackrest --stanza=12_data --reset-pg-host --type=restore

Conclusion

pgBackRest offers a robust and efficient solution for backing up PostgreSQL databases, especially in replication environments. By following the steps outlined in this article, you can ensure the safety and availability of your data on replication servers. Always remember to test your backup and restore processes regularly to maintain data integrity and readiness for any unforeseen events. For more detailed and technical articles like this, keep following our blog on Medium. If you have any questions or need further assistance, feel free to reach out in the comments below and directly.