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

[Tatsuo Ishii]

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

You need to set the application name *before* connection is
established (more precisely, the app name should be set in the startup
packet). In order to accomplish that, probably you need to set the
PGAPPNAME environment variable when you start up the desktop
application.

If the desktop app accepts connection string, you can try something like:

postgresql://10.201.249.2:5432/postgres?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'

Those settings look correct.
> 
> 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)

Can you test followings:

PGAPPNAME=QGIS psql -U toni -h localhost -p 5432 -d postgres

From psql prompt, issue "select 1" or some other read only query and
make sure that they are routed to the primary.
If not, please let me know.

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

I think this is because of the reason above.

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