Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t1NkQ-00G2DO-9Z for pgsql-general@arkaria.postgresql.org; Thu, 17 Oct 2024 10:28:50 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1t1NkN-002qoQ-VE for pgsql-general@arkaria.postgresql.org; Thu, 17 Oct 2024 10:28:48 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t1NkN-002qoG-GB for pgsql-general@lists.postgresql.org; Thu, 17 Oct 2024 10:28:47 +0000 Received: from mail-ej1-x62b.google.com ([2a00:1450:4864:20::62b]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1t1NkL-001Ldm-8P for pgsql-general@lists.postgresql.org; Thu, 17 Oct 2024 10:28:46 +0000 Received: by mail-ej1-x62b.google.com with SMTP id a640c23a62f3a-a9a0472306cso98911566b.3 for ; Thu, 17 Oct 2024 03:28:45 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1729160923; x=1729765723; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=4E5cyJGJB7RnRxxI/Y/WW+QIVTt4FvwEw2OSNdOfzMY=; b=J/Lw4VJdVWaPVsiJndutafczHQ8DqhJ+8xCTXlHhdtY9GOe0YPMEem2yOfM49Lwb+z pSX6qQRahlxZx+VD40mH4+WXTp2MUyTM9/01hZtrQ+Zicppvi9r1+894vHDRgSbT0ldr KMVQ1w7wT8Z0b2Uv+PMxNofy2tC8i6ijiYd8b1SSBdVTUcy5CbVLZMviT4LuKsldqvXb hKJxP21TQobYFPD2qo7YAd3vmryg6hQemOuXmyoIfz6olztolgr1mQTMyJxu4obJ9v/9 pfZM7LwStxjq/7SvPN37t2s01Rk966XA4zFbMtcRKwwFgtzkKvTIOkrrkVKPbg2DN9EB mqzQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1729160923; x=1729765723; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=4E5cyJGJB7RnRxxI/Y/WW+QIVTt4FvwEw2OSNdOfzMY=; b=RJPb7jmMlhFXJMs3tUnhxJ4HW+WpbjYmcHUbXVPC1bT0KeiEuZwtBTJETvja2x5ro2 NnIZ4fYLmffxdBCWUNT1GfI0Ym9g676E/tWxiYRfuWnZ+rt7ymw54EsNqrkSD6ky2XCB xJaCYCLpJBjvbh+rlxuPeSonLn3ViLg/7mxfA2AttujV8BK99VePZBtnDBcdktkjwPZg sOLcCtYmP9bvg5glQE4FndnYDf+75P/On6BfSaQ4OZeieZNWsbVU2d+nkSWrbVwKGbN7 j6fjo5V/1d/MEllwW0b96Qsew8e/06TvmXPdSJGYguuh+69bhK/KM6Yfxzk1gBekSc1w QgJQ== X-Forwarded-Encrypted: i=1; AJvYcCVTPbWNHgrXZD46P10/nLwnQNz79ARJ91r0Xp3rAqkZCHHOwuGADV8oFDcyj6XRhhG43a0hTWws19mgaTa0@lists.postgresql.org X-Gm-Message-State: AOJu0Yy2uETVdEFEhqFox8A8RqPeQwvgiw75luwRw2z2RNJgIGsO5FqG LFJ495Uxbb6AXZ9BRWb9P4KAcafzS6zBA+Mo9S8cOI80ZYACg/UoXChahEBHeb+ZDOW8IgS8sk0 GWzFgC+1ETV+h0udY5kJFxYJB+rQ= X-Google-Smtp-Source: AGHT+IH6fBJKLGDv154MI8JF4Z7n8E1PTTLYYpE38+HLK05mDQM/LqUcjpXpfLnIcHmIUsU/9eqp8+u1UBLCpZ/XRUI= X-Received: by 2002:a17:907:7f8d:b0:a9a:e91:68c5 with SMTP id a640c23a62f3a-a9a0e916af4mr1298702866b.33.1729160922421; Thu, 17 Oct 2024 03:28:42 -0700 (PDT) MIME-Version: 1.0 References: <657111A5-7475-4198-B8FD-83E87D628F5B@kleczek.org> In-Reply-To: <657111A5-7475-4198-B8FD-83E87D628F5B@kleczek.org> From: Koen De Groote Date: Thu, 17 Oct 2024 12:28:31 +0200 Message-ID: Subject: Re: Logical replication disabled, recovery not possible because of 1 large transaction with schema changes? To: =?UTF-8?B?TWljaGHFgiBLxYJlY3plaw==?= Cc: Muhammad Usman Khan , pgsql-general Content-Type: multipart/alternative; boundary="0000000000008a68440624a9a65f" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000008a68440624a9a65f Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hello Micha=C5=82, 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=E2=80=AFAM Micha=C5=82 K=C5=82eczek wrote: > > > > On 17 Oct 2024, at 11:07, Koen De Groote 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 firs= t > 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 a= s > ENABLE REPLICA TRIGGER or ENABLE ALWAYS TRIGGER. > > Kind regards, > Micha=C5=82 > > --0000000000008a68440624a9a65f Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hello Micha=C5=82,

Thanks for the reply= . I suspected as much, I was just wondering if there was an easy fix that d= idn'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 t= ables from the publisher before the patch, refresh subscription, do the pat= ch, recreate the tables on the subscriber and do the sync for only those ta= bles.

I will look in to your suggestion.

Regards,
Koen De Groote
<= table cellpadding=3D"0" class=3D"gmail-cf gmail-ix" style=3D"border-collaps= e:collapse;table-layout:fixed;width:2682.19px">



=

On Thu, Oct 17, 2= 024 at 11:17=E2=80=AFAM Micha=C5=82 K=C5=82eczek <michal@kleczek.org> wrote:


> On 17 Oct 2024, at 11:07, Koen De Groote <kdg.dev@gmail.com> wrote:
>
> Hello Muhammad,
>
> The problem with my scenario is the changes are written as a single tr= ansaction, with a BEGIN and COMMIT. In that transaction, there are first in= serts, 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 tab= le.
Publish this table on the publisher in the same publication as the tables a= ffected 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

--0000000000008a68440624a9a65f--