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