Thread: How to determine replication lag
I am wondering if there is a way to find replication lag (in time seconds etc.) between primary and standby in case of STREAMING REPLICATION.
I can use the function pg_last_xact_replay_timestamp () as below. Only problem is IF THERE IS NO UPDATE ACTIVITY in primary database the output of the following query keeps increasing but in reality standby is in sync with primary data-wise.
SELECT EXTRACT (EPOCH FROM AGE(current_timestamp, pg_last_xact_replay_timestamp()) )
I used the SQL from this link to determine replication lag: http://www.dansketcher.com/2013/01/27/monitoring-postgresql-streaming-replication/ 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; The one caveat is that if nothing else is committing changes and you have long running transactions that are generating wal,but have not committed, you will show a replication lag. As I understand it pg_last_xact_replay_timestamp() shows thelast committed xact timestamp. And Josh Berkus has done some nice write ups on replication lag: http://www.databasesoup.com/2014/04/simplifying-replication-position.html
try this :
SELECT
client_hostname,
client_addr,
sent_offset - (replay_offset - (sent_xlog - replay_xlog) * 255 * 16 ^ 6 ) AS byte_lag
FROM (
SELECT
client_hostname,
client_addr,
('x' || lpad(split_part(sent_location, '/', 1), 8, '0'))::bit(32)::bigint AS sent_xlog,
('x' || lpad(split_part(replay_location, '/', 1), 8, '0'))::bit(32)::bigint AS replay_xlog,
('x' || lpad(split_part(sent_location, '/', 2), 8, '0'))::bit(32)::bigint AS sent_offset,
('x' || lpad(split_part(replay_location, '/', 2), 8, '0'))::bit(32)::bigint AS replay_offset
FROM pg_stat_replication
) AS s;
client_hostname,
client_addr,
sent_offset - (replay_offset - (sent_xlog - replay_xlog) * 255 * 16 ^ 6 ) AS byte_lag
FROM (
SELECT
client_hostname,
client_addr,
('x' || lpad(split_part(sent_location, '/', 1), 8, '0'))::bit(32)::bigint AS sent_xlog,
('x' || lpad(split_part(replay_location, '/', 1), 8, '0'))::bit(32)::bigint AS replay_xlog,
('x' || lpad(split_part(sent_location, '/', 2), 8, '0'))::bit(32)::bigint AS sent_offset,
('x' || lpad(split_part(replay_location, '/', 2), 8, '0'))::bit(32)::bigint AS replay_offset
FROM pg_stat_replication
) AS s;
Vasilis Ventirozos
Alright.
I have a one-server “cluster” running on a server. I am currently downloading a dump from the standard postgres server on another server into postgres-xc on my new “cluster.” I am assuming I can point the clients at the new cluster and they will work transparently with it think it’s just a standard postgres server.
What is not clear at this point is how I configure a second machine to also be a postgres-cx server and add it to the cluster. The docs explain how to set up multiple datanodes on one machine but they are kind of unclear about how one sets up additional machines to be synched to an existing one. I really would appreciate any help anyone can give.