Re: PostgreSQL in Shared Disk Failover mode on FreeBSD+CARP+RAIDZ

On Mon, Dec 20, 2010 at 6:23 PM, <snoop(at)email(dot)it> wrote:
> Hi everybody,
> I'm trying to figure out a way to setup a PostgreSQL HA cluster solution.
>
> For the record:
> - I've already tried pgpool-II 2.x in Synchronous Multi Master Replication
> mode and I was satisfied by it's functionality but concerned about having
> the same data growing on several nodes

What actual concerns did you have? Just a question of too many
spinning disks in your machines or something? There are some issues
with race conditions and such with statement level replication to be
aware of.

> - I've upgraded to pgpool-II 3.0.x on FreeBSD (from ports) but it's very
> buggy at the moment
> - I don't like the idea of having fixed size (16 megs regardless of the
> committed transaction number!) WAL logs often "shipped" from one node to
> another endangering my network performance (asynchronous replication)

Streaming replication in 9.0 doesn't really work that way, so you
could use that now with a hot standby ready to be failed over to as
needed.

> I've done some research and I've an idea of different possible solutions,
> but I'd honestly like to implement it using CARP in a "Shared Disk Failover"
> fashion.
> Unfortunately this doesn't really seem to be a common way according to the
> very limited information available on the net and that's why I'm going to
> ask here.

Yeah, there's a lot of things you have to be careful of to prevent
data corruption. If two postgresql instances mount and start on the
same data share, you will immediately have a corrupted data store and
have to restore from backup.

> My idea: two nodes (i386) with FreeBSD 8.1 and PostgreSQL 9.0.2, CARP
> providing network failover and a shared data dir on a RAIDZ solution.

Only one at a time. Ever. So you'll need fencing software / hardware
for your shared data drives.

> I'm
> pretty sure that CARP would do the job properly indirectly avoiding even the
> dangerous writing on the data dir from both nodes at the same time (that
> would apparently badly screw up the DB) by redirecting any network
> connection to the active DB and to him only.

You'll need more than carp.

> BUT ... I'm seriously concerned about the already active connections client
> <-> server during the failover.
> Example:
>
> client A connects to server A
> server A fails so does the client A connection
> CARP redirects any upcoming connection to the DB to server B now
> client A reconnects and is now operating on server B
> THEN
> server A comes back up

Stop. FULL STOP. If A goes down, you need to STONITH (Shoot the
other node in the head) so it cannot under any circumstances come back
up on accident. It's a good idea to have either or both fencing for
your network switch and a power switch you can use a signal to to
power down server A.

> Did anyone here try such a configuration by any chance?

There are people on the list using multiple machines pointing at
single storage arrays with fencing and STONITH technology. I'm not,
we just use slony and manual failover for our stuff.