How can a PostgreSQL standby decide whether its primary is still active or not

Question

I have a setup with asynchronous streaming replication so that PSQL1 is primary and PSQL2 is hot standby. Since PSQL2 is running in hot standby mode I can do run queries against it.

Is it possible on PSQL2 to tell whether PSQL1 is still active or already shutdown? I tried select pg_is_in_recovery(); but it returns true regardless whether PSQL1 is running or not.

Answer 1

For streaming replication, you can check the pg_stat_wal_receiver view. There will be one line here when wal receiver is streaming.

Another question is why you need such information. For monitoring purposes, the amount of replication delay is much more important. Async replica may be significant lagged, but primary and wal receiver still alive (e.g. while reach the network bandwidth limit). Usually on hot standby:

select now() - pg_last_xact_replay_timestamp();

and pg_stat_replication on primary.