Subject: Re: How to debug logical replication error "columns are missing" if
 they are not
To: pgsql-admin@lists.postgresql.org
References: <6d2d142f-58b4-d2db-b271-700182b68b55@gmx.net>
From: Thomas Kellerer <shammat@gmx.net>
Message-ID: <86ce53d9-5b46-d652-78d4-2bdac368a6b0@gmx.net>
Date: Tue, 25 Aug 2020 07:51:50 +0200
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.23)
 Gecko/20090812 Thunderbird/2.0.0.23 Mnenhy/0.7.6.666
MIME-Version: 1.0
In-Reply-To: <6d2d142f-58b4-d2db-b271-700182b68b55@gmx.net>
Content-Type: text/plain; charset=utf-8
Content-Language: de-DE
Content-Transfer-Encoding: quoted-printable
Precedence: bulk

Thomas Kellerer schrieb am 25.08.2020 um 07:32:
> we have a a logical replication from Postgres 11 to Postgres 12.
>
> We did some structural changes to the published tables. The changes
> to the subscriber where applied about 60 minutes later then those on
> the publisher. Obviously before the subscriber's tables where synced
> the replication failed.
>
> But even though the tables are now completely identical, the subscriber =
still claims:
>
>     logical replication target relation "public.employee" is missing som=
e replicated columns
>
> I double checked multiple times that the tables are now identical by
> doing a diff of the "pg_dump -s" outputs - they are identical in the
> CREATE TABLE part.
>
> The table is listed with state "r" (ready) in pg_subscription_rel
>
> How can I find out which columns it thinks are missing? And how do I
> convince the subscriber that the tables *are* identical?


This is getting stranger:

I dropped the table from the publication.

After refreshing the subscription the error is still shown in the logfile =
even though that table shouldn't be replicated any more.
Is it possible that the error message mentions an unrelated table?

How can I find out which table really causes the problem?

Thomas