check postgres replication status

postgresql database-replication

Dino Daniel · Apr 13, 2017 · Viewed 72.7k times · Source

Can someone suggest the steps to check pgsql replication status and when to say replication is not happening properly?

We use streaming replication with pgsql9.0 and pgsql9.4

Thanks.

Answer

On your master, pg_stat_replication provides data about ongoing replication:

select client_addr, state, sent_location, write_location,
        flush_location, replay_location from pg_stat_replication;

On postgresql v10:

select client_addr, state, sent_lsn, write_lsn,
    flush_lsn, replay_lsn from pg_stat_replication;