Re: How is PG replication typically used to create a High Availability (HA) config ?

Hi David,

please don't top-post.

> On 11. Aug, 2020, at 22:57, David Gauthier <davegauthierpg@gmail.com> wrote:
>
> Thanks for the response Paul :-)
>
> Our code is actually perl which uses DBI which has functions to ping a DB on a specific server and connect to it.
> But my question was more along the lines  of whether or not the onus to do this sort of thing typically lies with the
appor something outside which is orchestrating the HA cfg. 

it should be handled outside the app, im my opinion. But then, many installations don't use pg-bouncer, HA-proxy,
virtualIP addresses or something like that. That's why I suggested using libpq. libpq can handle it. I'm not sure if
andhow it can in done in Perl, though. 

I played around a little with perl-DBI, perl-DBI-Pg, perl-URI and perl-URI-db and, though I managed to get connected, I
didnot manage to specifically select a connect to the primary or replica database cluster. 

Also, your initial steps should be done differently:

1. select count(*) from pg_stat_replication; => p
2. select count(*) from pg_stat_wal_receiver; => r

if:

p = 0 & r = 0 => single database cluster, no replication
p > 0 & r = 0 => primary database cluster
p = 0 & r > 0 => replica database cluster
p > 0 & r > 0 => primary and replica database cluster

The last case can for example happen, if you have database cluster A replicate to B, and B replicate to C, and then
connectto B. 

Also, the test that many people do to select pg_is_in_recovery(); is not a good idea because B and C of the above
exampleare probably in recovery mode, so you still don't know which end you're on. 

Also, pg_is_in_recovery() will probably not work with logical but only streaming replication (both async and sync)
becauseI expect B and C to not be in recovery mode when using logical replication. I didn't try logical replication, so
someoneplease correct me if I'm wrong here. 

If you just want to know, whether your connection is read-write or read-only, you can simply:

show transaction_read_only;

Cheers,
Paul