As PostgreSQL is more and more present in our client’s infrastructure, I wanted to describe you the barman installation and configuration. Barman is the backup and recovery tool for PostgreSQL, I configured it using DMK out tool for infrastructure administrators on Oracle, MySQL, and PostgreSQL.

I used two virtual severs running under RedHat Enterprise Libux 7.1, one for PostgreSQL database server (pg1) ands the second for barman (pg2).

At first I install PostgreSQL 9.6 on both servers:

[root@pg1 ~]# wget https://download.postgresql.org/pub/repos/yum/9.6/redhat/
rhel-7-x86_64/pgdg-redhat96-9.6-3.noarch.rpm
--2017-02-06 15:08:05--  https://download.postgresql.org/pub/repos/yum/9.6/redhat
/rhel-7-x86_64/pgdg-redhat96-9.6-3.noarch.rpm
Resolving download.postgresql.org (download.postgresql.org)... 
217.196.149.55, 174.143.35.246, 87.238.57.227, ...
Connecting to download.postgresql.org (download.postgresql.org)|
217.196.149.55|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 4816 (4.7K) [application/x-redhat-package-manager]
Saving to: âpgdg-redhat96-9.6-3.noarch.rpm
 
100%[======================================>] 4,816       
 
2017-02-06 15:08:05 (2.71 MB/s) - pgdg-redhat96-9.6-3.noarch.rpm saved 
 
[root@pg1 ~]# sudo yum localinstall -y pgdg-redhat96-9.6-3.noarch.rpm
Examining pgdg-redhat96-9.6-3.noarch.rpm: pgdg-redhat96-9.6-3.noarch
Marking pgdg-redhat96-9.6-3.noarch.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package pgdg-redhat96.noarch 0:9.6-3 will be installed
--> Finished Dependency Resolution
 
Dependencies Resolved
 
================================================================================
 Package           Arch       Version     Repository                       Size
================================================================================
Installing:
 pgdg-redhat96     noarch     9.6-3       /pgdg-redhat96-9.6-3.noarch     2.7 k
 
Transaction Summary
================================================================================
Install  1 Package
 
Total size: 2.7 k
Installed size: 2.7 k
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : pgdg-redhat96-9.6-3.noarch                                   1/1
  Verifying  : pgdg-redhat96-9.6-3.noarch                                   1/1
 
Installed:
  pgdg-redhat96.noarch 0:9.6-3
 
Complete!

I install barman on the barman server (pg2):

[root@pg2 ~]# sudo yum install barman
pgdg96                                                   | 4.1 kB     00:00
(1/2): pgdg96/7Server/x86_64/group_gz                      |  249 B   00:00
(2/2): pgdg96/7Server/x86_64/primary_db                    | 129 kB   00:02
Resolving Dependencies
--> Running transaction check
---> Package barman.noarch 0:2.1-1.rhel7 will be installed
--> Processing Dependency: python-psycopg2 >= 2.4.2 for package:
barman-2.1-1.rhel7.noarch
--> Processing Dependency: python-argh >= 0.21.2 for package: 
barman-2.1-1.rhel7.noarch
--> Processing Dependency: python-dateutil for package: 
barman-2.1-1.rhel7.noarch
--> Processing Dependency: python-argcomplete for package: 
barman-2.1-1.rhel7.noarch
--> Running transaction check
---> Package python-argcomplete.noarch 0:0.3.7-1.rhel7 will be installed
---> Package python-argh.noarch 0:0.23.0-1.rhel7 will be installed
---> Package python-dateutil.noarch 1:2.5.3-3.rhel7 will be installed
--> Processing Dependency: python-six for package: 1:
python-dateutil-2.5.3-3.rhel7.noarch
---> Package python-psycopg2.x86_64 0:2.6.2-3.rhel7 will be installed
--> Processing Dependency: postgresql96-libs for package: 
python-psycopg2-2.6.2-3.rhel7.x86_64
--> Running transaction check
---> Package postgresql96-libs.x86_64 0:9.6.1-1PGDG.rhel7 will be installed
---> Package python-six.noarch 0:1.9.0-2.el7 will be installed
--> Finished Dependency Resolution
 
Dependencies Resolved
 
================================================================================
 Package                Arch       Version                 Repository      Size
================================================================================
Installing:
 barman                 noarch     2.1-1.rhel7             pgdg96         248 k
Installing for dependencies:
 postgresql96-libs      x86_64     9.6.1-1PGDG.rhel7       pgdg96         308 k
 python-argcomplete     noarch     0.3.7-1.rhel7           pgdg96          23 k
 python-argh            noarch     0.23.0-1.rhel7          pgdg96          33 k
 python-dateutil        noarch     1:2.5.3-3.rhel7         pgdg96         241 k
 python-psycopg2        x86_64     2.6.2-3.rhel7           pgdg96         131 k
 python-six             noarch     1.9.0-2.el7             ol7_latest      28 k
 
Transaction Summary
================================================================================
Install  1 Package (+6 Dependent packages)
 
Total download size: 1.0 M
Installed size: 3.6 M
Is this ok [y/d/N]: y
Downloading packages:
(1/7): barman-2.1-1.rhel7.noarch.rpm                       | 248 kB   00:03
(2/7): python-argcomplete-0.3.7-1.rhel7.noarch.rpm         |  23 kB   00:00
(3/7): python-argh-0.23.0-1.rhel7.noarch.rpm               |  33 kB   00:00
(4/7): postgresql96-libs-9.6.1-1PGDG.rhel7.x86_64.rpm      | 308 kB   00:04
(5/7): python-six-1.9.0-2.el7.noarch.rpm                   |  28 kB   00:00
(6/7): python-dateutil-2.5.3-3.rhel7.noarch.rpm            | 241 kB   00:01
(7/7): python-psycopg2-2.6.2-3.rhel7.x86_64.rpm            | 131 kB   00:01
--------------------------------------------------------------------------------
Total                                              163 kB/s | 1.0 MB  00:06
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : python-argh-0.23.0-1.rhel7.noarch                            1/7
  Installing : postgresql96-libs-9.6.1-1PGDG.rhel7.x86_64                   2/7
  Installing : python-psycopg2-2.6.2-3.rhel7.x86_64                         3/7
  Installing : python-argcomplete-0.3.7-1.rhel7.noarch                      4/7
  Installing : python-six-1.9.0-2.el7.noarch                                5/7
  Installing : 1:python-dateutil-2.5.3-3.rhel7.noarch                       6/7
  Installing : barman-2.1-1.rhel7.noarch                                    7/7
  Verifying  : python-psycopg2-2.6.2-3.rhel7.x86_64                         1/7
  Verifying  : python-six-1.9.0-2.el7.noarch                                2/7
  Verifying  : python-argcomplete-0.3.7-1.rhel7.noarch                      3/7
  Verifying  : postgresql96-libs-9.6.1-1PGDG.rhel7.x86_64                   4/7
  Verifying  : python-argh-0.23.0-1.rhel7.noarch                            5/7
  Verifying  : barman-2.1-1.rhel7.noarch                                    6/7
  Verifying  : 1:python-dateutil-2.5.3-3.rhel7.noarch                       7/7
 
Installed:
  barman.noarch 0:2.1-1.rhel7
 
Dependency Installed:
  postgresql96-libs.x86_64 0:9.6.1-1PGDG.rhel7
  python-argcomplete.noarch 0:0.3.7-1.rhel7
  python-argh.noarch 0:0.23.0-1.rhel7
  python-dateutil.noarch 1:2.5.3-3.rhel7
  python-psycopg2.x86_64 0:2.6.2-3.rhel7
  python-six.noarch 0:1.9.0-2.el7
Complete!

Everything is installed on both servers :

– PostgreSQL 9.6

– DMK last version

– barman

Now we configure as follows:

The barman server is pg2 : 192.168.1.101

The database server is pg1 : 192.168.1.100

 

On the database server, we create a barman user:

postgres@:5432) [postgres] > create user barman superuser login encrypted password 
'barman';
CREATE ROLE

And a barman_streaming user:

postgres@: [postgres] > create user barman_streaming replication encrypted password 
'barman';
CREATE ROLE

We modify the following parameters max_replication_slots (which specifies the maximum number of replication slots the server can support), and max_wal_senders (specifies the maximum number of simultaneously running wal sender processes):

postgres@:5432) [postgres] > alter system set max_replication_slots=10;
ALTER SYSTEM
postgres@:5432) [postgres] > alter system set max_wal_senders=10;
ALTER SYSTEM

As those previous parameters have been modified, we need to restart the database, we use pgrestart which is a DMK alias for pg_ctl -D ${PGDATA} restart -m fast

postgres@pg1:/home/postgres/ [PG1] pgrestart
waiting for server to shut down.... done
server stopped
server starting
postgres@pg1:/home/postgres/ [PG1] 2017-02-06 15:59:14.756 CET - 1 - 17008 -  
- @ LOG:  redirecting log output to logging collector process
2017-02-06 15:59:14.756 CET - 2 - 17008 -  - 
@ HINT:  Future log output will appear in directory 
"/u01/app/postgres/admin/PG1/pg_log".

We modify the pg_hba.conf on the barman server in order to allow connections from the barman server to the database server as follows:

host    all             barman          192.168.1.101/24       md5
host    replication     barman_streaming 192.168.1.101/24      md5

We modify the .pgpass file on the barman server in order not to be asked for passwords:

postgres@pg2:/home/postgres/ [pg96] cat .pgpass
*:*:*:postgres:postgres
192.168.1.100:*:*:barman:barman
192.168.1.100:*:*:barman_streaming:barman

Finally we test the connection from the barman server to the database server:

postgres@pg2:/home/postgres/ [pg96] psql -c 'select version()'
 -U barman -h 192.168.1.100 -p 5432 postgres
                                                 version
 
--------------------------------------------------------------------------------

 PostgreSQL 9.6.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (
Red Hat 4.8.5-11), 64-bit
(1 row)
postgres@pg2:/home/postgres/ [pg96] psql -U barman_streaming -h 192.168.1.100 
-p 5432 -c "IDENTIFY_SYSTEM" replication=1
      systemid       | timeline |  xlogpos  | dbname
---------------------+----------+-----------+--------
 6384063115439945376 |        1 | 0/F0006F0 |
(1 row)

Now it’s time to create a configuration file pg96.conf in $DMK_HOME/etc/barman.d in the barman server:

[pg96]
description =  "PostgreSQL 9.6 server"
conninfo = host=192.168.1.100 port=5432 user=barman dbname=postgres
backup_method = postgres
streaming_conninfo = host=192.168.1.100 port=5432 user=barman_streaming 
dbname=postgres
streaming_wals_directory = /u99/received_wal
streaming_archiver = on
slot_name = barman

We create a barman.conf file in $DMK_HOME/etc as follows, mainly defining the barman_user, the configuration file directory and the barman backup home, the barman lock directory and the log directory:

postgres@pg2:/u01/app/postgres/local/dmk/etc/ [pg96] cat barman.conf
; Barman, Backup and Recovery Manager for PostgreSQL
; http://www.pgbarman.org/ - http://www.2ndQuadrant.com/
;
; Main configuration file
 
[barman]
; System user
barman_user = postgres
 
; Directory of configuration files. Place your sections in separate files 
with .conf extension
; For example place the 'main' server section in /etc/barman.d/main.conf
configuration_files_directory = /u01/app/postgres/local/dmk/etc/barman.d
 
; Main directory
barman_home = /u99/backup
 
; Locks directory - default: %(barman_home)s
barman_lock_directory = /u01/app/postgres/local/dmk/etc/
 
; Log location
log_file = /u01/app/postgres/local/dmk/log/barman.log
 
; Log level (see https://docs.python.org/3/library/logging.html#levels)
log_level = DEBUG
 
; Default compression level: possible values are None (default), 
bzip2, gzip, pigz, pygzip or pybzip2
compression = gzip
 
; Pre/post backup hook scripts
;pre_backup_script = env | grep ^BARMAN
;pre_backup_retry_script = env | grep ^BARMAN
;post_backup_retry_script = env | grep ^BARMAN
;post_backup_script = env | grep ^BARMAN
 
; Pre/post archive hook scripts
;pre_archive_script = env | grep ^BARMAN
;pre_archive_retry_script = env | grep ^BARMAN
;post_archive_retry_script = env | grep ^BARMAN
;post_archive_script = env | grep ^BARMAN
 
; Global retention policy (REDUNDANCY or RECOVERY WINDOW) - default empty
retention_policy = RECOVERY WINDOW OF 4 WEEKS
 
; Global bandwidth limit in KBPS - default 0 (meaning no limit)
;bandwidth_limit = 4000
 
; Immediate checkpoint for backup command - default false
;immediate_checkpoint = false
 
; Enable network compression for data transfers - default false
;network_compression = false
 
; Number of retries of data copy during base backup after an error - default 0
;basebackup_retry_times = 0
 
; Number of seconds of wait after a failed copy, before retrying - default 30
;basebackup_retry_sleep = 30
 
; Maximum execution time, in seconds, per server
; for a barman check command - default 30
;check_timeout = 30
 
; Time frame that must contain the latest backup date.
; If the latest backup is older than the time frame, barman check
; command will report an error to the user.
; If empty, the latest backup is always considered valid.
; Syntax for this option is: "i (DAYS | WEEKS | MONTHS)" where i is an
; integer > 0 which identifies the number of days | weeks | months of
; validity of the latest backup for this check. Also known as 'smelly backup'.
;last_backup_maximum_age =
 
; Minimum number of required backups (redundancy)
;minimum_redundancy = 1

 

In order to enable streaming of transaction logs and to use replication slots, we run the following command on the barman server:

postgres@pg2:/u01/app/postgres/local/dmk/etc/ [pg96] barman receive-wal 
--create-slot pg96
Creating physical replication slot 'barman' on server 'pg96'
Replication slot 'barman' created

Then we can test:

We can force a log switch on the database server:

postgres@pg2:/u01/app/postgres/local/dmk/etc/ [pg96] barman switch-xlog 
--force pg96
The xlog file 00000001000000000000000F has been closed on server 'pg96'

 

We start receive wal:

postgres@pg2:/u99/received_wal/ [pg96] barman -c 
/u01/app/postgres/local/dmk/etc/barman.conf receive-wal pg96
Starting receive-wal for server pg96
pg96: pg_receivexlog: starting log streaming at 0/68000000 (timeline 3)
pg96: pg_receivexlog: finished segment at 0/69000000 (timeline 3)
pg96: pg_receivexlog: finished segment at 0/6A000000 (timeline 3)
pg96: pg_receivexlog: finished segment at 0/6B000000 (timeline 3)
pg96: pg_receivexlog: finished segment at 0/6C000000 (timeline 3)

 

We can check the barman configuration:

postgres@pg2:/u99/restore_test/ [pg96] barman check pg96
Server pg96:
                    PostgreSQL: OK
                    superuser: OK
                    PostgreSQL streaming: OK
                    wal_level: OK
                    replication slot: OK
                    directories: OK
                    retention policy settings: OK
                    backup maximum age: OK (no last_backup_maximum_age provided)
                    compression settings: OK
                    failed backups: FAILED (there are 1 failed backups)
                    minimum redundancy requirements: OK (have 3 backups, 
                    expected at least 0)
                    pg_basebackup: OK
                    pg_basebackup compatible: OK
                    pg_basebackup supports tablespaces mapping: OK
                    pg_receivexlog: OK
                    pg_receivexlog compatible: OK
                    receive-wal running: OK
                    archiver errors: OK

We can run a barman archive-wal command:

postgres@pg2:/home/postgres/ [pg96] barman archive-wal pg96
Processing xlog segments from streaming for pg96
                    00000003.history
                    000000030000000000000067
                    000000030000000000000068

And finally you can run a backup with the command:

postgres@pg2:/home/postgres/ [pg96] barman backup pg96
Starting backup using postgres method for server pg96 in 
/u99/backup/pg96/base/20170214T103226
Backup start at xlog location: 0/69000060 (000000030000000000000069, 00000060)
Copying files.
Copy done.
Finalising the backup.
Backup size: 60.1 MiB
Backup end at xlog location: 0/6B000000 (00000003000000000000006A, 00000000)
Backup completed
Processing xlog segments from streaming for pg96
                    000000030000000000000069

We can list the backups :

postgres@pg2:/u02/pgdata/ [pg96] barman list-backup pg96
pg96 20170214T103226 - Tue Feb 14 09:32:27 2017 - Size: 60.2 MiB - WAL Size: 0 B 
(tablespaces: tab1:/u02/pgdata/PG1/mytab)
pg96 20170207T061338 - Tue Feb  7 06:19:38 2017 - Size: 29.0 MiB - WAL Size: 0 B
pg96 20170207T060633 - Tue Feb  7 06:12:33 2017 - Size: 29.0 MiB - WAL Size: 0 B

 

We have the possibility to test a restore for example on the barman server :

postgres@pg2:/u02/pgdata/ [pg96] barman recover pg96 20170214T103226 
/u99/restore_test/
Starting local restore for server pg96 using backup 20170214T103226
Destination directory: /u99/restore_test/
                    24648, tab1, /u02/pgdata/PG1/mytab
Copying the base backup.
Copying required WAL segments.
Generating archive status files
Identify dangerous settings in destination directory.
 
IMPORTANT
These settings have been modified to prevent data losses
 
postgresql.conf line 71: archive_command = false
postgresql.auto.conf line 4: archive_command = false

Your PostgreSQL server has been successfully prepared for recovery, the /u99/test_restore directory contains:

postgres@pg2:/u99/restore_test/ [pg96] ll

total 64
-rw-------  1 postgres postgres  208 Feb 14 10:32 backup_label
-rw-------  1 postgres postgres  207 Feb 14 10:32 backup_label.old
drwx------ 10 postgres postgres   98 Feb 14 10:32 base
drwx------  2 postgres postgres 4096 Feb 14 10:32 global
drwx------  2 postgres postgres    6 Feb 14 10:32 mytab
drwx------  2 postgres postgres   17 Feb 14 10:32 pg_clog
drwx------  2 postgres postgres    6 Feb 14 10:32 pg_commit_ts
drwx------  2 postgres postgres    6 Feb 14 10:32 pg_dynshmem
-rw-------  1 postgres postgres 4416 Feb 14 10:32 pg_hba.conf
-rw-------  1 postgres postgres 4211 Feb 14 10:32 pg_hba.conf_conf
-rw-------  1 postgres postgres 1636 Feb 14 10:32 pg_ident.conf
drwx------  4 postgres postgres   65 Feb 14 10:32 pg_logical
drwx------  4 postgres postgres   34 Feb 14 10:32 pg_multixact
drwx------  2 postgres postgres   17 Feb 14 10:32 pg_notify
drwx------  2 postgres postgres    6 Feb 14 10:32 pg_replslot
drwx------  2 postgres postgres    6 Feb 14 10:32 pg_serial
drwx------  2 postgres postgres    6 Feb 14 10:32 pg_snapshots
drwx------  2 postgres postgres    6 Feb 14 10:32 pg_stat
drwx------  2 postgres postgres    6 Feb 14 10:32 pg_stat_tmp
drwx------  2 postgres postgres   17 Feb 14 10:32 pg_subtrans
drwx------  2 postgres postgres   18 Feb 14 10:32 pg_tblspc
drwx------  2 postgres postgres    6 Feb 14 10:32 pg_twophase
-rw-------  1 postgres postgres    4 Feb 14 10:32 PG_VERSION
drwx------  3 postgres postgres   81 Feb 14 10:39 pg_xlog
-rw-------  1 postgres postgres  391 Feb 14 10:39 postgresql.auto.conf
-rw-------  1 postgres postgres  358 Feb 14 10:32 postgresql.auto.conf.origin
-rw-------  1 postgres postgres 7144 Feb 14 10:39 postgresql.conf
-rw-------  1 postgres postgres 7111 Feb 14 10:32 postgresql.conf.origin
-rw-------  1 postgres postgres   56 Feb 14 10:32 recovery.done

If you need to  restore your backup on the pg1 original database server, you have to use the –remote-ssh-command as follows (you specify the hostname where you want restore, and the PGDATA directory)

postgres@pg2:/home/postgres/.ssh/ [pg96] barman recover --remote-ssh-command "ssh postgres@pg1" pg96 20170214T103226 /u02/pgdata/PG1
Starting remote restore for server pg96 using backup 20170214T103226
Destination directory: /u02/pgdata/PG1
       24648, tab1, /u02/pgdata/PG1/mytab
Copying the base backup.
Copying required WAL segments.
Generating archive status files
Identify dangerous settings in destination directory.
 
IMPORTANT
These settings have been modified to prevent data losses
 
postgresql.conf line 71: archive_command = false
postgresql.auto.conf line 4: archive_command = false
 
Your PostgreSQL server has been successfully prepared for recovery!

You also have the possibility to realise a point in time recovery.

In my PG1 database I create a table employes and insert some data :

postgres@[local]:5432) [blubb] > create table employes (name varchar(10));
CREATE TABLE
(postgres@[local]:5432) [blubb] > insert into employes values ('fiona');
INSERT 0 1
(postgres@[local]:5432) [blubb] > insert into employes values ('cathy');
INSERT 0 1
(postgres@[local]:5432) [blubb] > insert into employes values ('helene');
INSERT 0 1
(postgres@[local]:5432) [blubb] > select * from employes;
  name  
--------
 fiona
 cathy
 helene

A few minutes later I insert some more records in the employes table:

postgres@[local]:5432) [blubb] > insert into employes values ('larry');
INSERT 0 1
(postgres@[local]:5432) [blubb] > insert into employes values ('bill');
INSERT 0 1
(postgres@[local]:5432) [blubb] > insert into employes values ('steve');
INSERT 0 1
(postgres@[local]:5432) [blubb] > select * from employes;
  name  
--------
 fiona
 cathy
 helene
 larry
 bill
 steve

The first data were create at 15:15, let’s see if the pitr barman restore works correctly:

I stop the PG1 database :

postgres@pg1:/u02/pgdata/ [PG1] pgstop
waiting for server to shut down....... done
server stopped

I delete the PGDATA directory:

postgres@pg1:/u02/pgdata/ [PG1] rm -rf PG1

And from the barman server I run the pitr recovery command using the –target-time argument:

postgres@pg2:/home/postgres/ [pg96] barman recover --remote-ssh-command "ssh postgres@pg1" pg96 
--target-time "2017-02-14 15:15:48"  20170214T141055 /u02/pgdata/PG1 
Starting remote restore for server pg96 using backup 20170214T141055
Destination directory: /u02/pgdata/PG1
Doing PITR. Recovery target time: '2017-02-14 15:15:48'
       24648, tab1, /u02/pgdata/PG1/mytab
Copying the base backup.
Copying required WAL segments.
Generating recovery.conf
Identify dangerous settings in destination directory.
 
IMPORTANT
These settings have been modified to prevent data losses
 
postgresql.conf line 72: archive_command = false
postgresql.auto.conf line 4: archive_command = false
 
Your PostgreSQL server has been successfully prepared for recovery!

I restart my PG1 database the data are correctly restored, just before the Larry, Bill and Steve insertion into the employes tables

postgres@[local]:5432) [blubb] > select * from employes;
  name  
--------
 fiona
 cathy
 helene
(3 rows)