Re: replication slot "pg_1015733_sync_1014718_7358407382484881476" does not exist - Mailing list pgsql-general

From Justin
Subject Re: replication slot "pg_1015733_sync_1014718_7358407382484881476" does not exist
Date
Msg-id CALL-XePuN-U_GKCD1-a8tTNT3VJ_=dhqXE9Tm=o4qJAgH1jHTw@mail.gmail.com
Whole thread Raw
In response to replication slot "pg_1015733_sync_1014718_7358407382484881476" does not exist  (Avi Weinberg <AviW@gilat.com>)
List pgsql-general
Hi Avi,

Based on the slot name this is an initial sync worker being created by the Logical Replication supervisor.  Subscriber started an initial sync either  failed to create the slot and now thinks it exists and keeps trying to drop it on the publisher or another process dropped the slot on the publisher before the sync was completed .

To fix this first identify the table involved so you know which table is causing the issue. The name of the  table can be figured from the slot name  pg_1015733_sync_1014718_7358407382484881476;  the number 1014718 is the oid of the table on the subscriber.

I suspect the table  1014718 is empty and not being replicated

Step to clean this up
1: remove the table from the publication 
2: refresh the subscription on the subscriber

This typically fixes it.

If the table is empty then can simply add the table back to the publication and refresh the subscription.  If not need to truncate the table watch out for foreign key constraints on the table.  If foreign key constraints  can drop them then truncate the table or using the replication role session variable to truncate the table or use some other means to catch the table backup and refresh the subscription with the copy_data = FALSE  set.  

If that does not work will need to be manually cleaned up to avoid a full resync of all the tables. 
Dropping the replication origin see the function pg_replication_origin_drop().  If the table is not removed the subscription PG will recreate the origin.  The only way to remove a table from a subscription SAFELY is refresh the subscription after the table been removed from the publication    

https://www.postgresql.org/docs/current/catalog-pg-replication-origin.html
https://www.postgresql.org/docs/15/functions-admin.html#FUNCTIONS-REPLICATION


On Wed, Apr 17, 2024 at 11:17 AM Avi Weinberg <AviW@gilat.com> wrote:

Fixed a typo…

 

Hi Experts,

 

For a second time in the past few months I'm getting the following errors in Postgres log.  Last time it was solved when I reset all Postgres pods.  Now reset no longer helps.  Logical replication is not working even after I performed the reset.

Any ideas what is wrong?

 

From: Avi Weinberg <AviW@gilat.com>
Sent: Wednesday, April 17, 2024 6:01 PM
To: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: [External] replication slot "pg_1015733_sync_1014718_7358407382484881476" does not exist

 

Hi Experts,

 

For a second time in the past few months I'm getting the following errors in Postgres log.  Last time it was solved when I reset all Postgres pods.  Now reset no longer helps.  Logical replication is now working even after I performed the reset.

Any ideas what is wrong?

 

 

ERROR:  replication slot "pg_1015733_sync_1014718_7358407382484881476" does not exist

STATEMENT:  DROP_REPLICATION_SLOT pg_1015733_sync_1014718_7358407382484881476 WAIT

ERROR:  replication slot "pg_1015733_sync_1014956_7358407382484881476" does not exist

STATEMENT:  DROP_REPLICATION_SLOT pg_1015733_sync_1014956_7358407382484881476 WAIT

192 ERROR:  replication slot "pg_927671_sync_927281_7358103111795036228" does not exist

START_REPLICATION SLOT "pg_927671_sync_927281_7358103111795036228" LOGICAL 0/AC888F00 (proto_version '3', publication_names '"aaa","bbb"')

replication slot "pg_1015733_sync_1015375_7358407382484881476" does not exist

DROP_REPLICATION_SLOT pg_1015733_sync_1015375_7358407382484881476 WAIT

 

 

Thanks

IMPORTANT - This email and any attachments is intended for the above named addressee(s), and may contain information which is confidential or privileged. If you are not the intended recipient, please inform the sender immediately and delete this email: you should not copy or use this e-mail for any purpose nor disclose its contents to any person.

IMPORTANT - This email and any attachments is intended for the above named addressee(s), and may contain information which is confidential or privileged. If you are not the intended recipient, please inform the sender immediately and delete this email: you should not copy or use this e-mail for any purpose nor disclose its contents to any person.

pgsql-general by date:

Previous
From: Marcin Giedz
Date:
Subject: Re: Performance degradation after upgrading from 9.5 to 14
Next
From: negora
Date:
Subject: Why does it sort rows after a nested loop that uses already-sorted indexes?