Re: AW: AW: AW: [HACKERS] Postgres Replication
- only full replication possible I do not understand that point, if it is trigger based, you have all the flexibility you need. (only some tables, only some rows, different rows to different targets ), (or do you mean not all targets, that could also be achieved with triggers) Currently with Postgres-R, it is one database replicating all tables to all servers in the group communication system. There are some ways around this by invoking the -r option when a SQL statement should be replicated, and leaving the -r option off for non-replicated scenarios. IMHO this is not a good solution. A better solution will need to be implemented, which involves a subscription table(s) with relation/server information. There are two ideas for subscribing and receiving replicated data. 1) Receiver driven propagation - A simple solution where all transactions are propagated and the receiving servers will reference the subscription information before applying updates. 2) Sender driven propagation - A more optimal and complex solution where servers do not receive any messages regarding data items for which they have not subscribed - what happens if one server is down? (or the network between) are commits still possible No, updates are not possible if one target is not reachable, AFAIK, Postgres-R can still replicate if one target is not reachable, but only to the remaining servers ;). There is a scenario that could arise if a server issues a lock request then fails or goes off line. There is code that checks for this condition, which needs to be merged with the branch we have. that would not be synchronous and would again need business rules to resolve conflicts. Yes the failed server would not be synchronized, and getting this failed server back in sync needs to be addressed. Allowing updates when a target is not reachable would require admin intervention. In its current state yes, but our goal would be to eliminate this requirement as well. Darren ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
AW: [HACKERS] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: AW: [HACKERS] Postgres Replication
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: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: AW: [HACKERS] Postgres Replication
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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
AW: AW: [HACKERS] Postgres Replication
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. Yes, points 2 and 3 are a given, although point 2 buys you the functionality of transparent locking across all involved db servers. Points 1 and 4 are only the case for a trigger mechanism that does not use remote connection and 2-phase commit. Imho an implementation that opens a separate client connection to the replication target is only suited for async replication, and for that a WAL based solution would probably impose less overhead. Andreas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: AW: [HACKERS] Postgres Replication
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 #! ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: AW: AW: [HACKERS] Postgres Replication
Imho an implementation that opens a separate client connection to the replication target is only suited for async replication, and for that a WAL based solution would probably impose less overhead. Yes there is significant overhead with opening a connection to a client, so Postgres-R creates a pool of backends at start up, coupled with the group communication system (Ensemble) that significantly reduces this issue. Very good points, Darren ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [Fwd: AW: [HACKERS] Postgres Replication]
I would be very interested in hearing about your experiences with this... Well, Eric thinks it works just spiffy. 8-) Recall is written in C++, and is meant to be extensible. It was extended for perl and the DBI layer. Note that this hack for perl is not perfect, especially in the area of transactions and locks (which weren't a big deal for MySQL). Recall supports active repliation with strict consistency. The perl interface is cool hack, but Recall can do even more. I would love it if you folks could take a look. I'm working on an CORBA version right now. Any feedback would be very helpful. Besides... it don't cost nothin' -Eric ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
RE: AW: [HACKERS] Postgres Replication
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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
RE: AW: [HACKERS] Postgres Replication
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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: AW: AW: [HACKERS] Postgres Replication
[EMAIL PROTECTED] (Reinoud van Leeuwen) writes: Well as I read back the thread I see 2 different approaches to replication: ... I can think of some scenarios where I would definitely want to *choose* one of the options. Yes. IIRC, it looks to be possible to support a form of async replication using the Postgres-R approach: you allow the cluster to break apart when communications fail, and then rejoin when your link comes back to life. (This can work in principle, how close it is to reality is another question; but the rejoin operation is the same as crash recovery, so you have to have it anyway.) So this seems to me to allow getting most of the benefits of the async approach. OTOH it is difficult to see how to go the other way: getting the benefits of a synchronous solution atop a basically-async implementation doesn't seem like it can work. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
