Re: Failover Datasource?

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