Re: Multiple databases?

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