Re: Logical replication disabled, recovery not possible because of 1 large transaction with schema changes?

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 <kdg(dot)dev(at)gmail(dot)com> 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(dot)doe(at)example(dot)com', 25),
> ('Jane Smith', 'jane(dot)smith(at)example(dot)com', 30),
> ('Michael Johnson', 'michael(dot)j(at)example(dot)com', 45),
> ('Emily Davis', 'emily(dot)d(at)example(dot)com', 27),
> ('Robert Brown', 'robert(dot)brown(at)example(dot)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
>
>