Re: Fwd: Monitoring Replication on Master/Slave Postgres(9.1) - Mailing list pgsql-admin

Thanks for the response:

I tried it and got the below result:

radius=# select procpid,usesysid, usename, application_name, client_addr, state, sent_location,write_location,sync_state from pg_stat_replication;
 procpid | usesysid | usename  | application_name | client_addr |   state   | sent_location | write_location | sync_state
---------+----------+----------+------------------+-------------+-----------+---------------+----------------+------------
    2152 |       10 | postgres | walreceiver      | 10.0.0.2    | streaming | 17B/EBA37AD8  | 17B/EBA37AD8   | async
(1 row)


The last column says async....could you please tell me how can we sync it?

On Tue, Nov 27, 2012 at 1:15 PM, Sergey Garas <aorashi@gmail.com> wrote:

on the master use

select procpid,usesysid, usename, application_name, client_addr, state, sent_location,write_location,sync_state from pg_stat_replication;

the last field is the state of your repliocation status sync or async

Best regards,
Sergey Garas

2012/11/27 Shams Khan <shams.khan22@gmail.com>

Can somebody help me on that????

---------- Forwarded message ----------
From: Shams Khan <shams.khan22@gmail.com>
Date: Mon, Nov 26, 2012 at 3:35 PM
Subject: Monitoring Replication on Master/Slave Postgres(9.1)
To: pgsql-admin@postgresql.org


HI All,

I want to know the ways to monitor the replication, whether the master and slave server are sync.
The way I am checking it is:

On Master:

[root@livedb1 ~]# ps -ef | grep sender
postgres  2152  7045  0 Nov05 ?        01:05:33 postgres: wal sender process postgres 10.0.0.2(45862) streaming 17A/35B62638
root     26030 10506  0 15:26 pts/2    00:00:00 grep sender

radius=# SELECT pg_current_xlog_location();
 pg_current_xlog_location
--------------------------
 17A/342A6F78
(1 row)


On slave database:

[root@liveDB2 ~]# ps -ef | grep receiver
root     11725  4286  0 15:28 pts/0    00:00:00 grep receiver
postgres 27250  3432  0 Nov05 ?        00:35:33 postgres: wal receiver process   streaming 17A/363EE668


radius=# select pg_last_xlog_receive_location();
 pg_last_xlog_receive_location
-------------------------------
 17A/34366C30
(1 row)

If I go to pg_xlog folder: I checked on slave WAL files are generated. However I could not find the same result whenever I run pg_current_xlog_location(); on master and on the other side I get the different number (on slave)

My question is how do we read these numbers(17A/342A6F78 and 17A/34366C30) I never got the same results on both servers. Is that mean slave is not synced with master?
If yes, then how do we ensure my replication is working fine?

Please share you knowledge and through some light.

thanx