Обсуждение: [JDBC] Multiple databases?
I’m the system administrator of our environment in AWS. We’re running a Scala app on there, which accesses a RDS (PostgreSQL) cluster in AWS. This cluster consists of one master (read/write) and several slaves/replicas (read/only). All these have a different endpoint to access them. If the master dies, AWS will promote one of the replicas as master automatically (if I understand the documentation correctly), but I’m not sure if it will also update the endpoint. I’m fairly certain it doesn’t. Meaning, the app can no longer access the DB! There’s two solutions to this: 1) Add all endpoints, after each other, in the config. 2) Add a DNS round-robin entry with all endpoints. For point one, will the driver only try subsequent endpoints/DBs IF the first fails? As in, no load balancing :(. For point two, my question is (and my developer don’t know and don’t have time to dig into this - he have googled, but have not found a determinate answer) is if the pgsql-jdbc driver can recognise which of these are the master automatically. As in, does it “know” which one it can write to?
On 14 September 2017 at 08:58, Turbo Fredriksson <turbo@bayour.com> wrote:
I’m the system administrator of our environment in AWS. We’re running
a Scala app on there, which accesses a RDS (PostgreSQL) cluster in
AWS.
This cluster consists of one master (read/write) and several slaves/replicas
(read/only).
All these have a different endpoint to access them. If the master dies,
AWS will promote one of the replicas as master automatically (if I understand
the documentation correctly), but I’m not sure if it will also update the
endpoint. I’m fairly certain it doesn’t.
I thought RDS kept the endpoint the same when one of the replica's dies?
Meaning, the app can no longer access the DB!
There’s two solutions to this:
1) Add all endpoints, after each other, in the config.
2) Add a DNS round-robin entry with all endpoints.
For point one, will the driver only try subsequent endpoints/DBs IF the
first fails? As in, no load balancing :(.
The driver doesn't do load balancing.
For point two, my question is (and my developer don’t know and don’t have
time to dig into this - he have googled, but have not found a determinate
answer) is if the pgsql-jdbc driver can recognise which of these are the master
automatically. As in, does it “know” which one it can write to?
Yes, it does know when it connects but if that situation changes it will not be able to figure it out
Turbo>All these have a different endpoint to access them. If the master dies,
AWS will promote one of the replicas as master automatically
AWS will promote one of the replicas as master automatically
Turbo>Meaning, the app can no longer access the DB!
Is application using some sort of a connection pool?
In case connection pool is used (e.g. HikariCP), the sequence would be:
1) pgjdbc is configured with a full set of connection URLs, targetServerType=master (default value is "any")
2) Application asks for a connection, HikariCP creates one, pgjdbc connects, picks a master host and stays with it forever
3) Failure happens. The connection gets broken in the background
4) Application asks a connection, HikariCP performs connection validation, it identifies the failure and recreates the connection
5) At this point pgjdbc reevaluates the list of current servers, identifies current master and connects to it.
pgjdbc caches the state of master/slave for 10 seconds by default (it is configured via hostRecheckSeconds)
This enables failover.
Of course the queries that were in-flight would fail, however the application should be able to continue.
The key point is to use a connection pool + connection validation.
Dave>The driver doesn't do load balancing
Typically you don't see much masters at the same time, however there can be multiple slaves.
In case targetServerType=slave or preferSlave, pgjdbc can load-balance across slaves in case loadBalanceHosts=true (it is false by default)
Vladimir
On 15 Sep 2017, at 02:34, Dave Cramer <pg@fastcrypt.com> wrote: > I thought RDS kept the endpoint the same when one of the replica's dies? I’m not sure and I don’t know how to test this. I can’t just stop the master. AWS refuses to if/when there’s replicas to it. I can “delete” it, but that won’t do anything to any of the replicas that I have seen. No promotion and no changing of endpoint address. And from what I remember, the only thing the documentation say about a crashed master is that “AWS will automatically promote one of the replicas to new master”. It doesn’t say if the endpoint changes with it. But considering that the DB instance name is part of the endpoint address, I doubt it WILL change! > The driver doesn't do load balancing. Ok, thanx. So how does that work if I add multiple DB endpoints? It will only use the first one, in a read/write capacity? What if that one stops responding, will the driver then try the next endpoint? Will the driver be “smart” enough to recognise that this is a read/only DB and not do writes? Or will it try again with the next in line (third one)? > Yes, it does know when it connects but if that situation changes it will not be able to figure it out Ok, so as long as one uses non-persistent DB connection and open up a connection every time it’s needed, this should “hopefully” work?
On 15 Sep 2017, at 07:55, Vladimir Sitnikov <sitnikov.vladimir@gmail.com> wrote: > Turbo>Meaning, the app can no longer access the DB! > > Is application using some sort of a connection pool? I think so. I can remember the developer talking about connection pools... But I will verify and give him your points below, thanx!
Turbo,
Apparently when RDS fails over the endpoint name does not change but the IP will as they use DNS for the failover.
I can confirm this with them and get back to you.
On 15 September 2017 at 02:31, Turbo Fredriksson <turbo@bayour.com> wrote:
On 15 Sep 2017, at 07:55, Vladimir Sitnikov <sitnikov.vladimir@gmail.com> wrote:
> Turbo>Meaning, the app can no longer access the DB!
>
> Is application using some sort of a connection pool?
I think so. I can remember the developer talking about connection pools...
But I will verify and give him your points below, thanx!
On 15 Sep 2017, at 07:55, Vladimir Sitnikov <sitnikov.vladimir@gmail.com> wrote: > 1) pgjdbc is configured with a full set of connection URLs, targetServerType=master (default value is "any”) Ok, so it uses a connection pool (c3p0) and he managed to figure out this config line on his machine (that only have a ONE local PGSQL): jdbc:postgresql://localhost,server2,server3/fc?targetServerType=master It connected just fine to his local server AND tried server2 and server3 if he shut that down. HOWEVER, that doesn’t solve my problem with load balancing :(. So my my question is still, if I give it jdbc:postgresql://round-robin/fc?targetServerType=master where ‘round-robin’ is a DNS round-robin entry for ALL the servers, master AND all the replicas, will this still work?
On 15 Sep 2017, at 12:06, Dave Cramer <pg@fastcrypt.com> wrote: > Apparently when RDS fails over the endpoint name does not change but the IP will as they use DNS for the failover. Why would the IP change? You mean the IP to the endpoint of the master changes to that of the new master? > I can confirm this with them and get back to you. Please do, that would be much helpful, thanx!
On 15 September 2017 at 04:13, Turbo Fredriksson <turbo@bayour.com> wrote:
On 15 Sep 2017, at 12:06, Dave Cramer <pg@fastcrypt.com> wrote:
> Apparently when RDS fails over the endpoint name does not change but the IP will as they use DNS for the failover.
Why would the IP change? You mean the IP to the endpoint of the master
changes to that of the new master?
Yes
> I can confirm this with them and get back to you.
Please do, that would be much helpful, thanx!
Sent an email to my contact at RDS
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