Обсуждение: Determine if postgresql cluster running is primary or not

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();

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

> On 20. Nov, 2020, at 10:03, Thomas Kellerer <shammat@gmx.net> wrote:

>
>   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:

Hi David,

> On 20. Nov, 2020, at 10:34, David G. Johnston <david.g.johnston@gmail.com> wrote:
>
>
> On Friday, November 20, 2020, Paul Förster <paul.foerster@gmail.com> wrote:
>
> > On 20. Nov, 2020, at 10:03, Thomas Kellerer <shammat@gmx.net> wrote:
>
> >
> >   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: 
>
> Do any of those other reasons allow connections that could execute that function to exist?

that always depends on what your application does. An application could still select a lot of things, maybe even
wronglyso, even if the cluster is in recovery mode. 

That was my idea when writing this query and it's been working fine for years now.

Cheers,
Paul

Hi Raul,

> On 20. Nov, 2020, at 10:41, Raul Kaubi <raulkaubi@gmail.com> wrote:
>
> 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

this query is tested to work on 10.x and newer, not 9.x. I don't have 9.x, so I can't say, sorry.

Cheers,
Paul

Hi Raul,

> On 20. Nov, 2020, at 11:45, Raul Kaubi <raulkaubi@gmail.com> wrote:
>
> Hmm, ok.
>
> But how is this possible..?
>
>  when b.sender>0 and c.receiver>0 then
>             'Primary+Replica'
>
> Raul

this happens for example if you have a primary a and replica b running as a normal cluster (we use Patroni for
automaticfailover) and then add another replica c to the existing replica b, effectively replicating: a => b => c In
thiscase, b would be the replica of a, but also be the primary for c. 

It's called cascading replication.

Cheers,
Paul

Hi David,

> On 20. Nov, 2020, at 10:34, David G. Johnston <david.g.johnston@gmail.com> wrote:
>
>
> On Friday, November 20, 2020, Paul Förster <paul.foerster@gmail.com> wrote:
>
> > On 20. Nov, 2020, at 10:03, Thomas Kellerer <shammat@gmx.net> wrote:
>
> >
> >   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:
>
> Do any of those other reasons allow connections that could execute that function to exist?

that always depends on what your application does. An application could still select a lot of things, maybe even wrongly so, even if the cluster is in recovery mode.