<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-2">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
span.StylE-mailovZprvy17
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 70.85pt 70.85pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="CS" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
<p class="MsoNormal"><span lang="EN-US">Hello,<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">We are running a pgpool/postgres cluster with 2 pgpool nodes and 2 postgres nodes in streaming replication setup with load-balancing enabled. Recently we have discovered a problem with certain queries from our Java application,
which is caused by the replication being asynchronous (we cannot really use the synchronous replication due to occasional problems with the underlaying infrastructure) – jobs of the application then fail due to finding different data on the STANDBY node than
what is expected by querying them on the PRIMARY node. We are now trying to find the way how to limit SELECT queries from this specific application to the PRIMARY node only without turning off the load-balancing completely, so it can still be used by other
applications, which don’t have a problem with the asynchronous replication. <o:p>
</o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">We have set the “ApplicationName” parameter in the URL of the data source for our application:<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">jdbc:postgresql://aisgpgpvip.cca.cz:9999/aisgdvyv?ApplicationName=AisgDozorovaDS<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">… and then used it in the pgpool.conf like so:<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">app_name_redirect_preference_list = 'AisgDozorovaDS:primary'<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">After reloading the pgpool service and restarting the application, it does not seem to work – read queries are still load-balanced between both nodes. When I tried to perform some queries from PSQL that is using a PGAPPNAME
parameter set to “AisgDozorovaDS” it seems to correctly prevent the session to load-balance itself to the STANDBY node. I am using the pgaudit extension to track the queries for a specific user (application is being ran under that user) and I noticed the following:<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">When I issue some query from PSQL it logs on postgres server (in SYSLOG) like this:<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Aug 25 08:53:29 AISGDBD01 postgres[14782]: [8] 2021-08-25 08:53:29.020 CEST ::: APP - AisgDozorovaDS :A: DB - aisgdvyv :D: USER - aisg :U: HOST - 172.20.15.64 :H: CMD - SELECT :C: |LOG: AUDIT: SESSION,1,1,READ,SELECT,,,SELECT
count(*) FROM databasechangelog;,<not logged><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">You can notice the application name here being used (“AisgDozorovaDS”) and also the type of the command being “SELECT”.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">When the application issues its own query it looks like this:<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Aug 25 09:28:58 AISGDBD02 postgres[14369]: [459] 2021-08-25 09:28:58.583 CEST ::: APP - AisgDozorovaDS :A: DB - aisgdvyv :D: USER - aisg :U: HOST - 172.20.15.64 :H: CMD - BIND :C: |LOG: AUDIT: SESSION,452,1,READ,SELECT,,,SELECT
* FROM quartz.qrtz_SCHEDULER_STATE WHERE SCHED_NAME = 'AISG-QUARTZ',<not logged><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">The application name is the same, but the type of the command is “BIND”.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Do you think this might be reason for the app_name_redirect_preference_list parameter working for the PSQL (and also for pgAdmin for example), but not the Java application? What can we test to verify that?<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">I have also considered it might be because the JDBC driver doesn’t set the application name from the get go (in the first packet of the session), but rather after a login, so the pgpool cannot utilize it, but according
to your documentation it was problem for JDBC version 9.3 or older, while we are using the version 42.2.5 for our application.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">I attached our current pgpool.conf to this message, in it you can also see that I have been trying the black_query_pattern_list parameter to catch some specific queries that our application uses, but I would prefer to
use it as a second option, since it does not have to cover 100% of the application activity and can also affect other applications that might use some similar query. Thank you for any help.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">With best regards,<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Vladimír Láznička <o:p></o:p></span></p>
</div>
</body>
</html>