PostgreSQL 12 High Availability Cookbook - Third Edition

Viewing replication status on a replica

Beginning with PostgreSQL 9.6, we can view a lot of information about the replication stream from the replica server. In previous versions, there were only a couple of functions, and they only really told us which transaction log the replica had recently processed. Version 9.6 introduces a view named pg_stat_wal_receiver to solve that issue. Consider we have our pg2 replica and it's using a replication slot named pg2_slot. We could use this query on the replica to learn a bit more:

SELECT status, latest_end_lsn, latest_end_time, slot_name  FROM pg_stat_wal_receiver;

The output of this should resemble the following screenshot:

This tells us that the streaming is active and it's using the slot as expected, ...