Hi
I'm setting up a new environment with a primary/hot standby replication
pair.
For read-only clients, I have found the host=host1,host2 connection
string[1] which allows a connection when any one of the servers is up.
However I'm unsure how to achieve something similar for read/write
clients. If I'm providing this list, the client will connect to the
first host, which might have become the standby in the meantime.
I see that in pgjdbc there are additional options for targetServerType =
any, primary, secondary, preferSlave and preferSecondary[2]. However
this seems to be java-specific and not implemented in libpq? Is there a
way to get this behaviour in PHP/Psycopg/Perl DBI?
If not, what is the best alternative to achieve this? My primary &
standby are not in the same L3 network, so moving around and IP address
with Linux OS clustering is not an option. I'm tending to scripting the
REST API of our DNS service to point a CNAME at the new primary during
promotion, but maybe there is an easier way I haven't found yet? Maybe
something in pgpool/pgbouncer etc?
Cheers
Christian
[1] https://www.postgresql.org/docs/current/libpq-connect.html
[2] https://jdbc.postgresql.org/documentation/head/connect.html