<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Hi Toni,</p>
<p>It makes sense to have in all connections to the database. The
application_name is already used by QGIS Server. <br>
</p>
<p>To have it in QGIS desktop (from core), a few edits must be made
to make it happen. Maybe there are plug<br>
</p>
<p>Can you add this a new feature request?</p>
<p>Regards,</p>
<p>Jorge<br>
</p>
<div class="moz-cite-prefix">On 30/06/22 10:47, Toni Schönbuchner
via Qgis-user wrote:<br>
</div>
<blockquote type="cite"
cite="mid:58B1028B-E153-40F2-B348-0494770AE518@csgis.de">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
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="moz-txt-link-freetext" moz-do-not-send="true">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>
<br>
<fieldset class="moz-mime-attachment-header"></fieldset>
<pre class="moz-quote-pre" wrap="">_______________________________________________
Qgis-user mailing list
<a class="moz-txt-link-abbreviated" href="mailto:Qgis-user@lists.osgeo.org">Qgis-user@lists.osgeo.org</a>
List info: <a class="moz-txt-link-freetext" href="https://lists.osgeo.org/mailman/listinfo/qgis-user">https://lists.osgeo.org/mailman/listinfo/qgis-user</a>
Unsubscribe: <a class="moz-txt-link-freetext" href="https://lists.osgeo.org/mailman/listinfo/qgis-user">https://lists.osgeo.org/mailman/listinfo/qgis-user</a>
</pre>
</blockquote>
</body>
</html>