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)