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 [email protected]
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.
