Re: Determine if postgresql cluster running is primary or not

Hi

Thanks.
Seems like 9.5 does not work.

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(dot)foerster(at)gmail(dot)com>) kirjutas kuupäeval R, 20.
november 2020 kell 11:29:

> Hi Thomas,
>
> > On 20. Nov, 2020, at 10:03, Thomas Kellerer <shammat(at)gmx(dot)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;
>
> Cheers,
> Paul
>
>