I'm running PHP web app that uses PDO to connect to postgres (https://github.com/fusionpbx/fusionpbx/blob/bc1e163c898ea2e410787f8e938ccbead172aa5a/resources/classes/database.php#L202).
I'm running a failover cluster and so basically I just put 2 hosts names and my connection string looks like this:
"pgsql:host=host1,host2 port=5432 dbname=fusionpbx user=fusionpbx password=password target_session_attrs=read-write"
This works ok, if host1 is standby, host2 is selected with very little delay. The only issue is the host1 is unreachable or down. In this case PDO/driver (?) always tries host1 first, waits 30s until it timeouts and goes to host2. It seems that the fact that host1 is not available is not being remembered. I found 3 workarounds:
- add PDO::ATTR_TIMEOUT=2 when creating PDO. Yes, stupid I know, but allows at least temporary workaround, in case of failure, until I figure out the right solution.
- externally monitor postgres and change nodes order in the connection string, putting active node always first. I'm starting to think it's least evasive.
- PDO::ATTR_PERSISTENT => true - I've tested this and on the first look it works quite nicely, but given that I'm not really PHP guy, and the application is not mine but 3rd party application, I'm reluctant to make such impactful change.
Maybe someone could share their experience? I'm quite surprised how little can be found about this over the net. Also, on same box I'm running lua scripts, also connecting to same postgres in the same way, and it seem to have no problem in handling this scenario. It's the same version of libpq since it's the same linux box and I'm not adding anything specific to the connection string.