<html><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;" class="">Hi all,<div class=""><br class=""></div><div class="">my QGIS project is saved in postgres/postgis. The database instance exists of 2 databases in</div><div class="">replication mode (primary, hot standby). Where hot standby is readonly. Before them pgpool2</div><div class="">acts as a load balancer where <i class="">WRITE</i> only goes to primary and <i class="">SELECT</i> queries go to both.</div><div class=""><br class=""></div><div class="">My Problem now is, that QGIS uses </div><div class=""><br class=""></div><div class="">SELECT pg_is_in_recovery(); </div><div class=""><br class=""></div><div class="">to detect if the database is readonly and if so, editing will be locked. </div><div class="">Now whenever the connection is establish from the load balancer with slave (as it sees a SELECT) </div><div class="">QGIS thinks it cannot write, which is wrong in this situation.</div><div class=""><br class=""></div><div class="">After advice from a pgpool2 developer I would now force all requests coming from QGIS to primary</div><div class="">only (and allow other clients like dbeaver to use the load balancing feature.).</div><div class=""><br class=""></div><div class="">The way to do this on side of pgpool2 is to catch the application name:</div><div class=""><pre style="white-space: pre-wrap; font-variant-ligatures: normal; orphans: 2; widows: 2; text-decoration-thickness: initial;" class="">app_name_redirect_preference_list = 'QGIS:primary'
</pre></div><div class=""><br class=""></div><div class="">I can further already see that QGIS sets a var in postgresql logs.</div><div class=""><br class=""></div><div class=""><br class=""></div><div class=""><pre style="white-space: pre-wrap; font-variant-ligatures: normal; orphans: 2; widows: 2; text-decoration-thickness: initial;" class=""><span style="font-style: normal;" class="">Connection matched pg_hba.conf line 94: "host all all 10.201.249.4/32 scram-sha-256</span></pre><pre style="white-space: pre-wrap; font-variant-ligatures: normal; orphans: 2; widows: 2; text-decoration-thickness: initial;" class="">2022-06-30 09:54:40.056 CEST [822446] toni@spatial_db LOG: statement: SET application_name='QGIS'</pre></div><div class=""><br class=""></div><div class=""><br class=""></div><div class="">The problem here is, that this var is set <u class="">after</u> the connection is established,</div><div class="">where I need to set it in the „startup packet“.</div><div class=""><div class=""><br class=""></div><div class="">My question is, is there some way to tell QGIS to use a connection string like:</div></div><div class=""><pre style="white-space: pre-wrap; font-variant-ligatures: normal; orphans: 2; widows: 2; text-decoration-thickness: initial;" class=""><a href="postgresql://10.201.249.2:5432/spatial_db" class="">postgresql://10.201.249.2:5432/spatial_db</a><b class="">?application_name=QGIS</b></pre><div class=""><br class=""></div><div class="">Or by use of some environment variable?</div></div><div class=""><br class=""></div><div class=""><br class=""></div><div class="">Thanks a lot for any help,</div><div class=""><br class=""></div><div class="">Toni</div><div class=""><br class=""></div><div class=""><br class=""></div></body></html>