pgBackRest is a reliable and simple to configure backup and restore solution for PostgreSQL, which provides a powerful solution for any PostgreSQL database; be it a small project, or scaled up to enterprise-level use cases.
Many powerful features are included in pgBackRest, including 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.
With the recent release of PostgreSQL 12 (and more recently 12.1), pgBackRest also received a number of updates and changes to take advantage of the latest features of Postgres.
On October 1st 2019, pgBackRest released version 2.18 which is the first release of pgBackRest to support PostgreSQL 12. As such, any deployment using PostgreSQL 12 where pgBackRest will be used requires version 2.18 or greater. At the time of this post, the latest version of pgBackRest is version 2.19.
In the following guide, we will explore the steps involved in configuring pgBackRest on a PostgreSQL 12 database, followed by simulating a disaster where the database files have been destroyed, and restoring a backup to regain the database.
Setting up the Demo
If following this guide for tutorial purposes, it is useful to have a similar working environment to ensure that the same settings, commands, and processes are used while performing the exercise.
For this example, we will be performing the install and configuration of PostgreSQL 12.1 and pgBackRest 2.19 on CentOS 7.
If using Vagrant, simply run:
cd temp_work_dir vagrant init centos/7 vagrant up vagrant ssh
Alternately, you can set up and configure a virtual machine manually, using the
- 12 GB single partition ext4 disk
- 2 GB RAM
- 2 CPU
Install PostgreSQL and pgBackRest
There are several methods for installing PostgreSQL on a CentOS 7 server which
are detailed on the PostgreSQL wiki.
For the purposes of this guide, we will install both PostgreSQL 12 and the
version of pgBackRest using the PGDG Yum repository.
At the time of this writing, the latest versions of PostgreSQL was version 12.1
pgBackRest was version 2.19.
sudo yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm sudo yum -y install postgresql12-server postgresql12-contrib pgbackrest
Next, initialize the PostgreSQL instance with the following commands:
sudo /usr/pgsql-12/bin/postgresql-12-setup initdb sudo systemctl enable postgresql-12.service sudo systemctl start postgresql-12.service
The first command is only necessary to run once, and is responsible for
initializing the database in
The second command ensures PostgreSQL will start automatically when the
operating system is started, and the third command will start the database at
the present time.
To verify PostgreSQL has started correctly, the following command will confirm:
sudo -iu postgres psql --version psql (PostgreSQL) 12.1
Next, verify pgBackRest was installed correctly by running the following
either as the default user, or as the
$ pgbackrest pgBackRest 2.19 - General help Usage: pgbackrest [options] [command] Commands: archive-get Get a WAL segment from the archive. archive-push Push a WAL segment to the archive. backup Backup a database cluster. check Check the configuration. expire Expire backups that exceed retention. help Get help. info Retrieve information about backups. restore Restore a database cluster. stanza-create Create the required stanza data. stanza-delete Delete a stanza. stanza-upgrade Upgrade a stanza. start Allow pgBackRest processes to run. stop Stop pgBackRest processes from running. version Get version. Use 'pgbackrest help [command]' for more information.
Ensuring that the basic commands return valid responses and expected versions
is generally a good practice to follow, as it ensures that the software is functioning
properly and that the versions are compatible with one another.
Depending on your specific use case, you may need to configure PostgreSQL's
options to meet your specific environment needs.
Possible considerations would be:
- Using replication or configuring a cluster
- How your backups will be stored (using the 3-2-1 Method?)
- Security concerns for your database
For the purposes of this exercise, we will be following a very simplistic
which will do best in demonstrating the process, but is not ideal for production
If you wish to configure your environment to have replica PostgreSQL instances,
for example, further information may be found in these Crunchy Blog Posts.
For the simple purposes of this demonstration, we will configure PostgreSQL
with the least number of changes to make a minimum working model.
If you’re unaware of where your configuration files are on the PostgreSQL host, you can run the following:
sudo -iu postgres psql -U postgres -c 'SHOW config_file' config_file ---------------------------------------- /var/lib/pgsql/12/data/postgresql.conf (1 row)
file with root privileges in your preferred text editor.
The following parameters will need to be defined:
listen_addresses = '*' # Optionally, define the address as the host IP: listen_addresses = '10.0.1.1' password_encryption=’scram-sha-256’ archive_mode = on
A brief aside about configured PostgreSQL settings
It is always best practice to have an understanding of the configuration changes made to a database.
For the purposes of this demonstration, three options were changed from their
defaults, and it is important
to know why this has happened.
pg_hba.conf is the file responsible for restricting
listen_addresses is set to
* (wildcard), it is possible
to discover the open port on
nmap and learn the database
exists, thereby possibly opening the server up for an exploit. Setting it
to the an IP address prevents PostgreSQL from listening on an unintended
interface, preventing this potential exploit. More information on this
specific attack vector and how to avoid it can be found in this blog
Starting with the release of PostgreSQL 10,
SCRAM-SHA-256 authentication was
available for use, and
is used in this example for the explicit purpose of encouraging secure connections to the database.
Specifically, this method of authentication prevents password sniffing on
untrusted connections and offers support for cryptographically hashing
passwords on the server in a secure manner.
More detailed information on this authentication method can be found
Another addition to recent versions of PostgreSQL, starting with the
major release for PostgreSQL 10, a change was introduced to reduce the
number of configuration edits that were necessary to perform streaming
backup and replication (specifically affecting the parameters
hot_standby - these are now
all set by default). The release notes regarding this change can be found
Once the changes have been made to the PostgreSQL configuration file, restart
the service to allow the changes to take effect.
A restart is necessary in this specific case, as all of the values changed
particularly require the PostgreSQL service to stop and start again.
sudo systemctl restart postgresql-12.service
It is possible to check if the configuration values have been correctly
applied to the database by running the following command:
sudo -iu postgres psql
SELECT name,setting,context,source FROM pg_settings WHERE NAME IN ('listen_addresses','archive_mode','password_encryption'); name | setting | context | source ---------------------+---------------+------------+-------------------- archive_mode | on | postmaster | configuration file listen_addresses | * | postmaster | configuration file password_encryption | scram-sha-256 | user | configuration file (3 rows)
\q to exit from the psql prompt.
Configure a location for the pgBackRest backup repository:
sudo mkdir -p /var/lib/pgbackrest sudo chmod 0750 /var/lib/pgbackrest sudo chown -R postgres:postgres /var/lib/pgbackrest
Configure the location and permissions on the pgbackrest log location:
sudo chown -R postgres:postgres /var/log/pgbackrest
Next, modify pgBackRest's configuration files to meet the needs of the environment.
As best practice, first create a backup of any existing
sudo cp /etc/pgbackrest.conf /etc/pgbackrest.conf.backup
Generate a secure, long, and random passphrase to encrypt the repository:
openssl rand -base64 48
This generated value will be used as the
NOTE: Once the repository has been configured and the stanza created and
checked, the repository encryption settings cannot be changed.
Next, edit the
pgbackrest.conf file as root, entering the following
[global] repo1-cipher-pass=uUQsaa7+CCFaqXVagFzNUix3XuLe9e2uqVskqfI6wcKf8BX8y5b+8bL3oimRpV1N repo1-cipher-type=aes-256-cbc repo1-path=/var/lib/pgbackrest repo1-retention-full=2 log-level-console=info log-level-file=debug [demo] pg1-path=/var/lib/pgsql/12/data
[global] section defines the location of backups, logging settings, and
[demo] section defines a stanza for the
demo backup repository, which we
As with the PostgreSQL settings, best practices encourage an understanding of
the configuration options.
More information can be found about these configuration options within the pgBackRest Configuration Guide.
Finally, initialize the pgBackRest stanza, which contains the definitions for
location, archiving options, backup settings, and other similar configurations
for the PostgreSQL database cluster.
There is generally one stanza defined for each database cluster that needs to have backups.
stanza-create command must be run on the primary host after
pgbackrest.conf has been configured.
sudo -u postgres pgbackrest --stanza=demo stanza-create 2019-11-15 18:08:57.158 P00 INFO: stanza-create command begin 2.19: --log-level-console=info --log-level-file=debug --pg1-path=/var/lib/pgsql/12/data --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --stanza=demo 2019-11-15 18:08:57.609 P00 INFO: stanza-create command end: completed successfully (455ms)
Pulling It All Together, Performing First Backup
Now that PostgreSQL and pgBackRest have been configured individually, a few
final steps must be
performed to tie them together and perform the backup process.
First, edit the
postgresql.conf file once more, and configure the
archive_command = 'pgbackrest --stanza=demo archive-push %p'
This configuration option informs PostgreSQL to use pgBackRest to handle the
segments, pushing them immediately to the archive.
Following this change to the configuration file, reload the PostgreSQL service:
sudo systemctl reload postgresql-12.service
Next, we will check the cluster with pgBackRest. This validates that
archive_command settings are both accurately configured and
sudo -iu postgres pgbackrest --stanza=demo check 2019-11-15 18:10:03.637 P00 INFO: check command begin 2.19: --log-level-console=info --log-level-file=debug --pg1-path=/var/lib/pgsql/12/data --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --stanza=demo 2019-11-15 18:10:04.757 P00 INFO: WAL segment 000000010000000000000001 successfully archived to '/var/lib/pgbackrest/archive/demo/12-1/0000000100000000/000000010000000000000001-bddaecf52ba8c3dd83e6157fea6a4dbeb6476010.gz' 2019-11-15 18:10:04.757 P00 INFO: check command end: completed successfully (1120ms)
If any errors are produced by this command, read and inspect the output for
recommendations on how to resolve the specific issue.
Now, after much ado; perform a full backup:
sudo -u postgres pgbackrest --stanza=demo --type=full backup 2019-11-15 18:10:32.421 P00 INFO: backup command begin 2.19: --log-level-console=info --log-level-file=debug --pg1-path=/var/lib/pgsql/12/data --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanz a=demo --type=full 2019-11-15 18:10:33.555 P00 INFO: execute non-exclusive pg_start_backup() with label "pgBackRest backup started at 2019-11-15 18:10:32": backup begins after the next regular checkpoint completes 2019-11-15 18:10:33.758 P00 INFO: backup start archive = 000000010000000000000003, lsn = 0/3000028 2019-11-15 18:10:35.930 P01 INFO: backup file /var/lib/pgsql/12/data/base/14187/1255 (632KB, 2%) checksum 60325e5cd07379af0ffe91eea27cfd4f2f07af69 [...] 2019-11-15 18:10:38.818 P00 INFO: full backup size = 24.2MB 2019-11-15 18:10:38.818 P00 INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive 2019-11-15 18:10:38.920 P00 INFO: backup stop archive = 000000010000000000000003, lsn = 0/3000138 2019-11-15 18:10:39.235 P00 INFO: new backup label = 20191115-181032F 2019-11-15 18:10:39.286 P00 INFO: backup command end: completed successfully (6866ms)
And finally, confirm the backup is working:
sudo -u postgres pgbackrest info stanza: demo status: ok cipher: aes-256-cbc db (current) wal archive min/max (12-1): 000000010000000000000003/000000010000000000000003 full backup: 20191115-181032F timestamp start/stop: 2019-11-15 18:10:32 / 2019-11-15 18:10:39 wal start/stop: 000000010000000000000003 / 000000010000000000000003 database size: 24.2MB, backup size: 24.2MB repository size: 2.9MB, repository backup size: 2.9MB
Restore a Backup
Now that a full backup is performed on a fresh database, it might be useful to test restoring from the full backup.
To do this, stop the PostgreSQL instance, and delete its data files, simulating a system administration disaster.
sudo systemctl stop postgresql-12.service sudo find /var/lib/pgsql/12/data -mindepth 1 -delete
At this point, trying to start the database will result in a failure:
sudo systemctl start postgresql-12.service ## THIS WILL FAIL Job for postgresql-12.service failed because the control process exited with error code. See "systemctl status postgresql-12.service" and "journalctl -xe" for details.
Perform a restore on the database:
sudo -iu postgres pgbackrest --stanza=demo --delta restore
Once the restore has completed, the database will start as expected:
sudo systemctl start postgresql-12.service
You can verify that pgBackRest is still working:
sudo -u postgres pgbackrest --stanza=demo check 2019-11-15 18:13:56.707 P00 INFO: check command begin 2.19: --log-level-console=info --log-level-file=debug --pg1-path=/var/lib/pgsql/12/data --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --stanza=demo 2019-11-15 18:13:57.594 P00 INFO: WAL segment 000000020000000000000005 successfully archived to '/var/lib/pgbackrest/archive/demo/12-1/0000000200000000/000000020000000000000005-bd01dc079338748cd9772a7c324eed0d68d45a9c.gz' 2019-11-15 18:13:57.594 P00 INFO: check command end: completed successfully (887ms)
After any sort of disaster instance, it is always best practice to follow up any restore with a fresh backup:
sudo -u postgres pgbackrest --stanza=demo --type=full backup
In conclusion, pgBackRest offers a large amount of possibilities and
It is quite simple to install, configure, and use, simplifying Point-in-time recovery through WAL archiving.
Ensuring that backups are working and valid allows for peace of mind, should any disaster strike.
November 25, 2019 •More by this author