Re: Determine if postgresql cluster running is primary or not

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