How to run EDB Backup and Recovery with Amazon EFS

Adam Wright Former EDB Member

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.

Screen Shot 2017-08-21 at 1.18.54 PM.png

 

Go to the EFS section of the AWS console to create a new file system:

Screen Shot 2017-08-21 at 12.49.53 PM.png

 

Be sure that the EC2 instances are in the same security group as the EFS mount point:

Screen Shot 2017-08-21 at 12.52.01 PM.png

 

 

Optionally, Add tags:

Screen Shot 2017-08-21 at 12.54.44 PM.png

Finally, create the file system:

Screen Shot 2017-08-21 at 12.55.32 PM.png

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.

Adam WrightFormer EDB Member