Thread: HA PostgreSQL

[I mistakenly posted this to pgsql-sql by mistake.  Sorry for the
duplicate to those on both lists]

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

On Fri, Nov 01, 2002 at 01:31:24PM -0500, Charles H. Woloszynski wrote:
>
> 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.

It is too easy.  This has come up more than once recently, and I've
never seen an argument that using rsync this way is no better than
using tar on a live data area.  If you want high availability, don't
do it.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

andrew@libertyrms.info (Andrew Sullivan) wrote:
> On Fri, Nov 01, 2002 at 01:31:24PM -0500, Charles H. Woloszynski wrote:
>>
>> 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.
>
> It is too easy.  This has come up more than once recently, and I've
> never seen an argument that using rsync this way is no better than
> using tar on a live data area.  If you want high availability, don't
> do it.

There's one /conceivable/ way for this to work, and that's if you're
filesystem mirroring using something along the lines of Digital's
"AdvFS."

The idea:  You have a duplicate mirror that is being kept up-to-date
at the filesystem level.

You then "break" the mirror, temporarily, and then run rsync on the
duplicate.

Then run [whatever is necessary] to get the sync back up and running.

There is the not-minor problem that this would require perform the
logical equivalent to 'fsync' on the database immediately before
breaking the link to ensure that the state of the DB is 'not
deranged.'

Enumerating the total set of vital non-obvious dependancies is left as
an exercise for the reader...
--
(reverse (concatenate 'string "ac.notelrac.teneerf@" "454aa"))
http://www.ntlug.org/~cbbrowne/sgml.html
Pay no attention to the PDP-11 behind the front panel.
-- PGS, in reference to OZ