Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3776.700.51\))
Subject: Re: Logical replication disabled, recovery not possible because of 1
 large transaction with schema changes?
From: =?utf-8?Q?Micha=C5=82_K=C5=82eczek?= <michal@kleczek.org>
In-Reply-To: <CAGbX52ErcaOPRczEWg_ZKeS61s4wZ4xLtfawSRsV5zUR_PtD0A@mail.gmail.com>
Date: Thu, 17 Oct 2024 11:16:41 +0200
Cc: Muhammad Usman Khan <usman.k@bitnine.net>,
 pgsql-general <pgsql-general@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <657111A5-7475-4198-B8FD-83E87D628F5B@kleczek.org>
References: <CAGbX52EDfA5DbD5qTsyHxGnZy_+cKup0=us7kskx932pwZ=xkQ@mail.gmail.com>
 <CAPnRvGuQ0T2Jj173TfPdJdxBvncNAVh3E+p3Tbg5xkfGaJRqrA@mail.gmail.com>
 <CAGbX52ErcaOPRczEWg_ZKeS61s4wZ4xLtfawSRsV5zUR_PtD0A@mail.gmail.com>
To: Koen De Groote <kdg.dev@gmail.com>
Archived-At: <https://www.postgresql.org/message-id/657111A5-7475-4198-B8FD-83E87D628F5B%40kleczek.org>
Precedence: bulk


> On 17 Oct 2024, at 11:07, Koen De Groote <kdg.dev@gmail.com> wrote:
>=20
> Hello Muhammad,
>=20
> The problem with my scenario is the changes are written as a single =
transaction, with a BEGIN and COMMIT. In that transaction, there are =
first inserts, then a schema change, and then inserts on the new schema.

I guess until logical replication of DDL is available you=E2=80=99re out =
of luck.

The best you can do is to have a separate table for recording and =
replaying schema changes.
Create triggers that perform actual DDL operations based on DML in this =
table.
Publish this table on the publisher in the same publication as the =
tables affected by the DDL.

On the subscriber side it is the same - just make the trigger is marked =
as ENABLE REPLICA TRIGGER or ENABLE ALWAYS TRIGGER.

Kind regards,
Micha=C5=82