libpq behavior with hostname with multiple addresses and target_session_attrs=primary - Mailing list pgsql-hackers

From Sameer M. Deshpande
Subject libpq behavior with hostname with multiple addresses and target_session_attrs=primary
Date
Msg-id 8B971EFB-E230-48AC-9C5D-C3A1ACA99109@palmarium-consultancy.com
Whole thread Raw
List pgsql-hackers
Hello,
I would like to make a feature request for existing implementation of connection object. What I would like to request you is as follows.

I have configured primary and hot standby PostgreSQL server. The replication works fine with repmanager. All I need to achieve is to keep the client connection string using a common hostname (also without specifying multiple hosts) and not to change due to role change. To simplify my need the details are as follows.

Primary host: host1.home.network, IP: 192.168.0.2
Standby host: host2.home.network, IP: 192.168.0.3 
Common host: myapphost.home.network: this resolves to IP: 192.168.0.2 and IP: 192.168.0.3

The connection string I want my applications to use is:

user@localnode:~> psql "host=myapphost.home.network dbname=appdb sslmode=require target_session_attrs=primary"

But when I test the connection multiple times, I have observe strange behaviour and I am not sure if that's the expectation. The client application uses the myapphost.home.network. By using "target_session_attrs=primary", my expectation is to connect always to primary node all the time, no matter which one it is due to manual role change, either host1 or host2.home.network

The reason of having two IPs to common hostname is, sometimes we manually perform the role switch using repmanager. And because of this, I do not want any changes to client connection and I expect attribute target_session_attrs will always hook client connection to primary node. But after testing few times, I get error:

connection to server at "myapphost.home.network" (192.168.0.3), port 5432 failed: server is in hot standby mode

Now here my expectation is, despite myapphost.home.network resolves to two different servers (primary and slave/standby), because of using attribute target_session_attrs=primary, why my session does not get redirected to only primary host? And of course load_balance_hosts default value is disable. Furthermore, this also gives me flexibility to add new ore remove hosts from DNS entry myapphost.home.network.

Based on above requirement, I would like to request you if it is possible to make an enhacenment to connection object when using target_session_attrs=primary, it will hook to always primary server and not to thrown an error saying next on in the list is server is in hot standby mode or read only etc…

Many thanks in advance,

Regards
Sameer Deshpande

pgsql-hackers by date:

Previous
From: Tomas Vondra
Date:
Subject: Re: BitmapHeapScan streaming read user and prelim refactoring
Next
From: "Amonson, Paul D"
Date:
Subject: RE: Popcount optimization using AVX512