Thread

HA PostgreSQL

Charles H. Woloszynski <chw@clearmetrix.com> — 2002-11-01T18:26:57Z

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.  

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.

Any and all feedback and comments are greatly appreciated.  And, as 
always, thanks in advance,

Charlie

-- 


Charles H. Woloszynski

ClearMetrix, Inc.
115 Research Drive
Bethlehem, PA 18015

tel: 610-419-2210 x400
fax: 240-371-3256
web: www.clearmetrix.com

Re: HA PostgreSQL

Robert Treat <xzilla@users.sourceforge.net> — 2002-11-01T22:12:50Z

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