Postgresql-12 replication status in recovery mode

Question

I have postgresql master server and a standby server. The standby will be in recovery mode(ie. will be consuming the wals shipped from the master node). The master will be read/write and the standby will not be able to query until its promoted(DR setup).

I wanted to know the replication status of the standby node(ie. Each wals restored in standby with time to time).

Version - postgresql 12.3

OS - centos 8

recovery.signal

Also is it possible to use streaming replication along with wal file shipping.

Answer 1

You can ask primary server about replication status of standby server(s) by using pg_stat_replication which is a system statistics view.

You can use WAL Archiving to setup a replication instead of / in addition to streaming replication

Answer 2

Start the replica in standby mode (using standby.signal instead of recovery.signal). Then you can monitor replication in the usual way. Mainly via select pg_last_xact_replay_timestamp();

---

recovery.signal is used to recover from data loss, with some recovery target configured.

Be aware:

Targeted recovery mode ends when the archived WAL is fully replayed, or when recovery_target is reached.

At the same time, this means the following: the server will complete the recovery if you do not deliver new WALs faster than the startup process has finished replaying previous WAL segment.

Another significant difference is that recovery mode does not imply retrying to read the WAL in case of some temporary error, such as a network error. restore_command did not create new WAL segment? Fine, we ends recovery mode.