Let's assume there is a PostgreSQL cluster consisting of a single master node and two read-only replicas via streaming replication.
How can I configure Tomcats DBCP to be aware that there are several different nodes within the db cluster, thus allocate mutating statements to a connection of the master node and respectively side-effect-free statements (load balanced) to a connection of any slave node?
As the documentation of PostgreSQL's JDBC driver states it is possible to declare multiple nodes within the JDBC connection URL:
jdbc:postgresql://host1:port1,host2:port2/database
So the idea came up to configure two connection pools in Tomcats server.xml. One JNDI resource for the read/write master datasource and another pool for read-only replicas: jdbc:postgresql://host1:port1/database?targetServerType=master and for the replicas jdbc:postgresql://host1:port1,host2:port2/database?targetServerType=preferSlave&loadBalanceHosts=true
In this case the application running on the Tomcat needs to be aware that there are two DB connection pools with different semantics and therefore needs to decide from which pool to request a connection based on each SQL statement. This seems to be a bad pattern since the app depends on the specific DB infrastructure and a JDBC pool should abstract the implementation of the underlying database.
Can a additional abstraction layer like pgpool-II between Tomcat DBCP and the PostgreSQL cluster help in this scenario? (Especially when in future the infrastructure grows to several Tomcat server instances or requirement of high availability and fail-over of the PostgreSQL DB rises)