Re: [JDBC] Multiple databases? - Mailing list pgsql-jdbc

From Mikko Tiihonen
Subject Re: [JDBC] Multiple databases?
Date
Msg-id HE1PR0701MB2826CD166D19B2EBE1601DC7E66C0@HE1PR0701MB2826.eurprd07.prod.outlook.com
Whole thread Raw
In response to Re: [JDBC] Multiple databases?  (Turbo Fredriksson <turbo@bayour.com>)
List pgsql-jdbc

To iterate on the response Vladimir already gave: The documentation https://jdbc.postgresql.org/documentation/94/connect.html has under the "Connection Fail-over" section an example how to set up the fail-over and load balancing correctly:

--clip---

For example an application can create two connection pools. One data source is for writes, another for reads.


The write pool limits connections only to master node:

jdbc:postgresql://node1,node2,node3/accounting?targetServerType=master


And read pool balances connections between slaves nodes, but allows connections also to master if no slaves are available:

jdbc:postgresql://node1,node2,node3/accounting?targetServerType=preferSlave&loadBalanceHosts=true

--clip--


So you just make your application code select the correct pool to use and you should have load balancing for reads and fail-over for writes.

As Vladimir already explained the load balancing only occurs when opening new connections, so do set your pool to close idle connections.

And if you can choose which connection pool to use I would like to recommend the actively maintained https://github.com/brettwooldridge/HikariCP.


-Mikko

pgsql-jdbc by date:

Previous
From: Dave Cramer
Date:
Subject: Re: [JDBC] Multiple databases?
Next
From: Vladimir Sitnikov
Date:
Subject: Re: [JDBC] [HACKERS] pgjdbc logical replication client throwing exception