Re: Determine if postgresql cluster running is primary or not

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;

Cheers,
Paul