MIME-Version: 1.0
References: 
 <CAAD30U+pVmfKwUKy8cbZOnUXyguJ-uBNejwD75Kyo=OjdQGJ9g@mail.gmail.com>
In-Reply-To: 
 <CAAD30U+pVmfKwUKy8cbZOnUXyguJ-uBNejwD75Kyo=OjdQGJ9g@mail.gmail.com>
From: Dilip Kumar <dilipbalaut@gmail.com>
Date: Sun, 13 Mar 2022 17:05:18 +0530
Message-ID: 
 <CAFiTN-tM1zOk50xoeP5vdzaO0U_iQ3u8oZ2P1L7J=0ka+c7Utw@mail.gmail.com>
Subject: Re: Support logical replication of DDLs
To: Zheng Li <zhengli10@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAFiTN-tM1zOk50xoeP5vdzaO0U_iQ3u8oZ2P1L7J%3D0ka%2Bc7Utw%40mail.gmail.com>
Precedence: bulk

On Mon, Feb 21, 2022 at 9:43 PM Zheng Li <zhengli10@gmail.com> wrote:
>
> Hello,
>
> One of the most frequently requested improvements from our customers
> is to reduce downtime associated with software updates (both major and
> minor versions). To do this, we have reviewed potential contributions to
> improving logical replication.
>
> I=E2=80=99m working on a patch to support logical replication of data
> definition language statements (DDLs). This is a useful feature when a
> database in logical replication has lots of tables, functions and
> other objects that change over time, such as in online cross major
> version upgrade.

+1

> I put together a prototype that replicates DDLs using the generic
> messages for logical decoding. The idea is to log the candidate DDL
> string in ProcessUtilitySlow() using LogLogicalMessge() with a new
> flag in WAL record type xl_logical_message indicating it=E2=80=99s a DDL
> message. The xl_logical_message record is decoded and sent to the
> subscriber via pgoutput. The logical replication worker process is
> dispatched for this new DDL message type and executes the command
> accordingly.

If you don't mind, would you like to share the POC or the branch for this w=
ork?

> However, there are still many edge cases to sort out because not every
> DDL statement can/should be replicated. Some of these include:

> 3. CREATE TABLE AS and SELECT INTO, For example:
>
>     CREATE TABLE foo AS
>     SELECT field_1, field_2 FROM bar;
>
>     There are a few issues that can occur here. For one, it=E2=80=99s pos=
sible
> that table bar doesn't exist on the subscriber. Even if =E2=80=9Cbar=E2=
=80=9D does
> exist, it may not be fully up-to-date with the publisher, which would
> cause a data mismatch on =E2=80=9Cfoo=E2=80=9D between the publisher and =
subscriber.

In such cases why don't we just log the table creation WAL for DDL
instead of a complete statement which creates the table and inserts
the tuple?  Because we are already WAL logging individual inserts and
once you make sure of replicating the table creation I think the exact
data insertion on the subscriber side will be taken care of by the
insert WALs no?


--=20
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com