Physical backup using Barman tools for postgreSQL database

Backup and Recovery Manager (BARMAN) is free and open source backup and recovery tool and is developed by 2ndQuadrant. The barman tool written in python and is currently available for Linux operating systems. The barman supports from PostgreSQL version 8.3 onward and capable of taking backup for different PostgreSQL versions and its completely free. The main features of barman,

• Remote backup and recovery
• Backup Multiple PostgreSQL instance into central location
• Centralized Backup catalogs
• Incremental backups and Retention policies
• WAL streaming and compression

Environment for this post,

• pg01 linux server, running PostgreSQL production database server.
• pg03 linux server, used for Barman(2.1).

1.) Installing Barman : The barman can be installed either using rpm or from source code and can be downloaded from www.pgbarman.org. If you are using RHEL or CentOS, you need to install extra package for EPEL (Extra Package for Enterprise Linux) as superuser or root.

[root@pg01 ~]# yum install epel-release

then install the barman, Note, using barman rpm will create user and group barman by default. if you are doing installation using source code. make sure to create the barman user and group before installation.

[root@pg01 ~]# yum install barman

2.) Setup the passwordless SSH connectivity between pg01 and pg02. Note, my database on pg01 running with user postgres and the barman is configured using barman user. refer my other post for passwordless SSH configuration.

3.) Configure barman: After installation of barman, you can see the file /etc/barman.conf which contains global barman configuration and separate section for each server configuration. There are other configuration files that exist in /etc/barman.d directory. you can use the sample files for backup server configuration. I prefer in my environment to keep the global configuration in /etc/barman.conf and the server level configuration in /etc/barman.d/ for each server. You can use the /etc/barman.conf file for both global as well as server level configuration. So i kept the below parameters in barman.conf. Edit the /etc/barman.conf file as root or superuser privilege.

[root@pg01 ~]# vi /etc/barman.conf
[barman]
barman_user = barman
configuration_files_directory = /etc/barman.d
barman_home = /u01/barman
barman_lock_directory = /u01/barman/run
log_file = /u01/barman/barman.log
log_level = INFO
compression = gzip
retention_policy = RECOVERY WINDOW OF 7 DAYS
immediate_checkpoint = true
basebackup_retry_times = 3
basebackup_retry_sleep = 30

Comments, The above parameter are self explanatory, i will explain the important one here.

• compression : The default value is gzip. this mean the PostgreSQL WAL files will be compressed while copying into backup directory to save the space.
• retention_policy : How many days or weeks you want to retain the particular backup before cleaning up or overwriting. Look for official document for more detail.
• immediate_checkpoint : This directs the PostgreSQL database to perform the checkpoint to write the memory data into datafiles before starting the backup.

Now i have created the separate server level configuration files into /etc/barman.d directory. You can copy the sample file as superuser privilege and edit it. on my barman server i have the following files.

[root@pg03 ~]# cd /etc/barman.d/

[root@pg03 barman.d]# ls -ltr

-rw-r--r--. 1 root root  263 Jan  4 10:23 pg02.conf

-rw-r--r--. 1 root root  199 Jan  4 10:30 pg01.conf

[root@pg03 barman.d]# cat pg01.conf

[pg01]

description = "Postgres Database on PG01 server"

active = off

archiver = on

backup_method = rsync

ssh_command = ssh postgres@pg01

conninfo = host=pg01 user=barman dbname=postgres

4.) Now, i have added the pg01 server to the list of barman. currently i kept the server in inactive state until complete all the configuration. You can verify this by using command as follows either by superuser or barman user, here i am using root server.

[root@pg03 barman.d]# barman list-server
pg01 - Postgres Database on PG01 server (inactive)
pg02 - PostgreSQL Database on PG02 server (inactive)

5.) Setup the continuous archiving of WAL files between PostgreSQL and Barman. Execute the below command and note down the incoming_wals_directory location. 

[root@pg03 barman.d]# barman show-server pg01 |grep incoming_wals_directory
incoming_wals_directory: /u01/barman/pg01/incoming

6.) Now its time to initialize the directories layout for pg01 server and to fulfill that, use the command as follows,

[root@pg03 barman.d]# barman check pg01

Server pg01 (inactive):

 WAL archive: FAILED (please make sure WAL shipping is setup)

PostgreSQL: OK

is_superuser: OK

wal_level: FAILED (please set it to a higher level than 'minimal')

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 2 backups, expected at least 0)

ssh: OK (PostgreSQL server)

not in recovery: OK

archive_mode: FAILED (please set it to 'on' or 'always')

archive_command: FAILED (please set it accordingly to documentation)

archiver errors: OK

[root@pg03 barman.d]# 

7.) You can see there are multiple failed commands returned and hence pg01 server is not yet ready for backup. So, go to pg01 server where my PostgreSQL database is running and edit the postgresql.conf file, update the archive_mode, archive_command and wal_level parameters. After setting the below value restart the PostgreSQL database server.

[postgres@pg01 data]$ vi postgresql.conf

wal_level = replica

archive_mode = on

archive_command = 'rsync -a %p barman@pg03:/u01/barman/pg01/incoming/%f'

8.) Now activate the pg01 server by removing the parameter active or set it to active=on in pg01.conf on barman server in /etc/barman.d

[root@pg03 barman.d]# vi pg01.conf

[pg01]

description = "Postgres Database on PG01 server"

#active = off

active = on

archiver = on

backup_method = rsync

ssh_command = ssh postgres@pg01

conninfo = host=pg01 user=barman dbname=postgres

9.) Now run the check command again to see if the pg01 is ready for backup.

[root@pg03 barman.d]# barman check pg01

Server pg01:

PostgreSQL: OK

is_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 2 backups, expected at least 0)

ssh: OK (PostgreSQL server)

not in recovery: OK

archive_mode: OK

archive_command: OK

continuous archiving: OK

archiver errors: OK

[root@pg03 barman.d]# 

everything looks good and no failures, note if your server is complaining wal archive : failed even your set the parameter correctly, this means there is not WAL files have been generated and shipped because of low or no workload on postgres database. hence you can force the postgreSQL database to generate and archive the files by using below command. I have not see that error because, i have sufficient load generated using customized scripts to insert the data on multiple tables.

[root@pg03 barman.d]# bamrna switch-xlog --force --archive pg01

[root@pg03 barman.d]# barman archive-wal pg01

verify the barman log files at log_file = /u01/barman/barman.log stated in /etc/barman.conf to see any error.

9.) Now we are to go ahead and take our first backup as below.

[root@pg03 barman.d]# barman backup pg01

Starting backup using rsync-exclusive method for server pg01 in /u01/barman/pg01/base/20180104T114652

Backup start at LSN: 0/51000028 (000000040000000000000051, 00000028)

Starting backup copy via rsync/SSH for 20180104T114652

Copy done (time: 23 seconds)

Asking PostgreSQL server to finalize the backup.

Backup size: 527.2 MiB

Backup end at LSN: 0/51000130 (000000040000000000000051, 00000130)

Backup completed (start time: 2018-01-04 11:46:52.345168, elapsed time: 26 seconds)

Processing xlog segments from file archival for pg01

000000040000000000000051

000000040000000000000051.00000028.backup

[root@pg03 barman.d]# 

10.) Check the backup is created in the location as per barman.conf files.

[root@pg03 pg01]# cd /u01/barman/pg01

[root@pg03 pg01]# ls -ltr

drwxrwxr-x. 2 barman barman 4096 Jan  2 20:52 streaming

drwxrwxr-x. 2 barman barman 4096 Jan  2 20:52 errors

drwxrwxr-x. 3 barman barman 4096 Jan  2 21:50 wals

drwxrwxr-x. 5 barman barman 4096 Jan  4 11:46 base

drwxrwxr-x. 2 barman barman 4096 Jan  4 11:47 incoming

Comments,

• base : Base directory where the backup directories created with timestamp and files were saved. you can go through each of the directories.
• incoming : Directory where the PostgreSQL database sends the WALS for archiving.
• wals : Directory where the barman copies the WALS from incoming directory

11.) List the backup by using the command list-backup

[root@pg03 20180104T114652]# barman list-backup pg01

pg01 20180104T114652 - Thu Jan  4 11:47:18 2018 - Size: 543.2 MiB - WAL Size: 32.0 MiB

Here, the format is,

server - backup_id - timestamp for backup - size of backup - size of WALS.

you can see the detailed information about backup as,

[root@pg03 20180104T114652]# barman show-backup pg01 20180104T114652

Backup 20180104T114652:

  Server Name            : pg01

  Status                 : DONE

  PostgreSQL Version     : 90605

  PGDATA directory       : /u01/app/postgres/data

  Base backup information:

    Disk usage           : 527.2 MiB (543.2 MiB with WALs)

    Incremental size     : 527.2 MiB (-0.00%)

    Timeline             : 4

    Begin WAL            : 000000040000000000000051

    End WAL              : 000000040000000000000051

    WAL number           : 1

    Begin time           : 2018-01-04 11:46:51.788912+05:30

    End time             : 2018-01-04 11:47:18.643722+05:30

    Copy time            : 23 seconds + 2 seconds startup

    Estimated throughput : 22.0 MiB/s

    Begin Offset         : 40

    End Offset           : 304

    Begin LSN           : 0/51000028

    End LSN             : 0/51000130

   WAL information:

    No of files          : 2

    Disk usage           : 32.0 MiB

    WAL rate             : 0.08/hour

    Last available       : 000000040000000000000051

  Catalog information:

    Retention Policy     : not enforced

    Previous Backup      : 20180102T221901

    Next Backup          : - (this is the latest base backup)

Note: if you multiple backups taken you can list out them using the unique backup_id.

If you are interested to go and look out what are the contents of these backups, you can use.

[root@pg03 20180104T114652]# barman list-files pg01 20180104T114652

/u01/barman/pg01/base/20180104T114652/backup.info

/u01/barman/pg01/base/20180104T114652/data/pg_ident.conf

/u01/barman/pg01/base/20180104T114652/data/PG_VERSION

/u01/barman/pg01/base/20180104T114652/data/postgresql.auto.conf

/u01/barman/pg01/base/20180104T114652/data/backup_label

/u01/barman/pg01/base/20180104T114652/data/logfile.log

/u01/barman/pg01/base/20180104T114652/data/backup_label.old

/u01/barman/pg01/base/20180104T114652/data/pg_hba.conf

/u01/barman/pg01/base/20180104T114652/data/postgresql.conf.sample

<< Output truncated for clarity >>

you can also use the below command to see the latest or oldest backup.

[root@pg03 20180104T114652]# barman show-backup pg01 latest

[root@pg03 20180104T114652]# barman show-backup pg01 oldest

• last or latest -> show you the recent available backup
• first or oldest -> show you the oldest available backup

12.) Scheduling and automate backup in crontab : The backup should be scheduled during off-peak hours and to schedule and automate the backup, we will use OS crontab. The Barman comes up with the predefined backup scripts and as DBA you need to just schedule in to crontab. if you have installed the Barman using rpm, then there will be automated jobs created for you to move the WALS from incoming directory to wals directory and archive it. the jobs runs every minute. you can find the jobs in /etc/cron.d/barman

[root@pg03 ~]# cd /etc/cron.d/
[root@pg03 cron.d]# cat barman 
# m h  dom mon dow   user     command
  * *    *   *   *   barman   [ -x /usr/bin/barman ] && /usr/bin/barman -q cron

[root@pg03 cron.d]# 

If the above file is not there because you have installed it from source. still you can create the job and add into crontab as barman user.

[barman@pg02 ~] $ crontab -e
30 19 * * * /usr/bin/barman backup pg01 
* * * * * /usr/bin/barman cron

I have scheduled the backup to run at 19:30 everyday and the entry is for WALS maintenance and it runs everyday and every minute.

Hence, this is the completion of Barman for PostgreSQL database backup. The Barman is ultimate tool for backup and recoveries and its free. The Barman is ready for production ready tool and can be deployed to production environment. Always make sure to test it in development or test environment before going to production.

**** Watch the Post for Recoveries scenarios in upcoming posts*******