Maintaining a connection to Postgresql during database failover

[Dean Schulze]

I have a web service in Gin that calls Postgresql running on AWS RDS with multi-az failover.  When I reboot with failover the primary database and failover starts calls to the web service hang without even getting to the event handler.  Once the failover is complete calls to the web service fail due to not being able to find the Postgresql table.  If I restart the web service then calls to the web service succeed.

I've done this with both sql/db and the github.com/jackc/pgx/v5/pgxpool connection pool.  I hoped the pgxpool would be resilient enough to reconnect once multi-az failover had completed, but this isn't happening.

I could implement retry logic which would probably require a new connection, but the calls that hang don't even get to the event handler.

Is there a pattern for supporting continuing connection or reconnect to Postgresql when failover happens?  Are there drivers or packages that implement this?

[Tamás Gulácsi]

This is just from reading the docs:

https://pkg.go.dev/github.com/jackc/pgx/v...@v5.5.1/pgconn#FallbackConfig
is a member of https://pkg.go.dev/github.com/jackc/pgx/v...@v5.5.1/pgconn#Config

and its comment seems to match your use case.

I don;t know AWS RDS how does the failover (when is the connection closed),

but the "table not found" error suggest you have to set the Config.AfterConnect parameter, too (see its comment).