Backup and Recovery Tool(BART)
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. Use version 1.0 for testing purpose which is free. Still this tool you can use for open source postgresql.Using BART 2.0 or above, you can take incremental backups also. In postgres open source there is no incremental backup till version 12.
The first version of BART 1.0 was released in October of 2014. It:
- Supports complete, hot, physical backups of multiple Postgres Plus Advanced Server and PostgreSQL database servers
- Provides backup and recovery management of the database servers on local or remote hosts
- Uses a single, centralized catalog for backup data
- Displays backup information in an easy-to-read form
- Verifies backup’s with checksums
- Simplifies the point-in-time recovery process
Install BART rpm to configure to take backup from Multiple clusters from multiple locations
-- RMP Installation
[root@postgres PostgresSoftware]# rpm -ivh edb-bart-1.1.0-1.rhel6.x86_64.rpm
warning: edb-bart-1.1.0-1.rhel6.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID 7e30651c: NOKEY
Preparing... ########################################### [100%]
1:edb-bart ########################################### [100%]
[root@postgres PostgresSoftware]#
- for demonstration purpose, let us create two cluster in a single host and enable archive log
Data directories and region defined for BART backup
----------------------------------------------------
1) cluster-1 : /opt/demodata_bart - port: 5434 - Region: Hyderabad
2) Cluster-2 : /opt/PostgreSQL/10/data - port: 5432 - Region: Bangalore
Let us start the POC:
step#1 - Let us assume, a cluster / instance is running on 5432 from '/opt/PostgreSQL/10/data' data directory. Now enable archive log and configure backup location
root@postgres archive_software]# su - postgres
-bash-4.1$
-bash-4.1$ exit
logout
[root@postgres archive_software]# cd /opt/
[root@postgres opt]# mkdir bart_archive
[root@postgres opt]#
[root@postgres opt]# mkdir bart_backup
[root@postgres opt]#
[root@postgres opt]# chown postgres:postgres bart_* -R
[root@postgres opt]#-bash-4.1$ psql -p 5432
psql.bin (10.12)
Type "help" for help.
postgres=# alter system set archive_mode to on;
ALTER SYSTEM
postgres=#
postgres=# alter system set archive_command to 'cp %p /opt/bart_archive/%f';
ALTER SYSTEM
postgres=#
postgres=# \q
-bash-4.1$
-bash-4.1$ pg_ctl -D $PGDATA restart
step#2 - Let us assume, another cluster / instance is running on 5434 from '/opt/demodata_bart' data directory. Now enable archive log and configure backup location
-bash-4.1$ cd /var/demodata/
-bash-4.1$
-bash-4.1$ vi postgresql.conf
archive_mode = on # enables archiving; off, on, or always
# (change requires restart)
archive_command = 'cp %p /opt/demodata_bart/%f'
:wq
[root@postgres opt]# mkdir demodata_bart
[root@postgres opt]#
[root@postgres opt]# chown postgres:postgres demodata_bart/ -R
[root@postgres opt]#
-bash-4.1$ pg_ctl -D /var/demodata/ restart
Step# 3 - Configure each region as per the plan
[root@postgres etc]# cd /usr/edb-bart-1.1/etc/
[root@postgres etc]#
[root@postgres etc]# vi bart.cfg
[BART]
bart-host= postgres@127.0.0.1
backup_path = /opt/PostgreSQL/10/bart_backup
pg_basebackup_path = /opt/PostgreSQL/10/bin/pg_basebackup
logfile = /tmp/bart.log
[hyderabad]
host = 127.0.0.1
port = 5434
user = postgres
description = "Postgres server"
retention_policy= 1 BACKUPS
[bangalore]
host = 127.0.0.1
port = 5432
user = postgres
description = "Postgres server"
retention_policy= 1 BACKUPS
:wq
Step# 3 -setup environment variables and Library path
[root@postgres etc]# su - postgres
-bash-4.1$
-bash-4.1$ cd /usr/edb-bart-1.1/bin/
-bash-4.1$
-bash-4.1$ source /opt/PostgreSQL/10/pg_env.sh
-bash-4.1$
-bash-4.1$ export LD_LIBRARY_PATH=/opt/PostgreSQL/10/lib/
-bash-4.1$
step# 4 - Take backup using BART and see these backups
-bash-4.1$ ./bart BACKUP -s hyderabad
-bash-4.1$ ./bart BACKUP -s hyderabad
INFO: creating backup for server 'hyderabad'
INFO: backup identifier: '1588444892965'
40984/40984 kB (100%), 1/1 tablespace
INFO: backup completed successfully
INFO: backup checksum: 195ac082e96df56aeaba4b48310e8bb6 of base.tar
INFO:
BACKUP DETAILS:
BACKUP STATUS: active
BACKUP IDENTIFIER: 1588444892965
BACKUP NAME: none
BACKUP LOCATION: /opt/PostgreSQL/10/bart_backup/hyderabad/1588444892965
BACKUP SIZE: 40.02 MB
BACKUP FORMAT: tar
XLOG METHOD: fetch
BACKUP CHECKSUM(s): 1
ChkSum File
195ac082e96df56aeaba4b48310e8bb6 base.tar
TABLESPACE(s): 0
START WAL LOCATION: 000000010000000000000002
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2020-05-03 00:11:37 IST
STOP TIME: 2020-05-03 00:12:02 IST
TOTAL DURATION: 25 sec(s)
-bash-4.1$
-- List backup
-bash-4.1$ ./bart SHOW-BACKUPs -s hyderabad
SERVER NAME BACKUP ID BACKUP TIME BACKUP SIZE WAL(s) SIZE WAL FILES STATUS
hyderabad 1588444892965 2020-05-03 00:12:02 IST 40.03 MB 0.00 bytes 0 active
-bash-4.1$ ./bart BACKUP -s hyderabad
-bash-4.1$ ./bart SHOW-BACKUPs -s hyderabad
SERVER NAME BACKUP ID BACKUP TIME BACKUP SIZE WAL(s) SIZE WAL FILES STATUS
hyderabad 1588445017589 2020-05-03 00:13:43 IST 40.03 MB 0.00 bytes 0 active
hyderabad 1588444892965 2020-05-03 00:12:02 IST 40.03 MB 0.00 bytes 0 active
-bash-4.1$
-bash-4.1$ ./bart SHOW-BACKUPs -s bangalore
SERVER NAME BACKUP ID BACKUP TIME BACKUP SIZE WAL(s) SIZE WAL FILES STATUS
-bash-4.1$ ./bart BACKUP -s bangalore
ERROR: backup failed for server 'bangalore'
connection to the server failed: fe_sendauth: no password supplied
The error because of permissions not given to bangalore region where 5432 cluster is configured.
let us fix:
-bash-4.1$ cd cd /opt/PostgreSQL/10/data
-bash-4.1$
-bash-4.1$ vi pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
host all all 0.0.0.0/0 trust
# IPv6 local connections:
host all all ::1/128 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 md5
:wq
-bash-4.1$ pg_ctl -D $PGDATA reload
server signaled
-bash-4.1$ cd /usr/edb-bart-1.1/bin/
-bash-4.1$
-bash-4.1$ ./bart BACKUP -s bangalore
-bash-4.1$ ./bart BACKUP -s bangalore
-bash-4.1$ ./bart SHOW-BACKUPS -s bangalore
SERVER NAME BACKUP ID BACKUP TIME BACKUP SIZE WAL(s) SIZE WAL FILES STATUS
bangalore 1588445851470 2020-05-03 00:27:36 IST 117.46 MB 0.00 bytes 0 active
bangalore 1588445680120 2020-05-03 00:24:56 IST 117.46 MB 0.00 bytes 0 active
If you will go to physical location, you will find folder names with region where you have configured in BART configuration file.
e.g.,
-bash-4.1$ cd /opt/PostgreSQL/10/bart_backup
-bash-4.1$ ll
total 8
drwx------. 5 postgres postgres 4096 May 3 00:27 bangalore
drwxr-xr-x. 5 postgres postgres 4096 May 3 00:13 hyderabad
-bash-4.1$
let us see what inside these folders.
-bash-4.1$ pwd
/opt/PostgreSQL/10/bart_backup/bangalore
-bash-4.1$ ls
1588445680120 1588445851470 archived_wals
-bash-4.1$
Now see here, backup IDs are matching to these directories what we saw in "bart SHOW-BACKUPS -s bangalore" outputs. i.e., for each backup a folder is created.
and actually .tar file created what we are usually doing in pg_basebackup
e.g.,
-bash-4.1$ cd 1588445680120
-bash-4.1$ pwd
/opt/PostgreSQL/10/bart_backup/bangalore/1588445680120
-bash-4.1$ ls
16430.tar 16434.tar backupinfo base.tar
-bash-4.1$
But, you may notice one thing, we took backup two times for each region and in BART configuration we mentioned retention as one. So here, why it is showing 'ACTIVE' for each backup.
let us see:
-bash-4.1$ ./bart SHOW-BACKUPS -s bangalore
SERVER NAME BACKUP ID BACKUP TIME BACKUP SIZE WAL(s) SIZE WAL FILES STATUS
bangalore 1588445851470 2020-05-03 00:27:36 IST 117.46 MB 0.00 bytes 0 active
bangalore 1588445680120 2020-05-03 00:24:56 IST 117.46 MB 0.00 bytes 0 active
-- Manage backups
So here we need to manage backup using 'MANAGE' command from BART.
e.g.,
-bash-4.1$ ./bart MANAGE -s bangalore
INFO: processing server 'bangalore', backup '1588445851470'
INFO: processing server 'bangalore', backup '1588445680120'
INFO: marking backup '1588445680120' as obsolete
INFO: 0 WAL file(s) marked obsolete
-bash-4.1$
Now list the backups again:
-bash-4.1$ ./bart SHOW-BACKUPS -s bangalore
SERVER NAME BACKUP ID BACKUP TIME BACKUP SIZE WAL(s) SIZE WAL FILES STATUS
bangalore 1588445851470 2020-05-03 00:27:36 IST 117.46 MB 0.00 bytes 0 active
bangalore 1588445680120 2020-05-03 00:24:56 IST 117.46 MB 0.00 bytes 0 obsolete
-bash-4.1$
-- Delete backup piece from BART
-bash-4.1$ ./bart DELETE -s bangalore -i 1588445680120
INFO: deleting backup '1588445680120' of server 'bangalore'
INFO: deleting backup '1588445680120'
INFO: 0 WAL file(s) will be removed
INFO: backup(s) deleted
-bash-4.1$ ./bart SHOW-BACKUPS -s bangalore
SERVER NAME BACKUP ID BACKUP TIME BACKUP SIZE WAL(s) SIZE WAL FILES STATUS
bangalore 1588445851470 2020-05-03 00:27:36 IST 117.46 MB 0.00 bytes 0 active
-bash-4.1$
No comments:
Post a Comment