Re: Automatic Client Failover

On Fri, 2008-08-15 at 14:25 -0400, Bruce Momjian wrote:
> Simon Riggs wrote:
> > > > Implementation would be to make PQreset() try secondary connection if
> > > > the primary one fails to reset. Of course you can program this manually,
> > > > but the feature is that you wouldn't need to, nor would you need to
> > > > request changes to 27 different interfaces either.
> > >
> > > I assumed share/pg_service.conf would help in this regard; place the
> > > file on a central server and modify that so everyone connects to another
> > > server. Perhaps we could even add round-robin functionality to that.
> >
> > I do want to keep it as simple as possible, but we do need a way that
> > will work without reconfiguration at the time of danger. It needs to be
> > preconfigured and tested, then change controlled so we all know it
> > works.
>
> OK, so using share/pg_service.conf as an implementation example, how
> would this work? The application supplies multiple service names and
> libpq tries attaching to each one in the list until one works?

This could work in one of two ways (maybe more)
* supply a group for each service. If main service goes down, try other
services in your group
* supply a secondary service for each main service. If primary goes down
we look at secondary service

It might also be possible to daisy-chain the retries, so after trying
the secondary/others we move onto the secondary's secondary in much the
same way that a telephone hunt group works.

This was suggested as a complementary feature alongside other
server-side features I'm working on. I'm not thinking of doing this
myself, since I know much less about the client side code than I'd need
to do this in the time available. Also, I'm not sure whether it is
unpopular or simply misunderstood.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support