Postgresql 9.2 streaming replication step by step

Using below steps we can create postgresql high availability setup.

Postgres replication setup:

Primary server configuration:

step1: edit postgresql.conf

wal_level = hot_standby
max_wal_senders = 1
wal_keep_segments = 50
archive_mode = on
archive_command = 'cp %p /archive_log/archive/%f'

step2: edit pg_hba.conf

host replication postgres 192.168.X.X/32 trust ----DR server IP address

step3:restart postgres service

as a root user go to /etc/init.d/

#service postgresql-9.2 restart

step4:Next thing we need to do is take data snapshot of data from master and then move that to slave server.

psql -c "SELECT pg_start_backup('replbackup');"
(GO TO ROOT AND TAR DATA)
tar cfvP /opt/Postgresql/9.2/data.tar /opt/Postgresql/9.2/data
psql -c "SELECT pg_stop_backup();"

step5:Now move this data to slave/standby database server.

scp /tmp/db_file_backup.tar root@opensourcedbms_pg_slave:/tmp/

Standby server configuration:

step1:First stop server if it is running

#service postgresql-9.2 stop

step2:Rename the existing data directory.

mv /var/lib/pgsql/9.2/data/ /var/lib/pgsql/9.2/data.old

step3:Unzip master/primary server data snapshot file that is copied into this server.

tar xvfP /tmp/db_file_backup.tar

step4:Remove postmaster.pid so standby server does not see the primary server’s pid as its own.

rm -f /var/lib/pgsql/9.2/data/postmaster.pid

step5:Now edit configuration file and tweak hot_standby variable.

vi /var/lib/pgsql/9.2/data/postgresql.conf

hot_standby = on

step6:cp /usr/pgsql-9.2/share/recovery.conf.sample /var/lib/pgsql/9.2/data/recovery.conf

step7:Edit this recovery.conf file and update standby server settings:

vi /var/lib/pgsql/9.2/data/recovery.conf

standby_mode = on
primary_conninfo = 'host=192.168.1.4 port=5432 user=postgres password=postgres' ------Give the primary database IP address

step8:Update permissions on recovery.conf file

chown postgres.postgres /var/lib/pgsql/9.2/data/recovery.conf

step9: Start the postgresql service

service postgresql-9.2 start

Using Below queries check the replication status

select client_addr, state, sent_location, write_location,flush_location, replay_location from pg_stat_replication;

select * from pg_stat_replication ;

select pg_last_xlog_replay_location(); (standby side)

select pg_is_in_recovery();( to know the recovery mode)

To Know the log delay:

SELECT CASE WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location() THEN 0 ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())
END AS log_delay;