Open EC2 Machine on AWS

Install PostgreSQL Software

The following commands should be performed on both the master and slave servers.

$ sudo apt-get update
$ sudo apt-get install postgresql postgresql-contrib postgresql-client
$ sudo passwd postgres
$ sudo su - postgres

Set ssh login

The following commands also should be performed on both the master and slave servers.

# Press "ENTER" to all of the prompts that follow.
$ ssh-keygen
$ cat ~/.ssh/id_rsa.pub
# Copy the pub key to authorized_keys in another server
$ vim ~/.ssh/authorized_keys
$ /etc/init.d/ssh restart
# Test
$ ssh postgres@ip

Set configuration on the Master Server

# create a user called "rep" that can be used solely for replication
$ psql -c "CREATE USER rep REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD 'yourpassword';"
# move to the postgres configuration directory
$ cd /etc/postgresql/9.3/main

Modify pg_hba.conf

$ vim pg_hba.conf

# add following line to the file
host    replication     rep     IP_address_of_slave/32   md5

Modify postgresql.conf

$ vim postgresql.conf

# modify following lines
listen_addresses = '*'
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 1
hot_standby = on

Restart the master server

service postgresql restart

Set configuration on the Slave Server

# stop postgres on slave serve first
$ service postgresql stop
# move to the postgres configuration directory
$ cd /etc/postgresql/9.3/main

Modify pg_hba.conf

$ vim pg_hba.conf

# add following line to the file
host    replication     rep     IP_address_of_master/32  md5

Modify postgresql.conf

$ vim postgresql.conf

# modify following lines
listen_addresses = '*'
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 1
hot_standby = on

Edit inbound Rules for EC2

  • step 1. select security group

  • step 2. chose the group of ur master & slave server

  • step 3. edit inbound Rules

  • step 4. choose PostgreSQL in type

  • step 5. type custom ip info for each server

Replicating the Initial database

on the master server

$ psql -c "select pg_start_backup('initial_backup');"
$ rsync -cva --inplace --exclude=*pg_xlog* /var/lib/postgresql/9.3/main/ slave_IP_address:/var/lib/postgresql/9.3/main/
$ psql -c "select pg_stop_backup();"

on the slave server

$ cd /var/lib/postgresql/9.1/main
$ vim recovery.conf

# copy the following lines to recovery.conf
standby_mode = 'on'
primary_conninfo = 'host=master_IP_address port=5432 user=rep password=yourpassword'
trigger_file = '/tmp/postgresql.trigger.5432'

# then start postgres on the slave
$ service postgresql start

# check log 
$ less /var/log/postgresql/postgresql-9.3-main.log
P.S if u get an error like following, please redo this section again.
2016-06-05 13:24:21 UTC LOG:  started streaming WAL from primary at 0/3000000 on timeline 1
2016-06-05 13:24:21 UTC FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment 000000010000000000000003 has already been removed

Test the Replication

on the master server

# login to the postgres
$ psql

# type following commands on postgres command line

CREATE TABLE rep_test (test varchar(40));

INSERT INTO rep_test VALUES ('data one');
INSERT INTO rep_test VALUES ('some more words');
INSERT INTO rep_test VALUES ('lalala');
INSERT INTO rep_test VALUES ('hello there');
INSERT INTO rep_test VALUES ('blahblah');

# exist
\q

on the slave server

$ psql

# sql command
SELECT * FROM rep_test;
# u will see

      test       
-----------------
 data one
 some more words
 lalala
 hello there
 blahblah
(5 rows)

Congrats u finish all of this.

Reference