Notes on postgres master slave configuration.

master-slave-notes.rb

REFERENCES 
# https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-on-postgresql-on-an-ubuntu-12-04-vps
# https://sonnguyen.ws/replication-master-slave-with-postgresql-9-4-in-ubuntu-14-04/
# http://senvichet.com/how-to-setup-postgres-9-4-master-slave-streaming-replication/
============================================================================================================
 INITIAL STEPS FOR BOTH MASTER AND SLAVE
============================================================================================================
# -----------------------------------------------------------------------------------------------------------
TODO ( STEPS SIMILAR TO SETTING UP AN EC2 INSTANCE ): Install essential software dependencies after system upgrade
# -----------------------------------------------------------------------------------------------------------
 sudo apt-get install libpq-dev

# -----------------------------------------------------------------------------------------------------------
#Install postgres
# -----------------------------------------------------------------------------------------------------------
# sudo apt-get install postgresql postgresql-contrib postgresql-client

sudo add-apt-repository "deb https://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main"
wget --quiet -O - https://postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - 
sudo apt-get update
sudo apt-get install postgresql-9.4
# -----------------------------------------------------------------------------------------------------------
# Set up password for postgres
# -----------------------------------------------------------------------------------------------------------
sudo passwd postgres

# -----------------------------------------------------------------------------------------------------------
# Switch over to the postgres user like this:
# -----------------------------------------------------------------------------------------------------------
  sudo su - postgres

# -----------------------------------------------------------------------------------------------------------
# Generate an ssh key for the postgres user:
# -----------------------------------------------------------------------------------------------------------
  ssh-keygen

# -----------------------------------------------------------------------------------------------------------
# Transfer the keys to the other server by typing
# -----------------------------------------------------------------------------------------------------------
  ssh-copy-id IP_address_of_opposite_server
  # opposite server being master / slave
  # note: while configuring EC2 instance, configure elastic IPs so that IPs are not changed across reboots.

============================================================================================================
 SET UP FOR MASTER INSTANCE
============================================================================================================

# -----------------------------------------------------------------------------------------------------------
# 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';"

# -----------------------------------------------------------------------------------------------------------
# Modify postgres access config file
# -----------------------------------------------------------------------------------------------------------
vi /etc/postgresql/9.4/main/pg_hba.conf

# -----------------------------------------------------------------------------------------------------------
# Add the below line.
# -----------------------------------------------------------------------------------------------------------
host    replication     rep     IP_address_of_slave/32   md5

# -----------------------------------------------------------------------------------------------------------
# Modify main postgres config file
# -----------------------------------------------------------------------------------------------------------
vi /etc/postgresql/9.4/main/postgresql.conf

# -----------------------------------------------------------------------------------------------------------
# Add the below config values
# -----------------------------------------------------------------------------------------------------------
listen_addresses = 'localhost,IP_address_of_THIS_host'
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 1
hot_standby = on

# -----------------------------------------------------------------------------------------------------------
# Restart postgres server
# -----------------------------------------------------------------------------------------------------------
service postgresql restart

============================================================================================================
 SET UP FOR SLAVE INSTANCE
============================================================================================================

# -----------------------------------------------------------------------------------------------------------
# First stop the postgres server
# -----------------------------------------------------------------------------------------------------------
sudo service postgresql stop

# -----------------------------------------------------------------------------------------------------------
# Modify postgres access config file
# -----------------------------------------------------------------------------------------------------------
vi /etc/postgresql/9.4/main/pg_hba.conf

# -----------------------------------------------------------------------------------------------------------
# Add the below line.
# -----------------------------------------------------------------------------------------------------------
host    replication     rep     IP_address_of_master/32   md5

# -----------------------------------------------------------------------------------------------------------
# Modify main postgres config file
# -----------------------------------------------------------------------------------------------------------
vi /etc/postgresql/9.4/main/postgresql.conf

# ----- SAME AS THAT FOR MASTER ---------

============================================================================================================
REPLICATE THE INITIAL DATABASE
============================================================================================================
QUESTION (@akshay) : How do further migration changes replicate...?
                     check out an alternate approach at http://senvichet.com/how-to-setup-postgres-9-4-master-slave-streaming-replication/

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

# -----------------------------------------------------------------------------------------------------------
# ON SLAVE : CREATE A RECOVERY FILE
# -----------------------------------------------------------------------------------------------------------
vi /var/lib/postgresql/9.4/main/recovery.conf

# -----------------------------------------------------------------------------------------------------------
# WITH CONTENTS
# -----------------------------------------------------------------------------------------------------------
standby_mode = 'on'
primary_conninfo = 'host=master_IP_address port=5432 user=rep password=yourpassword'
trigger_file = '/tmp/postgresql.trigger.5432'

# -----------------------------------------------------------------------------------------------------------
# START THE SLAVE SERVER
# -----------------------------------------------------------------------------------------------------------
service postgresql start

# -----------------------------------------------------------------------------------------------------------
# Notes: If you create a file at that location on your slave machine,
#        your slave will reconfigure itself to act as a master.
#
# This will break your current replication, especially if the master server is still running,
# but is what you would need to do if your master server goes down.
# This will allow the slave to begin accepting writes.
# You can then fix the master server and turn that into the slave.
# -----------------------------------------------------------------------------------------------------------

============================================================================================================
CHECK LOGS
============================================================================================================

# -----------------------------------------------------------------------------------------------------------
# The logs are present in files at location
# -----------------------------------------------------------------------------------------------------------
/var/log/postgresql/postgresql-9.1-main.log

============================================================================================================
TEST THE REPLICATION
============================================================================================================

# -----------------------------------------------------------------------------------------------------------
# ON MASTER : CREATE SOME SCHEMA AND DATA ON MASTER
# -----------------------------------------------------------------------------------------------------------

psql

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');

\q

# -----------------------------------------------------------------------------------------------------------
# ON SLAVE : TRY READING THE MASTER DATA
#            TRY WRITING DATA - SHOULD GIVE ERRORS
# -----------------------------------------------------------------------------------------------------------

psql

SELECT * FROM rep_test;

INSERT INTO rep_test VALUES ('oops'); # SHOULD GIVE ERROR:  cannot execute INSERT in a read-only transaction

\q

Thank you for this, it was very helpful