Subject: Re: HA PostgreSQL
From: Robert Treat <xzilla@users.sourceforge.net>
To: "Charles H. Woloszynski" <chw@clearmetrix.com>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <3DC2C771.7030106@clearmetrix.com>
References: <3DC2C771.7030106@clearmetrix.com>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Date: 01 Nov 2002 17:12:50 -0500
Message-Id: <1036188770.20462.44.camel@camel>
Mime-Version: 1.0

On Fri, 2002-11-01 at 13:26, Charles H. Woloszynski wrote:
> I am trying to develop a plan for a high-availability (HA) 
> implementation of a database using PostgreSQL.  One wrinkle; the data we 
> receive is performance data, and occassional loss of some measurements 
> is Ok for us.  [I know, this is not in the main stream of database users 
> :-)].
> 
> I have looked ar rserv and pg-replicator, and they seem to be targeted 
> at replication without specific HA support.  Replication is great for 
> lots of things; but I need HA more than ACID replication.  
> 
> I have seen a proposed solution that uses *rsync* on the database files 
> between machines and linux-ha to roll over the network access to the 
> available machine.  My question is pretty simple; can something as 
> *simple* as rsync make a full copy of the database consistently between 
> the machines?  That seems just too easy.  
> 

Well, some folks have argued that it is likely to cause extreme data
corruption, my understanding being that if even one file is off your
going to end up in trouble.  Granted, I don't know that this has ever
been proven one way or the other, but I buy the argument enough not to
rely on it. 

> If I replace the process with something that uses pg_dump and rsync that 
> file (ok, now that seems more likely to generate a consistent database 
> image) that and restore that into the slave, does this work?  Obviously, 
> this approach is kinda a hammer approach; the poor active server will be 
> dumping till the cows come home.

it does seem more likely to give you a consistent db image, but
depending on the size of your database your going to have some delay
between snapshots depending how long it takes to dump the database. On a
small database this might be acceptable, on a large enough database it
will be unworkable.  I suppose you could run concurrent pg_dump's, but
I'd hate to think what that might do to the poor db server.  Don't
forget you'll have an issue actually loading the data onto the secondary
sever as well. Personally I think you'd be better off setting up some
type of master/slave replication and devise some networking fail over
scripts to switch hosts if need be.
> 
> Any and all feedback and comments are greatly appreciated.  And, as 
> always, thanks in advance,
> 

If you haven't looked at dbbalancer yet, you might want to. Someone
mentioned it just a few days ago in a very similar thread on the general
list (iirc)

Robert Treat