pgstef's blog

SELECT * FROM pgstef

Home About me Talks PITR tools View on GitHub

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 pg_basebackup.

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

Installation

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

Replace the 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

Authorize the 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

Configuration

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';

Configure pg_hba.conf:

host   postgres   barman   backup-srv   md5

We’ll now prepare the backup-srv configuration file. Create a new file, called demo.conf, in /etc/barman.d 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

Configure ~barman/.pgpass:

$ 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 barman_home directory: /var/lib/barman.

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 postgresql.conf file:

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';

Configure pg_hba.conf:

host   replication   replic_user   pg2-srv   md5
host   replication   replic_user   pg3-srv   md5

Reload configuration:

$ sudo systemctl reload edb-as-13

Configure ~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 postgresql.auto.conf:

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.


Conclusion

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.