EnterpriseDB Backup and Recovery Tool (BART)

By Mouhamadou Diaw

Many tools are proposed for backup and recovery operation of a PostgreSQL database. EnterpriseDB has developed a product named BART (Backup and Recovery Tool). Use of this product needs a subscription but you can download a trial version for test purpose.
In this blog we are using BART with PostgreSQL 9.6.1. BART allows backing up over the net and can centralize backups of many PostgreSQL servers.
We are using the following configuration:
Bart Host: pgserver.localdomain
PostgreSQL server 1: pgserve1.localdomain
PostgreSQL server 2: pgserve2.localdomain
We suppose that there are clusters running on pgserver1 and pgserver2 and the archive mode is activated for both clusters
What is needed on the server hosting BART (i.e. pgserver in our case)?
1. We need to install rpm package using yum or rpm command
2. We need PostgreSQL binaries installed. Note that the version of the binaries should be at least the same than those installed on the servers to be backed up. In our case as we are using 9.6.1 for databases, binaries should be 9.6.1 or higher. Note that we don’t need any cluster running on pgserver.
For the package installation, we are using yum.

[root@pgserver 96]# yum localinstall edb-bart-1.1.0-1.rhel7.x86_64.rpm

By default BART is installed in /usr/edb-bart-1.1.

[root@pgserver 96]# cd /usr/edb-bart-1.1/
[root@pgserver edb-bart-1.1]# ls
bart_license.txt bin etc
[root@pgserver edb-bart-1.1]# ls -l
total 16
-rw-r--r--. 1 root root 15272 Jul 3 2015 bart_license.txt
drwxr-xr-x. 2 root root 17 Dec 6 14:15 bin
drwxr-xr-x. 2 root root 21 Dec 6 14:15 etc
[root@pgserver edb-bart-1.1]#

Before configuring the BART configuration file, the ssh connectivity should be configured between servers for the Linux user used for backups, here we are using postgres.

[postgres@pgserver ~]$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/home/postgres/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/postgres/.ssh/id_rsa.
Your public key has been saved in /home/postgres/.ssh/id_rsa.pub.
The key fingerprint is:
b9:fb:1a:90:c2:76:73:6e:fe:28:38:cf:42:52:7a:81 [email protected]
The key's randomart image is:
+--[ RSA 2048]----+
| |
| |
| . |
| E.o . . |
| o+.= S |
| o.oo = . |
| + . = |
| +..o + |
| +o.=+o |
+-----------------+
[postgres@pgserver ~]$ ssh-copy-id -i .ssh/id_rsa.pub pgserver1
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
postgres@pgserver1's password:
Number of key(s) added: 1
Now try logging into the machine, with: "ssh 'pgserver1'"
and check to make sure that only the key(s) you wanted were added.
[postgres@pgserver ~]$ ssh-copy-id -i .ssh/id_rsa.pub pgserver2
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
postgres@pgserver2's password:
Number of key(s) added: 1
Now try logging into the machine, with: "ssh 'pgserver2'"
and check to make sure that only the key(s) you wanted were added.
[postgres@pgserver ~]$


[postgres@pgserver ~]$ ssh pgserver1 date
Tue Dec 6 14:24:35 CET 2016
[postgres@pgserver ~]$ ssh pgserver2 date
Tue Dec 6 14:24:40 CET 2016
[postgres@pgserver ~]$

We also need a super user created on each cluster to be backed up

postgres=# CREATE ROLE backupuser WITH LOGIN SUPERUSER PASSWORD 'root';
CREATE ROLE
postgres=#

And we will create a .pgpass for this user to be able to connect without being asked for password

[postgres@pgserver1 ~]$ echo "*:5432:*:backupuser:root" >> ~/.pgpass
[postgres@pgserver1 ~]$ chmod 600 .pgpass

[postgres@pgserver2 ~]$ echo "*:5432:*:backupuser:root" >> ~/.pgpass
[postgres@pgserver2 ~]$ chmod 600 .pgpass

The configuration file is located under $BART_INSTALL_DIRECTORY/etc. It must contain a part for BART itself and a part for each server to be backed up. Below an example of our bart.cfg

[root@pgserver etc]# pwd
/usr/edb-bart-1.1/etc


[root@pgserver etc]# cat bart.cfg | grep -v ^#
[BART]
bart-host= [email protected]
backup_path = /u01/app/backup
pg_basebackup_path = /u01/app/PostgreSQL/9.6/bin/pg_basebackup
logfile = /tmp/bart.log
xlog-method = fetch
retention_policy = 1 DAYS
wal_compression = enabled

[PGSERVER1]
host = 192.168.56.36
port = 5432
user = backupuser
backup-name = PGSERVER1_%year-%month-%dayT%hour:%minute
remote-host = postgres@pgserver1
description = “Postgres server1”

[PGSERSERVER2]
host = 192.168.56.37
port = 5432
user = backupuser
backup-name = PGSERVER2_%year-%month-%dayT%hour:%minute
remote-host = postgres@pgserver2
description = "Postgres server2"
[root@pgserver etc]#

The backup_path /u01/app/backup is only needed on the BART host (pgserver) as backup will be done over the net. But on both servers pgserver1 and pgserver2 the pg_hba.conf need to be updated to allow connection from the BART host. Below two lines we add

[postgres@pgserver1 data]$ grep 192.168.56.0 pg_hba.conf
host all all 192.168.56.0/24 md5
host replication backupuser 192.168.56.0/24 md5
[postgres@pgserver1 data]$


[postgres@pgserver2 data]$ grep 192.168.56.0 pg_hba.conf
host all all 192.168.56.0/24 md5
host replication backupuser 192.168.56.0/24 md5
[postgres@pgserver2 data]$

Now we can initialize the catalog, first for pgserver1 and after for pgserver2 (note this can be done for both servers in a single command)

[postgres@pgserver bin]$ ./bart -d -c /usr/edb-bart-1.1/etc/bart.cfg init -s PGSERVER1 -o
DEBUG: Server: Global, Now: 2016-12-06 15:51:04 CET, RetentionWindow: 86400 (secs) ==> 24 hour(s)
INFO: setting archive_command for server 'pgserver1'
DEBUG: SHOW archive_command: test ! -f /u01/app/archive/%f && cp %p /u01/app/archive/%f
DEBUG: archive command set to: ALTER SYSTEM SET archive_command TO 'scp %p [email protected]:/u01/app/backup/pgserver1/archived_wals/%f'
WARNING: archive_command is set. server restart is required
[postgres@pgserver bin]$


[postgres@pgserver bin]$ ./bart -d -c /usr/edb-bart-1.1/etc/bart.cfg init -s PGSERVER2 -o
DEBUG: Server: Global, Now: 2016-12-06 19:01:08 CET, RetentionWindow: 86400 (secs) ==> 24 hour(s)
INFO: setting archive_command for server 'pgserver2'
DEBUG: SHOW archive_command: test ! -f /u01/app/archive/%f && cp %p /u01/app/archive/%f
DEBUG: archive command set to: ALTER SYSTEM SET archive_command TO 'scp %p [email protected]:/u01/app/backup/pgserver2/archived_wals/%f'
WARNING: archive_command is set. server restart is required
[postgres@pgserver bin]$

Now it’s time to launch a backup

[postgres@pgserver bin]$ ./bart -c /usr/edb-bart-1.1/etc/bart.cfg BACKUP -s PGSERVER1

INFO: creating backup for server ‘pgserver1’
INFO: backup identifier: ‘1481037296469’
39433/39433 kB (100%), 1/1 tablespace

INFO: backup completed successfully
INFO: backup checksum: fb105a542d8856e9d5bd95410337463f of base.tar
INFO:
BACKUP DETAILS:
BACKUP STATUS: active
BACKUP IDENTIFIER: 1481037296469
BACKUP NAME: PGSERVER1_2016-12-06T16:14
BACKUP LOCATION: /u01/app/backup/pgserver1/1481037296469
BACKUP SIZE: 38.51 MB
BACKUP FORMAT: tar
XLOG METHOD: fetch
BACKUP CHECKSUM(s): 1
ChkSum File
fb105a542d8856e9d5bd95410337463f base.tar

TABLESPACE(s): 0
START WAL LOCATION: 000000010000000000000008
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2016-12-06 16:14:56 CET
STOP TIME: 2016-12-06 16:14:58 CET
TOTAL DURATION: 2 sec(s)

[postgres@pgserver bin]$


postgres@pgserver bin]$ ./bart -c /usr/edb-bart-1.1/etc/bart.cfg BACKUP -s PGSERVER2

INFO: creating backup for server ‘pgserver2’
INFO: backup identifier: ‘1481047475191’
39216/39216 kB (100%), 1/1 tablespace

INFO: backup completed successfully
INFO: backup checksum: 052d82f3df1def72e9962cdef965fe8f of base.tar
INFO:
BACKUP DETAILS:
BACKUP STATUS: active
BACKUP IDENTIFIER: 1481047475191
BACKUP NAME: PGSERVER2_2016-12-06T19:04
BACKUP LOCATION: /u01/app/backup/pgserver2/1481047475191
BACKUP SIZE: 38.30 MB
BACKUP FORMAT: tar
XLOG METHOD: fetch
BACKUP CHECKSUM(s): 1
ChkSum File
052d82f3df1def72e9962cdef965fe8f base.tar

TABLESPACE(s): 0
START WAL LOCATION: 000000010000000000000003
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2016-12-06 19:04:36 CET
STOP TIME: 2016-12-06 19:04:38 CET
TOTAL DURATION: 2 sec(s)

[postgres@pgserver bin]$

We can list backups for pgserver2 for example

[postgres@pgserver bin]$ ./bart SHOW-BACKUPS -s pgserver2
SERVER NAME BACKUP ID BACKUP TIME BACKUP SIZE WAL(s) SIZE WAL FILES STATUS

pgserver2 1481047475191 2016-12-06 19:04:38 CET 38.30 MB 26.54 kB 1 active

[postgres@pgserver bin]$


It’s good to have a backup, but it is better to know how to restore. And now we are going to simulate a crash of pgserver2 and then do a restore. First let’s create a database and a table.

[postgres@pgserver2 data]$ psql
Password:
psql.bin (9.6.1)
Type "help" for help.
postgres=# create database sales;
CREATE DATABASE
postgres=# c sales
You are now connected to database "sales" as user "postgres".
sales=# create table article(id int);
CREATE TABLE
sales=# insert into article values (1);
INSERT 0 1
sales=# table article;
id
----
1
(1 row)
sales=#

And then let’s do a backup

[postgres@pgserver bin]$ ./bart -c /usr/edb-bart-1.1/etc/bart.cfg BACKUP -s PGSERVER2
INFO: creating backup for server 'pgserver2'
INFO: backup identifier: '1481051725125'
46814/46814 kB (100%), 1/1 tablespace
INFO: backup completed successfully
INFO: backup checksum: 19069965fa53444b31459f6d984682d0 of base.tar
INFO:
BACKUP DETAILS:
BACKUP STATUS: active
BACKUP IDENTIFIER: 1481051725125
BACKUP NAME: PGSERVER2_2016-12-06T20:15
BACKUP LOCATION: /u01/app/backup/pgserver2/1481051725125
BACKUP SIZE: 45.72 MB
BACKUP FORMAT: tar
XLOG METHOD: fetch
BACKUP CHECKSUM(s): 1
ChkSum File
19069965fa53444b31459f6d984682d0 base.tar
TABLESPACE(s): 0
START WAL LOCATION: 000000010000000000000005
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2016-12-06 20:15:28 CET
STOP TIME: 2016-12-06 20:15:28 CET
TOTAL DURATION: 0 sec(s)


[postgres@pgserver bin]$ ./bart SHOW-BACKUPS -s PGSERVER2
SERVER NAME BACKUP ID BACKUP TIME BACKUP SIZE WAL(s) SIZE WAL FILES STATUS
pgserver2 1481051725125 2016-12-06 20:15:28 CET 45.72 MB 16.00 MB 1 active
pgserver2 1481047475191 2016-12-06 19:04:38 CET 38.30 MB 16.03 MB 2 active
[postgres@pgserver bin]$

Now let’s crash our cluster

[root@pgserver2 9.6]# pwd
/u01/app/PostgreSQL/9.6
[root@pgserver2 9.6]# mv data/ data_crash
[root@pgserver2 9.6]#


[postgres@pgserver2 9.6]$ pg_ctl start
pg_ctl: directory "/u01/app/PostgreSQL/9.6/data" does not exist
[postgres@pgserver2 9.6]$

For the restore, we have to provide the backup id and the path to restore. This path must exist.

[postgres@pgserver bin]$ ./bart RESTORE -s pgserver2 -i 1481047475191 -p /u01/app/PostgreSQL/9.6/data
INFO: restoring backup '1481047475191' of server 'pgserver2'
INFO: restoring backup to postgres@pgserver2:/u01/app/PostgreSQL/9.6/data
INFO: base backup restored
INFO: copying WAL file(s) to postgres@pgserver2:/u01/app/PostgreSQL/9.6/data/archived_wals
INFO: archiving is disabled
[postgres@pgserver bin]$

Before starting cluster, let’s create a recovery.conf file

[postgres@pgserver2 data]$ cat recovery.conf
restore_command='cp /u01/app/PostgreSQL/9.6/data/archived_wals/%f %p'
[postgres@pgserver2 data]$

And then let’s start the cluster

[postgres@pgserver2 data]$ pg_ctl start
server starting
[postgres@pgserver2 data]$ 2016-12-06 20:33:01 CET LOG: redirecting log output to logging collector process
2016-12-06 20:33:01 CET HINT: Future log output will appear in directory "pg_log".

After the restore finished, the recovery.conf is automatically renamed to recovery.done because PostgreSQL will go into recovery mode again after a restart otherwise.

[postgres@pgserver2 data]$ cat recovery.done
restore_command='cp /u01/app/PostgreSQL/9.6/data/archived_wals/%f %p'
[postgres@pgserver2 data]$

While connecting we can verify our data

[postgres@pgserver2 data]$ psql
Password:
psql.bin (9.6.1)
Type "help" for help.
postgres=# c sales
You are now connected to database "sales" as user "postgres".
sales=# table article
sales-# ;
id
----
1
(1 row)
sales=#

In this blog we have seen how to configure EDB BART, how to use it for backup and recovery.