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 and not to change due to role change. To simply, 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 app 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 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.
Any guidance any appreciated.
Thanks
I tested the connection string multiple times, checked the various posts but unfortunately didnt succeed to find the right answer