Is there some kind of query/view/table available in PostgreSQL that can reflect the actual status of a master-slave replication process? Perhaps the "degree" of sync between the nodes?

The above is to periodically monitor our servers to ensure they are actually "in sync" and the slave hasn't gone adrift.

  • postgresql.org/docs/9.4/static/… and the next section might help Dec 7, 2016 at 14:09
  • @dezso I get the overall of the functions, but don't realize how to use them. At first glance seems that pg_is_in_recovery() and pg_last_xact_replay_timestamp() might help, but could you please elaborate more on the comment? Dec 7, 2016 at 14:20
  • Once I have time (an no one answers until then), I'll do that. Dec 7, 2016 at 17:01

3 Answers 3


In the primary side, you have to use the view pg_stat_replication and compare differents fields to see the drift. The function pg_xlog_location_diff give the drift in size :

select   pid, client_addr, state, sync_state,  
         pg_xlog_location_diff(sent_location, write_location) as write_lag,  
         pg_xlog_location_diff(sent_location, flush_location) as flush_lag,  
         pg_xlog_location_diff(sent_location, replay_location) as replay_lag
from pg_stat_replication ;

You can use the lag in monitoring tools, as gauge, to monitor your replication.

If a standby is missing, you have to check in the log's standby why.

If you use replication slots, you have to check the pg_replication_slots view.

  • Isn't this applicable to streaming replication only? And directly connected replicas. Dec 13, 2016 at 19:48
  • Yes it is, including logical replication as pglogical. Other mode of replication (Slony, Londiste) have their own monitoring view.
    – slardiere
    Dec 15, 2016 at 5:37

One of the methods I use (in 9.3) when I'm on the replica servers is to have a small Bash function in the .bash_profile that can be invoked to show the replica delay. This works for both Streaming Replication and for servers only following WAL file replay.

    MASTER='IP or FQDN of master'
    MASTER_CURRENT_XLOG=$(timeout 5 psql -d postgres -U readonlyuser -h "$MASTER" -qtAXc "select pg_current_xlog_location()")
    if [ -n "$MASTER_CURRENT_XLOG" ]; then
        REPLICA_MB_SQL="SELECT ROUND(pg_xlog_location_diff ( '$MASTER_CURRENT_XLOG',  pg_last_xlog_replay_location() ) / 1000000::NUMERIC,3)"
        REPLICA_MINS_SQL="SELECT(CASE WHEN '$MASTER_CURRENT_XLOG' = pg_last_xlog_replay_location() THEN 0 ELSE ROUND((EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())::NUMERIC)/60,2) END) as replication_lag_seconds"

        # Determine replica delay
        DELAY_IN_MB_FROM_MASTER=$(psql -d postgres -U postgres -qtAXc "$REPLICA_MB_SQL")
        DELAY_IN_MINUTES_FROM_MASTER=$(psql -d postgres -U postgres -qtAXc "$REPLICA_MINS_SQL")

        # Emit replica delay
        echo "Replication delay from master: ${DELAY_IN_MB_FROM_MASTER} MB, ${DELAY_IN_MINUTES_FROM_MASTER} minutes"

This function can be invoked on the replica's command line with a simple rep command. This snippet assumes that you have a user in the master db named "readonlyuser" (in my case, that is literally a user with read-only privs), and that connectivity between the servers over port 5432 is working. This is only a subset of the details that my rep() command emits, I have a few other queries that show me health details of the master but they are specific to my data.


For versions >= PostgreSQL 10:

select   pid, client_addr, state, sync_state,  
         pg_wal_lsn_diff(sent_lsn, write_lsn) as write_lag,  
         pg_wal_lsn_diff(sent_lsn, flush_lsn) as flush_lag,  
         pg_wal_lsn_diff(sent_lsn, replay_lsn) as replay_lag
from pg_stat_replication;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.