Introduction
If your organization uses EDB Postgres in Amazon Web Services and you want to leverage advanced EDB Backup and Recovery Tool (BART) features such as incremental backup, one way to accomplish this is with Amazon Elastic File System (EFS). Operationally, Amazon EFS will remind you of file share solutions commonly found on-premise. However, EFS has several advantages over those solutions, the biggest being that the capacity grows and shrinks automatically as you add and delete files, and you only pay for the storage you are using.
If you are not already running BART, the documentation for version 2.0 is located here.
Installation and Configuration
Create an Elastic File System
From the AWS console, this example has two running EC2 instances, each in a different availability zone in the us-east-1 region.
Go to the EFS section of the AWS console to create a new file system:
Be sure that the EC2 instances are in the same security group as the EFS mount point:
Optionally, Add tags:
Finally, create the file system:
Postgres configuration
Both Postgres servers in this example are running with the basic configurations outlined in the EDB BART documentation.
Note: Later the BART INIT subcommand is used to modify the archive_command.
postgresql.conf
wal_level = hot_standby
max_wal_senders = 3
archive_mode = on
archive_command = '/bin/true'
hot_standby = on #ignored on primarypg_hba.conf
host template1 rep_user 127.0.0.1/32 md5
host replication rep_user 127.0.0.1/32 md5
Configure EC2
Install EDB BART and Mount the file system on your EC2 instances:
# yum install edb-bart20 -y
# yum install nfs-utils -y
# mkdir -p /edb/pgbackups
# sudo mount -t nfs4 -o nfsvers=4.1,rsize=1048576,wsize=1048576,hard,timeo=600,retrans=2 fs-a95a3ce0.efs.us-east-1.amazonaws.com:/ /edb/pgbackups
Configure BART
With Amazon EFS, SCP is not required to move files from server to server as the central backup repository is mounted on each server.
On EC2 instance 1, configure BART:
[root@ip-172-31-25-184 ~]# vi /usr/edb/bart2.0/etc/bart.cfg
[BART]
bart_host= enterprisedb@127.0.0.1
pg_basebackup_path = /usr/edb/as9.6/bin/pg_basebackup
retention_policy = 6 BACKUPS
backup_path = /edb/pgbackups
logfile = /tmp/bart.log
scanner_logfile = /tmp/bart_scanner.log
[ip-172-31-25-184]
host = 127.0.0.1
port = 5444
user = rep_user
cluster_owner = enterprisedb
backup_name = ip-172-31-25-184-%year-%month-%dayT%hour:%minute
pg_basebackup_path = /usr/edb/as9.6/bin/pg_basebackup
copy_wals_during_restore = enabled
archive_command = 'rsync -aq %p %a/%f'
description = "EDB Postgres Advanced Server on ip-172-31-25-184"On EC2 instance 2, install and configure BART:
[root@ip-172-31-62-18 ~]# vi /usr/edb/bart2.0/etc/bart.cfg
[BART]
bart_host= enterprisedb@127.0.0.1
pg_basebackup_path = /usr/edb/as9.6/bin/pg_basebackup
retention_policy = 6 BACKUPS
backup_path = /edb/pgbackups
logfile = /tmp/bart.log
scanner_logfile = /tmp/bart_scanner.log
[ip-172-31-62-18]
host = 127.0.0.1
user = rep_user
port = 5444
cluster_owner = enterprisedb
backup_name = ip-172-31-62-18-%year-%month-%dayT%hour:%minute
copy_wals_during_restore = enabled
archive_command = 'rsync -aq %p %a/%f'
description = "EDB Postgres Advanced Server on ip-172-31-62-18"
Initialize the BART backup on EC2 instance 1:
[root@ip-172-31-25-184 ~]# su - enterprisedb
$ /usr/edb/bart2.0/bin/bart init -o
INFO: setting archive_command for server 'ip-172-31-25-184'
WARNING: archive_command is set. server restart is required
Since archive_mode was previously on, the Postgres service just needs to be reloaded for the new archive_command to take effect:
$ /usr/edb/as9.6/bin/pg_ctl reload -D $PGDATA
Verify the archive_command setting:
$ psql -c "show archive_command" edb enterprisedb
archive_command
-----------------------------------------------------------------------------
rsync -aq %p /edb/pgbackups/ip-172-31-25-184/archived_wals/%f
(1 row):Verify WAL files are being archived to the BART directory on EFS
$ ls /edb/pgbackups/ip-172-31-25-184/archived_wals/
000000010000000000000009 00000001000000000000000A 00000001000000000000000B 00000001000000000000000C 00000001000000000000000D
Finally, take a backup of the cluster using the BART BACKUP command:
$ /usr/edb/bart2.0/bin/bart backup -s ip-172-31-25-184
INFO: creating backup for server 'ip-172-31-25-184'
INFO: backup identifier: '1503345083132'
63437/63437 kB (100%), 1/1 tablespace
INFO: backup completed successfully
INFO: backup checksum: 5cd29482e085e0528d875001743c6c55 of base.tar
INFO:
BACKUP DETAILS:
BACKUP STATUS: active
BACKUP IDENTIFIER: 1503345083132
BACKUP NAME: ip-172-31-25-184-2017-08-21T19:51
BACKUP PARENT: none
BACKUP LOCATION: /edb/pgbackups/ip-172-31-25-184/1503345083132
BACKUP SIZE: 61.95 MB
BACKUP FORMAT: tar
BACKUP TIMEZONE: UTC
XLOG METHOD: fetch
BACKUP CHECKSUM(s): 1
ChkSum File
5cd29482e085e0528d875001743c6c55 base.tar
TABLESPACE(s): 0
START WAL LOCATION: 00000001000000000000000F
STOP WAL LOCATION: 00000001000000000000000F
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2017-08-21 19:51:23 UTC
STOP TIME: 2017-08-21 19:51:23 UTC
TOTAL DURATION: 0 sec(s)
On EC2 instance 2, verify you can see the backups from instance 1 in a different availability zone:
$ ls /edb/pgbackups/ip-172-31-25-184/
1503345083132 archived_wals
Run the BART INIT command:
$ /usr/edb/bart2.0/bin/bart init -o
INFO: setting archive_command for server 'ip-172-31-62-18'
WARNING: archive_command is set. server restart is required
Since archive_mode was previously on, the Postgres service just needs to be reloaded for the new archive_command to take effect:
$ /usr/edb/as9.6/bin/pg_ctl reload -D $PGDATA
server signaled
Verify the archive_command setting:
$ psql -c "show archive_command" edb
archive_command
--------------------------------------------------------------
rsync -aq %p /edb/pgbackups/ip-172-31-62-18/archived_wals/%f
(1 row)Verify WAL files are being archived the BART directory on EFS:
$ ls /edb/pgbackups/ip-172-31-62-18/archived_wals/
000000010000000000000003
Take a backup of the cluster using the BART BACKUP command:
$ /usr/edb/bart2.0/bin/bart backup -s ip-172-31-62-18
INFO: creating backup for server 'ip-172-31-62-18'
INFO: backup identifier: '1503346032794'
63369/63369 kB (100%), 1/1 tablespace
INFO: backup completed successfully
INFO: backup checksum: 922cbd31cf746a26322941d7b1ab7dc8 of base.tar
INFO:
BACKUP DETAILS:
BACKUP STATUS: active
BACKUP IDENTIFIER: 1503346032794
BACKUP NAME: ip-172-31-62-18-2017-08-21T20:07
BACKUP PARENT: none
BACKUP LOCATION: /edb/pgbackups/ip-172-31-62-18/1503346032794
BACKUP SIZE: 61.89 MB
BACKUP FORMAT: tar
BACKUP TIMEZONE: UTC
XLOG METHOD: fetch
BACKUP CHECKSUM(s): 1
ChkSum File
922cbd31cf746a26322941d7b1ab7dc8 base.tar
TABLESPACE(s): 0
START WAL LOCATION: 000000010000000000000005
STOP WAL LOCATION: 000000010000000000000005
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2017-08-21 20:07:12 UTC
STOP TIME: 2017-08-21 20:07:13 UTC
TOTAL DURATION: 1 sec(s)
Run the SHOW-BACKUPS command to see information about for the backup:
$ /usr/edb/bart2.0/bin/bart show-backups -s ip-172-31-62-18 -t
SERVER NAME : ip-172-31-62-18
BACKUP ID : 1503346032794
BACKUP NAME : ip-172-31-62-18-2017-08-21T20:07
BACKUP PARENT : none
BACKUP STATUS : active
BACKUP TIME : 2017-08-21 20:07:13 UTC
BACKUP SIZE : 61.89 MB
WAL(S) SIZE : 16.00 MB
NO. OF WALS : 1
FIRST WAL FILE : 000000010000000000000005
CREATION TIME : 2017-08-21 20:07:13 UTC
LAST WAL FILE : 000000010000000000000005
CREATION TIME : 2017-08-21 20:07:13 UTC
Conclusion
Through this quick start gem, we can see how the AWS infrastructure can be leveraged with the advanced backup and restore features of EDB BART to simplify Postgres administrations tasks.