ERROR: relation "pg_stat_wal_receiver" does not exist LINE 20: from pg_stat_wal_receiver
Any ide how to achieve this in 9.5 ?
Raul
Kontakt Paul Förster (<paul.foerster@gmail.com>) kirjutas kuupäeval R, 20. november 2020 kell 11:29:
Hi Thomas,
> On 20. Nov, 2020, at 10:03, Thomas Kellerer <shammat@gmx.net> wrote: > > Raul Kaubi schrieb am 20.11.2020 um 09:53: >> CentOS 7 >> Postgres 9 to 12 >> >> For monitoring purpose, I would like that certain scripts are only run in primary server. >> So I am looking ways to universally discover if postgresql cluster that is running is primary or not. > > As the standby will be in constant recovery, you can use > > select pg_is_in_recovery();
I usually don't recommend using pg_is_in_recovery() only because a database cluster can be in recovery for other reasons. This is why I always do the following:
select distinct case when b.sender=0 and c.receiver=0 then 'Standalone' when b.sender>0 and c.receiver=0 then 'Primary' when b.sender=0 and c.receiver>0 then 'Replica' when b.sender>0 and c.receiver>0 then 'Primary+Replica' end as pgrole from pg_database a, ( select count(*) as sender from pg_stat_replication ) b, ( select count(*) as receiver from pg_stat_wal_receiver ) c where not a.datistemplate;