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 primary
pg_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.