Re: Failover Datasource?

Both pgBouncer and pgPool add another process between the client and the
server.

Their primary goal appears to be connection pooling. My Java application
server (Apache Tomcat) is already pooling database connections.

Adding another chunk of middle-ware adds another thing I have to worry
about failing and is likely to slow things down.

If I write code to add failover support into the JDBC driver, what are
my chances of getting that patch into the next release?

- Bruce

On 06/16/2011 03:56 PM, Thomas Kellerer wrote:
> Bruce Adams wrote on 16.06.2011 21:33:
>> I, too, would like to be able to specify multiple Postgres servers in
>> a JDBC connection URL. I want the client application to prefer
>> connecting to a master database, but automatically failover to a
>> replica when the master is unavailable.
>>
>> Many other databases have this feature in their JDBC drivers. For
>> example a MySQL JDBC URL can have a comma separated list of host:port
>> in the URL, like this:
>> jdbc:mysql://master:3306,slave:3306/databasename
>>
>> Is there some other way to setup client failover?
>>
>> Writings I've found for Postgres always talk about a proxy, or even
>> multiple proxies (!), between the Java client and the real database
>> servers. I'm trying to build a robust system; any additional layer is
>> yet another thing that can break. I don't mind having the application
>> see errors in a failover, I just want some measure of recovery to be
>> automatic, even if the automatic recovery is to a read-only replica.
>>
>> I plan to use streaming replication in PostgreSQL 9.0 for the hot
>> backup(s).
>
> pgBouncer or pgPool can both do that as far as I know
>
> http://wiki.postgresql.org/wiki/PgBouncer
> http://pgpool.projects.postgresql.org/
>
> Thomas