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

Thanks again Paul and Rob.

I'm going to need more specifics from my IT department regarding exactly
what they did... what tool they used to create what they are calling this
"High Availability" DB (pg-bouncer, etc...). If I can determine that, then
maybe there are already some hooks in place that I can leverage. But for
this to be seamless, I suspect I'll also have to do something on the app
end beyond making a hard connection from my perl script using DBI. I did
find something about an enhanced version of the DBI connect method which
might redirect connections in the event of a primary/backup swap.

-dave

On Wed, Aug 12, 2020 at 4:10 AM Paul Förster <paul(dot)foerster(at)gmail(dot)com>
wrote:

> Hi David,
>
> please don't top-post.
>
> > On 11. Aug, 2020, at 22:57, David Gauthier <davegauthierpg(at)gmail(dot)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 app or 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, virtual IP addresses or
> something like that. That's why I suggested using libpq. libpq can handle
> it. I'm not sure if and how 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 did not 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 connect to 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 example are 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) because I expect B and C to not
> be in recovery mode when using logical replication. I didn't try logical
> replication, so someone please 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