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 1siq5R-00A1Ip-41 for pgsql-general@arkaria.postgresql.org; Tue, 27 Aug 2024 06:53:53 +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 1siq5P-002tiw-7h for pgsql-general@arkaria.postgresql.org; Tue, 27 Aug 2024 06:53:51 +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 1siq5O-002thu-Mi for pgsql-general@lists.postgresql.org; Tue, 27 Aug 2024 06:53:51 +0000 Received: from mail-am6eur05on2112.outbound.protection.outlook.com ([40.107.22.112] helo=EUR05-AM6-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1siq5L-001iRf-CQ for pgsql-general@lists.postgresql.org; Tue, 27 Aug 2024 06:53:50 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector10001; d=microsoft.com; cv=none; b=FZATDGsajqSbCTEJV/0/yOF7EowEdvgv4+Tun4I70oSitObq9Nj8dOsn46YrbSskxksuXtiWci+5K769VJqr2aEb9GD2LeqZFcQYqBVAyspFvRZcjZPQhtQ+/cj2Kp9zPYvedEk58IrAG4/aS9+69Dc/TmlKnlKYiwP9GyVfFxt0IJk/fRDjesW6XloA+FH1OoHUPJV93VYAK7DtPuyKYdthYfX8+P7mZHphqBzDvcLjXZutCvcg53LV+mCn/OsFNYUGa4zFGqXFswlC+RpViKT5pWR87Gai1Eevu4H5WixEuYBvY1DmKwjdMX6ENXssmYKX51OQNff7djTG5JqFJg== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector10001; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-AntiSpam-MessageData-ChunkCount:X-MS-Exchange-AntiSpam-MessageData-0:X-MS-Exchange-AntiSpam-MessageData-1; bh=ANbWqcth5HGXyUoTS1zrq7L5Afqth6UP3XSxk6Kiyws=; b=TKv+9DZTF19dKjVRcj7tu7cuZjtsXyJBG9MCxkeRo4IA05hVukaVdzZZahlq7db15jmsUt9wFKnZK6YtOsRuGJh2a4b/g1ZXO9TBAyrNbnd7y6qD4wPw1U0Tq8bk5lkO483hDo0u00u2UONdjGJM+KMgUzXQ6eVJUIQl/53Raw4wJEZV2ArEp/+YKc1e1m0C/ntnMPUJwYbU5yy8jIVpBB00CwvarELEutyJRKgFGQr6LTGF/wlPJuXz5jziOrEMIxfC3E4HYwdd0w/MxVptNPfvDfmxiyGMFtV75C+vRzHdWRLlLlabA8UlAeBIdKT05GV0yskybyThV1W/31geMA== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=pass smtp.mailfrom=gilat.com; dmarc=pass action=none header.from=gilat.com; dkim=pass header.d=gilat.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gilat.com; s=selector1; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=ANbWqcth5HGXyUoTS1zrq7L5Afqth6UP3XSxk6Kiyws=; b=q+2fWKOVaNiO9x3M9EmyZGZl8sbqkDsIpZK3I4/A2UGWNjAnPozzdqNYhMH2sIff8dm7advW/zOp7JWyKzw/MBT9EkYba4aXJEwTPTi0cXmQCnfOgJRRqO93W6+Rt8HzpwuQJhX8RJukOsYizs+KYhff+IYIAfC3qi0TKuV7XAA= Received: from DB9PR07MB7180.eurprd07.prod.outlook.com (2603:10a6:10:215::5) by GV1PR07MB8950.eurprd07.prod.outlook.com (2603:10a6:150:3d::15) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.7897.24; Tue, 27 Aug 2024 06:53:43 +0000 Received: from DB9PR07MB7180.eurprd07.prod.outlook.com ([fe80::270e:36ee:e6c1:1c92]) by DB9PR07MB7180.eurprd07.prod.outlook.com ([fe80::270e:36ee:e6c1:1c92%4]) with mapi id 15.20.7897.021; Tue, 27 Aug 2024 06:53:42 +0000 From: Avi Weinberg To: pgsql-generallists.postgresql.org Subject: logical replication - who is managing replication slots created automatically during initial sync Thread-Topic: logical replication - who is managing replication slots created automatically during initial sync Thread-Index: Adr4TdnvtA4d6G55Qs+lRcv5YbVvOw== Date: Tue, 27 Aug 2024 06:53:42 +0000 Message-ID: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: authentication-results: dkim=none (message not signed) header.d=none;dmarc=none action=none header.from=gilat.com; x-ms-publictraffictype: Email x-ms-traffictypediagnostic: DB9PR07MB7180:EE_|GV1PR07MB8950:EE_ x-ms-office365-filtering-correlation-id: 5be8a963-f3c4-4606-7648-08dcc664fd4b x-ms-exchange-senderadcheck: 1 x-ms-exchange-antispam-relay: 0 x-microsoft-antispam: BCL:0;ARA:13230040|376014|1800799024|366016|38070700018; x-microsoft-antispam-message-info: =?us-ascii?Q?ScBjnLTDBmpZX/SA44M5GyegKhcfsYtJMpOa21p9XqN7QYd2KvWjou1ERaww?= =?us-ascii?Q?5wFkmPVyJD831KCepV7BTVDKH4bPTTFM/rhisGuMld6HVJtFeF5RRRqt+cSX?= =?us-ascii?Q?J8FVd22yvmCiJKRZpo6JsTkrcqhnn2KGppAMIxmd/rTfW3JiIBZbnam5/0a7?= =?us-ascii?Q?w7uUXCw/FIlEevVuY+CX65wTFRnVJL2dAr+deok+T8K12NW3fNbvF4tLrx65?= =?us-ascii?Q?QqcJ9ha/63I1foxuYkNQEKLN9HJHKaJ7f5u6gIZ+5SitcOwADFtnCWIkjMgM?= =?us-ascii?Q?HFKBqdcmtUxQxj5T5NOBmsu1Pe6YhgZKWetGliQLYC57j6BG4kiPdmDPlTTv?= =?us-ascii?Q?JQQ8AK+eoGTalmrRRidKUAGYMkOab0BaDw6sa5y00JnsxJFGylKBSCmIMsut?= =?us-ascii?Q?OSRAGGVSU1Z76TaBTntbOJ5mFJRYWCQVu9h2/entV16Nir6DGRcEc6eGK2wW?= =?us-ascii?Q?JSCgSZ+yhZQHX8Z8TfCFoptOaGb/hArb9gyUBlmAy33Wms4lO8boNqLk+GJy?= =?us-ascii?Q?N0b7rHPvH755yCUDtbfgDLeSM+oMCdcSIfe2p8EEJ5zsHX1EvXAnkh9JrWFq?= =?us-ascii?Q?FPF+xU54vQOtzGOwv32gVsB3QvadfpPkAZAkbMGTZM+ZPM8PYhhi3IlbWZVr?= =?us-ascii?Q?tRdWnZx7JKjDdb9dh7EutfCodkEjpvw5HF31TPQ5Emn1oaFPCvnNBqq1KG4t?= =?us-ascii?Q?pw8FGKhnlnN31k6u015BKMOqkbYB2u3qnDHpkEbkgv1BSNOhY2FizKKcn9us?= =?us-ascii?Q?OFanT2lZiehAuKmOdgGVp8X4yVZdLatg4dMX1nw2KcMPorHCsgncz11fqGTq?= =?us-ascii?Q?CY9/vE250cIqWu9+hrsBqNQi4/phpMqte3vrTCNgYhpM44ZMUc0VZSQ8rSod?= =?us-ascii?Q?nJi+h68A+bWIjY/GlgNRB5uyXvdbOVDyR0ryThentWZwgx1c8HbIAsl1ohVQ?= =?us-ascii?Q?4+i0PTeXoC9mpvQMmq2HSKKl451g+dT5ASnyd1yIjHPH7qBr3s1yW9Gvk0N0?= =?us-ascii?Q?58YubCmVNEX5X5Zji7Y1S6cn/E+S0Hiir4LGTJjFXyP1Gs9qnSCtqW9xaKqh?= =?us-ascii?Q?xkghZ4v2ZFyYWfq6dF3mdbgHEGS62PeKi/isLXxgJnYq8NiZgV5JumcBCli0?= =?us-ascii?Q?O77VxMAMh3ZesdJvgUD/BtqsmZqXcZBVaFYc6EWGNKeSUrOiefbxFFuY8mNZ?= =?us-ascii?Q?lxU2kKYVp/FUM7t7Dm9Oo/6dcbasMYfzgsXkE1e5t0TEHyKIA1QGUn95ttrz?= =?us-ascii?Q?KgR+BY+Lw2RNMxbkrjninMjyPnAzAdwj7bBm5bVUFPD1uZg32+6YNWejrMPq?= =?us-ascii?Q?Cju+Cph48OrNoAhM8Rx0p1aZNcLkfjfprFvHTltAId2QHHOZ2uaqjLdyJYpd?= =?us-ascii?Q?J8NItNRBVuyIyEqE3YstmwqTWoHVJ9Ou9P/J4gIUFTS/rB08rA=3D=3D?= x-forefront-antispam-report: CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:DB9PR07MB7180.eurprd07.prod.outlook.com;PTR:;CAT:NONE;SFS:(13230040)(376014)(1800799024)(366016)(38070700018);DIR:OUT;SFP:1102; x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?us-ascii?Q?Jk8Hu966qDiRGPJId2ZPPRMFnRIrdQczdFO0/J54u/ysv13BcPSzaenQdLuJ?= =?us-ascii?Q?09faxBbHG3/k41DD8wZT3bGGeLHazqVGFCi97xBHRYnLR6pZqCVaPYxK/ZlA?= =?us-ascii?Q?mnyzVK9GNMJbxyO+U7QvbiAGbahuDGfxGfQ6t0U8NMCHpQ6ZrNQKAYP3Kfxn?= =?us-ascii?Q?wylJN6uOE31UKhKPiWUNKbRWv5Z/BYTaVegVj7qB/Cz0BotAdpw7XuPXTupV?= =?us-ascii?Q?isyiFl4McPaSDUWjIS7QG/bFudrw6pVNllJLcr6Z2Bfxmtkc9DtavomJh7aq?= =?us-ascii?Q?BwPMRaHjQt6v+HRJwEV2akUItRs0EGWhX/4r6kN706PcHxSGiY2s0+/eSTO3?= =?us-ascii?Q?MUj8WeRC2dCckf3E4bhzp/OitvfyaFt4YZwLVjE8DgalAJ+rHc+OD/iAz+RF?= =?us-ascii?Q?ibNK5CQljw1yWBUoNMkyXzh0xOyg6UiYcYhVgYD6UoKdPjFqYswMVWP7c4VV?= =?us-ascii?Q?xutyjv9LHy7e5bqvzAV8qKafwInY2cjrbO9zMqIrJDr/TqeqhRxavfzq4u3R?= =?us-ascii?Q?p70qg5XMQE22TMmxAyZf1xZMVqyRpWjs3RDiIF4XnvUj+Z0r1e1jjC5D/BFD?= =?us-ascii?Q?G4TYPHxGO7FF0A5+dE3BbNedl/bj5WoNhFc0g1EuGJbp59Vm9uKJZtM6LDO4?= =?us-ascii?Q?cmddDvo6GaC6vhavvn54caCpfvrXIxoo/Lo/jvGASLHbf1avnQWn3fYsRe6P?= =?us-ascii?Q?nR/fYouKNkekwszL70NHoZQtkSejrcCR+Ro5N/2qhLjg3eZzxAoWHl83oIA+?= =?us-ascii?Q?mK1YH7byt2pToksUjOu7Q+0bErTR/m1ZY/aL8MRDkTIrTbyhgJepA7jM3LCP?= =?us-ascii?Q?FmTtmx4bTaFrDHZTn9H5tba/JvcAizkEOPmfYeyA7Nbft/fxzEqiFuQJGKoO?= =?us-ascii?Q?8UXxOpYxZ/V1gW9S4r2ksa0Nxs/Nc9nljRd72JxsCjPsxgOqL8BZBkwjQos3?= =?us-ascii?Q?BpqW2xLAyGBuQOFitGMZk2EovYeNiJYBXvoS0DtK2Rx14pTzfkmJHJ9Bpt09?= =?us-ascii?Q?jo0CuBKlQsJH5XUcDJ868lgkK4DISaHBfY5LvyBCylXkiXiVANzyxgHnQsHo?= =?us-ascii?Q?58KRMupxLdpSqMTRV8DatKmhW+Fnb51wYDqfcytGUSFsGSuhfSkYliFT6Ali?= =?us-ascii?Q?ijSW05EkJQQyRPViolCJaQxc7PZxKCMfJzNXY4QMhmpfSL8RABp+zXdDUNc8?= =?us-ascii?Q?fCypMATuS3HnmQUU6FhXE0lqoy0hnOt1MMQ8P0ZOrcDoebbRemMP7wBiJy15?= =?us-ascii?Q?AkGFX8231Chez3PzmIRYMU7V5oCdUE+uyEl0IDd5fHR22aUE/swH1bCFAxDr?= =?us-ascii?Q?5y42bn7M7wcTOM9Q5SbpgJNZY47rbSEkNbtzg9Mi/pPBeI8DL/R8jhXe/EGR?= =?us-ascii?Q?LlIDyAK4/c+N0whc1N9a3aEkU+eJGp5TC2FckNiBTjVC1tvwbW4c4PROBZHU?= =?us-ascii?Q?h4JAkVqvSL2Gjh1ctylLXvGGVkAZloUwGH1GtHwmCqCOpx+piosNpue82T8l?= =?us-ascii?Q?eHqzFvYZ2akx93EmibUzd1mYAQRjf9nGgNWnc7bZzSTY/7PbLMLmFjJtAAlM?= =?us-ascii?Q?5bobAZPaj38nuvqn4zg=3D?= Content-Type: multipart/alternative; boundary="_000_DB9PR07MB7180EA18C0A832E3B59D18B8CB942DB9PR07MB7180eurp_" MIME-Version: 1.0 X-OriginatorOrg: gilat.com X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: DB9PR07MB7180.eurprd07.prod.outlook.com X-MS-Exchange-CrossTenant-Network-Message-Id: 5be8a963-f3c4-4606-7648-08dcc664fd4b X-MS-Exchange-CrossTenant-originalarrivaltime: 27 Aug 2024 06:53:42.8002 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 7300b1a3-573a-4010-92a6-1c65cd85e927 X-MS-Exchange-CrossTenant-mailboxtype: HOSTED X-MS-Exchange-CrossTenant-userprincipalname: Zbp26N353nCr4vOqpmP5SELAWjqQP+zNoDHhOSeVbSZppyiHIvii21bOCHka9whz X-MS-Exchange-Transport-CrossTenantHeadersStamped: GV1PR07MB8950 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_DB9PR07MB7180EA18C0A832E3B59D18B8CB942DB9PR07MB7180eurp_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi Experts I have seen that logical replication slots created automatically by Postgre= s during initial sync (a slot per table), are marked as "wal_status =3D los= t" and "active =3D false". 1. Who is responsible for removing those faulty replication slots? 2. Can a slot with "wal_status =3D lost" recover from this state? 3. Do I need to drop the subscription in such a case? 4. Are those replication slots that synchronize a single table each, u= se a connection from "max_connections" and replication slot from "max_logic= al_replication_slots"? 5. If I sync many tables will it be better to increase the number of m= ax_logical_replication_slots or to have some of the tables "wait" for other= tables to complete and release the replication slot for them to use. I'm using Postgres 15.2 slot_name plugin slot_type datoid database temporary active act= ive_pid xmin catalog_xmin restart_lsn confirmed_flush_lsn wal_status= safe_wal_size two_phase pg_8034820_sync_8033089_7371741997992267844 pgoutput logical 16707 aaa= _db FALSE FALSE NULL NULL 295098502 NULL D9/EB7317B0 lost = NULL FALSE pg_6839631_sync_6837833_7371741997992267844 pgoutput logical 16707 aaa= _db FALSE FALSE NULL NULL 288349892 NULL D2/80068A78 lost = NULL FALSE Thanks! 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 contents to any person. --_000_DB9PR07MB7180EA18C0A832E3B59D18B8CB942DB9PR07MB7180eurp_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi Experts

 

I have seen that logical replication slots created automat= ically by Postgres during initial sync (a slot per table), are marked as &q= uot;wal_status =3D lost" and "active =3D false".<= /span>

1.  = ;    Who is responsible for removing those faulty replication slots?

2.  = ;    Can a slot with "wal_status =3D lost" recover from this= state?

3.  = ;    Do I need to drop the subscription in such a case?

4.  = ;    Are those replication slots that synchronize a single table each,= use a connection from "max_connections" and replication slot from "max_logical_replication_slots"?

5.  = ;    If I sync many tables will it be better to increase the number of= max_logical_replication_slots or to have some of the tables "wait" for other tables to complete and release th= e replication slot for them to use.

 

I'm using Postgres 15.2

slot_= name   plugin  slot_type   datoid  database&n= bsp;   temporary   active  active_pid  xmin&n= bsp;   catalog_xmin    restart_lsn confirmed_flush= _lsn wal_status  safe_wal_size   two_phase

pg_80= 34820_sync_8033089_7371741997992267844 pgoutput    logical 1= 6707   aaa_db  FALSE   FALSE   NULL = ;   NULL    295098502   NULL    D9/EB7317B0 lost    NULL  =   FALSE

pg_68= 39631_sync_6837833_7371741997992267844 pgoutput    logical 1= 6707   aaa_db  FALSE   FALSE   NULL = ;   NULL    288349892   NULL    D2/80068A78 lost    NULL  =   FALSE

=  

Thanks!

 

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. --_000_DB9PR07MB7180EA18C0A832E3B59D18B8CB942DB9PR07MB7180eurp_--