Primary DB Server:
Name:dbserver1 IP Address: 192.168.160.147 Data directory: /opt/PostgresPlus/9.2AS/data Port: 5444
Stand by DB Server
Name:dbserver2 IP Address: 192.168.160.150 Data Directory: /opt/PostgresPlus/9.2AS/data2 Port: 5222
Change the below parameter:
wal_level = hot_standby max_wal_senders = 3 wal_keep_segments = 128 (optional/depending on load) replication_timeout = 5 sec (optional) hot_standby = on (required/effective only for hot stand by server)
Add a new value in pg_hba.conf
host replication all 192.168.160.0/24 trust
standby_mode = 'on' primary_conninfo = 'host=192.168.160.147 port=5444 user=enterprisedb password=ashnik' restore_command = 'scp firstname.lastname@example.org:/mnt/arch/%f %p' # optional # needs archiving command to be enabled on primary recovery_target_timeline = 'latest' #optional trigger_file = '/opt/PostgresPlus/9.2AS/data/recover.trigger'
standby_mode = 'on' primary_conninfo = 'host=192.168.160.150 port=5222 user=enterprisedb password=ashnik' restore_command = 'scp email@example.com:/mnt/arch/%f %p' # optional # needs archiving command to be enabled on primary recovery_target_timeline = 'latest' #optional trigger_file = '/opt/PostgresPlus/9.2AS/data/recover.trigger'
You need to take a cold backup of your primary server and restore it on secondary server. With rest of the files in the backup you will also get below files:
postgresql.conf.dbserver1 postgresql.conf.dbserver2 recovery.conf.dbserver1_is_master recovery.conf.dbserver2_is_master
- Check the primary and secondary server. Connect to each of them to confirm the connectivity, role and read/write accessibility.
- You can use “pg_ctl status” OS command and “pg_is_in_recovery()” SQL function to confirm the status of each database.
So for that, first there has to be a failure. On the primary site, fire a command
kill -9 <>
On the secondary server create the trigger file (as specified in recovery.conf)
First of all clean up the database cluster on old Primary server:
rm -Rf /opt/PostgresPlus/9.2AS/data
Now, take a backup from new primary (dbserver2) to rebuild the master:
pg_basebackup -D /opt/PostgresPlus/9.2AS/data -h 192.168.160.150 -W -p 5222 -Fp --xlog-method=stream
Now start the db cluster on master node. Once the start-up is successful, make sure everything is alright by connecting to the database and firing the below command:
SELECT pg_is_in_recovery(); #expected output is “t”
Then fire the below command on dbserver1 and dbserver2 and both should be same:
$ psql c "SELECT pg_current_xlog_location()" h192.168.160.147 (primary host) pg_current_xlog_location 0/2000000 (1 row) $ psql c "select pg_last_xlog_receive_location()" h192.168.160.150 (standby host) pg_last_xlog_receive_location 0/2000000 (1 row) $ psql c "select pg_last_xlog_replay_location()" h192.168.160.150 (standby host) pg_last_xlog_replay_location 0/2000000 (1 row)
Step 1:Need to create table on Primary using below command.
create table stream_delay (tstamp timestamp without time zone ); insert into stream_delay select now();
Step 2: schedule the below command on primary to execute every minute on cronjob.
update stream_delay set tstamp='now()';
You can also check the progress of streaming replication by using ps command.#The displayed LSNs indicate the byte position that the standby server has written up to in the xlogs.
[primary] $ ps ef | grep sender postgres 6879 6831 0 10:31 ? 00:00:00 postgres: wal sender process postgres 127.0.0.1(44663) streaming 0/2000000 [standby] $ ps ef | grep receiver postgres 6878 6872 1 10:31 ? receiver process streaming 0/2000000