[Qgis-user] Add application_name=QGIS to postgis connection string

[Toni Schönbuchner]

Hi all,

my QGIS project is saved in postgres/postgis. The database instance exists of 2 databases in
replication mode (primary, hot standby). Where hot standby is readonly. Before them pgpool2
acts as a load balancer where WRITE only goes to primary and SELECT queries go to both.

My Problem now is, that QGIS uses 

SELECT pg_is_in_recovery(); 

to detect if the database is readonly and if so, editing will be locked. 
Now whenever the connection is establish from the load balancer with slave (as it sees a SELECT) 
QGIS thinks it cannot write, which is wrong in this situation.

After advice from a pgpool2 developer I would now force all requests coming from QGIS to primary
only (and allow other clients like dbeaver to use the load balancing feature.).

The way to do this on side of pgpool2 is to catch the application name:
app_name_redirect_preference_list = 'QGIS:primary'

I can further already see that QGIS sets a var in postgresql logs.


Connection matched pg_hba.conf line 94: "host	all		all		10.201.249.4/32		scram-sha-256
2022-06-30 09:54:40.056 CEST [822446] toni at spatial_db LOG:  statement: SET application_name='QGIS'


The problem here is, that this var is set after the connection is established,
where I need to set it in the „startup packet“.

My question is, is there some way to tell QGIS to use a connection string like:
postgresql://10.201.249.2:5432/spatial_db?application_name=QGIS

Or by use of some environment variable?


Thanks a lot for any help,

Toni


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20220630/890adf85/attachment.htm>