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?
