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

David J.

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 (<paul.foerster@gmail.com>) kirjutas kuupäeval R, 20.
november 2020 kell 11:29:

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.

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

Hmm, ok.

But how is this possible..?

when b.sender>0 and c.receiver>0 then

'Primary+Replica'

Raul

Kontakt Paul Förster (<paul.foerster@gmail.com>) kirjutas kuupäeval R, 20.
november 2020 kell 12:04:

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

Ok, Thanks!

Raul

Kontakt Paul Förster (<paul.foerster@gmail.com>) kirjutas kuupäeval R, 20.
november 2020 kell 12:54:

On Friday, November 20, 2020, Paul Förster <paul.foerster@gmail.com> wrote:

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.

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.