First steps with pgBackRest, a backup solution for PostgreSQL

pgBackRest is an open source backup tool that creates physical backups with some improvements compared to the classic pg_basebackup tool. pg_basebackup is included in the PostgreSQL binaries, and it offers a great set of features for hot binary backups, remote backups, and standby building, etc.

pgBackRest is a solution that addresses the shortcomings of pg_basebackup. pgBackRest implements all backup features internally using a custom protocol for communicating with remote systems. Powerful features of pgBackRest include parallel backup and restore, local or remote operation, full, incremental, and differential backup types, backup rotation, archive expiration, backup integrity, page checksums, backup resume, streaming compression and checksums, delta restore, and much more.
pgBackRest doesn’t rely on traditional backup tools like tar or rsync. This is a custom solution, and the protocol used by pgBackRest is a perfect fit for PostgreSQL-specific backup challenges. It allows for more flexibility and limits the types of connections that are required to perform a backup, which increases security. pgBackRest is a simple, but feature-rich, reliable backup and restore system that can seamlessly scale up to the largest databases and workloads.

Important features of pgBackRest

Full, incremental, and differential backups: pgBackRest supports full, incremental, and differential backups. It uses a differential backup model, which allows for efficient backup storage while maintaining the ability to restore to any point in time (PITR).

Parallel backup and restore. pgBackRest can perform backups and restores in parallel, taking advantage of multiple CPU cores and significantly reducing the time required for these operations.

Point-in-time recovery (PITR). pgBackRest enables Point-in-Time Recovery, allowing you to restore a PostgreSQL database to a specific point in time, rather than just the time of the last backup.

Compression, encryption and integrity checks. It provides options for compressing backup data on the fly to reduce storage requirements and supports encryption of backup files for enhanced security. pgBackRest also performs integrity checks on backup files, ensuring that they are consistent and can be trusted for recovery.

Backup rotation, retention and throttling. The tool includes built-in retention policies to manage backup rotation automatically. You can control how many backups are kept and when they are removed. pgBackRest also provides the ability to throttle the backup and restore operations, preventing them from overloading the system and affecting production performance.

Delta restore and parallel backup verification. Thanks to the delta restore feature, pgBackRest can quickly apply incremental changes to an existing database to reduce the time required for restoration. The tool also supports parallel verification of backups, ensuring that the backups are valid and reliable for recovery.

How to use pgBackRest

Installation

A prerequisite for installation is the PGDG repository. If we don’t have it already installed, we should be doing that from yum.postgresql.org.

sudo yum install pgbackrest

pgBackRest is developed in Perl, so when you install pgBackRest, all dependent Perl libraries will also get installed if they are not already present.

Configuring backups

The first step is to create a stanza definition in /etc/pgbackrest.conf. Here is a simple example. We can see that all the pg options are specified with a 1, which serves as the index of the configuration. These indexes are intended for configuring multiple PostgreSQL hosts. For example, a single master is configured with the pg1-path, pg1-host, and similar options. If a standby is configured, then index the pg- options as pg2-host, pg2-path, etc.

sudo bash -c 'cat << EOF  > /etc/pgbackrest.conf              

[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2

[pg0app]
pg1-path=/var/lib/pgsql/11/data
pg1-port=5432
EOF'

The next step is the creation of a stanza. This needs to be done on the server where the repository is located. It is highly recommended to use the same non-root user under which PostgreSQL processes are configured and running. As mentioned before, a stanza holds the backup configuration related to one PostgreSQL instance. In this step the actual internal structure and the definition of stanza will be created out of the stanza definition.

pgbackrest stanza-create --stanza=pg0app --log-level-console=info

Do the necessary parameter changes in PostgreSQL. At a minimal level, WAL archiving should be enabled, and archive_command should be using pgbackrest as shown below.  

ALTER SYSTEM SET wal_level = 'replica';
ALTER SYSTEM SET archive_mode = 'on';
ALTER SYSTEM SET archive_command = 'pgbackrest --stanza=pg0app archive-push %p';
ALTER SYSTEM SET max_wal_senders = '10';
ALTER SYSTEM SET hot_standby = 'on';

The above-mentioned parameter changes require the restart of the PostgreSQL instance:

sudo systemctl restart postgresql-11

Now we can check whether we have configured everything correctly using the following command:

pgbackrest check --stanza=pg0app --log-level-console=info

Running backups

Database backup using pgbackrest can be performed using the following command:

pgbackrest backup --stanza=pg0app --log-level-console=info

Conclusion

In this blog post, we have covered basic understanding and configuration details of the pgBackRest backup tool. In our future articles, we will tackle its advanced features. This backup tool has a number of them, so we expect the post to be helpful for PostgreSQL users. If you are looking for expert PostgreSQL professional services, please contact the Vettabase team.

Reference

You can find the pgBackRest documentation here.