Monitoring warm standby

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Monitoring warm standby

Damir Markovic
I have multiple warm standby replicas of PostgreSQL 11, used by analytics.
Some of the databases are not very active and I have a problem to figure out if the recovery is in progress or actually there is nothing to recover (because of master inactivity).

I set target time on replica like this:
recovery_target_time = '2019-03-13 12:00:00 UTC'

I checkĀ pg_is_wal_replay_paused() - false. Ok, replica is still catching up.

select max(modification) from pg_ls_waldir(); - '2019-03-13 10:25:15'. Ok, I didn't receive any WAL files in a while.

select pg_last_xact_replay_timestamp(); - '2019-03-13 10:15:42.289668'. Last transaction is even older.

The question now is how can I know if that last WAL file downloaded is fully processed or maybe there is a long running transaction that is still being in progress?

Thank you very much,
Damir