Thursday, September 1, 2016

[PostgreSQL] How to verify if replication catch up with master change

[Run on master] Check master location with
SELECT pg_current_xlog_location();
 [Run on replica] Check replica replayed location with
SELECT pg_last_xlog_replay_location();
[Run on master] Check replica status from master
SELECT client_addr, state, sent_location, write_location, flush_location, replay_location FROM pg_stat_replication;
*write, flush, replay are status of replica

[Run on replica] Check time from the latest reply
SELECT now() - pg_last_xact_replay_timestamp() AS replication_delay;
Credit:

Collectd PostgreSQL Plugin

I couldn't find this link when searching with google https://www.collectd.org/documentation/manpages/collectd.conf.html#plugin-postgresql