Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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
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_basebackup
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
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.conf
standby_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
3.monitor the replication using below functions
$ 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_replication
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.

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

Popular posts from this blog

Oracle DBMS SCHEDULER Examples

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

How to Enable/Disable autovacuum on PostgreSQL