PG HA on GCE.md

Objectives

Set up two Compute Engine instances running Postgres.
Create a new table for a guestbook app.
Configure the primary server.
Back up the primary server to the standby server.
Configure the standby server to run in Hot Standby mode.
Start the standby server and test it.

Creating the guestbook table on Primary Server

sudo -s
sudo -u postgres psql postgres

enter these SQL command:

CREATE TABLE guestbook (visitor_email text, vistor_id serial, date timestamp, message text);
INSERT INTO guestbook (visitor_email, date, message) VALUES ( '[email protected]', current_date, 'This is a test.');
\q

Configuring the primary server

Create a user for replication

sudo -u postgres createuser -U postgres repuser -P -c 5 --replication

Create the archive directory

mkdir -p ../../var/lib/postgresql/main/mnt/server/archivedir

Edit pg_hba.conf

# Allow replication connections
host     replication     repuser         [standby-IP]/32        md5

Edit postgresql.conf

...
...

wal_level = hot_standby
archive_mode = on
archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
max_wal_senders = 3

Restart the PRIMARY server

sudo service postgresql restart

Backing up the primary server to the STANDBY server

Stop standby server

sudo service postgresql stop

Run the backup utility

sudo -s
$ mv ../../var/lib/postgresql/9.3/main ../../var/lib/postgresql/9.3/main_old
$ sudo -u postgres pg_basebackup -h [primary IP] -D /var/lib/postgresql/9.3/main -U repuser -v -P --xlog-method=stream

Configuring the STANDBY server

Edit postgresql.conf

hot_standby = on

Create the recovery configuration file

cp -avr ../../usr/share/postgresql/9.3/recovery.conf.sample /../../var/lib/postgresql/9.3/main/recovery.conf
vi /../../var/lib/postgresql/9.3/main/recovery.conf

content:

standby_mode = on
primary_conninfo = 'host=[primary-external-IP] port=5432 user=repuser password=[password]'
trigger_file = '/tmp/postgresql.trigger.5432'

Start the standby server

service postgresql start

Seeing the replication at work

on STANDBY server:

$ sudo -u postgres psql postgres

enter

select * from guestbook;

on PRIMARY server:

sudo -u postgres psql postgres

enter

INSERT INTO guestbook (visitor_email, date, message) VALUES ('[email protected]', current_date, 'Now we are replicating.');

back to STANDBY

select * from guestbook;

REff: https://cloud.google.com/community/tutorials/setting-up-postgres-hot-standby

akhdaniel/PG HA on GCE.md