Hi Thomas,

> On 20. Nov, 2020, at 10:03, Thomas Kellerer <sham...@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

Reply via email to