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
