public inbox for [email protected]  

help / color / mirror / Atom feed

From: Koen De Groote <[email protected]>
To: Michał Kłeczek <[email protected]>
Cc: Muhammad Usman Khan <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Logical replication disabled, recovery not possible because of 1 large transaction with schema changes?
Date: Thu, 17 Oct 2024 12:28:31 +0200
Message-ID: <CAGbX52HFYFioKY6YMdr861OTcsRNnZ0Et2VNTSRV3NLpOjsMZw@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAGbX52EDfA5DbD5qTsyHxGnZy_+cKup0=us7kskx932pwZ=xkQ@mail.gmail.com>
	<CAPnRvGuQ0T2Jj173TfPdJdxBvncNAVh3E+p3Tbg5xkfGaJRqrA@mail.gmail.com>
	<CAGbX52ErcaOPRczEWg_ZKeS61s4wZ4xLtfawSRsV5zUR_PtD0A@mail.gmail.com>
	<[email protected]>

Hello Michał,

Thanks for the reply. I suspected as much, I was just wondering if there
was an easy fix that didn't involve dropping the entire subscription and
having to re-do all the table because of that. Guess my only option is to
remove the affected tables from the publisher before the patch, refresh
subscription, do the patch, recreate the tables on the subscriber and do
the sync for only those tables.

I will look in to your suggestion.

Regards,
Koen De Groote





On Thu, Oct 17, 2024 at 11:17 AM Michał Kłeczek <[email protected]> wrote:

>
>
> > On 17 Oct 2024, at 11:07, Koen De Groote <[email protected]> wrote:
> >
> > Hello Muhammad,
> >
> > 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’re 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ł
>
>

view thread (4+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Logical replication disabled, recovery not possible because of 1 large transaction with schema changes?
  In-Reply-To: <CAGbX52HFYFioKY6YMdr861OTcsRNnZ0Et2VNTSRV3NLpOjsMZw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox