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 – dezso Dec 7 '16 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? – Gonzalo Vasquez Dec 7 '16 at 14:20
  • Once I have time (an no one answers until then), I'll do that. – dezso Dec 7 '16 at 17:01

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.

| improve this answer | |
  • Isn't this applicable to streaming replication only? And directly connected replicas. – dezso Dec 13 '16 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 '16 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.

| improve this answer | |

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;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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