[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