edb bart

PostgreSQL DBA part 7 :-PostgreSQL full and incremental backup restore using BART 2.0 tool

Posted on by debasis maity

BART is a backup and recovery tool for EDB Postgres Databases

EDB Backup and Recovery Tool (BART) is a key component of an enterprise-level Postgres-based data management strategy.  BART implements retention policies and point-in-time recovery requirements for large-scale Postgres deployments. Now available, Bart 2.0 provides block-level incremental backup.

P.C https://www.enterprisedb.com/products/edb-postgres-platform/edb-backup-and-recovery-tool

A.Full backup and restore using BART 2.0 for EDB

 

1. Prerequisite: This step is required for YUM configuration to install BART tool from internet

First enable internet on server via PROXY setting as informed by Sysadmin:
Go to the /etc/profile, uncoment last lines 

proxc="http://172.16.0.65:3128/"
HTTP_PROXY=$proxc
HTTPS_PROXY=$proxc
FTP_PROXY=$proxc
http_proxy=$proxc
https_proxy=$proxc
ftp_proxy=$proxc
export HTTP_PROXY HTTPS_PROXY FTP_PROXY http_proxy https_proxy ftp_proxy
Go to the /etc/wgetrc, uncoment

http_proxy = http://172.16.0.65:3128/
https_proxy = http:// 172.16.0.65:3128/
ftp_proxy = http:// 172.16.0.65:3128/
Go to the /etc/yum.conf, uncoment

proxy=http://172.16.0.65:3128

2. Configure yum repository for BART tool

2.1 Configure your /etc/yum.repos.d with the edb repository information
rpm -Uvh http://yum.enterprisedb.com/edbrepos/edb-repo-9.6-4.noarch.rpm
(** Download the mentioned RPM)
2.2 This RPM will created the edb.repo file in the /etc/yum.repo.d/ directory path

2.3 Edit the /etc/yum.repos.d/edb.repo  file to enable (change enabled=0 to enabled=1)
 the repositories you want to use ([edbas96, enterprisedb-tools, enterprisedb-dependencies]) and specify your username / password in the connection string: (sample edb.repo file present in last point)

2.4 We need some extra package for BART installation for which we need to configure below yum  configuration repository

[root@rac1 yum.repos.d]# cat epel.repo
[epel]
name=Extra Packages for Enterprise Linux 6 - $basearch
#baseurl=http://download.fedoraproject.org/pub/epel/6/$basearch
mirrorlist=https://mirrors.fedoraproject.org/metalink?repo=epel-6&arch=$basearch
failovermethod=priority
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6

[epel-debuginfo]
name=Extra Packages for Enterprise Linux 6 - $basearch - Debug
#baseurl=http://download.fedoraproject.org/pub/epel/6/$basearch/debug
mirrorlist=https://mirrors.fedoraproject.org/metalink?repo=epel-debug-6&arch=$basearch
failovermethod=priority
enabled=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6
gpgcheck=1

[epel-source]
name=Extra Packages for Enterprise Linux 6 - $basearch - Source
#baseurl=http://download.fedoraproject.org/pub/epel/6/SRPMS
mirrorlist=https://mirrors.fedoraproject.org/metalink?repo=epel-source-6&arch=$basearch
failovermethod=priority
enabled=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6
gpgcheck=1

[root@rac1 yum.repos.d]# cat epel-testing.repo
[epel-testing]
name=Extra Packages for Enterprise Linux 6 - Testing - $basearch
#baseurl=http://download.fedoraproject.org/pub/epel/testing/6/$basearch
mirrorlist=https://mirrors.fedoraproject.org/metalink?repo=testing-epel6&arch=$basearch
failovermethod=priority
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6

[epel-testing-debuginfo]
name=Extra Packages for Enterprise Linux 6 - Testing - $basearch - Debug
#baseurl=http://download.fedoraproject.org/pub/epel/testing/6/$basearch/debug
mirrorlist=https://mirrors.fedoraproject.org/metalink?repo=testing-debug-epel6&arch=$basearch
failovermethod=priority
enabled=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6
gpgcheck=1

[epel-testing-source]
name=Extra Packages for Enterprise Linux 6 - Testing - $basearch - Source
#baseurl=http://download.fedoraproject.org/pub/epel/testing/6/SRPMS
mirrorlist=https://mirrors.fedoraproject.org/metalink?repo=testing-source-epel6&arch=$basearch
failovermethod=priority
enabled=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6
gpgcheck=1

3. Run the following yum install commands:

yum install epel-release*
yum install edb-as96-server-client (if installing on a host without the psql client)
yum install edb-bart20

4. Please set the profile now

 

Login to enterprisedb user from root and set the profile

-bash-4.1$ vi .profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

# User specific environment and startup programs
export PATH=/usr/edb/bart2.0/bin/:$PATH
PATH=$PATH:$HOME/bin

export PATH
alias config='vi /usr/edb/bart2.0/etc/bart.cfg'

. pgplus_env.sh

5. Please create directory where backup needs to be taken

[root@rac1 bin]# mkdir -p /u01/edb_bkp
[root@rac1 bin]# chmod -R 777 /u01/edb_bkp
[root@rac1 bin]# chown enterprisedb:enterprisedb /u01/edb_bkp

6.Edit the BART configuration file

 

[BART]
bart_host= enterprisedb@127.0.0.1
backup_path = /u01/edb_bkp
pg_basebackup_path = /u01/edb/as9.6/bin/pg_basebackup
logfile = /tmp/bart.log
scanner_logfile = /tmp/bart_scanner.log
xlog_method = stream
retention_policy = 1 BACKUPS

[PPAS96]
host = 127.0.0.1
port = 5444
user = enterprisedb
cluster_owner = enterprisedb
description = "PPAS 96 server"
backup_name = edb_%year-%month-%dayT%hour:%minute
archive_command='cp %p %a/%f'
#allow_incremental_backups=enabled

7.Enable ssh to self user

-bash-4.1$ chown enterprisedb .ssh
-bash-4.1$ chgrp enterprisedb .ssh
-bash-4.1$ chmod 700 .ssh
-bash-4.1$
-bash-4.1$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/u01/edb/as9.6/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /u01/edb/as9.6/.ssh/id_rsa.
Your public key has been saved in /u01/edb/as9.6/.ssh/id_rsa.pub.
The key fingerprint is:
f7:72:e1:17:c3:5d:c9:3a:f8:e9:eb:9c:6e:e0:53:be enterprisedb@edb2prod
The key's randomart image is:
+--[ RSA 2048]----+
| |
| . .|
| o.|
| .....|
| S ...o+ .|
| . +.ooo |
| o Bo. |
| =o+. |
| =Eo |
+-----------------+
-bash-4.1$ pwd
/u01/edb/as9.6
-bash-4.1$ cd .ssh/
-bash-4.1$ ll
total 12
-rw-------. 1 enterprisedb enterprisedb 1675 Nov 7 21:13 id_rsa
-rw-r--r--. 1 enterprisedb enterprisedb 403 Nov 7 21:13 id_rsa.pub
-rw-r--r--. 1 enterprisedb enterprisedb 1968 Nov 3 21:47 known_hosts
-bash-4.1$ cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
-bash-4.1$ chmod 600 ~/.ssh/authorized_keys
-bash-4.1$ ssh enterprisedb@127.0.0.1
The authenticity of host '127.0.0.1 (127.0.0.1)' can't be established.
RSA key fingerprint is b7:b2:44:3f:5b:19:8a:7d:13:a3:a9:45:67:45:7d:8c.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '127.0.0.1' (RSA) to the list of known hosts.
Last login: Thu Apr 20 10:33:36 2017 from 172.19.8.95

8.Add the appropriate entries on pg_hba.conf file:

local all enterprisedb trust
host replication enterprisedb 127.0.0.1/32 trust
host template1 enterprisedb 127.0.0.1/32 trust

9.Now we can check whether backup configuration is fine or not

-bash-4.1$ bart check-config
INFO: Verifying that pg_basebackup is executable
INFO: success - pg_basebackup(/u01/edb/as9.6/bin/pg_basebackup) returns version 9.600000

-bash-4.1$ bart check-config -s ppas96
INFO: Checking server ppas96
INFO: Verifying cluster_owner and ssh/scp connectivity
INFO: success
INFO: Verifying user, host, and replication connectivity
INFO: success
INFO: Verifying that user is a database superuser
INFO: success
INFO: Verifying that cluster_owner can read cluster data files
INFO: success
INFO: Verifying that you have permission to write to vault
INFO: success
INFO: /u01/edb_bkp/ppas96
INFO: Verifying database server configuration
INFO: success
INFO: Verifying that WAL archiving is working
INFO: waiting 30 seconds for /u01/edb_bkp/ppas96/archived_wals/000000020000000000000027
INFO: waiting 20 seconds for /u01/edb_bkp/ppas96/archived_wals/000000020000000000000027
INFO: waiting 10 seconds for /u01/edb_bkp/ppas96/archived_wals/000000020000000000000027
ERROR: can't verify archiving: required file has not reached archived_wals directory

10.Now we can initialize the catalog

-bash-4.1$ bart INIT -s PPAS96 -o
INFO: setting archive_command for server 'ppas96'
WARNING: archive_command is set. server restart is required

11.Take full backup now

-bash-4.1$ bart backup -s ppas96 -F p
INFO: creating backup for server 'ppas96'
INFO: backup identifier: '1527547489386'
106132/106132 kB (100%), 1/1 tablespace

INFO: backup completed successfully
INFO:
BACKUP DETAILS:
BACKUP STATUS: active
BACKUP IDENTIFIER: 1527547489386
BACKUP NAME: edb_2018-05-28T18:44
BACKUP PARENT: none
BACKUP LOCATION: /u01/edb_bkp/ppas96/1527547489386
BACKUP SIZE: 118.45 MB
BACKUP FORMAT: plain
BACKUP TIMEZONE: US/Eastern
XLOG METHOD: stream
BACKUP CHECKSUM(s): 0
TABLESPACE(s): 0
START WAL LOCATION: 000000020000000000000029
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2018-05-28 18:44:50 EDT
STOP TIME: 2018-05-28 18:44:53 EDT
TOTAL DURATION: 3 sec(s)

12.Verify the backup now

-bash-4.1$ bart show-backups
SERVER NAME BACKUP ID BACKUP NAME BACKUP PARENT BACKUP TIME BACKUP SIZE WAL(s) SIZE WAL FILES STATUS

ppas96 1527547489386 edb_2018-05-28T18:44 none 2018-05-28 18:44:53 EDT 118.45 MB 0.00 bytes 0 active

13.Create a demo database (tst) and create some sample tables.We will verify if we will be able to restore this database and table after we fully restore backup

psql
edb=# create database tst;
CREATE DATABASE

sales=# \c tst
You are now connected to database "tst" as user "enterprisedb".
tst=# create table article(id int);
CREATE TABLE
tst=# insert into article values (1);
INSERT 0 1
tst=# table article;
id
----
1
(1 row)

14.Create the full backup again

-bash-4.1$ bart backup -s ppas96 -F p
INFO: creating backup for server 'ppas96'
INFO: backup identifier: '1527547795678'
117720/117720 kB (100%), 1/1 tablespace

INFO: backup completed successfully
INFO:
BACKUP DETAILS:
BACKUP STATUS: active
BACKUP IDENTIFIER: 1527547795678
BACKUP NAME: edb_2018-05-28T18:49
BACKUP PARENT: none
BACKUP LOCATION: /u01/edb_bkp/ppas96/1527547795678
BACKUP SIZE: 129.58 MB
BACKUP FORMAT: plain
BACKUP TIMEZONE: US/Eastern
XLOG METHOD: stream
BACKUP CHECKSUM(s): 0
TABLESPACE(s): 0
START WAL LOCATION: 00000002000000000000002B
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2018-05-28 18:49:59 EDT
STOP TIME: 2018-05-28 18:50:00 EDT
TOTAL DURATION: 1 sec(s)

15.Simulate the crash now after moving data directory to another name and create empty directory

-bash-4.1$ cd /u01/edb/as9.6/
-bash-4.1$ mv data data_old
-bash-4.1$ mkdir -p /u01/edb/as9.6/data

16.Please restore full backup now

-bash-4.1$ bart RESTORE -s PPAS96 -i 1527547795678 -p /u01/edb/as9.6/data/
INFO: restoring backup '1527547795678' of server 'ppas96'
INFO: base backup restored
INFO: archiving is disabled
INFO: permissions set on $PGDATA
INFO: restore completed successfully

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

open /u01/edb/as9.6/recovery.conf and add the following lines

restore_command='cp /u01/edb/as9.6/data/archived_wals/%f %p'

18.Please restart database

-bash-4.1$ pg_ctl -D /u01/edb/as9.6/data -mf start
server starting
-bash-4.1$ 2018-05-29 19:02:37 EDT LOG: redirecting log output to logging collector process
2018-05-29 19:02:37 EDT HINT: Future log output will appear in directory "pg_log".

19.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.

-bash-4.1$ cat recovery.done
restore_command=’cp /u01/edb/as9.6/data/archived_wals/%f %p’

20.Now we will check if the database and table restored successfully after restore full database

-bash-4.1$ psql
psql.bin (9.6.2.7)
Type "help" for help.

edb=# \c tst
You are now connected to database "tst" as user "enterprisedb".
tst=# table article;
id
----
1
(1 row)

B.Incremental backup and restore using BART 2.0 for EDB

 

1.Please enable allow_incremental_backup in BART configuration file

Set the parameter allow_incremental_backups to ENABLED in the BART configuration file.Restart the server and post that check whether the INCREMENTAL BACKUP feature has been enabled or not.

2.With BART 2.0 there is a new component called the “WAL scanner”. The binary is at the same location as BART itself

3.Start WAL_SCANNER

The WAL scanner (as the name implies) scans the WALs that are archived from your PostgreSQL instances to the BART host for changes and then writes a so called “modified block map (MBM)” (this is the reason why wal compression is not supported in incremental backup). As this should happen as soon as the WALs arrive on the BART host the WAL scanner should run all the time.
We need to start it in daemon mode :

-bash-4.1$ /usr/edb/bart2.0/bin/bart-scanner --daemon
-bash-4.1$ ps -ef|grep bart
54323 27284 1 0 20:30 ? 00:00:00 /usr/edb/bart2.0/bin/bart-scanner --daemon
54323 27285 27284 0 20:30 ? 00:00:00 /usr/edb/bart2.0/bin/bart-scanner --daemon
54323 27318 4555 0 20:30 pts/0 00:00:00 grep bart

4.Re-check configuration settings:

-bash-4.1$ bart check-config -s PPAS96
INFO: Checking server PPAS96
INFO: Verifying cluster_owner and ssh/scp connectivity
INFO: success
INFO: Verifying user, host, and replication connectivity
INFO: success
INFO: Verifying that user is a database superuser
INFO: success
INFO: Verifying that cluster_owner can read cluster data files
INFO: success
INFO: Verifying that you have permission to write to vault
INFO: success
INFO: /u01/edb_bkp/ppas96
INFO: Verifying database server configuration
INFO: success
INFO: Verifying that WAL archiving is working
INFO: waiting 30 seconds for /u01/edb_bkp/ppas96/archived_wals/00000003000000000000002C
INFO: success
INFO: Verifying that bart-scanner is configured and running
INFO: success

5.Execute a DB FULL backup post enabling WAL_SCANNER:

-bash-4.1$ bart backup -s PPAS96 --backup-name full0
INFO: creating backup for server 'ppas96'
INFO: backup identifier: '1527640567564'
pg_basebackup: WAL streaming can only be used in plain mode
Try "pg_basebackup --help" for more information.

ERROR: backup failed for server 'ppas96'
command failed with exit code 1
Try "pg_basebackup --help" for more information.

Changed the xlog_method = fetch in bart config file and  execute following command

-bash-4.1$ bart INIT -s PPAS96 -o
INFO: setting archive_command for server 'ppas96'
WARNING: archive_command is set. server restart is required
-bash-4.1$ bart backup -s PPAS96 --backup-name full0
INFO: creating backup for server 'ppas96'
INFO: backup identifier: '1527640746966'
134117/134117 kB (100%), 1/1 tablespace

INFO: backup completed successfully
INFO: backup checksum: 7de18e9077a038e2944ee4b0f92e2612 of base.tar
INFO:
BACKUP DETAILS:
BACKUP STATUS: active
BACKUP IDENTIFIER: 1527640746966
BACKUP NAME: full0
BACKUP PARENT: none
BACKUP LOCATION: /u01/edb_bkp/ppas96/1527640746966
BACKUP SIZE: 130.97 MB
BACKUP FORMAT: tar
BACKUP TIMEZONE: US/Eastern
XLOG METHOD: fetch
BACKUP CHECKSUM(s): 1
ChkSum File
7de18e9077a038e2944ee4b0f92e2612 base.tar

TABLESPACE(s): 0
START WAL LOCATION: 00000003000000000000002F
STOP WAL LOCATION: 00000003000000000000002F
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2018-05-29 20:39:08 EDT
STOP TIME: 2018-05-29 20:39:08 EDT
TOTAL DURATION: 0 sec(s)
-bash-4.1$ bart show-backups
SERVER NAME BACKUP ID BACKUP NAME BACKUP PARENT BACKUP TIME BACKUP SIZE WAL(s) SIZE WAL FILES STATUS

ppas96 1527640746966 full0 none 2018-05-29 20:39:09 EDT 130.98 MB 16.00 MB 1 active
ppas96 1527547795678 edb_2018-05-28T18:49 none 2018-05-28 18:50:00 EDT 129.58 MB 48.00 MB 3 active
ppas96 1527547489386 edb_2018-05-28T18:44 none 2018-05-28 18:44:53 EDT 118.45 MB 0.00 bytes 0 active

6.Now create a demo table and insert some rows

-bash-4.1$ psql
psql.bin (9.6.2.7)
Type "help" for help.

edb=# \c tst
You are now connected to database "tst" as user "enterprisedb".
tst=# create table chk_incr(c1 char(10));
CREATE TABLE
tst=# insert into chk_incr values('DEBASIS');
INSERT 0 1
tst=# table chk_incr;
c1
------------
DEBASIS
(1 row)

7.Please take incremental backup now with tag incr1

-bash-4.1$ bart backup -s ppas96 -F p --parent 1527640746966 --backup-name incr1
INFO: creating incremental backup for server 'ppas96'
INFO: checking mbm files /u01/edb_bkp/ppas96/archived_wals
INFO: new backup identifier generated 1527641049438
INFO: reading directory /u01/edb_bkp/ppas96/archived_wals
INFO: all files processed
NOTICE: pg_stop_backup complete, all required WAL segments have been archived
INFO: incremental backup completed successfully
INFO:
BACKUP DETAILS:
BACKUP STATUS: active
BACKUP IDENTIFIER: 1527641049438
BACKUP NAME: incr1
BACKUP PARENT: 1527640746966
BACKUP LOCATION: /u01/edb_bkp/ppas96/1527641049438
BACKUP SIZE: 33.34 MB
BACKUP FORMAT: plain
BACKUP TIMEZONE: US/Eastern
XLOG METHOD: fetch
BACKUP CHECKSUM(s): 0
TABLESPACE(s): 0
START WAL LOCATION: 000000030000000000000031
STOP WAL LOCATION: 000000030000000000000032
BACKUP METHOD: pg_start_backup
BACKUP FROM: master
START TIME: 2018-05-29 20:44:10 EDT
STOP TIME: 2018-05-29 20:44:12 EDT
TOTAL DURATION: 2 sec(s)

8.Please insert another values in the table.

-bash-4.1$ psql
psql.bin (9.6.2.7)
Type "help" for help.

edb=# \c tst
You are now connected to database "tst" as user "enterprisedb".
tst=# insert into chk_incr values('MAITY');
INSERT 0 1
tst=# table chk_incr;
c1
------------
DEBASIS
MAITY
(2 rows)

tst=# quit

9.Please take another incremental backup tag as incr2

-bash-4.1$ bart backup -s ppas96 -F p --parent 1527640746966 --backup-name incr2
INFO: creating incremental backup for server 'ppas96'
INFO: checking mbm files /u01/edb_bkp/ppas96/archived_wals
INFO: new backup identifier generated 1527641642651
INFO: reading directory /u01/edb_bkp/ppas96/archived_wals
INFO: all files processed
NOTICE: pg_stop_backup complete, all required WAL segments have been archived
INFO: incremental backup completed successfully
INFO:
BACKUP DETAILS:
BACKUP STATUS: active
BACKUP IDENTIFIER: 1527641642651
BACKUP NAME: incr2
BACKUP PARENT: 1527640746966
BACKUP LOCATION: /u01/edb_bkp/ppas96/1527641642651
BACKUP SIZE: 33.34 MB
BACKUP FORMAT: plain
BACKUP TIMEZONE: US/Eastern
XLOG METHOD: fetch
BACKUP CHECKSUM(s): 0
TABLESPACE(s): 0
START WAL LOCATION: 000000030000000000000034
STOP WAL LOCATION: 000000030000000000000035
BACKUP METHOD: pg_start_backup
BACKUP FROM: master
START TIME: 2018-05-29 20:54:03 EDT
STOP TIME: 2018-05-29 20:54:04 EDT
TOTAL DURATION: 1 sec(s)

10.Please check all backups available now

-bash-4.1$ bart show-backups
SERVER NAME BACKUP ID BACKUP NAME BACKUP PARENT BACKUP TIME BACKUP SIZE WAL(s) SIZE WAL FILES STATUS

ppas96 1527641642651 incr2 full0 2018-05-29 20:54:04 EDT 33.34 MB active
ppas96 1527641049438 incr1 full0 2018-05-29 20:44:11 EDT 33.34 MB active
ppas96 1527640746966 full0 none 2018-05-29 20:39:09 EDT 130.98 MB 112.00 MB 7 active
ppas96 1527547795678 edb_2018-05-28T18:49 none 2018-05-28 18:50:00 EDT 129.58 MB 48.00 MB 3 active
ppas96 1527547489386 edb_2018-05-28T18:44 none 2018-05-28 18:44:53 EDT 118.45 MB 0.00 bytes 0 active

11.Now I will restore incr1 tagged backup.Please create empty directory /u01/edb_incr1 owner as enterprisedb.

-bash-4.1$ bart restore -s ppas96 -i 1527641049438 -p /u01/edb_incr1/
INFO: restoring incremental backup '1527641049438' of server 'ppas96'
INFO: base backup restored
INFO: archiving is disabled
INFO: permissions set on $PGDATA
INFO: incremental restore completed successfully

12.Please stop old instance and restart instance with new path where we restored incremental backup.

-bash-4.1$ pg_ctl -D /u01/edb/as96/data -mf stop
waiting for server to shut down.... done
server stopped
-bash-4.1$ pg_ctl -D /u01/edb_incr1 -mf start
server starting
-bash-4.1$ 2018-05-30 02:54:34 EDT LOG: redirecting log output to logging collector process
2018-05-30 02:54:34 EDT HINT: Future log output will appear in directory "pg_log".

13.Now we can check we successfully restored incr1 backup verifying table data.

-bash-4.1$ psql
psql.bin (9.6.2.7)
Type "help" for help.

edb=# \c tst
You are now connected to database "tst" as user "enterprisedb".
tst=# table chk_incr;
c1
------------
DEBASIS
(1 row)