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)