pgBackRest is an open source backup tool for PostgreSQL which offers easy configuration and reliable backups. So if you want to protect your database and create backups easily, pgBackRest is a good solution to make that happen. In this blog, we are going to go through the basic steps of using pgBackRest for full and differential backup of PostgreSQL.
Table of Contents
Some of the key features of pgBackRest are:
To read about them more in detail, please visit pgbackrest.org.
Here, we are going to build pgBackRest from the source and install it on the host where a test DB cluster is running.
Installing from Debian / Ubuntu packages:
|
1 |
sudo apt-get install pgbackrest |
For manual installation, download the source on a build host. Please avoid building the source on a production server, as the tools required should not be installed on a production machine:
|
1 2 3 |
sudo wget -q -O - https://github.com/pgbackrest/pgbackrest/archive/release/2.14.tar.gz | sudo tar zx -C /root |
|
1 2 3 |
sudo apt-get install build-essential libssl-dev libxml2-dev libperl-dev zlib1g-dev perl -V | grep USE_64_BIT_INT |
|
1 2 |
(cd /root/pgbackrest-release-2.14/src && ./configure) make -s -C /root/pgbackrest-release-2.14/src |
|
1 2 |
sudo scp BUILD_HOST:/root/pgbackrest-release-2.14/src/pgbackrest /usr/bin/ sudo chmod 755 /usr/bin/pgbackrest |
|
1 |
sudo apt-get install libdbd-pg-perl |
|
1 2 3 4 5 6 7 |
sudo mkdir -p -m 770 /var/log/pgbackrest sudo chown postgres:postgres /var/log/pgbackrest sudo mkdir -p /etc/pgbackrest sudo mkdir -p /etc/pgbackrest/conf.d sudo touch /etc/pgbackrest/pgbackrest.conf sudo chmod 640 /etc/pgbackrest/pgbackrest.conf sudo chown postgres:postgres /etc/pgbackrest/pgbackrest.conf |
|
1 2 3 |
sudo mkdir -p /var/lib/pgbackrest sudo chmod 750 /var/lib/pgbackrest sudo chown postgres:postgres /var/lib/pgbackrest |
|
1 2 3 4 5 6 |
cat /etc/pgbackrest/pgbackrest.conf [demo] pg1-path=/data/postgres/pgdata/data1 [global] repo1-path=/var/lib/pgbackrest |
Change the following parameters in postgresql.conf:
|
1 2 3 4 5 6 |
archive_command = 'pgbackrest --stanza=demo archive-push %p' archive_mode = on listen_addresses = '*' log_line_prefix = '' max_wal_senders = 3 wal_level = replica |
postgres user:The stanza-create command must be run on the host where the repository is located to initialize the stanza. It is recommended that the check command be run after stanza-create to ensure archiving and backups are properly configured.
|
1 2 3 4 5 6 7 8 |
$ pgbackrest --stanza=demo --log-level-console=info stanza-create 2019-07-03 12:26:40.060 P00 INFO: stanza-create command begin 2.14: --log-level-console=info --pg1-path=/data/postgres/pgdata/data1 --repo1-path=/var/lib/pgbackrest --stanza=demo 2019-07-03 12:26:40.494 P00 INFO: stanza-create command end: completed successfully (435ms) $ pgbackrest --stanza=demo --log-level-console=info check 2019-07-03 12:27:11.996 P00 INFO: check command begin 2.14: --log-level-console=info --pg1-path=/data/postgres/pgdata/data1 --repo1-path=/var/lib/pgbackrest --stanza=demo 2019-07-03 12:27:13.386 P00 INFO: WAL segment 000000010000000000000003 successfully stored in the archive at '/var/lib/pgbackrest/archive/demo/10-1/0000000100000000/000000010000000000000003-b346d07d4b31e54e31d9204204816cde3cfcca3a.gz' 2019-07-03 12:27:13.387 P00 INFO: check command end: completed successfully (1392ms) |
info command to get information about the backupSince we haven't made any backups yet, we will get the following result:
|
1 2 3 4 5 6 7 |
$ pgbackrest info stanza: demo status: error (no valid backups) cipher: none db (current) wal archive min/max (10-1): 000000010000000000000001/000000010000000000000003 |
Let's make the first backup. By default, it will be full even if we specify type as differential:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
$ pgbackrest --stanza=demo --log-level-console=info backup 2019-07-03 12:37:38.366 P00 INFO: backup command begin 2.14: --log-level-console=info --pg1-path=/data/postgres/pgdata/data1 --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=demo WARN: no prior backup exists, incr backup has been changed to full 2019-07-03 12:37:39.200 P00 INFO: execute non-exclusive pg_start_backup() with label 'pgBackRest backup started at 2019-07-03 12:37:38': backup begins after the next regular checkpoint completes 2019-07-03 12:37:39.500 P00 INFO: backup start archive = 000000010000000000000005, lsn = 0/5000028 . . . 2019-07-03 12:37:45.212 P00 INFO: full backup size = 22.5MB 2019-07-03 12:37:45.212 P00 INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive 2019-07-03 12:37:45.313 P00 INFO: backup stop archive = 000000010000000000000005, lsn = 0/5000130 2019-07-03 12:37:45.558 P00 INFO: new backup label = 20190703-123738F 2019-07-03 12:37:45.586 P00 INFO: backup command end: completed successfully (7221ms) 2019-07-03 12:37:45.586 P00 INFO: expire command begin 2019-07-03 12:37:45.594 P00 INFO: full backup total < 2 - using oldest full backup for 10-1 archive retention 2019-07-03 12:37:45.596 P00 INFO: expire command end: completed successfully (10ms) |
Now when we run the info command again:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
$ pgbackrest info stanza: demo status: ok cipher: none db (current) wal archive min/max (10-1): 000000010000000000000005/000000010000000000000005 full backup: 20190703-123738F timestamp start/stop: 2019-07-03 12:37:38 / 2019-07-03 12:37:45 wal start/stop: 000000010000000000000005 / 000000010000000000000005 database size: 22.6MB, backup size: 22.6MB repository size: 2.7MB, repository backup size: 2.7MB |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
$ pgbackrest --stanza=demo --log-level-console=info --type=diff backup 2019-07-03 12:40:05.749 P00 INFO: backup command begin 2.14: --log-level-console=info --pg1-path=/data/postgres/pgdata/data1 --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=demo --type=diff 2019-07-03 12:40:05.951 P00 INFO: last backup label = 20190703-123738F, version = 2.14 2019-07-03 12:40:06.657 P00 INFO: execute non-exclusive pg_start_backup() with label 'pgBackRest backup started at 2019-07-03 12:40:05': backup begins after the next regular checkpoint completes 2019-07-03 12:40:06.958 P00 INFO: backup start archive = 000000010000000000000007, lsn = 0/7000028 2019-07-03 12:40:08.414 P01 INFO: backup file /data/postgres/pgdata/data1/global/pg_control (8KB, 99%) checksum c8b3635ef4701b19bff56fcd5ca33d41eaf3ce5b 2019-07-03 12:40:08.421 P01 INFO: backup file /data/postgres/pgdata/data1/pg_logical/replorigin_checkpoint (8B, 100%) checksum 347fc8f2df71bd4436e38bd1516ccd7ea0d46532 2019-07-03 12:40:08.439 P00 INFO: diff backup size = 8KB 2019-07-03 12:40:08.439 P00 INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive 2019-07-03 12:40:08.540 P00 INFO: backup stop archive = 000000010000000000000007, lsn = 0/70000F8 2019-07-03 12:40:08.843 P00 INFO: new backup label = 20190703-123738F_20190703-124005D 2019-07-03 12:40:08.938 P00 INFO: backup command end: completed successfully (3189ms) 2019-07-03 12:40:08.938 P00 INFO: expire command begin 2019-07-03 12:40:08.949 P00 INFO: full backup total < 2 - using oldest full backup for 10-1 archive retention 2019-07-03 12:40:08.951 P00 INFO: expire command end: completed successfully (13ms) |
info:|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
$ pgbackrest info stanza: demo status: ok cipher: none db (current) wal archive min/max (10-1): 000000010000000000000005/000000010000000000000007 full backup: 20190703-123738F timestamp start/stop: 2019-07-03 12:37:38 / 2019-07-03 12:37:45 wal start/stop: 000000010000000000000005 / 000000010000000000000005 database size: 22.6MB, backup size: 22.6MB repository size: 2.7MB, repository backup size: 2.7MB diff backup: 20190703-123738F_20190703-124005D timestamp start/stop: 2019-07-03 12:40:05 / 2019-07-03 12:40:08 wal start/stop: 000000010000000000000007 / 000000010000000000000007 database size: 22.6MB, backup size: 8.2KB repository size: 2.7MB, repository backup size: 468B backup reference list: 20190703-123738F |
If you want to learn more about how to protect data, check out my blog on PostgreSQL TDE ("Transparent Data Encryption") .
Also if you want to make sure that your database performs well, check out our blog posts on PostgreSQL performance.
In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.
+43 (0) 2622 93022-0
office@cybertec.at
Great article!
I really love pgbackrest, even if I don't like very much the design of the configuration file with the numbers within parameters (repo1-path, ecc.).