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

Tatsuo Ishii ishii at sraoss.co.jp
Thu Jun 30 15:48:07 JST 2022


Hi,

There's a parameter which does exactly what you want in pgpool.

> 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?

You can use:

app_name_redirect_preference_list = 'QGIS:primary'

in pgpool.conf.

This will redirect all queries from applications "QGIS" to primary PostgreSQL server.

You can test this using psql:

PGAPPNAME=QGIS psql -p 11000 test -c "show pool_nodes"

 node_id | hostname  | port  | status | pg_status | lb_weight |  role   | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change  
---------+-----------+-------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
 0       | localhost | 11002 | up     | up        | 0.500000  | primary | primary | 1          | true              | 0                 |                   |                        | 2022-06-30 15:35:04
 1       | localhost | 11003 | up     | up        | 0.500000  | standby | standby | 2          | false             | 0                 | streaming         | async                  | 2022-06-30 15:35:04
(2 rows)

As you can see, the "load_balance_node" column for node 0 (primary) is
"true", which means any read only queries will be redirected to the
primary server.

> 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

Hope this helps,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp


More information about the pgpool-general mailing list