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 1rxAmf-006o6W-R7 for pgsql-general@arkaria.postgresql.org; Wed, 17 Apr 2024 19:17:30 +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 1rxAme-00EjGf-6U for pgsql-general@arkaria.postgresql.org; Wed, 17 Apr 2024 19:17:28 +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 1rxAmd-00EjGS-N3 for pgsql-general@lists.postgresql.org; Wed, 17 Apr 2024 19:17:27 +0000 Received: from mail-io1-xd32.google.com ([2607:f8b0:4864:20::d32]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rxAma-001VYF-Gn for pgsql-general@lists.postgresql.org; Wed, 17 Apr 2024 19:17:27 +0000 Received: by mail-io1-xd32.google.com with SMTP id ca18e2360f4ac-7d5f0a155e3so3471639f.0 for ; Wed, 17 Apr 2024 12:17:24 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1713381442; x=1713986242; 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=8Iuh73lWWqg9PQiSCa4gEcw8bPLh/DHAb2dtacAfRYc=; b=FFTzVJ/Q4BZDKQBrHDlEpKib8D7Pp8WoabYqZSh8Z7KxI2w4QTOIcMcwYKC9q8VUbc 0SD0uZQxLUjOj1H3yGvsoytNrt5rjqw4DGAL9GdlLStSTiClyLqacPWNcWthqcFCwFaD gU5WcW94KOrlMn9FwKX9aa5YSCK3Wko5OarHpENx5AnKd6lTIhhANcmAWnwlDcTXxGTa AkhE+e40KVA48TzmN+3CCVPUDtrgO52hSLf1pb9/99iDsFfPsGrEbkZGX2Z0jDMvP0nY hQtFrbyKLbf3Wr3qMXf6pDKTEYu59Z3WtTMFSMC+ST1mFpRKO1+3JMTF+ZttaJ+lvhxL 2qTA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1713381442; x=1713986242; 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=8Iuh73lWWqg9PQiSCa4gEcw8bPLh/DHAb2dtacAfRYc=; b=q2W59Yr4K3nRayN/83Rd25T6185xwjkf/PKYl8b0nLFp0njqUpAymC/rNNIqVf9wIh eyJ0cG5H8cmarrKkknAObDhRKbHNV9mo10G3gW728OlQHMkHpKGMVGv8syu61pL0SLow CxOfX0w7BSixyhg3gpuBuaBLmmV88BDBRKArPNLPcyOTJdCX62zi2J2/T27DlKRvD0PJ Ui5VnU6FT0/gVb/hs+/+JeVuo3N9lyeqv/GRC2JMy7baprPnsxuy9L7S1W3jQaamC15E uti6pbu4F29H8HSahaMw3z+Jtnhu3v3BTirNrdf2SZnJMR2991umlnAb/jITJ3DrmWjc A1mQ== X-Gm-Message-State: AOJu0YzD7K9p0Sx4j+IMPYKChgN4Rs5dC4KMm1Sm/6MQjPkcCPul6IbL 0mDwN37g9Sn2dQHoRJyquD79L1Lqd6FsvYKcbUM3F9lIR2FkzgEEzFzNBNVUirFL1QZyPzE9DVf OOzmI0E8jvbcyuXgu4NHxfyJX9rQ= X-Google-Smtp-Source: AGHT+IF9Gxjx+mhRBSz8u+5xzhlz/udD0xIBy7XVFvTjXCvYCfSZKbJWeV14gy5NO1M+iWuiIdVJjNfXjAw9B+x4AH4= X-Received: by 2002:a5e:8d08:0:b0:7d5:e4ae:e697 with SMTP id m8-20020a5e8d08000000b007d5e4aee697mr750190ioj.10.1713381442110; Wed, 17 Apr 2024 12:17:22 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Justin Date: Wed, 17 Apr 2024 15:17:10 -0400 Message-ID: Subject: Re: replication slot "pg_1015733_sync_1014718_7358407382484881476" does not exist To: Avi Weinberg Cc: "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="00000000000038cd3406164fb456" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000038cd3406164fb456 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 =3D 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-REPLICATI= ON On Wed, Apr 17, 2024 at 11:17=E2=80=AFAM Avi Weinberg wrot= e: > Fixed a typo=E2=80=A6 > > > > 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 > *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" LOGICA= L > 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 name= d > addressee(s), and may contain information which is confidential or > privileged. If you are not the intended recipient, please inform the send= er > 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 name= d > addressee(s), and may contain information which is confidential or > privileged. If you are not the intended recipient, please inform the send= er > immediately and delete this email: you should not copy or use this e-mail > for any purpose nor disclose its contents to any person. > --00000000000038cd3406164fb456 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi=C2=A0Avi,

Based on the slot name this is an init= ial sync worker being created by the Logical Replication supervisor.=C2=A0 = Subscriber=C2=A0started an initial sync either=C2=A0 failed to create the= =C2=A0slot and now thinks it exists and keeps trying to drop it on the publ= isher 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=C2=A0 table can be = figured from the slot name=C2=A0 pg_1015733_sync_1014718_735840738248488147= 6;=C2=A0 the number 1014718 is the oid of the table on the subscriber.
<= br>I suspect the table=C2=A0 1014718 is empty and not being replicated
<= br>Step to clean this up
1: remove the table from the publication=C2=A0<= br>2: refresh the subscription on the subscriber

This typically=C2= =A0fixes it.

If the table is empty then can simply add the table bac= k to the publication and refresh the subscription.=C2=A0 If not need to tru= ncate the table watch out for foreign=C2=A0key constraints on the table.=C2= =A0 If foreign key constraints=C2=A0 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 wi= th the=C2=A0copy_data =3D FALSE=C2= =A0=C2=A0set.=C2=A0=C2=A0

If that does not work will need to = be manually cleaned up to avoid a full resync of all the tables.=C2=A0
= Dropping the replication origin=C2=A0see the function=C2=A0pg_replication_o= rigin_drop().=C2=A0 If the table is not removed the subscription PG will re= create the origin.=C2=A0 The only way to remove a table from a subscription= SAFELY is refresh the subscription after the table been removed from the p= ublication=C2=A0 =C2=A0=C2=A0

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


On Wed, Apr 17, 2024 at 11:17=E2=80=AFAM Avi Weinberg <AviW@gilat.com> wrote:

Fixed a typo=E2=80=A6

=C2=A0

Hi Experts,

=C2=A0

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

Any ideas what is wrong?

=C2=A0

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

=C2=A0

Hi Experts,

=C2=A0

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

Any ideas what is wrong?

=C2=A0

=C2=A0

ERROR:=C2=A0 replication slot "pg_1015733_sync_1014718_73584073= 82484881476" does not exist

STATEMENT:=C2=A0 DROP_REPLICATION_SLOT pg_1015733_sync_1014718_73584= 07382484881476 WAIT

ERROR:=C2=A0 replication slot "pg_1015733_sync_1014956_73584073= 82484881476" does not exist

STATEMENT:=C2=A0 DROP_REPLICATION_SLOT pg_1015733_sync_1014956_73584= 07382484881476 WAIT

192 ERROR:=C2=A0 replication slot "pg_927671_sync_927281_7358103111= 795036228" does not exist

START_REPLICATION SLOT "pg_927671_sync_927281_73581031117950362= 28" LOGICAL 0/AC888F00 (proto_version '3', publication_names &= #39;"aaa","bbb"')

replication slot "pg_1015733_sync_1015375_7358407382484881476&q= uot; does not exist

DROP_REPLICATION_SLOT pg_1015733_sync_1015375_7358407382484881476 WA= IT

=C2=A0

=C2=A0

Thanks

IMPORTANT - This email and any attach= ments is intended for the above named addressee(s), and may contain informa= tion which is confidential or privileged. If you are not the intended recipient, please inform the se= nder immediately and delete this email: you should not copy or use this e-m= ail 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 privileg= ed. If you are not the intended recipient, please inform the sender immedia= tely and delete this email: you should not copy or use this e-mail for any purpose nor disclose its conten= ts to any person.
--00000000000038cd3406164fb456--