postgres pgpool holds on to the previously active primary server connection

[zipakgow]

Questions

we have F5 VIP for our postgres db, with gtm_monitor set which in realtime points to either the primary or secondary or DR server based on whichever is active.
using the PgPool seems to hold on to the connections to the previously active server and does not discard it.
postgres seems to be specifically returning an error code: 25006
exception message returned to app server (when connections from previous active servers are used): cannot execute INSERT in a read-only transaction
error code: 25006

can there be a method to discard connections from the pool when such error occurs? or this be handled internally?

Version

Which version(s) did you encounter this bug ?
vertx-pg-client: 3.9.7

Do you have a reproducer?

No

Steps to reproduce

Note: application connects to F5 VIP created for postgres

1. primary server is ACTIVE, secondary server is on STANDBY
2. APP starts sending requests to db with connection pool size atleast 1
3. EFM is used on postgres to do the switch over for DB maintainence. This sets secondary server as ACTIVE and primary server to STANDBY.
4. App continues to send requests, and all the requests fail with error code: 25006 and message: cannot execute INSERT in a read-only transaction

[vietj]

can you determine why Postgres returns 25006 ?

[zipakgow]

It’s returning 25006 as primary is now only in read mode. connection pool isn’t purging old connections (created for primary server), even though at the db secondary is now set as Active.

Primary server: now is in standby (read only mode)
Secondary server: is the new active server (read write mode)

25006 ->
https://www.postgresql.org/docs/11/errcodes-appendix.html

[vietj]

I'm assuming that creating a new socket connection to the same socket address will resolve to the new server ?

[zipakgow]

yep, thats right.

[vietj]

do you know if there is way to reproduce it with test container ?

[vietj]

do you also know if other traditional solutions (ie jdbc + pool) do something specific for this ?

[vietj]

do you also know if in such case when a database become a primary then Postgres will emit a notification that could be used by the client to evict the connection from the pool before failing ?

[zipakgow]

do you know if there is way to reproduce it with test container ?

we can, but it would need a VIP (F5- it has option to monitor for active server) to be setup

do you also know if other traditional solutions (ie jdbc + pool) do something specific for this ?

we are very new to this setup, so i posted the question on postgres slack channel, with limited help.

do you also know if in such case when a database become a primary then Postgres will emit a notification that could be used by the client to evict the connection from the pool before failing ?

per the EDM EFM switchover documentation, the primary server does restart during this switchover,. but no other notification is sent when it happens.

[vietj]

do you know whether we could fake this somehow ? e.g pass the database in read only mode with a specific command, etc...

[vietj]

it seems possible (https://dba.stackexchange.com/questions/271685/postgresql-9-3-set-database-or-all-to-read-only-mode), I'm thinking we could use this to check we get an error and then that the connection is removed from the pool.

[zipakgow]

sure, as a workaround for now, we are trying to purge the pool and re-initialize it.