[pgpool-general: 8273] Problems with QGIS, pg_is_in_recovery() and Load Balancing

Toni Schönbuchner toni.schoenbuchner at csgis.de
Thu Jun 30 15:26:14 JST 2022


Hi there,

I’m running two Postgresql server in primary - hot standby replication behind pgpool2.
Pgpool2 is acting nicely as a load balancer, and database clients like dbeaver or pgadmin
work without problems.

However, on a spatial database, Users connect with a Desktop software called QGIS.
The software initially loads „layers“ (spatial information) that should be editable.
The problem is, QGIS uses 

SELECT pg_is_in_recovery(); 

to check if the database is readonly, and if true, it denies editing. (Forms are greyed out)
As this is a SELECT, the query of course arrives from time to time at the readonly slave server,
which is answering with true and blocking the GUI.

(This is useless as if a write request would arrive pgpool2 would route it correctly to the primary server).
Unfortunately it looks I cannot stop this behavior on side of QGIS. 

As a test I’ve set pg_is_in_recovery() on pgpools blacklist, which always routes those request
to master. Of course this is more than ugly, but it shows where the problem comes from.

Is there something else I can do? For example the application sets
`SET application_name='QGIS‘` after connection. Maybe I could use this to always route QGIS
to master but keep load balancing for other clients?

Or is there some way to use a regex on users and decide where they should arrive?
(F.e. all *._write should always use master).

I would guess that people have had a similar problem with read/write Clients and HA Clusters
and can share their solution.

Best regards + thanks,

Toni



More information about the pgpool-general mailing list