The rest of this recipe assumes that you have enabled hot_standby. This is not an absolute requirement, but it makes things much, much easier.
Both repmgr and pgpool provide replication monitoring facilities. Munin plugins are available for graphing replication and apply delay.
Replication works by processing the WAL transaction log on other servers. You can think of WAL as a single, serialized stream of messages. Each message in the WAL is identified by an 8-byte integer known as a Log Sequence Number (LSN). For historical reasons, we show this as two separate hex numbers; for example, the LSN value X is shown as XXXX/YYYY.
You can compare any two LSNs using pg_wal_lsn_diff(). In some places, prior to PostgreSQL 10, an LSN was referred to as a "location", a term no longer in use.
To understand how to monitor progress, you need to understand a little more about replication as a transport mechanism. The stream of messages flows through the system like water through a pipe. You can work out how much progress has been made by measuring the LSN at different points in the pipe. You can also check for blockages in the pipe by measuring the relative progress between points.
New WAL records are inserted into the WAL files on the master. The current insert LSN can be found using this query:
SELECT pg_current_wal_insert_lsn();
However, WAL records are not replicated until they have been written and synced to the WAL files on the master. The LSN of the most recent WAL write is given by this query on the master:
SELECT pg_current_wal_lsn();
Once written, WAL records are then sent to the standby. The recent status can be found by running this query on the standby (this and the later functions return NULL on a master):
SELECT pg_last_wal_receive_lsn();
Once WAL records have been received, they are written to WAL files on the standby. When the standby has written those records, they can then be applied to it. The LSN of the most recent apply is found using this standby query:
SELECT pg_last_wal_replay_lsn();
Remember that there will always be timing differences if you run status queries on multiple nodes. What we really need is to see all of the information on one node. A view called pg_stat_replication provides the information that we need:
SELECT pid, application_name /* or other unique key */
,pg_current_wal_insert_lsn() /* WAL Insert lsn */
,sent_lsn /* WALSender lsn */
,write_lsn /* WALReceiver write lsn */
,flush_lsn /* WALReceiver flush lsn */
,replay_lsn /* Standby apply lsn */
,backend_start /* Backend start */
FROM pg_stat_replication;
-[ RECORD 1 ]-------------------+------------------------------ pid | 16496
application_name | pg_basebackup pg_current_wal_insert_lsn | 0/80000D0
sent_lsn |
write_lsn |
flush_lsn |
replay_lsn |
backend_start | 2017-01-27 15:25:42.988149+00
-[ RECORD 2 ]-------------------+-------------------pid
16497
application_name | pg_basebackup pg_current_wal_insert_lsn | 0/80000D0
sent_lsn | 0/80000D0
write_lsn | 0/8000000
flush_lsn | 0/8000000
replay_lsn |
backend_start | 2017-01-27 15:25:43.18958+00
Each row in this view represents one replication connection. The preceding snippet shows the output from a pg_basebackup that is using --wal-method=stream. The first connection shown is the base backup, while the second session is streaming WAL changes. Note that the replay_lsn is NULL, indicating that this is not a standby.
Standby nodes send regular status messages to let the sender know how far it has progressed. If you run this query on the master, you'll be able to see all the directly connected standbys. If you run this query on a standby, you'll see values representing any cascaded standbys, but nothing about the master or any of the other standbys connected to the master. Note that because the data has been sent from a remote node, it is very likely that processing will have progressed beyond the point being reported, but we don't know that for certain. That's just physics. Welcome to the world of distributed systems!
In PostgreSQL 10, replication delay times are provided directly using sampled message timings to provide the most accurate viewpoint of current delay times. Use this query:
SELECT pid, application_name /* or other unique key */
,write_lag, flush_lag, replay_lag
FROM pg_stat_replication;
Another view called pg_stat_wal_receiver provides information about the standby that we may be interested in; this view returns zero rows on the master.