In an HA environment commands are misdirected during switchover

[d-maumary]

We want to build a HA setup, using PG-Autofailover as the monitor & and employ multi-host connection strings to connect to the primary (writable) database from the client. Now we find that during failover the sql commands get directed to the wrong server. connstr="postgres://localhost:5433,localhost:5434/postgres?target_session_attrs=read-write&sslmode=require" `It appears that during fail-over the command show transaction_read_only returns nothing for a while. According to the docs the "libq" process looks for a result of "on" to detect & ignore/close the read-only connection. So when it gets an empty reply it mistakes it for the writable node. The result depends on whether the primary is the first or second entry in the connection string. Any suggestions for a work-around? Script to show problem: show_fo_demo.sh.gz Output: show_fo_demo.log.gz

[d-maumary]

Output including postgres error messages: show_fo_demo.msg.log.gz

[s4ke]

Which version of libpq are you using? I recently did the same with our django app and needed to upgrade libpq for the HA behaviour to work properly.

[d-maumary]

Hi Martin, thanks for your reply. I'm getting the effect when using psql (I have attached the script to the issue). The version of psql is: $ psql --version psql (PostgreSQL) 13.1 (Ubuntu 13.1-1.pgdg20.04+1)

$ apt list|grep libpq|grep installed libpq-dev/focal-pgdg,now 13.1-1.pgdg20.04+1 amd64 [installed,automatic] libpq5/pgadmin4,focal-pgdg,now 13.1-1.pgdg20.04+1 amd64 [installed,automatic]

I would have assumed this to use the latest version of libpq.

With best regards, --daniel maumary DBA SBI Security Solutions

On Wed, Feb 3, 2021 at 5:17 AM Martin Braun [email protected] wrote:

Which version of libpq are you using? I recently did the same with our django app and needed to upgrade libpq for the HA behaviour to work properly.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/citusdata/pg_auto_failover/issues/575#issuecomment-771950441, or unsubscribe https://github.com/notifications/unsubscribe-auth/AQ3HK2ALUS6DXNJKAWUSKI3S5BMWDANCNFSM4WYCME4A .