Обсуждение: How is PG replication typically used to create a High Availability (HA) config ?

Hi David,

> On 11. Aug, 2020, at 17:12, David Gauthier <davegauthierpg@gmail.com> wrote:
>
> Hi:
>
> Our IT dept has created what they call a High Availability DB for our PG DB (9.6.7 on linux).  If the primary fails,
theypromise to promote the backup to be the new primary but leave it at that.  But from the perspective of the app, I'm
leftwith.... 
> 1) detecting an SQL error is a DB connectivity problem.
> 2) Attempt to reconnect.  If fails, try connecting to the backup server (assume it's the new primary ?)
> 3) If that works, then test to see if it is indeed a primary (try a write statement) ?
> 4) Somehow remember that the backup server is the one to connect to as the primary for future connections.
> 5) When the original primary server is fixed and brought back on-line, and they promote it to being the new primary,
thenI guess my write statements will fail because I'm still connecting to the backup.  So go figure that out and reset
apointer of sorts to the new primary ? 
>
> Seems like a lot of work for an app and that it should be more seamless.  But maybe I'm wrong.
>
> I was hoping for something like a server alias that the IT team toggles as needed between the servers so that I
alwaysknow what to connect to.  The best solution would be something completely seamless... my app isn't even aware
thatthe primary went down, the backup was promoted, and that I'm actually running on a different server. Is something
likethat possible ? 
>
> Thanks for any replies !

If you're a developer, you can take a look at:
https://www.postgresql.org/docs/current/libpq.html

If your application uses libpq, then you can have it connect to the primary database, no matter on what host the
primaryand the replica currently is. 

Look at: https://jdbc.postgresql.org/documentation/head/connect.html

You can especially do something like:

jdbc:postgresql://host-a:5432,host-b:5433/postgres?targetServerType=master

You can always download the current driver from:
https://jdbc.postgresql.org

Does that help?

Cheers,
Paul

On 8/11/20 2:57 PM, David Gauthier 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.
> 
> On Tue, Aug 11, 2020 at 11:46 AM Paul Förster <paul.foerster@gmail.com 
> <mailto:paul.foerster@gmail.com>> wrote:
> 
>     Hi David,
> 
>      > On 11. Aug, 2020, at 17:12, David Gauthier
>     <davegauthierpg@gmail.com <mailto:davegauthierpg@gmail.com>> wrote:
>      >
>      > Hi:
>      >
>      > Our IT dept has created what they call a High Availability DB for
>     our PG DB (9.6.7 on linux).  If the primary fails, they promise to
>     promote the backup to be the new primary but leave it at that.  But
>     from the perspective of the app, I'm left with....
>      > 1) detecting an SQL error is a DB connectivity problem.
>      > 2) Attempt to reconnect.  If fails, try connecting to the backup
>     server (assume it's the new primary ?)
>      > 3) If that works, then test to see if it is indeed a primary (try
>     a write statement) ?
>      > 4) Somehow remember that the backup server is the one to connect
>     to as the primary for future connections.
>      > 5) When the original primary server is fixed and brought back
>     on-line, and they promote it to being the new primary, then I guess
>     my write statements will fail because I'm still connecting to the
>     backup.  So go figure that out and reset a pointer of sorts to the
>     new primary ?
>      >
>      > Seems like a lot of work for an app and that it should be more
>     seamless.  But maybe I'm wrong.
>      >
>      > I was hoping for something like a server alias that the IT team
>     toggles as needed between the servers so that I always know what to
>     connect to.  The best solution would be something completely
>     seamless... my app isn't even aware that the primary went down, the
>     backup was promoted, and that I'm actually running on a different
>     server. Is something like that possible ?
>      >
>      > Thanks for any replies !
> 
>     If you're a developer, you can take a look at:
>     https://www.postgresql.org/docs/current/libpq.html
> 
>     If your application uses libpq, then you can have it connect to the
>     primary database, no matter on what host the primary and the replica
>     currently is.
> 
>     Look at: https://jdbc.postgresql.org/documentation/head/connect.html
> 
>     You can especially do something like:
> 
>     jdbc:postgresql://host-a:5432,host-b:5433/postgres?targetServerType=master
> 
>     You can always download the current driver from:
>     https://jdbc.postgresql.org
> 
>     Does that help?
> 
>     Cheers,
>     Paul
> 
If it's not seamless to the app, it isn't HA.

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