Thread: Can I tell libpq to connect to the primary?

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

On 22.04.20 21:10, Christian Ramseyer wrote:
> 
> 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?
> 

Never mind, after RTFM'ing to the very end of
https://www.postgresql.org/docs/current/libpq-connect.html I have
discovered target_session_attrs=read-write|any which seems to do exactly
what I want.

Cheers
Christian

On Thu, Apr 23, 2020 at 08:54:35AM +0200, Christian Ramseyer wrote:
> Never mind, after RTFM'ing to the very end of
> https://www.postgresql.org/docs/current/libpq-connect.html I have
> discovered target_session_attrs=read-write|any which seems to do exactly
> what I want.

Yes, as long as you don't have as requirement to make sure to connect
to a standby session this would not be a problem.  Supporting a
read-only mode for target_session_attrs is a problem we have been
stuck on for some time because of the potential issues you could face
when attempting to connect a cluster that has just been freshly
promoted: when connecting to the server libpq may see the connection
as read-only but if the standby gets promoted that could become
incorrect.
--
Michael