[pgpool-general: 7672] Load-balancing issue with app_name_redirect_preference_list

Láznička Vladimír Vladimir.Laznicka at cca.cz
Wed Aug 25 17:04:57 JST 2021


Hello,

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.

We have set the "ApplicationName" parameter in the URL of the data source for our application:

jdbc:postgresql://aisgpgpvip.cca.cz:9999/aisgdvyv?ApplicationName=AisgDozorovaDS

... and then used it in the pgpool.conf like so:

app_name_redirect_preference_list = 'AisgDozorovaDS:primary'

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:

When I issue some query from PSQL it logs on postgres server (in SYSLOG) like this:

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>

You can notice the application name here being used ("AisgDozorovaDS") and also the type of the command being "SELECT".

When the application issues its own query it looks like this:

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>

The application name is the same, but the type of the command is "BIND".

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?

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.

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.

With best regards,
Vladimír Láznička
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20210825/cd40d9f5/attachment-0001.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: pgpool.conf
Type: application/octet-stream
Size: 44817 bytes
Desc: pgpool.conf
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20210825/cd40d9f5/attachment-0001.obj>


More information about the pgpool-general mailing list