Barman allows you to implement disaster recovery solutions for databases with high requirements of business continuity.
Traditionally, Barman has always operated remotely via SSH, taking advantage of
rsync for physical backup operations.
Version 2.0 introduces native support for Streaming Replication backup operations, via
Choosing one of these two methods is a decision you will need to make. The official documentation deeply describes how to implement it and covers a lot of important general considerations.
In this post, we’ll briefly see how to setup a dedicated Barman server to backup an Advanced Server 3-nodes cluster, using the SSH method.
The Barman dedicated server will be called backup-srv and the 3 Advanced Server nodes in Streaming Replication: pg1-srv, pg2-srv, pg3-srv. All the nodes will be running on CentOS 7.
If you’re familiar with Vagrant, here’s a simple
Vagrantfile to initiate 4 virtual machines using those names:
# Vagrantfile Vagrant.configure(2) do |config| config.vm.box = 'centos/7' config.vm.provider 'libvirt' do |lv| lv.cpus = 1 lv.memory = 1024 end nodes = 'backup-srv', 'pg1-srv', 'pg2-srv', 'pg3-srv' nodes.each do |node| config.vm.define node do |conf| conf.vm.hostname = node end end end
First of all, Barman and Advanced Server will both require some dependencies located in the EPEL repository. If not already done already on your system, add it:
$ sudo yum install -y epel-release
Then, to install Advanced Server on pg1-srv, pg2-srv and pg3-srv, please refer to the official documentation.
In short, install the EDB repository configuration package:
$ sudo yum -y install https://yum.enterprisedb.com/edbrepos/edb-repo-latest.noarch.rpm
USERNAME:PASSWORD variable in the following command with the username and password of a registered EDB user:
$ sudo sed -i "s@<username>:<password>@USERNAME:PASSWORD@" /etc/yum.repos.d/edb.repo
Update the cache and install Advanced Server:
$ sudo yum makecache $ sudo yum -y install edb-as13-server
On backup-srv, update the cache and install Advanced Server libs:
$ sudo yum makecache $ sudo yum -y install edb-as13-server-libs
Now, install Barman and check its version:
$ sudo yum install -y barman $ sudo -iu barman barman -v 2.12
On the Advanced Server nodes, to be able to use the
barman-wal-archive command for WAL archiving, install the following package:
$ sudo yum install -y barman-cli $ barman-wal-archive --version barman-wal-archive 2.12
If the latest release packages couldn’t be found in the already installed repositories, you might need to configure the 2ndQuadrant Public RPM repository as stated in the official documentation.
Finally, create a basic Advanced Server instance on pg1-srv:
$ sudo -i root# PGSETUP_INITDB_OPTIONS="-E UTF-8 --data-checksums" /usr/edb/as13/bin/edb-as-13-setup initdb root# systemctl enable edb-as-13 root# systemctl start edb-as-13
Setup password-less SSH
SSH key exchange is a very common practice that is used to implement secure password-less connections between users on different machines, and it’s needed to use rsync for WAL archiving and for backups.
Create the backup-srv key pair:
$ sudo -u barman ssh-keygen -N "" -t rsa -b 4096 -f /var/lib/barman/.ssh/id_rsa $ sudo -u barman restorecon -R /var/lib/barman/.ssh
Perform the same operation on all the Advanced Server nodes and authorize the public key from the backup-srv:
$ sudo -u enterprisedb ssh-keygen -N "" -t rsa -b 4096 -f /var/lib/edb/.ssh/id_rsa $ sudo -u enterprisedb restorecon -R /var/lib/edb/.ssh $ ssh root@backup-srv cat /var/lib/barman/.ssh/id_rsa.pub | \ sudo -u enterprisedb tee -a /var/lib/edb/.ssh/authorized_keys
enterprisedb user public keys on the backup-srv:
$ ssh root@pg1-srv cat /var/lib/edb/.ssh/id_rsa.pub | \ sudo -u barman tee -a /var/lib/barman/.ssh/authorized_keys $ ssh root@pg2-srv cat /var/lib/edb/.ssh/id_rsa.pub | \ sudo -u barman tee -a /var/lib/barman/.ssh/authorized_keys $ ssh root@pg3-srv cat /var/lib/edb/.ssh/id_rsa.pub | \ sudo -u barman tee -a /var/lib/barman/.ssh/authorized_keys
To test the connection:
# From the Advanced Server nodes $ sudo -u enterprisedb ssh barman@backup-srv # From the backup server $ sudo -u barman ssh enterprisedb@pg1-srv $ sudo -u barman ssh enterprisedb@pg2-srv $ sudo -u barman ssh enterprisedb@pg3-srv
In case you’d use Vagrant boxes, it might be needed to enable the SSH password authentication to proceed with the key exchange:
$ sudo -i root# sed -i 's/PasswordAuthentication no/PasswordAuthentication yes/g' /etc/ssh/sshd_config root# systemctl restart sshd.service root# passwd
On the pg1-srv server, create a specific user for the backups:
$ sudo -iu enterprisedb psql -d postgres postgres=# CREATE ROLE barman WITH LOGIN SUPERUSER PASSWORD 'mypwd';
host postgres barman backup-srv md5
We’ll now prepare the backup-srv configuration file. Create a new file, called
directory, with the following content:
[demo] description = "Example of Advanced Server (via Ssh)" ssh_command = ssh enterprisedb@pg1-srv conninfo = host=pg1-srv user=barman dbname=postgres backup_method = rsync backup_options = concurrent_backup reuse_backup = link archiver = on
$ echo "*:*:postgres:barman:mypwd" >> ~barman/.pgpass $ chown barman: ~barman/.pgpass $ chmod 0600 ~barman/.pgpass
By default, the backups and archives are stored locally in the
Try the WAL archiving command on pg1-srv:
$ sudo -iu enterprisedb barman-wal-archive backup-srv demo DUMMY --test Ready to accept WAL files for the server demo
Configure archiving in the
listen_addresses = '*' archive_mode = on archive_command = 'barman-wal-archive backup-srv demo %p'
The Advanced Server instance must be restarted after making these changes and before performing a backup.
$ sudo systemctl restart edb-as-13
Check the configuration and that WAL archiving is working from backup-srv:
$ sudo -iu barman barman check demo Server demo: PostgreSQL: OK superuser or standard user with backup privileges: 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 (PostgreSQL server) systemid coherence: OK (no system Id stored on disk) archive_mode: OK archive_command: OK continuous archiving: OK archiver errors: OK $ sudo -iu barman barman switch-wal --force --archive demo The WAL file ... has been closed on server 'demo' Waiting for the WAL file ... from server 'demo' (max: 30 seconds) Processing xlog segments from file archival for demo ...
Perform a backup
Let’s take our first backup on the backup-srv from the pg1-srv primary server:
$ sudo -iu barman barman backup demo --wait Starting backup using rsync-concurrent method for server demo in /var/lib/barman/demo/base/20210115T133837 Backup start at LSN: 0/5000028 (000000010000000000000005, 00000028) This is the first backup for server demo WAL segments preceding the current backup have been found: 000000010000000000000002 from server demo has been removed 000000010000000000000003 from server demo has been removed Starting backup copy via rsync/SSH for 20210115T133837 Copy done (time: 1 second) This is the first backup for server demo Asking PostgreSQL server to finalize the backup. Backup size: 49.9 MiB. Actual size on disk: 49.9 MiB (-0.00% deduplication ratio). Backup end at LSN: 0/5000138 (000000010000000000000005, 00000138) Backup completed (start time: 2021-01-15 13:38:37.321112, elapsed time: 4 seconds) Waiting for the WAL file 000000010000000000000005 from server 'demo' Processing xlog segments from file archival for demo 000000010000000000000004 000000010000000000000005 000000010000000000000005.00000028.backup
Get the backups list:
$ sudo -iu barman barman list-backup demo demo 20210115T133837 - Fri Jan 15 13:38:39 2021 - Size: 65.9 MiB - WAL Size: 0 B
Show more information about that backup:
$ sudo -iu barman barman show-backup demo 20210115T133837 Backup 20210115T133837: Server Name : demo System Id : 6917964532224614077 Status : DONE PostgreSQL Version : 130001 PGDATA directory : /var/lib/edb/as13/data Base backup information: Disk usage : 49.9 MiB (65.9 MiB with WALs) Incremental size : 49.9 MiB (-0.00%) Timeline : 1 Begin WAL : 000000010000000000000005 End WAL : 000000010000000000000005 WAL number : 1 Begin time : 2021-01-15 13:38:37.226367+00:00 End time : 2021-01-15 13:38:39.656877+00:00 Copy time : 1 second Estimated throughput : 36.0 MiB/s Begin Offset : 40 End Offset : 312 Begin LSN : 0/5000028 End LSN : 0/5000138 WAL information: No of files : 0 Disk usage : 0 B Last available : 000000010000000000000005 Catalog information: Retention Policy : not enforced Previous Backup : - (this is the oldest base backup) Next Backup : - (this is the latest base backup)
Prepare the servers for Streaming Replication
On the pg1-srv server, create a specific user for the replication:
$ sudo -iu enterprisedb psql -d postgres postgres=# CREATE ROLE replic_user WITH LOGIN REPLICATION PASSWORD 'mypwd';
host replication replic_user pg2-srv md5 host replication replic_user pg3-srv md5
$ sudo systemctl reload edb-as-13
~enterprisedb/.pgpass on pg2-srv and pg3-srv:
$ echo "*:*:replication:replic_user:mypwd" >> ~enterprisedb/.pgpass $ chown enterprisedb: ~enterprisedb/.pgpass $ chmod 0600 ~enterprisedb/.pgpass
Setup the standby servers
On backup-srv, restore the backup taken from the pg1-srv on pg2-srv and pg3-srv.
Example for pg2-srv:
$ sudo -iu barman barman recover demo latest /var/lib/edb/as13/data --standby-mode --remote-ssh-command="ssh enterprisedb@pg2-srv" Starting remote restore for server demo using backup 20210115T133837 Destination directory: /var/lib/edb/as13/data Remote command: ssh enterprisedb@pg2-srv Copying the base backup. Copying required WAL segments. Generating archive status files Generating recovery configuration Identify dangerous settings in destination directory. IMPORTANT These settings have been modified to prevent data losses postgresql.auto.conf line 4: archive_command = false Recovery completed (start time: 2021-01-15 15:35:32.143974, elapsed time: 5 seconds) Your PostgreSQL server has been successfully prepared for recovery!
Add the Streaming Replication settings to
primary_conninfo = 'host=pg1-srv user=replic_user port=5444'
All we have to do now is to start the PostgreSQL instances:
$ sudo systemctl enable edb-as-13 $ sudo systemctl start edb-as-13
If the replication setup is correct, you should see those processes on the pg1-srv server:
$ sudo -iu enterprisedb ps -o pid,cmd fx PID CMD 27643 ps -o pid,cmd fx 27226 /usr/edb/as13/bin/edb-postmaster -D /var/lib/edb/as13/data ... 27255 \_ postgres: walsender replic_user ... streaming ... 27639 \_ postgres: walsender replic_user ... streaming ...
We now have a 3-nodes cluster working with Streaming Replication and archives recovery as safety net.
As you can see, backups and restores are executed from the dedicated backup server. This server may even be configured to backup multiple Advanced Server clusters by setting up multiple configuration files.