tag:blogger.com,1999:blog-20038672.post1456606912627192755..comments2024-03-28T00:58:29.187-04:00Comments on Robert Haas: The Case For Logical ReplicationRobert Haashttp://www.blogger.com/profile/08393677427643988650noreply@blogger.comBlogger19125tag:blogger.com,1999:blog-20038672.post-35550780108315614262012-12-14T00:43:09.232-05:002012-12-14T00:43:09.232-05:00Hey Robert hii,can you tell something about how i ...Hey Robert hii,can you tell something about how i can extract tuples and then generate sql statements which you are talking about in your last comment, am also working on similar project means logical replication n was trying to extract sql statements from xlog files of postgresql in pg_xlog directory. thanks in advance :) Prashant Guptahttps://www.blogger.com/profile/04045829923431485407noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-33525089034845587182012-04-27T10:59:03.479-04:002012-04-27T10:59:03.479-04:00Nice summary, looks like we agree on the way forwa...Nice summary, looks like we agree on the way forwards.Simon Riggshttp://www.2ndquadrant.comnoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-21299290181398828992012-04-26T09:27:33.144-04:002012-04-26T09:27:33.144-04:00100% agreed on that. I'm working on this now.100% agreed on that. I'm working on this now.Simon Riggshttp://www.2ndquadrant.comnoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-84059294376336335422011-03-08T08:56:12.664-05:002011-03-08T08:56:12.664-05:00I don't think anyone wants to extract statemen...I don't think anyone wants to extract statements. What would be nice is to extract tuples, from which insert/update/delete SQL statements could be generated. So a statement that deletes 3 tuples would eventually turn into three delete statements, targeted by primary key.Robert Haashttps://www.blogger.com/profile/08393677427643988650noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-15667945052885489272011-03-07T20:24:23.184-05:002011-03-07T20:24:23.184-05:00It's not actually possible to extract DML from...It's not actually possible to extract DML from our transaction log. Our transaction log is full of data pages, not statements.Josh Berkushttp://www.pgexperts.comnoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-76166660565552084732011-03-02T20:03:19.405-05:002011-03-02T20:03:19.405-05:00I agree with what Denish Patel proposed. Oracle li...I agree with what Denish Patel proposed. Oracle like logical standby is a good idea. I don't like Slony because it incurs load on primary and it is very troublesome to maintain database schema changes with slony. Extracting DML or DDL from Xlog and reapply it to standby is a good way to go... I hope those PostgreSQL hackers can seriously consider this.Pius Channoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-37279560500395002352011-02-28T15:18:24.718-05:002011-02-28T15:18:24.718-05:00After reading the post, i think PostgreSQL can use...After reading the post, i think PostgreSQL can use method used by oracle to read from xlogs. Oracle uses "SQL apply" method for Logical standby database. Oracle reads transactions from the transactions logs and convert it to SQL. Oracle uses primary-key or unique-constraint/index supplemental logging to logically identify a modified row in the logical standby database. <br /><br />When database-wide primary-key and unique-constraint/index supplemental logging is enabled, each UPDATE statement also writes the column values necessary in the redo log to uniquely identify the modified row in the logical standby database.<br /><br />If a table has a primary key defined, then the primary key is logged along with the modified columns as part of the UPDATE statement to identify the modified row.<br /><br />In the absence of a primary key, the shortest nonnull unique-constraint/index is logged along with the modified columns as part of the UPDATE statement to identify the modified row.<br /><br />In the absence of both a primary key and a nonnull unique constraint/index, all columns of bounded size are logged as part of the UPDATE statement to identify the modified row.Denish Patelhttps://www.blogger.com/profile/16320175389416603668noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-15551603329658873402011-02-28T10:41:23.784-05:002011-02-28T10:41:23.784-05:00@Robert Hass
I think cluster-hackers is the best ...@Robert Hass<br /><br />I think cluster-hackers is the best forum to start a discussion. Many people from the various projects are already subscribed and it is less noisy than -hackers.Steve Singerhttp://scanningpages.wordpress.comnoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-8195704415869110002011-02-28T10:27:18.201-05:002011-02-28T10:27:18.201-05:00@Steve Singer: I agree with all of your comments. ...@Steve Singer: I agree with all of your comments. I'd like to see us tackle some of those hard problems, particularly performance and ease of use. What do you think the right forum for that discussion is?Robert Haashttps://www.blogger.com/profile/08393677427643988650noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-61509720159557568322011-02-28T10:05:07.241-05:002011-02-28T10:05:07.241-05:00I'm not convinced ALTER TABLE FOR REPLICATION ...I'm not convinced ALTER TABLE FOR REPLICATION hooks that call into a replication system living outside of core will make deploying any of the logical replication systems that much easier.<br /><br />The big issues with logical replication are <br />i)none of the projects do everything and knowing the limitations which one for a particular job is difficult<br />ii) Many of them are difficult to use and difficult to troubleshoot when things go wrong<br />iii) The performance overhead of the trigger based ones can be significant for a lot of workloads<br />iv) Lack of DDL triggers means DDL changes requires special intervention<br /><br />I think to some extent people on the cluster-hackers mailing list knows how hard these problems are so they ignore them.Steve Singerhttp://scanningpages.wordpress.comnoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-70358556694414941272011-02-28T00:10:12.157-05:002011-02-28T00:10:12.157-05:00@Fazal, interesting you bring up Rep Server. A co...@Fazal, interesting you bring up Rep Server. A couple of us working on Tungsten used to be in the Sybase Rep Server group. It's still a great product. We have been working for the last 3 years getting similar features (and better) into Tungsten Replicator.Robert Hodgeshttps://www.blogger.com/profile/05379726998057344092noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-79843409907892191222011-02-28T00:07:38.601-05:002011-02-28T00:07:38.601-05:00Logical replication is a useful project and does n...Logical replication is a useful project and does not need to be deeply enmeshed in the PostgreSQL core. It does need some changes to logging to work well. We are pushing Tungsten Replicator for MySQL into open source the first half of this year. I hope that later in the year we can start to look for companies in the PostgreSQL space willing to sponsor reading the PG log. Maybe we'll even have a chance to work on this together. :)Robert Hodgeshttps://www.blogger.com/profile/05379726998057344092noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-76968748397455817992011-02-27T12:53:40.229-05:002011-02-27T12:53:40.229-05:00Josh, I'm not sure if hooks are enough, or if ...Josh, I'm not sure if hooks are enough, or if we really need to have the whole thing in core. But certainly hooks would be a good place to start. I know there has been work done on this in the past, but as you say I think we need to step up our efforts in that area.Robert Haashttps://www.blogger.com/profile/08393677427643988650noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-84124752095433247112011-02-26T20:47:20.510-05:002011-02-26T20:47:20.510-05:00Robert,
I'm pointing out that the idea of hav...Robert,<br /><br />I'm pointing out that the idea of having features and hooks to enable a variety of replication systems for PostgreSQL is not a new idea, even if it's new to you. There's even a wiki page:<br /><br />http://wiki.postgresql.org/wiki/ClusterFeatures<br /><br />What's been lacking ... for the last 6 years, in fact ... is sufficient concentrated work on any of these features. Or, for that matter, even enough urgency around the features that the various replication teams are willing to compromise on exactly how they work. Sometimes it seems like -hackers is the only place where people are willing to compromise on specs.Josh Berkushttps://www.blogger.com/profile/09671139717468724246noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-90226999781979567012011-02-26T16:51:45.267-05:002011-02-26T16:51:45.267-05:00We could learn much from Sybase's Replication ...We could learn much from Sybase's Replication Server. It was really fast (at France Telecom, we actually used it as a message queue), supported function replication, filtering, routing and other advanced capabilities, many of which have yet to be replicated by other commercial vendors.Fazal Majidhttp://www.majid.info/noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-72603538000357635122011-02-26T16:13:13.807-05:002011-02-26T16:13:13.807-05:00Josh, I know that there is a pgsql-cluster-hackers...Josh, I know that there is a pgsql-cluster-hackers mailing list, but it seems like there's very little activity - 8 messages in the last 9 months. There have been no significant patches in this area during the 9.1 development cycle.<br /><br />As for the cluster-hackers summit, I saw an invitation on pgsql-hackers for people actively doing work in this area, but since this is more of a wish than a current project I'm not sure I qualify. I may also be giving a tutorial that day.Robert Haashttps://www.blogger.com/profile/08393677427643988650noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-3718622640617612652011-02-26T15:12:10.507-05:002011-02-26T15:12:10.507-05:00Several of the fundamental pieces needed here, the...Several of the fundamental pieces needed here, the ones that always pop up when you poll people about how to actually start building this feature, are already listed at <a href="http://wiki.postgresql.org/wiki/ClusterFeatures" rel="nofollow">ClusterFeatures</a>. "API into the Parser", "DDL Triggers", and "Modification trigger into core / Generalized Data Queue" are the three most relevant here. The more detailed page for <a href="http://wiki.postgresql.org/wiki/ModificationTriggerGDQ" rel="nofollow">General Modification Trigger and Generalized Data Queue</a> is probably the biggest and most important of those.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-79615698055560209842011-02-26T14:42:59.558-05:002011-02-26T14:42:59.558-05:00Robert,
You seem to have missed entirely the exis...Robert,<br /><br />You seem to have missed entirely the existence of the pg-cluster-hackers list and the clustering summit (at pgCon this year). The purpose of that group is to get tools into core which make building replication solutions easy/possible. Progress has been slow.<br /><br />Maybe you should get involved? You got invited to the clustering summit, you could show up.Josh Berkushttps://www.blogger.com/profile/09671139717468724246noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-72806530548364382902011-02-26T13:14:20.446-05:002011-02-26T13:14:20.446-05:00IMHO, core could start by providing SQL support fo...IMHO, core could start by providing SQL support for replication. For example, adding ALTER TABLE tablename FOR REPLICATION, which could, say, set a boolean in pg_class. Altering a database for replication would then set in motion the propagation of changes or make them available for external processes.<br /><br />To allow writes on a standby or for multi-master, you need smart policies for dealing with conflicts. For example, there used to be a product (whose name escapes me) that allowed a change to a customer name in one master to be merged properly with a change to the same customer's phone from another master.<br /><br />As you said, it's hard.Joehttps://www.blogger.com/profile/18006904787527262772noreply@blogger.com