what are the steps in PostgreSQL Replication and tell with prerequest and postrequest?
Prerequest:
1.We will determine the mount points of the Postgresql data directories and the WAL directories.
2.Check the ip address,port number and hostname, of the master server using "hostname -i,hostname" if using linux this is for knowledge purposes.
3.Allocate the mount point directories for data and wal
4.Set the the ip address and check the availability of port number at slave server using "netstat -na"
5.Check the firewall whether enable or not for both server of master and slave using "service iptables status"
if firewall is enabled you need to disable using "service iptables stop" "or chkconfig ip6tables off".
6.Check the both server whether ping or not using "ping master ip from slave server and ping slave ip from master server"
Replication steps:
1.install the postgres software on salve server
2.On the master, edit the $PGDATA/postgresql.conf
4.Modify the master pg_hba.conf and enable the replication user for the IP of the slave
5.Restart the master server using "pg_ctl restart -d $PGDATA"
slave:
1.On the slave, from $HOME, issue the pg_basebackup command to start setting up the hot standby from the master.
2.On the slave, after pg_basebackup has successfully completed, edit $PGDATA/postgresql.conf
Postrequest:
1.monitor the replication using pg_stat_replication
2.check the streaming replication using
3.monitor the replication using below functions
The pg_stat_replication view will contain one row per WAL sender process, showing statistics about replication to that sender's connected standby server. Only directly connected standbys are listed; no information is available about downstream standby servers.
1.We will determine the mount points of the Postgresql data directories and the WAL directories.
2.Check the ip address,port number and hostname, of the master server using "hostname -i,hostname" if using linux this is for knowledge purposes.
3.Allocate the mount point directories for data and wal
4.Set the the ip address and check the availability of port number at slave server using "netstat -na"
5.Check the firewall whether enable or not for both server of master and slave using "service iptables status"
if firewall is enabled you need to disable using "service iptables stop" "or chkconfig ip6tables off".
6.Check the both server whether ping or not using "ping master ip from slave server and ping slave ip from master server"
Replication steps:
1.install the postgres software on salve server
2.On the master, edit the $PGDATA/postgresql.conf
wal_level = hot_standby
archive_mode = on ## /pgdata/WAL_Archive is a staging directory on the slave
archive_command = 'rsync -W -az %p postgres@$SLAVE_IP_HERE:/pgdata/WAL_Archive/'
max_wal_senders = 5
wal_keep_segments = 5000 # If you have the room, to help the pg_basebackup
# not fail due to WAL segments being removed from master.
# For clusters will very little traffic, 100 is probably fine
listen_addresses = #master ip address
port (integer) =5432
3.create replication role which will be copied to the slave via pg_basebackup4.Modify the master pg_hba.conf and enable the replication user for the IP of the slave
5.Restart the master server using "pg_ctl restart -d $PGDATA"
slave:
1.On the slave, from $HOME, issue the pg_basebackup command to start setting up the hot standby from the master.
2.On the slave, after pg_basebackup has successfully completed, edit $PGDATA/postgresql.conf
hot_standby = on #off # "on" allows queries during recovery
max_standby_archive_delay = 15min # max delay before canceling queries,
# set to hours if backups will be taken from here
max_standby_streaming_delay = 15min # max delay before canceling queries
hot_standby_feedback = on #off
listen_addresses = #slave ip address
port (integer) =5444
3.On the slave, create $PGDATA/recovery.confstandby_mode = on
trigger_file = '/tmp/promote_db_slave'
primary_conninfo = 'host=IP_OF_MASTER port=5432 user=replication password=CHANGEME'
restore_command = 'cp /pgdata/WAL_Archive/%f "%p" 2>>/pgdata/9.3/data/pg_log/standby.log'
archive_cleanup_command = '/usr/pgsql-9.3/bin/pg_archivecleanup /pgdata/WAL_Archive %r'
recovery_target_timeline = 'latest'
4.start the salve using "pg_ctl -D $PGDATA start"Postrequest:
1.monitor the replication using pg_stat_replication
2.check the streaming replication using
[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
$ 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)
pg_stat_replicationThe pg_stat_replication view will contain one row per WAL sender process, showing statistics about replication to that sender's connected standby server. Only directly connected standbys are listed; no information is available about downstream standby servers.
Column
|
Type
|
Description
|
pid
|
integer
|
Process ID of
a WAL sender process
|
usesysid
|
oid
|
OID of the
user logged into this WAL sender process
|
usename
|
name
|
Name of the
user logged into this WAL sender process
|
application_name
|
text
|
Name of the
application that is connected to this WAL sender
|
client_addr
|
inet
|
IP address of
the client connected to this WAL sender. If this field is null, it indicates
that the client is connected via a Unix socket on the server machine.
|
client_hostname
|
text
|
Host name of
the connected client, as reported by a reverse DNS lookup of client_addr. This field will only be non-null
for IP connections, and only when log_hostname is enabled.
|
client_port
|
integer
|
TCP port
number that the client is using for communication with this WAL sender,
or -1 if a Unix socket is used
|
backend_start
|
timestamp with time zone
|
Time when
this process was started, i.e., when the client connected to this WAL sender
|
backend_xmin
|
xid
|
This
standby's xmin horizon reported by hot_standby_feedback.
|
state
|
text
|
Current WAL
sender state
|
sent_location
|
pg_lsn
|
Last
transaction log position sent on this connection
|
write_location
|
pg_lsn
|
Last
transaction log position written to disk by this standby server
|
flush_location
|
pg_lsn
|
Last
transaction log position flushed to disk by this standby server
|
replay_location
|
pg_lsn
|
Last
transaction log position replayed into the database on this standby server
|
sync_priority
|
integer
|
Priority of
this standby server for being chosen as the synchronous standby
|
sync_state
|
text
|
Synchronous
state of this standby server
|
Comments
Post a Comment