Determine if postgresql cluster running is primary or not

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

Determine if postgresql cluster running is primary or not

Raul Kaubi
Hi

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.

What would be the best way to achieve this?

Regards
Raul
Reply | Threaded
Open this post in threaded view
|

Re: Determine if postgresql cluster running is primary or not

Thomas Kellerer-4
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();



Reply | Threaded
Open this post in threaded view
|

Re: Determine if postgresql cluster running is primary or not

Paul Förster
Hi Thomas,

> On 20. Nov, 2020, at 10:03, Thomas Kellerer <[hidden email]> 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 | Threaded
Open this post in threaded view
|

Re: Determine if postgresql cluster running is primary or not

David G Johnston

On Friday, November 20, 2020, Paul Förster <[hidden email]> wrote:

> On 20. Nov, 2020, at 10:03, Thomas Kellerer <[hidden email]> 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?

David J.
 
Reply | Threaded
Open this post in threaded view
|

Re: Determine if postgresql cluster running is primary or not

Raul Kaubi
In reply to this post by Paul Förster
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 (<[hidden email]>) kirjutas kuupäeval R, 20. november 2020 kell 11:29:
Hi Thomas,

> On 20. Nov, 2020, at 10:03, Thomas Kellerer <[hidden email]> 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 | Threaded
Open this post in threaded view
|

Re: Determine if postgresql cluster running is primary or not

Paul Förster
In reply to this post by David G Johnston
Hi David,

> On 20. Nov, 2020, at 10:34, David G. Johnston <[hidden email]> wrote:
>
>
> On Friday, November 20, 2020, Paul Förster <[hidden email]> wrote:
>
> > On 20. Nov, 2020, at 10:03, Thomas Kellerer <[hidden email]> 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.

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

Cheers,
Paul

Reply | Threaded
Open this post in threaded view
|

Re: Determine if postgresql cluster running is primary or not

Paul Förster
In reply to this post by Raul Kaubi
Hi Raul,

> On 20. Nov, 2020, at 10:41, Raul Kaubi <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: Determine if postgresql cluster running is primary or not

Raul Kaubi
Hmm, ok.

But how is this possible..?

 when b.sender>0 and c.receiver>0 then
            'Primary+Replica'

Raul

Kontakt Paul Förster (<[hidden email]>) kirjutas kuupäeval R, 20. november 2020 kell 12:04:
Hi Raul,

> On 20. Nov, 2020, at 10:41, Raul Kaubi <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: Determine if postgresql cluster running is primary or not

Paul Förster
Hi Raul,

> On 20. Nov, 2020, at 11:45, Raul Kaubi <[hidden email]> 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 automatic failover) and then add another replica c to the existing replica b, effectively replicating: a => b => c In this case, b would be the replica of a, but also be the primary for c.

It's called cascading replication.

Cheers,
Paul

Reply | Threaded
Open this post in threaded view
|

Re: Determine if postgresql cluster running is primary or not

Raul Kaubi
Ok, Thanks! 

Raul

Kontakt Paul Förster (<[hidden email]>) kirjutas kuupäeval R, 20. november 2020 kell 12:54:
Hi Raul,

> On 20. Nov, 2020, at 11:45, Raul Kaubi <[hidden email]> 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 automatic failover) and then add another replica c to the existing replica b, effectively replicating: a => b => c In this case, b would be the replica of a, but also be the primary for c.

It's called cascading replication.

Cheers,
Paul
Reply | Threaded
Open this post in threaded view
|

Re: Determine if postgresql cluster running is primary or not

David G Johnston
In reply to this post by Paul Förster
On Friday, November 20, 2020, Paul Förster <[hidden email]> wrote:
Hi David,

> On 20. Nov, 2020, at 10:34, David G. Johnston <[hidden email]> wrote:
>
>
> On Friday, November 20, 2020, Paul Förster <[hidden email]> wrote:
>
> > On 20. Nov, 2020, at 10:03, Thomas Kellerer <[hidden email]> 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.

I don’t follow - i posit that if psql successfully connects to a server that reports it is is recovery that server is a secondary to some other server, period.  Can you provide a counter-example for when that isn’t true (given the whole psql connects successfully bit).

David J.