Обсуждение: BDR DML Only
Is it already possible or would you consider a configuration option that would only replicate DML but not DDL ? This should of course be combined with a predictable way of manually handling DDL errors. Like simply manually adding any missing DDL on the "slave". Thanks Poul
## pba@mailme.dk (pba@mailme.dk): > Is it already possible or would you consider a configuration option that > would only replicate DML but not DDL ? bdr.skip_ddl_replication = true can even be set at transaction level Regards, Christoph -- Spare Space
On 2014-09-15 19:54:42 +0200, pba@mailme.dk wrote: > Is it already possible or would you consider a configuration option that > would only replicate DML but not DDL ? > > This should of course be combined with a predictable way of manually > handling DDL errors. Like simply manually adding any missing DDL on the > "slave". As Christoph wrote this pretty much is already possible. Even if it could be prettier. What I'd like to know is what the exact use case you want it for is. Could you quickly explain? Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
> On 2014-09-15 19:54:42 +0200, pba@mailme.dk wrote: >> Is it already possible or would you consider a configuration option that >> would only replicate DML but not DDL ? >> >> This should of course be combined with a predictable way of manually >> handling DDL errors. Like simply manually adding any missing DDL on the >> "slave". > > As Christoph wrote this pretty much is already possible. Even if it > could be prettier. What I'd like to know is what the exact use case you > want it for is. Could you quickly explain? > Hi Christoph and Andres Thanks for your quick replies. I am not sure I have a very good case other than the very lousy argument that I would trust it to be stable much more quickly :-) I tried it and it works fine for the simple examples, but obvious once things get a bit more complicated my case falls to pieces. Trying to load the postgis extension then fails. It worked with DDL at least on the first attempt. So we are back to the error recovery. As far as I can see it will still "block" DDL operations on the second server even if they are not replicated. Poul
Hi, On 2014-09-16 19:32:38 +0200, pba@mailme.dk wrote: > I am not sure I have a very good case other than the very lousy argument > that I would trust it to be stable much more quickly :-) Well, we'll get the much more quickly if people report problems ;) > I tried it and it works fine for the simple examples, but obvious once > things get a bit more complicated my case falls to pieces. > > Trying to load the postgis extension then fails. It worked with DDL at > least on the first attempt. So we are back to the error recovery. Hm, that's a bit odd. I think that should actually work. Which version did you use? > As far as I can see it will still "block" DDL operations on the second > server even if they are not replicated. That's a security feature. You can circumvent it, but I'd strongly advise against it. It's unproblematic if you have only one node being written to, but if you DDL against a table which is written to by multiple nodes you can very quickly get into nasty situations. Consider a table with one column being written to on three nodes. It gets a column added to it. Some nodes will have pending changes with 1 column, others with 2. In neither case it's clear how to replicate these. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
> Hi, > > On 2014-09-16 19:32:38 +0200, pba@mailme.dk wrote: >> I am not sure I have a very good case other than the very lousy argument >> that I would trust it to be stable much more quickly :-) > > Well, we'll get the much more quickly if people report problems ;) > >> I tried it and it works fine for the simple examples, but obvious once >> things get a bit more complicated my case falls to pieces. >> >> Trying to load the postgis extension then fails. It worked with DDL at >> least on the first attempt. So we are back to the error recovery. > > Hm, that's a bit odd. I think that should actually work. Which version > did you use? > >> As far as I can see it will still "block" DDL operations on the second >> server even if they are not replicated. > > That's a security feature. You can circumvent it, but I'd strongly > advise against it. It's unproblematic if you have only one node being > written to, but if you DDL against a table which is written to by > multiple nodes you can very quickly get into nasty situations. Consider > a table with one column being written to on three nodes. It gets a > column added to it. Some nodes will have pending changes with 1 column, > others with 2. In neither case it's clear how to replicate these. > Yes that makes sense. With DDL disabled then create extension posgis gives a loop on the "slave" LOG: starting background worker process "bdr (6059699842869179629,1,16384,): beta: apply" ERROR: relation "public.spatial_ref_sys" does not exist LOG: worker process: bdr (6059699842869179629,1,16384,): beta: apply (PID 10482) exited with exit code 1 And then I can't create the extension on the "slave". Per the concept of BDR this is also reasonable since the postgis extension is a good mix of DDL and DML. So DDL disabled with DDL "locks" is probably not the right way to go. With DDL enabled I did get the Postgis loaded (version 2.1.x compiled with the BDR setup). I also managed to run pg_bulkload in buffered writer mode (I wasn't sure if that was supposed to work). However in all of my tests I sooner or later end up with a DDL endless loop error due to a create or replace something in my scripts and applications => reinstallation. Hence my interest in error recovery :-) But I will keep on testing because the end result will be very valuable. So keep up the good work! Poul
On 2014-09-16 20:03:21 +0200, pba@mailme.dk wrote:
> With DDL disabled then create extension posgis gives a loop on the "slave"
>
> LOG: starting background worker process "bdr
> (6059699842869179629,1,16384,): beta: apply"
> ERROR: relation "public.spatial_ref_sys" does not exist
> LOG: worker process: bdr (6059699842869179629,1,16384,): beta: apply (PID
> 10482) exited with exit code 1
>
> And then I can't create the extension on the "slave".
> Per the concept of
> BDR this is also reasonable since the postgis extension is a good mix of
> DDL and DML.
Right. I guess we could actually relatively easily "fix" that. Something like:
SELECT pg_replication_identifier_create('dont-replay');
SELECT pg_replication_identifier_setup_replaying_from('dont-replay');
BEGIN;
SET LOCAL bdr.skip_ddl_replication=on;
SET LOCAL bdr.permit_unsafe_commands=on;
CREATE EXTENSION whatever;
COMMIT;
SELECT pg_replication_identifier_reset_replaying_from();
Which obviously isn't particularly nice... Although a bit cool :P
> However in all of my tests I sooner or later end up with a DDL endless
> loop error due to a create or replace something in my scripts and
> applications => reinstallation. Hence my interest in error recovery :-)
The CREATE OR REPLACE VIEW errors already are fixed :). Albeit only in
the newly structured branches (branches bdr-plugin/next and
bdr-pg/REL9_4_STABLE) for bdr 9.4. For which no instructions about
compiling exist yet :(
> But I will keep on testing because the end result will be very valuable.
Cool!
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services