Re: Feature Request - DDL deployment with logical replication - Mailing list pgsql-hackers

From Jeremy Finzel
Subject Re: Feature Request - DDL deployment with logical replication
Date
Msg-id CAMa1XUh2_jfMnhZ-bhOSMZNFuorqWKknG7XgUkLN9jPnOFvDGw@mail.gmail.com
Whole thread Raw
In response to Re: Feature Request - DDL deployment with logical replication  (Konstantin Knizhnik <k.knizhnik@postgrespro.ru>)
Responses Re: Feature Request - DDL deployment with logical replication  (Nikhil Sontakke <nikhils@2ndquadrant.com>)
List pgsql-hackers


On Fri, Mar 30, 2018 at 2:26 AM, Konstantin Knizhnik <k.knizhnik@postgrespro.ru> wrote:

Our team in PostgresPro is also very interested in this discussion, because we are using logical decoding in multimaster.
Right now in multimaster DDLs are replicated in this same way as in pglogical, using "logical messages" with correspondent DDL statements.
There are a couple of problems related with this approach, for example mix of DDL with DML (create table as...) which we have addressed in multimaster.

IMO, I wouldn't even allow CREATE TABLE AS especially as an initial feature.  In our environment, I have rarely if even seen an application migration that uses CREATE TABLE AS, and it's an acceptable compromise for us to simply disallow it.  The way I have written pgl_ddl_deploy is to disallow CREATE TABLE AS from being replicated.  IMO that's a very small price to pay.

My whole point is that in most architectures, DBAs decide to deploy the same SQL on providers and subscribers.  Yes it isn't perfect, but IMO, it is very helpful to try to automate that idea, as opposed to trying to actually replicate DDL at the low level.  The latter is better, yes, but seems to have proven extremely difficult.  Hence, why you see the advent of functions to pipe DDL through the replication stream.

Thanks,
Jeremy

pgsql-hackers by date:

Previous
From: "Bossart, Nathan"
Date:
Subject: Re: Change RangeVarGetRelidExtended() to take flags argument?
Next
From: David Steele
Date:
Subject: Re: PATCH: Configurable file mode mask