BDR DML Only

## 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general