Thread: 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
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@hub.org secondary: scrappy@{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
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
#!
> > 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. > I don't know about other *async* replication engines but Rserv > keeps track of transactions (if I understood you corectly). > Rserv transfers not individual modified data items but > *consistent* snapshot of changes to move slave database from > one *consistent* state (when all RI constraints satisfied) > to another *consistent* state. I thought Andreas did a good job of correcting me here. Transaction- based replication with triggers do not apply to points 1 and 4. I should have made a distinction between non-transaction and transaction based replication with triggers. I was not trying to single out rserv or any other project, and I can see how my wording implies this misinterpretation (my apologies). > > 4) The activation of triggers in a database cannot be easily > > rolled back or undone. > What do you mean? Once the trigger fires, it is not an easy task to abort that execution via rollback or undo. Again this is not an issue with a transaction-based trigger approach. Sincerely, Darren
> 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. I don't know about other *async* replication engines but Rserv keeps track of transactions (if I understood you corectly). Rserv transfers not individual modified data items but *consistent* snapshot of changes to move slave database from one *consistent* state (when all RI constraints satisfied) to another *consistent* state. > 4) The activation of triggers in a database cannot be easily > rolled back or undone. What do you mean? Vadim