Database Reference
In-Depth Information
Monitoring streaming replication is very important and noted previously.
WALSender
processes don't show up in
pg_stat_activity
, though the details are there if you want to
access them. Use the following view:
CREATE OR REPLACE VIEW pg_stat_replication AS
SELECT
S.procpid,
S.usesysid,
U.rolname AS usename,
S.application_name,
S.client_addr,
S.client_port,
S.backend_start
FROM pg_stat_get_activity(NULL) AS S, pg_authid U
WHERE S.usesysid = U.oid AND S.datid = 0;
Managing log shipping replication
Whether you use file-based or streaming replication as the transport mechanism, managing a
log shipping replication cluster is in many ways very similar.
Getting ready
Discussion here assumes you have already set up file-based log shipping, streaming
replication, or both.
How to do it...
Monitoring
Monitoring of log shipping is essential. You'll find it best to enable Hot Standby mode, as the
information is both easier to obtain and more accurate if you do.
Repmgr and pgpool both provide replication monitoring facilities. Munin plugins are available
for graphing replication and apply delay.
You may wish to calculate the delays yourself. To do that, we request current values from Master
and Standby, and then compare the values. On the Master, execute the following query:
SELECT
pg_current_xlog_location();
On the Standby, execute the following query:
SELECT
pg_last_xlog_receive_location();
