Обсуждение: Please implement simple failover scenario
Hi,
we are going to switch from Oracle to PostgreSQL and we would like to setup a simple hot-standby/warm-standby replication scenario.
Actually your JDBC driver does not have native support for failover to the slave host.
We are interestend in this scenario in order to implement the warm-standby case:
Case 1: WARM-STANDBY (slave is not accepting connections)
JDBC configuration: use URL1 on getConnection….and URL2 as failover
Step of “getConnection”:
1) Connect to URL1
2) Connection OK à proceed with URL1
3) Connection FAILED à proceed with URL2
Case 2: HOT-STANDBY (slave is accepting connections)
In order to implement the hot-standby we need to configure a query on getConnection in order to ask to the server if actually it is the actual “master”
1) Connect to URL1
2) Connection OK
a. Ask “Are you the master?”
b. Yes à proceed with URL1
c. No à proceed with URL2
3) Connection FAILED
a. proceed with URL2
I known that this implementation is really “skinny” and some more advanced features are needed for more complete setups but this is what we need
We can contribute with some patch
Enrico Olivelli |
Rimani aggiornato sul mondo dell’email marketing e del digital marketing: visita il nostro blog! http://blog.magnews.it
Enrico Olivelli - Diennea wrote: > we are going to switch from Oracle to PostgreSQL and we would like to setup a simple hot-standby/warm- > standby replication scenario. > > Actually your JDBC driver does not have native support for failover to the slave host. Why don't you implement that on a lower level with virtual IP addresses? You could configure failover so that a certain IP address always points to the current master. That way you only have to reestablish the connection when it fails. Yours, Laurenz Albe
Actually we are going to implement the virtual IP address setup but only for the first stages of the porting it is a more difficult have a virtual IP address setup in a large system with many servers it requires many components to be setup and monitored having simple failover mechanics at JDBC driver level will simplify many aspects of the problem we could use something like ha-jdbc but s too much complex for our needs (and its impact on performances for us is not justified) Enrico Olivelli Software Development Manager @Diennea Tel.: (+39) 0546 066100 - Int. 925 Viale G.Marconi 30/14 - 48018 Faenza (RA) MagNews - E-mail Marketing Solutions http://www.magnews.it Diennea - Digital Marketing Solutions http://www.diennea.com -----Messaggio originale----- Da: Albe Laurenz [mailto:laurenz.albe@wien.gv.at] Inviato: venerdì 19 settembre 2014 12:55 A: Enrico Olivelli - Diennea; pgsql-jdbc@postgresql.org Oggetto: RE: Please implement simple failover scenario Enrico Olivelli - Diennea wrote: > we are going to switch from Oracle to PostgreSQL and we would like to > setup a simple hot-standby/warm- standby replication scenario. > > Actually your JDBC driver does not have native support for failover to the slave host. Why don't you implement that on a lower level with virtual IP addresses? You could configure failover so that a certain IP address always points to the current master. That way you only have toreestablish the connection when it fails. Yours, Laurenz Albe Rimani aggiornato sul mondo dell’email marketing e del digital marketing: visita il nostro blog! http://blog.magnews.it
Actually we are going to implement the virtual IP address setup but only for the first stages of the porting
it is a more difficult have a virtual IP address setup in a large system with many servers
it requires many components to be setup and monitored
having simple failover mechanics at JDBC driver level will simplify many aspects of the problem
we could use something like ha-jdbc but s too much complex for our needs (and its impact on performances for us is not justified)
IMHO a JDBC driver shouldn't include the complexity of managing failover - there's plenty of existing solutions for that, including one that you mentioned.
Can you suggest some alternative to ha-jdbc ? I can’t find any other library which is currently under active development
thanks
Rimani aggiornato sul mondo dell’email marketing e del digital marketing: visita il nostro blog! http://blog.magnews.it
On Fri, Sep 19, 2014 at 12:39 PM, Enrico Olivelli - Diennea <enrico.olivelli@diennea.com> wrote: > > Can you suggest some alternative to ha-jdbc ? I can't find any other library which is currently under active development > > I don't know what your application and infrastructure architecture is but I'd look at the solutions provided by Postgresql natively such as Transaction Log Shipping [0] which could be provided by virtual IPs are previously suggested. If your application is web-based and lives in a Java app server, for HA I would expect you would have a hot standby. In this case you could use an intelligent load balancer to use the endpoint of the active server. Finally, I have used ha-jdbc in the past and it worked well for our needs in an active-active configuration. Also it looks like it is under active development [1] Hope this helps, Stephen [0] http://www.postgresql.org/docs/9.3/interactive/different-replication-solutions.html [1] https://github.com/ha-jdbc/ha-jdbc/commits/master