Secwall's notes

Barman page-level incremental backups

Warning

I present experimental fork of pgbarman in this post. Some config parameters could change in merging to upstream process. Stay tuned.

TL;DR

I've added agent (barman-incr) which implements parallel compressed page-level incremental backups support to barman.

Quick example

I'll use CentOS 7 in this example. If you are using different distro you should probably change install phase.

Install

We'll add some required repos:

$ sudo yum install epel-release
$ sudo yum install https://download.postgresql.org/pub/repos/yum/9.5/redhat/rhel-7-x86_64/pgdg-centos95-9.5-2.noarch.rpm

Now we'll build barman and barman-incr packages from source (I assume that you are in your home dir now):

$ sudo yum install git rpm-build
$ git clone https://github.com/secwall/barman
$ mv barman barman-1.6.2a1
$ mkdir -p rpmbuild/SOURCES
$ tar -zcf /home/vagrant/rpmbuild/SOURCES/barman-1.6.2a1.tar.gz barman-1.6.2a1
$ rpmbuild -bb barman-1.6.2a1/rpm/barman.spec

In result we should get barman-1.6.2-0.1.a1.el7.centos.noarch.rpm and barman-incr-1.6.2-0.1.a1.el7.centos.noarch.rpm in ~/rpmbuild/RPMS/noarch

Installing pkgs:

$ sudo yum install rpmbuild/RPMS/noarch/barman-*

Postgresql setup

We'll setup pgsql without any replicas on localhost (this is only for demo purposes, you should never do this on production environment, always use replication, strong passwords, ssl, and so on).

$ sudo yum install postgresql95-server postgresql95-contrib
$ sudo /usr/pgsql-9.5/bin/postgresql95-setup initdb
Initializing database ... OK

$ sudo systemctl restart postgresql-9.5

$ sudo -u postgres psql
postgres=# create user barman with encrypted password 'barman' superuser;
CREATE ROLE

$ cat <<EOF | sudo tee --append /var/lib/pgsql/9.5/data/postgresql.conf
wal_level = hot_standby
archive_mode = on
archive_command = 'rsync %p [email protected]:/var/lib/barman/test/incoming/%f'
EOF

$ sudo sed -i -e 's/ident/md5/g' /var/lib/pgsql/9.5/data/pg_hba.conf
$ sudo grep -v '^#' /var/lib/pgsql/9.5/data/pg_hba.conf | sed '/^\s*$/d'
local   all             all                                     peer
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

$ sudo systemctl restart postgresql-9.5

Barman setup

Now we'll configure barman to backup our local postgresql

$ cat <<EOF | sudo tee --append /etc/barman.conf
[test]
backup_method = incr
description =  "Test PostgreSQL Database"
ssh_command = ssh [email protected]
conninfo = host=localhost user=barman dbname=postgres password=barman
incr_compress = gzip-4
incr_parallel = 4
EOF

Seems simple. We'll compress our backups with gzip -4, use 4 processes to make backup. Refer to man 5 barman for more info.

OpenSSH Auth Setup

Gen ssh-keys for barman and postgres users. Add them into authorized_keys and disable strict host checking.

$ sudo -u barman mkdir -p /var/lib/barman/.ssh
$ sudo -u barman ssh-keygen -f /var/lib/barman/.ssh/id_rsa -N ''

$ sudo -u postgres mkdir -p /var/lib/pgsql/.ssh
$ sudo -u postgres ssh-keygen -f /var/lib/pgsql/.ssh/id_rsa -N ''

$ sudo cat /var/lib/barman/.ssh/id_rsa.pub | sudo tee /var/lib/pgsql/.ssh/authorized_keys
$ sudo cat /var/lib/pgsql/.ssh/id_rsa.pub | sudo tee /var/lib/barman/.ssh/authorized_keys

$ cat <<EOF | sudo -u barman tee /var/lib/barman/.ssh/config
Host *
    StrictHostKeyChecking no

$ cat <<EOF | sudo -u postgres tee /var/lib/pgsql/.ssh/config
Host *
    StrictHostKeyChecking no

$ sudo chmod 700 /var/lib/barman/.ssh && sudo chown barman:barman /var/lib/barman/.ssh -R
$ sudo chmod 700 /var/lib/pgsql/.ssh && sudo chown postgres:postgres /var/lib/pgsql/.ssh -R

Check that everything works as expected:

$ sudo barman check test
Server test:
        PostgreSQL: OK
        superuser: OK
        wal_level: OK
        directories: OK
        retention policy settings: OK
        backup maximum age: OK (no last_backup_maximum_age provided)
        compression settings: OK
        failed backups: OK (there are 0 failed backups)
        minimum redundancy requirements: OK (have 0 backups, expected at least 0)
        ssh: OK (barman-incr)
        version: OK (ok)
        not in recovery: OK
        archive_mode: OK
        archive_command: OK
        continuous archiving: OK
        archiver errors: OK

If you see WAL archive check failure. Try to use pg_switch_xlog() function on postgres cluster and check the logs.

Backup

We'll add some data with pgbench, make full backup, change some data, and finally make incremental backup.

$ sudo -u postgres /usr/pgsql-9.5/bin/pgbench -i -s 100 --foreign-keys

$ sudo du -hs /var/lib/pgsql/9.5/data/base/
1.5G    /var/lib/pgsql/9.5/data/base/

Full backup:

$ sudo barman backup test
$ barman list-backup test
test 20160618T013124 - Sat Jun 18 01:32:55 2016 - Size: 84.3 MiB - WAL Size: 0 B

Actually I cheat in some way here because pgbench data compressed really great. You'll not see such impressive results on real data.

Now we'll update 1000 rows:

$ sudo -u postgres psql
postgres=# update pgbench_accounts set abalance = 100 where aid % 10000 = 2;
UPDATE 1000

Another backup (now it'll be really fast):

$ sudo barman backup test
$ sudo barman list-backup test
test 20160618T013605 - Sat Jun 18 01:37:41 2016 - Size: 170.1 KiB - WAL Size: 0 B
test 20160618T013124 - Sat Jun 18 01:32:55 2016 - Size: 84.3 MiB - WAL Size: 1.8 MiB

Wow. This looks really good.

What is next?

Read the docs (man 5 barman) and adjust options to suit your requrements. Important note: in real-world setup you'll need to install barman-incr on database host to make backup (ssh check will fail if you enable incr backups without agent on host).

Comments