[Pgpool-general] pgpool redundancy?

[Jeff Davis]

On Wed, 2007-05-30 at 13:46 -0600, Kevin Kempter wrote:
> Hi List;
> 
> It seems that in a replication scenario pgpool becomes the single point of 
> failure, i.e. if we loose the pgpool box were dead. Any thoughts, 
> suggestions, best practices, etc per creating redundancy in the pgpool layer?
> 

PostgreSQL and pgpool use tcp/ip, so unless you can maintain tcp
connections seamlessly through hardware failure, there's no possible way
to _not_ have a single point of failure.

Maintaining every database connection in the event of major hardware
failure is a very challenging problem, and I'm not sure there are any
real relational systems that can do so. It's not very important anyway,
since the application can just reconnect and retry the transaction.

Pgpool is great because the actual _data_ is already replicated
synchronously, you just need to bring pgpool up and it starts working
again. You can easily have a spare pgpool server waiting around, and use
IP-based failover so that clients connect to the failover system when
they make a new connection. Then, just make sure that your applications
detect a bad database connection, reconnect (at which time they'll be
connected to the failover system), and retry the transaction.

Since the _data_ is all replicated synchronously, the application will
never know that a failover happened (except the fact that it needed to
reconnect).

There are a couple cases you have to be careful about if the pgpool
server suffers catastrophic failure. See the thread I just started
called "replication_mode safety" to see if you think those are real
problems or not.

Regards,
	Jeff Davis