Infrastructure at your Service

Mouhamadou Diaw

EnterpriseDB Backup and Recovery Tool (BART)

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.

[[email protected] 96]# yum localinstall edb-bart-1.1.0-1.rhel7.x86_64.rpm

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

[[email protected] 96]# cd /usr/edb-bart-1.1/
[[email protected] edb-bart-1.1]# ls
bart_license.txt bin etc
[[email protected] 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
[[email protected] 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.

[[email protected] ~]$ 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 |
+-----------------+
[[email protected] ~]$ 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
[email protected]'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.
[[email protected] ~]$ 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
[email protected]'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.
[[email protected] ~]$


[[email protected] ~]$ ssh pgserver1 date
Tue Dec 6 14:24:35 CET 2016
[[email protected] ~]$ ssh pgserver2 date
Tue Dec 6 14:24:40 CET 2016
[[email protected] ~]$

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

[[email protected] ~]$ echo "*:5432:*:backupuser:root" >> ~/.pgpass
[[email protected] ~]$ chmod 600 .pgpass

[[email protected] ~]$ echo "*:5432:*:backupuser:root" >> ~/.pgpass
[[email protected] ~]$ 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

[[email protected] etc]# pwd
/usr/edb-bart-1.1/etc


[[email protected] 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 = [email protected]
description = "Postgres server1"

[PGSERSERVER2] host = 192.168.56.37
port = 5432
user = backupuser
backup-name = PGSERVER2_%year-%month-%dayT%hour:%minute
remote-host = [email protected]
description = "Postgres server2"
[[email protected] 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

[[email protected] 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
[[email protected] data]$


[[email protected] 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
[[email protected] 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)

[[email protected] 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
[[email protected] bin]$


[[email protected] 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
[[email protected] bin]$

Now it’s time to launch a backup

[[email protected] 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)

[[email protected] bin]$


[email protected] 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)

[[email protected] bin]$

We can list backups for pgserver2 for example

[[email protected] 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

[[email protected] 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.

[[email protected] 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

[[email protected] 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)


[[email protected] 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
[[email protected] bin]$

Now let’s crash our cluster

[[email protected] 9.6]# pwd
/u01/app/PostgreSQL/9.6
[[email protected] 9.6]# mv data/ data_crash
[[email protected] 9.6]#


[[email protected] 9.6]$ pg_ctl start
pg_ctl: directory "/u01/app/PostgreSQL/9.6/data" does not exist
[[email protected] 9.6]$

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

[[email protected] 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 [email protected]:/u01/app/PostgreSQL/9.6/data
INFO: base backup restored
INFO: copying WAL file(s) to [email protected]:/u01/app/PostgreSQL/9.6/data/archived_wals
INFO: archiving is disabled
[[email protected] bin]$

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

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

And then let’s start the cluster

[[email protected] data]$ pg_ctl start
server starting
[[email protected] 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.

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

While connecting we can verify our data

[[email protected] 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.

2 Comments

  • sn says:

    Hi Mouhamadou,

    Thanks for great article .

    what is the configuration / setting required to restore Database with BART from Server 1 to server 2 .

    The backups are on NFS mount .

  • ashu says:

    Great article .
    I have one master server and a standby server .Now if I configure bart for both ,how can it work for standby as it will be in recovery mode ?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Mouhamadou Diaw
Mouhamadou Diaw

Senior Consultant