PostgreSQL in Shared Disk Failover mode on FreeBSD+CARP+RAIDZ - Mailing list pgsql-admin

From snoop@email.it
Subject PostgreSQL in Shared Disk Failover mode on FreeBSD+CARP+RAIDZ
Date
Msg-id 3702fd882cd904ce76ccd7c09d7645d9@213.152.156.147
Whole thread Raw
Responses Re: PostgreSQL in Shared Disk Failover mode on FreeBSD+CARP+RAIDZ  (Scott Marlowe <scott.marlowe@gmail.com>)
Re: PostgreSQL in Shared Disk Failover mode on FreeBSD+CARP+RAIDZ  (Achilleas Mantzios <achill@matrix.gatewaynet.com>)
List pgsql-admin
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
- 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)

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.

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. 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.

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
CARP now obviously redirects any new connection to the DB to server A again
client B connects to server A
what about the existing connection of the client A to the server B? there's
an existing connection state between client A and server B
now there's the chance that a transaction can be committed on the server B
while there's someone else operating on server A too!

I understand that in a server that doesn't commit many transaction but is
mainly answering queries this could be a remote situation but it can
probably happen.
Please correct me if I'm wrong (as I really hope to be).

Did anyone here try such a configuration by any chance?

Many thanks in advance for your time.
 --
 Caselle da 1GB, trasmetti allegati fino a 3GB e in piu' IMAP, POP3 e SMTP
autenticato? GRATIS solo con Email.it: http://www.email.it/f

 Sponsor:
 Paghe e stipendi, consulenza e collocamento, tutto con Emailpaghe! Provalo
gratuitamente fino al 31/12/2010
 Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=10682&d=20101221



pgsql-admin by date:

Previous
From: Scott Marlowe
Date:
Subject: Re: Data duplication when moving datafiles from one server to another.
Next
From: Scott Marlowe
Date:
Subject: Re: PostgreSQL in Shared Disk Failover mode on FreeBSD+CARP+RAIDZ