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

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

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

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.foerster@gmail.com>
wrote: