Thread: Logical Replication not working for few Tables
Hi All,
I configured logical replication some time back .Now except for 4 table replication is happening.Few weeks back replication got stopped because of some conflict and these 4 tables were part of that conflict. Someone resolved the conflict but replication has not started for these four tables .I dropped these tables from publication and added it back and refreshed the subscription but still replication is not happening for these four . For testing I created a new table and added it to publication and refreshed subscription , that was sussues.
Is there any system table where these 4 tables are marked as conflict and not to be replicated?
Why is replication not happening only to those four tables.is this a bug?
Thanks,
Padmakkumar
On Fri, Nov 12, 2021, at 11:36 AM, Padmakumar Kadayaprth wrote:
I configured logical replication some time back .Now except for 4 table replication is happening.Few weeks back replication got stopped because of some conflict and these 4 tables were part of that conflict. Someone resolved the conflict but replication has not started for these four tables .I dropped these tables from publication and added it back and refreshed the subscription but still replication is not happening for these four . For testing I created a new table and added it to publication and refreshed subscription , that was sussues.
You didn't provide enough information. Is it native logical replication or
another tool (pglogical, Slony, etc)? What's the exact version?
Is there any system table where these 4 tables are marked as conflict and not to be replicated?
There is no system table/view that shows the conflicts. You have to inspect your
log files.
Why is replication not happening only to those four tables.is this a bug?
It is hard to guess without at least the error messages.
I am using postgresql 11 on windows.it is native logical replication.
I am really stuck .
I am thinking of doing a work around.
After dropping four tables from publication,
if i am creating four new tables ,import data from old tables to new tables ,drop old tables and rename newly created tables to the actual table name which i dropped and add it back to publication and refresh subscription .
Does this step help me in fixing this issue because the id of the relation will change even though I have the same table name.
Any thoughts?
Thanks,
Padmakumar
On Fri, Nov 12, 2021 at 11:00 PM Euler Taveira <euler@eulerto.com> wrote:
On Fri, Nov 12, 2021, at 11:36 AM, Padmakumar Kadayaprth wrote:I configured logical replication some time back .Now except for 4 table replication is happening.Few weeks back replication got stopped because of some conflict and these 4 tables were part of that conflict. Someone resolved the conflict but replication has not started for these four tables .I dropped these tables from publication and added it back and refreshed the subscription but still replication is not happening for these four . For testing I created a new table and added it to publication and refreshed subscription , that was sussues.You didn't provide enough information. Is it native logical replication oranother tool (pglogical, Slony, etc)? What's the exact version?Is there any system table where these 4 tables are marked as conflict and not to be replicated?There is no system table/view that shows the conflicts. You have to inspect yourlog files.Why is replication not happening only to those four tables.is this a bug?It is hard to guess without at least the error messages.
On Fri, Nov 12, 2021 at 8:06 PM Padmakumar Kadayaprth <padmakumark26@gmail.com> wrote: > > Hi All, > I configured logical replication some time back .Now except for 4 table replication is happening.Few weeks back replicationgot stopped because of some conflict and these 4 tables were part of that conflict. Someone resolved the conflictbut replication has not started for these four tables .I dropped these tables from publication and added it backand refreshed the subscription but still replication is not happening for these four . > The refresh won't do anything if the tables have already been replicated previously. > For testing I created a new table and added it to publication and refreshed subscription , that was sussues. > > Is there any system table where these 4 tables are marked as conflict and not to be replicated? > No. > Why is replication not happening only to those four tables.is this a bug? > It is quite surprising that replication changes for a few tables is getting skipped whereas it is successful for others. One guess is that new changes are updates and deletes of rows that didn't exist in the tables on subscriber-side, as one might have removed/changed those rows while resolving conflicts. If that happens then we log the message at DEBUG1 log level. I think you can try to construct an independent test on the same lines and resolve conflict in a similar way and then see what is happening? -- With Regards, Amit Kapila.
I created new tables and renamed the old tables like table_old .
ex: Table Node to node_old and node_new to node
But still replication is not happening with the four tables .I added node_old to publication and subscribed to it .It worked fine .
What I am seeing is a table with a specific name is not getting replicated . I am not sure why .
On Tue, Nov 16, 2021 at 7:42 AM Amit Kapila <amit.kapila16@gmail.com> wrote:
On Fri, Nov 12, 2021 at 8:06 PM Padmakumar Kadayaprth
<padmakumark26@gmail.com> wrote:
>
> Hi All,
> I configured logical replication some time back .Now except for 4 table replication is happening.Few weeks back replication got stopped because of some conflict and these 4 tables were part of that conflict. Someone resolved the conflict but replication has not started for these four tables .I dropped these tables from publication and added it back and refreshed the subscription but still replication is not happening for these four .
>
The refresh won't do anything if the tables have already been
replicated previously.
> For testing I created a new table and added it to publication and refreshed subscription , that was sussues.
>
> Is there any system table where these 4 tables are marked as conflict and not to be replicated?
>
No.
> Why is replication not happening only to those four tables.is this a bug?
>
It is quite surprising that replication changes for a few tables is
getting skipped whereas it is successful for others. One guess is that
new changes are updates and deletes of rows that didn't exist in the
tables on subscriber-side, as one might have removed/changed those
rows while resolving conflicts. If that happens then we log the
message at DEBUG1 log level.
I think you can try to construct an independent test on the same lines
and resolve conflict in a similar way and then see what is happening?
--
With Regards,
Amit Kapila.
Padmakumar Kadayaprth <padmakumark26@gmail.com> writes:
> What I am seeing is a table with a specific name is not getting replicated
> . I am not sure why .
What's the problematic name exactly? If it involves upper-case letters,
maybe you were sloppy about double-quoting it on one or both ends of the
setup?
regards, tom lane