A D Vishnu Prasad

Director of Cloud Engineering @ Team8Solutions, Freelancer

Creating a Postgres Master and Slave Streaming Server With S3 Backup

Setup

API docs image * Image credit: google images

Master server setup

1
2
3
4

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'
wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O - | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

Create user for replication

1
2
3

sudo su postgres
psql
CREATE USER replicator REPLICATION LOGIN ENCRYPTED PASSWORD 'password';

Edit the following line in postgresql.conf

/etc/postgresql/9.5/postgresql.conf
1
2
3

wal_level = hot_standby
max_wal_senders = 3
wal_keep_segments = 8

Add the following line in pg_hba.conf

/etc/postgresql/9.5/pg_hba.conf
1

host     replication     replicator      <slave ip>            md5

Restart master postgres.

Slave server setup

Setup postgresql 9.5 in slave server. Stop the server before chaning any configurations

1
2

sudo su postgres
service postgresql stop

Edit the following line in postgresql.conf

/etc/postgresql/9.5/postgresql.conf
1
2

wal_level = hot_standby
hot_standby = on

Pull base backup from master server

1
2

rm -rf /var/lib/postgresql/9.5/main
pg_basebackup -h <master_ip> -D /var/lib/postgresql/9.5/main -U replicator -v -P

Create a recovery file for streaming

/var/lib/postgresql/9.5/main/recovery.conf
1
2
3

standby_mode = 'on'
primary_conninfo = 'host=<master_ip> port=5432 user=replicator password=password'
trigger_file = '/var/lib/postgresql.trigger'

Now start the slave server

1

service postgresql start

Run the following command in master to check whether replication is on or not.

1

select * from pg_stat_replication;

Send Wal-E files to S3 for backup

Run the following commands to setup wal-e

1
2
3
4
5

sudo su
sudo apt-get install daemontools libevent-dev python-all-dev lzop pv
sudo apt-get install python-setuptools
sudo easy_install pip
sudo pip install wal-e --upgrade

Add your s3 credentials

1
2
3
4
5
6

mkdir -p /etc/wal-e.d/env
echo <ACCESS_KEY> > /etc/wal-e.d/env/AWS_ACCESS_KEY_ID
echo <SECRET_KEY> > /etc/wal-e.d/env/AWS_SECRET_ACCESS_KEY
echo 's3://<bucket_path>' > /etc/wal-e.d/env/WALE_S3_PREFIX
echo 'us-east-1' > /etc/wal-e.d/env/AWS_REGION
chown -R root:postgres /etc/wal-e.d

Create a base backup

1

envdir /etc/wal-e.d/env wal-e backup-push $PG_DATA

List all the backup files

1

envdir /etc/wal-e.d/env /usr/local/bin/wal-e backup-list

Push Latest backup from S3

1

envdir /etc/wal-e.d/env /usr/local/bin/wal-e backup-fetch LATEST

Setup daily backup push crontab

1
2
3
4

crontab -e
/usr/bin/envdir /etc/wal-e.d/env /usr/local/bin/wal-e backup-push /e_data/main/ > /tmp/postgres_wale_backup_push.log;
#keep last 5 base backups
/usr/bin/envdir /etc/wal-e.d/env /usr/local/bin/wal-e delete --confirm retain 5 > /tmp/postgres_wale_backup_delete.log;

Please comment here if you face any problems during the setup.