[pgpool-general: 8276] Re: Problems with QGIS, pg_is_in_recovery() and Load Balancing
Toni Schönbuchner
toni.schoenbuchner at csgis.de
Thu Jun 30 17:07:06 JST 2022
Hi Tatsuo,
Thank you so much for your fast reply!
This looks promising but unfortunately SELECTS are still receiving at Slave.
May I just repeat what I did to double check all is correct:
1) In postgresql logs on master and slave I can see that after a connection the application sets application_name QGIS as:
Connection matched pg_hba.conf line 94: "host all all 10.201.249.4/32 scram-sha-256"
2022-06-30 09:54:39.551 CEST [822446] toni at spatial_db LOG: statement: SELECT postgis_version()
2022-06-30 09:54:39.656 CEST [822446] toni at spatial_db LOG: statement: SELECT postgis_geos_version(), postgis_proj_version()
2022-06-30 09:54:40.056 CEST [822446] toni at spatial_db LOG: statement: SET application_name='QGIS'
…
On side of pgpool my configuration looks like:
app_name_redirect_preference_list = 'QGIS:primary'
load_balance_mode = on
master_slave_mode = on
master_slave_sub_mode = 'stream'
backend_hostname0 = '10.201.249.2'
backend_port0 = 5432
backend_weight0 = 4
backend_flag0 = 'DISALLOW_TO_FAILOVER'
backend_hostname1 = '10.201.249.3'
backend_port1 = 5432
backend_weight1 = 6
backend_flag1 = 'DISALLOW_TO_FAILOVER'
2) After a restart I do get your result on node check
sudo service pgpool2 restart
PGAPPNAME=QGIS psql -U toni -h localhost -p 5432 -d postgres -c ’show pool_nodes'
node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
---------+--------------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
0 | 10.201.249.2 | 5432 | up | 0.400000 | primary | 0 | true | 0 | | | 2022-06-30 09:50:01
1 | 10.201.249.3 | 5432 | up | 0.600000 | standby | 0 | false | 0 | | | 2022-06-30 09:50:01
(2 rows)
3) But still if I reconnect with the qgis desktop app, the GUI is blocked
and I can see requests to slave in logs:
2022-06-30 09:54:40.056 CEST [822446] toni at spatial_db LOG: statement: SET application_name='QGIS'
2022-06-30 09:54:40.166 CEST [822446] toni at spatial_db LOG: statement: SET extra_float_digits=3
2022-06-30 09:54:40.267 CEST [822446] toni at spatial_db LOG: statement: SELECT * FROM "rgk"."campaign" LIMIT 1
2022-06-30 09:54:40.371 CEST [822446] toni at spatial_db LOG: statement: SELECT pg_is_in_recovery()
2022-06-30 09:54:40.575 CEST [822446] toni at spatial_db LOG: statement: SELECT * FROM "rgk"."campaign" LIMIT 0
...
Is there something I have overseen?
Thank you so much for your help again,
– Toni
> Am 30.06.2022 um 08:48 schrieb Tatsuo Ishii <ishii at sraoss.co.jp>:
>
> 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 <http://www.sraoss.co.jp/index_en.php>
> Japanese:http://www.sraoss.co.jp <http://www.sraoss.co.jp/>
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net <mailto:pgpool-general at pgpool.net>
> http://www.pgpool.net/mailman/listinfo/pgpool-general <http://www.pgpool.net/mailman/listinfo/pgpool-general>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20220630/bf368670/attachment-0001.htm>
More information about the pgpool-general
mailing list