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 1t1HWa-00FW2Y-N9 for pgsql-general@arkaria.postgresql.org; Thu, 17 Oct 2024 03:50:09 +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 1t1HWX-00FIiB-HQ for pgsql-general@arkaria.postgresql.org; Thu, 17 Oct 2024 03:50:05 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t1HWW-00FIhh-VE for pgsql-general@lists.postgresql.org; Thu, 17 Oct 2024 03:50:05 +0000 Received: from mail-pf1-x430.google.com ([2607:f8b0:4864:20::430]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1t1HWS-001SxV-KD for pgsql-general@lists.postgresql.org; Thu, 17 Oct 2024 03:50:03 +0000 Received: by mail-pf1-x430.google.com with SMTP id d2e1a72fcca58-71e7086c231so346138b3a.0 for ; Wed, 16 Oct 2024 20:49:59 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=bitnine-net.20230601.gappssmtp.com; s=20230601; t=1729136997; x=1729741797; 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=Mz8dhwxlKxhAwvtGuPoxolLVxvGMqpG+D2SJqX2kflY=; b=drKaFqawJY08VXiUJKOrb5RFlqXC/wUUl/SQiBSzUoheh3rr4s9TtXdUC2Atfpev8f WATG+Ji3rqXPKamkFzDJHqevWFaDN+5KYMuHVIaE8g6XPdxwx/nfyLIVG6u+zN7mXixr mM63nlAietWBDkkbNGmAojnWDhJB6wYBP/Xl9LDsR37gBCvzzgSHxrmNtDIMOiajnYxR 0vqHxuqRbvo/Fg15Ri5NhqoDn3aCgbBsARPb2qw/gcoU0ocPejIPu+0oatnQziX2nV3R 7dOrt/J07apfnUfXQW6QnIGNOFddjggHXU7jEttP/q19h932ZPcanvDbEVuXfH24qEQP J4wQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1729136997; x=1729741797; 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=Mz8dhwxlKxhAwvtGuPoxolLVxvGMqpG+D2SJqX2kflY=; b=PmkBQnwbkTgjamK5GJjinb+mUH9CV+j8DQbfcXhZ1ySpRYFeKlIFAGGSr+JpeMZxUp JwaDrgKJfcCfDmyAE77GCcs+qCKIO+IBvc8zaWV6K3wuBcgrZirlJyfccGv+GMEldwdL V1N/AqCS9ze+9i/CYn6/jXxJxg5nXNxrTNqMdN0KheFto2vWLf9DEKcepqttIeunXo32 L4NsrNcJ9WUVucafeS5lspABzDG6jFPslBC8Ttia3bGGh77J1WV9bX+NRZ6PAfSIJ7K/ r1jaRBB0X1nx1fKlqh9drnL1pyAtdM6H8j1UmTfS7B5T9mzT+mPgPXVWsmfhsB3QsVlx ve1Q== X-Gm-Message-State: AOJu0YwSdb6rHb0n5kknsXrrDYa+pmEL7vM6vA0ml3eYKcqmHLh+IrxV abeLtLw7Eti37Ag4njJaoF0L6Jw3U1KXjzyL5+76yeBx0Ah8o4isbDzBpti8KQbLaQ72QjxK3Zb 6L0155oKGGdKT4+WYFeZa71MsF0hRduIEJobWWw== X-Google-Smtp-Source: AGHT+IHovRJvlPKFfF+FsRhc3H1IX+PWpMV4xVKO4ZEpxEl2D7CNIsrfGXBldnLmoJITG8ZS9A10dFSKaynPAUdzV/I= X-Received: by 2002:a05:6a00:218e:b0:71e:7046:c0f8 with SMTP id d2e1a72fcca58-71e7db0056amr8386547b3a.26.1729136997206; Wed, 16 Oct 2024 20:49:57 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Muhammad Usman Khan Date: Thu, 17 Oct 2024 08:49:45 +0500 Message-ID: Subject: Re: Logical replication disabled, recovery not possible because of 1 large transaction with schema changes? To: Koen De Groote Cc: PostgreSQL General Content-Type: multipart/alternative; boundary="0000000000007ca6860624a4145e" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000007ca6860624a4145e Content-Type: text/plain; charset="UTF-8" Hi, When you execute schema-altering operations on the publisher, these changes are not automatically replicated to the subscriber which causes the following error logical replication target relation "public.dummy_table" is missing replicated columns: "contact_email", "status", "phone_number", "username" Before making schema changes, temporarily disable the subscription to prevent replication errors. ALTER SUBSCRIPTION your_subscription_name DISABLE; Manually apply the same schema modifications to the subscriber database to ensure alignment. Once the schema changes are applied to both databases, re-enable the subscription: ALTER SUBSCRIPTION your_subscription_name ENABLE; On Thu, 17 Oct 2024 at 02:59, Koen De Groote wrote: > If this question is more suitable for another mailing list, please let me > know. > > I've set up the following table on both publisher and subscriber, both are > pg16: > > CREATE TABLE dummy_table ( > id SERIAL PRIMARY KEY, > name VARCHAR(100) NOT NULL, > email VARCHAR(100) UNIQUE NOT NULL, > age INT, > created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP > ); > > Added to publication, refreshed subscription. > > Add some data on the publisher side: > INSERT INTO dummy_table (name, email, age) > VALUES > ('John Doe', 'john.doe@example.com', 25), > ('Jane Smith', 'jane.smith@example.com', 30), > ('Michael Johnson', 'michael.j@example.com', 45), > ('Emily Davis', 'emily.d@example.com', 27), > ('Robert Brown', 'robert.brown@example.com', 40); > > The data can be seen on the subscriber. So far, so good. > > I then execute the following patch on the publisher: > https://gist.github.com/KoenDG/d1c06d8c740c64e4e5884d0c64b81f11 > > It is a single transaction that does the following: > > 1/ Insert data, 1000 items > 2/ Drop a column > 3/ Alter a column name > 4/ Add 2 columns, nullable > 5/ Add a column and give it a unique constraint > 6/ Update values for a column with NULL values, added in step 4. > 7/ Set the column updated in step 6 to be NOT NULL > 8/ Create a unique index with the columns from step 3 and 6 > 9/ Insert a column with a default value > 10/ Insert data for this schema, another 1000 items. > > The subscription disabled, this is to be expected, there are new columns > names, the schema needs to be updated on the subscriber side. > > However, it seems I'm stuck. > > I can't enable the subscription. This is to be expected, it will try to > resume and run into the same issues. > > Ok, I update the schema and enable again. It runs into an error for the > inserts of step 1. These set values for columns dropped in step 2. > > I revert to the old schema and enable again. It runs into an error again, > this time for values that don't exist yet at step 1. > > I tried dropping the table at the subscriber side, recreating the correct > schema, but this runs into the same error. > > I remove the table from the publication and retry. Same error. Even with > the table no longer in the publication, and the table on the subscriber > side dropped and re-created, I'm still getting the exact same errors of > "logical replication target relation "public.dummy_table" is missing > replicated columns: "contact_email", "status", "phone_number", "username"" > > > The only solution I've found is to drop the table from the publication, > and then drop the entire subscription and set it back up again, with the > correct schema. > > Am I making a mistake? Or does putting all these commands in a single > transaction ruin my chances? > > Clarification much appreciated. > > Regards, > Koen De Groote > > --0000000000007ca6860624a4145e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi,
When you execu= te schema-altering operations on the publisher, these changes are not autom= atically replicated to the subscriber=C2=A0 which causes the following erro= r
logical replication target relation "public.dummy_table" is = missing replicated columns: "contact_email", "status", = "phone_number", "username"

Before making schema = changes, temporarily disable the subscription to prevent replication errors= .=C2=A0=C2=A0
ALTER SUBSCRIPTION your_subscription_name DISAB= LE;

Manually apply the same schema modificatio= ns to the subscriber database to ensure alignment.=C2=A0=C2=A0

Once the schema changes are applied to both databases, re-= enable the subscription:=C2=A0=C2=A0
ALTER SUBSCRIPTION your_= subscription_name ENABLE;


On Thu, 17 Oct 202= 4 at 02:59, Koen De Groote <kdg.dev= @gmail.com> wrote:
If this question is more suitable for anot= her mailing list, please let me know.

I've set= up the following table on both publisher and subscriber, both are pg16:

CREATE TABLE dummy_table (
id SERIAL PRIMARY KEY= ,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL, age INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
<= div>
Added to publication, refreshed subscription.
=
Add some data on the publisher side:
INSERT INTO d= ummy_table (name, email, age)
VALUES
('John Doe', 'john.doe@example.com', 25),
('Jane Smith', 'jane.smith@example.com', 30),
('= ;Michael Johnson', 'michael.j@example.com', 45),
('Emily Davis',= 'emily.d@exam= ple.com', 27),
('Robert Brown', 'robert.brown@example.com'= ;, 40);

The data can be seen on the subscriber. So= far, so good.

I then execute the following patch = on the publisher:=C2=A0https://gist.github.com/KoenDG/d1= c06d8c740c64e4e5884d0c64b81f11

It is a single = transaction that does the following:

1/ Insert dat= a, 1000 items
2/ Drop a column
3/ Alter a column name
4/ Add 2 columns, nullable
5/ Add a column and give it a= unique constraint
6/ Update values for a column with NULL values= , added in step 4.
7/ Set the column updated in step 6 to be NOT = NULL
8/ Create a unique index with the columns from step 3 and 6<= /div>
9/ Insert a column with a default value
10/ Insert data= for this schema, another 1000 items.

The subscrip= tion disabled, this is to be expected, there are new columns names, the sch= ema needs to be updated on the subscriber side.

Ho= wever, it seems I'm stuck.

I can't enable = the subscription. This is to be expected, it will try to resume and run int= o the same issues.

Ok, I update the schema and ena= ble again. It runs into an error for the inserts of step 1. These set value= s for columns dropped in step 2.

I revert to the o= ld schema and enable again. It runs into an error again, this time for valu= es that don't exist yet at step 1.

I tried dro= pping the table at the subscriber side, recreating the correct schema, but = this runs into the same error.

I remove the table = from the publication and retry. Same error. Even with the table no longer i= n the publication, and the table on the subscriber side dropped and re-crea= ted, I'm still getting the exact same errors of "logical replicati= on target relation "public.dummy_table" is missing replicated col= umns: "contact_email", "status", "phone_number&quo= t;, "username""


The = only solution I've found is to drop the table from the publication, and= then drop the entire subscription and set it back up again, with the corre= ct schema.

Am I making a mistake? Or does putting = all these commands in a single transaction ruin my chances?

<= /div>
Clarification much appreciated.

Regards,=
Koen De Groote

--0000000000007ca6860624a4145e--