Re: AW: Postgres Replication

> Although
> Postgres-R is a synchronous approach, I believe it is the closest to
> the goal mentioned above. Here is an abstract of the advantages.

If you only want synchronous replication, why not simply use triggers ?
All you would then need is remote query access and two phase commit,
and maybe a little script that helps create the appropriate triggers.

Doing a replicate all or nothing approach that only works synchronous
is imho not flexible enough.

Andreas

Hello

I have hacked up a replication layer for Perl code accessing a
database throught the DBI interface. It works pretty well with MySQL
(I can run pre-bender slashcode replicated, haven't tried the more
recent releases).

Potentially this hack should also work with Pg but I haven't tried
yet. If someone would like to test it out with a complex Pg app and
let me know how it went that would be cool.

The replication layer is based on Eric Newton's Recall replication
library (www.fault-tolerant.org/recall) and requires that all
database accesses be through the DBI interface.

The replicas are live, in that every operation affects all the
replicas in real time. Replica outages are invisible to the user, so
long as a majority of the replicas are functioning. Disconnected
replicas can be used for read-only access.

The only code modification that should be required to use the
replication layer is to change the DSN in connect():

my $replicas = '192.168.1.1:7000,192.168.1.2:7000,192.168.1.3:7000';
my $dbh = DBI->connect("DBI:Recall:database=$replicas");

You should be able to install the replication modules with:

perl -MCPAN -eshell
cpan> install Replication::Recall::DBServer

and then install DBD::Recall (which doesn't seem to be accessible from
the CPAN shell yet, for some reason), by:

wget http://www.cpan.org/authors/id/AGUL/DBD-Recall-1.10.tar.gz
tar xzvf DBD-Recall-1.10.tar.gz
cd DBD-Recall-1.10
perl Makefile.PL
make install

I would be very interested in hearing about your experiences with
this...

Thanks

#!

which I believe is what the rserv implementation in contrib currently does
... no?

its funny ... what is in contrib right now was developed in a weekend by
Vadim, put in contrib, yet nobody has either used it *or* seen fit to
submit patches to improve it ... ?

On Tue, 12 Jun 2001, Zeugswetter Andreas SB wrote:

>
> > Although
> > Postgres-R is a synchronous approach, I believe it is the closest to
> > the goal mentioned above. Here is an abstract of the advantages.
>
> If you only want synchronous replication, why not simply use triggers ?
> All you would then need is remote query access and two phase commit,
> and maybe a little script that helps create the appropriate triggers.
>
> Doing a replicate all or nothing approach that only works synchronous
> is imho not flexible enough.
>
> Andreas
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl
>

Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy(at)hub(dot)org secondary: scrappy(at){freebsd|postgresql}.org

> which I believe is what the rserv implementation in contrib currently
does
> ... no?

We tried rserv, PG Link (Joseph Conway), and PosrgreSQL Replicator. All
these projects are trigger based asynchronous replication. They all have
some advantages over the current functionality of Postgres-R some of
which I believe can be addressed:

1) Partial replication - being able to replicate just one or part of a
table(s)
2) They make no changes to the PostgreSQL code base. (Postgres-R can't
address this one ;)
3) PostgreSQL Replicator has some very nice conflict resolution schemes.

Here are some disadvantages to using a "trigger based" approach:

1) Triggers simply transfer individual data items when they are modified,
they do not keep track of transactions.
2) The execution of triggers within a database imposes a performance
overhead to that database.
3) Triggers require careful management by database administrators.
Someone needs to keep track of all the "alarms" going off.
4) The activation of triggers in a database cannot be easily
rolled back or undone.

> On Tue, 12 Jun 2001, Zeugswetter Andreas SB wrote:

> > Doing a replicate all or nothing approach that only works synchronous
> > is imho not flexible enough.
> >

I agree. Partial and asynchronous replication need to be addressed,
and some of the common functionality of Postgres-R could possibly
be used to meet those needs.

Thanks for your feedback,

Darren