Master Slave Replication setup with PostgreSQL 9.4.1 on RHEL6

         Master Server Configuration

1. Enable variables in configuration file : vi /var/lib/pgsql/9.4/data/postgresql.conf

   wal_level = hot_standby
   max_wal_senders = 1
   wal_keep_segments = 50

2. Enable variables in host based configuration file: <replace this ip with your slave host name or IP>

   host replication postgres 192.168.1.2/24 trust

3. Restart database engine 

   [root@hostname_master ~]# service postgresql-9.4 restart

4. Take backup and move it to slave server

   [root@hostname_master ~]#  su - postgres
   -bash-4.1$ psql -c "SELECT pg_start_backup('repl_backup');"
   -bash-4.1$tar cfP /tmp/db_file_backup.tar /var/lib/pgsql/9.4/data
   -bash-4.1$ psql -c "SELECT pg_stop_backup();"
   -bash-4.1$ scp /tmp/db_file_backup.tar root@slave_server_IP:/tmp/      

   Slave server configuration

5.  Stop service on slave server

   [root@hostname_slave ~]# service postgresql-9.4 stop

6. Rename slave datadir and untar master backup 

   [root@hostname_slave ~]# mv /var/lib/pgsql/9.4/data /var/lib/pgsql/9.4/data.old
   [root@hostname_slave ~]# tar xvfP /tmp/db_file_backup.tar
   [root@hostname_slave ~]# rm -f /var/lib/pgsql/9.4/data/postmaster.pid

   
   

7. Enable hot_standby option in configuration file

   [root@hostname_slave ~]# vi /var/lib/pgsql/9.4/data/postgresql.conf
   Enable "hot_standby = on" in conf file

8. Copy and Update recovery file

   [root@hostname_slave ~]# cp /usr/pgsql-9.4/share/recovery.conf.sample /var/lib/pgsql/9.4/data/recovery.conf
   [root@hostname_slave ~]# chmod 600 /var/lib/pgsql/9.4/data/recovery.conf && chown -R postgres.postgres /var/lib/pgsql/9.4/data
   [root@hostname_slave ~]# vi /var/lib/pgsql/9.4/data/recovery.conf
   standby_mode = on
   primary_conninfo = 'host=hostname_master port=5432'

   
   

9. Start PostgreSQL service

   [root@hostname_slave ~]# service postgresql-9.4 start

   Replication setup complete
   

10.  Test your setup by creating a database/table on master and query the same on slave server

    [root@remote_host ~]# psql -U admin -d postgres -h 192.168.1.1
    Password for user admin:
    psql (9.4.1)
    Type "help" for help.
    postgres=# create database test_replication;
    CREATE DATABASE
    postgres=#

    
    Slave server

    [root@hostname_slave ~]# su - postgres
    -bash-4.1$ psql
    psql (9.4.1)
    Type "help" for help.
    postgres=# \l
                                         List of databases
           Name       |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
    ------------------+----------+----------+-------------+-------------+-----------------------
     postgres         | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
     template0        | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                      |          |          |             |             | postgres=CTc/postgres
     template1        | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                      |          |          |             |             | postgres=CTc/postgres
     test_replication | admin    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
    (4 rows)