Hi David, please don't top-post.
> On 11. Aug, 2020, at 22:57, David Gauthier <davegauthie...@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 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
