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 1uvdPI-00GVke-Al for pgsql-general@arkaria.postgresql.org; Mon, 08 Sep 2025 15:03:49 +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 1uvdPG-005GQl-6E for pgsql-general@arkaria.postgresql.org; Mon, 08 Sep 2025 15:03:46 +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 1uvdPF-005GQQ-LW for pgsql-general@lists.postgresql.org; Mon, 08 Sep 2025 15:03:46 +0000 Received: from mail-francesouthazolkn19011024.outbound.protection.outlook.com ([52.103.39.24] helo=MRWPR03CU001.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1uvdPC-001Ddk-1d for pgsql-general@lists.postgresql.org; Mon, 08 Sep 2025 15:03:45 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector10001; d=microsoft.com; cv=none; b=VzAoMgz+xyo3tjstA8jxtmA9yWBp7MB6I28C7N8WOSgJmvyQzExiQagr/zXnnC6MvgZdilvofnXNmWjr7XCUyHzwruKAPFWZQOcNKdxr8LSVQgMECQX3ePsTqmLLolkMTYFFFpXr5aLYC+UezvH/mGbKoUvPGlC58H8q5rmT6SBVkFHDXPT5sGlv8ca9zdd1n5PxljEO+B2p+/2rXZ3Y/qubIHUuU+6o6E4PqNKDAutdkp2ZmNw89p6ysi+uZriC1X04Pacpv3Mre3C6UHUXW1dgd/Kdn98wOYaIFw7P7OQjzgt0aKaxTma+8qAgU0AWS3qlIa/TAJaXS6hKR3Vnew== 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=AIppw9n8x3zTZPWF3848tRjSCZAKj4Y+XmfRA93tAfA=; b=EwJ15icfdIKl9Z02uQ4iSXqilu6g/2cjfw43LacprUocIkdhXOPgjwK59OMy/2al7Qj38Lmx8H9jeKkOH0OU93aR296wePIybyG8ki5lyarZAKRTjUFx4AS8sksRWntAu+nO6BX6qnkasrdOiVCnYitmAuFFYJQp6FM6O4D9KsZs692iBZpXR7DVOW9WAscXyX0ySHtmRrqjpEXcF56tRpa2MZxOciwB4Y7Vw4kkWM1EFJidF5aSmqAhQBbgXL9CXP51vjN0TjQxb5sm9OwOKvHVmWIzNqU1NzQWEp+rWLnFJO20ivFV5kZct4tt4E4YarqTDzM1hyAuthcCttJ94Q== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=none; dmarc=none; dkim=none; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=hotmail.com; s=selector1; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=AIppw9n8x3zTZPWF3848tRjSCZAKj4Y+XmfRA93tAfA=; b=hsGYEycoxq5jX0wK95uu37X/d7T/vIidPsuSxrvxYMV0r/uz4MnbYEw25tdpuMVN5Ubp3ESWqdSlOoqV6/657GoFO4Z8CBD2vNUdy/6t+Q2DkxOiP6ZEtgmH37/+oo+3nV9NY2kuKe8+mKZ2NZ8ayug0eTPFCt57PR/5k4B4Z9Xkt9KgvnbVcL9cYAfXRxRwAlLBupaBXDkhHfoHM485GADkknIWtvWDLjnCtkaKXlt0Me13kzrCPSaXtcGWg4qXZ1WdanS97l3zSeXlowQJV8QWl7SaEK6GhnYmTLt7p/nFw8hiw91XRFbfz1NFYjSkEA0vYZEjx35yLj7jrDOihQ== Received: from AS1P190MB1701.EURP190.PROD.OUTLOOK.COM (2603:10a6:20b:4ad::13) by DU4P190MB2198.EURP190.PROD.OUTLOOK.COM (2603:10a6:10:56f::11) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.9094.22; Mon, 8 Sep 2025 15:03:41 +0000 Received: from AS1P190MB1701.EURP190.PROD.OUTLOOK.COM ([fe80::75f6:c25c:b93:6d60]) by AS1P190MB1701.EURP190.PROD.OUTLOOK.COM ([fe80::75f6:c25c:b93:6d60%6]) with mapi id 15.20.9073.026; Mon, 8 Sep 2025 15:03:41 +0000 From: legrand legrand To: "pgsql-general@lists.postgresql.org" Subject: Fast switchover Thread-Topic: Fast switchover Thread-Index: AQHcINFrApJU6FDeoE+9lZlKhEILLg== Date: Mon, 8 Sep 2025 15:03:41 +0000 Message-ID: Accept-Language: fr-FR, en-US Content-Language: fr-FR X-MS-Has-Attach: X-MS-TNEF-Correlator: msip_labels: x-ms-exchange-messagesentrepresentingtype: 1 x-ms-publictraffictype: Email x-ms-traffictypediagnostic: AS1P190MB1701:EE_|DU4P190MB2198:EE_ x-ms-office365-filtering-correlation-id: 400eefd7-9147-423a-a4fd-08ddeee8e5ec x-ms-exchange-slblob-mailprops: 9qw5+ftluCCdsMVM4anb8CIHN7zzhZvICsR/MZDyR6QTyBWa/llsmT6X+K9nSUQIs4ZcKV98CArQ/nnmzm5sKwehBuxeTQknh3KivqaQjYZI7vGlqaCTZ4Qz0rjU7XuSkeBdq1/IZei2Z+yScvQBatTKmpou4C74CVtQJ+wU/sKJ7OFAGbSoQPa69t7RxcnznurCaDCAcpO8Iay+uZ5r0OhnLxaWhRMisIRY/NUvGpQb8mLWICGH9L9UdClSn9MQ7K9BuSVmN45HNfRx7Sm9Pc+2W7TR/gQAt70jn914byeWC4V86O5Hh8b7HOnjTQysbp1KFcwtzk9XF9OCNYq/0B3skeXZSwFJUCXpWOVkGaWWR3WCqq/+zRjdsPDBgU6yj662Ii7cjTO4CLdPzuB0FatKEG0EGXEUdtVqgQGrZ08PBkeT5eU0gxVS2bBH+CEl9FzbVTMJlo+GcumXfWK9L3Pvkex0qeR0z8y5k3yZVFS8p8Dw/7a2+hLNBceT0BdFSUzKWfqpiRt6BULq2vYHjIuhpNjzIqk0T/gASFv2lQ+9LnQilExfUYI1+GiSNjgeBVnu0ZZDaoIMfxldNxpMQg4jjQVUMy7aCITXHlg72lV4kMs5ASwM3sNF1tU/pTmEwgG71dWiu15K/C2xxBXUqKCTRvOXmFi7/flh3x7eALEBNsrkQSMnWb1wYcxBIsUsS9L3GV77jm0= x-microsoft-antispam: BCL:0;ARA:14566002|15030799006|15080799012|41001999006|8060799015|8062599012|31061999003|461199028|19110799012|56899033|3412199025|440099028|40105399003|102099032|12091999003; x-microsoft-antispam-message-info: =?iso-8859-1?Q?TgzO29sxcMM21v8g3009fxMZ46mOkNZlS8JDgxIjvQxTkbvDZ3redCYNS2?= =?iso-8859-1?Q?5dy2B1oJ2+RU6NoPu0ucKDg4iO4WgShc3DBKAAexgpj2ocWIJY2LRYCjww?= =?iso-8859-1?Q?P5bKnS95RNYDAxfnHQ9ZuBWGEm5uJOkwQB0EEcWqJPNcSYe0lvlgVjkm3+?= =?iso-8859-1?Q?38+fQ1yk/cl8ns/DgQzOGaa9+amr4a1q4SMorOvfWdBa1lDDCuJmUNz0C+?= =?iso-8859-1?Q?57VfQYixf//Oz7VT9eI3qeObx/WSN/eoUwrPeOIgP1hg/RDxNaq4TrZZgh?= =?iso-8859-1?Q?Lp27VuX95Mg/rxR00PjU4/ct0tYC3WPShu7WxA+IaUjBWbRy1DGHGJFZfQ?= =?iso-8859-1?Q?xgBxfrrUlyjz8hN3PJ4zPVyquq+RyjDposeEfLCuAE9RvhG4m9mFvmpXZ6?= =?iso-8859-1?Q?KX7fUmMLK2dRXH0niVlcl1brZ4MPg7ngpoWiKjMim1jSAWp7CJubk9uLdK?= =?iso-8859-1?Q?OJ1mfR7UAUiDlonzG8tLwJxJqejlajYy8anO1bte+8o+v9UWbJzt1baU+u?= =?iso-8859-1?Q?WqvX5LnHt7sgD+acbX5S8cXPR5AYJbAMtl6vejl8+EBNV8qKfX0JNCaiJY?= =?iso-8859-1?Q?rsGS683AX6qeoHOrcasGYNJm3aaoiud4CLpm88XHc19SGnXxE+2dXXeW0b?= =?iso-8859-1?Q?kEbuvNHZdnljPxt5cCZ3v/TT6QvKE9r10kCgfuBSE4nJVafDTBKps5fRC0?= =?iso-8859-1?Q?xkMi95xClizIQGWdIuisDKDdAvxvhFUrOX7l0/cKFUmS3hus2gEu7EcY0/?= =?iso-8859-1?Q?zcw0sS5zEiTxkOvzLOx4Cw08NdFEVlNLXMM+JK3gmpSdTVi/bg8452BaVo?= =?iso-8859-1?Q?RJcQ3uiPW2imZh7ivZlAQ4jGvgPu7GoGXhFYJP0HMppoHrgtNCf9FCnL/u?= =?iso-8859-1?Q?SGvM8hJ5AWqBb1ldmOuJiZND52PiQVQfk5UTzmYyZzTmziOmPZpDbUVm/1?= =?iso-8859-1?Q?IHmH+lYL0f/kuQkI3mMLCKLQPwHso5vqw0FTUKjx75fDYrxiGh1pWyLg3s?= =?iso-8859-1?Q?cDDfhZSDajrD7eBbFsBbi9nT5xNAqgz1jVPOwaHz68Pk+jVoIizkLlPfd1?= =?iso-8859-1?Q?NcKGw1ubZ3MBlxeQ3ZtQo7PRAqJ3tqeYK6BRwHUKNCdTPfeFJ/SaSZ1pR7?= =?iso-8859-1?Q?QRBHo2Sj3L7gzHx83n7tRRS/jo8rwMQHsYMceXiki6DOCBrY51B1rPCxpc?= =?iso-8859-1?Q?+ppR7Gn9mpOtP17hFXmACd/EmFXeCN6bjJvo4rCmLd2WUVSiou6vEYlcDh?= =?iso-8859-1?Q?EqjzAR/g9rXa2BosMymkND8tZkQ+L7q/WxXdAzEq8JBxhQwdDVKSRY7psW?= =?iso-8859-1?Q?oO7g?= x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?iso-8859-1?Q?YMSdshoYJZ41Uh+461SXQ3wZxSgKwHMB4yI40exTG7uO/MDXP4Hu0hOB+n?= =?iso-8859-1?Q?Sro9avJNQfaYNKlbrVoA9OvgF2SzseGEf1ZnJg+V7kn9OQagI+8ubdJcCZ?= =?iso-8859-1?Q?gWbyhByW8kJzbgUNIAyKsAYxnwyJqaHUiv3sOqu1ma8Za2OOUXe54iFC/q?= =?iso-8859-1?Q?kMZw1RjFbucV68zJulLnH3FWoOYMUtu1hTp/gVPuXw66XkIjW8nr2I1BUm?= =?iso-8859-1?Q?fcmIid5cVhM417zUrXdugToikQEA1oVgod6XQtqLLC9jNJ5a3e/XPPDw+z?= =?iso-8859-1?Q?lOiZCb8FW4UwqgxQx1q5pft6wIs41hcUx3tfp1h4MKqXhTH6qvx12WuT3O?= =?iso-8859-1?Q?L0x1ohAS52KCpfy3qwA6Od1/Uajr3U0dWasDuU6l5WXxyPtrpOgRQfBCUP?= =?iso-8859-1?Q?53m6I0u6n8+ZyaKh7GeJQemHoQMIc1XjRsPjdaRj/FmVeVKZR2fgXrVt5r?= =?iso-8859-1?Q?DVqBuGRONPDjyBS4YJVhET7X3dDFZQQnnRPta4Y0RTTNprkpl5HJXMsrmS?= =?iso-8859-1?Q?Q86FT2ZdsQv23uVLH9wMs/9RYcY+mKJTi53yJ5tKxODPeb1W1jXcvHAxYp?= =?iso-8859-1?Q?eS1J1n2vtHwZOeDRCgxilWgPn3Y+4koigk4a/+JCabxwnV1wuU3pobeVQe?= =?iso-8859-1?Q?fwIJMq+y6TGGow2hycE98tRvVQphK3ZzsOMS1PrakIPnRBv3gN2MJc957x?= =?iso-8859-1?Q?8dllx4FZe+Z5EBHAw7bOI85LQy8zzGZmiz1FDmgV3WFjnPnZJdOMUbSB01?= =?iso-8859-1?Q?nPH0Buj/FHKKqDGFIuxzLBcGOinL1ZlKJbbuP8Aw+68YznoGHXfWkOz8l1?= =?iso-8859-1?Q?NiCSkMX2RVqCPEXpaNA6wJt0HwGzpT0qfBNMEVgNZeaybG82MhB7Gm+UZP?= =?iso-8859-1?Q?7Vyxkxfia50MaUMPdmZoeTLtlnNUzy4HSEISwJcCrPZag+DdBsQiEVP4A2?= =?iso-8859-1?Q?MoFlVE7KC3hRHeyxHY7QNpbmVeJ0uMyyzFS//Z1mOcIhmFpcnwYHq7A2xO?= =?iso-8859-1?Q?gIaw2+lOswdVgnR7AIPqgW/QHtS5AAh9DG98KPPhjTEQD8bfirArfRfwh/?= =?iso-8859-1?Q?9SMdl0brrczJGRxSBJ0ozlhE5GByBQHOW9Q/hfOZpDJUNhueJ2rAt6h7Wu?= =?iso-8859-1?Q?O9vo9qHHALdLBpYP3h6fA1xKFFY9BV2WuC2em/ARNzQ6k45Gkr/cz0EGJ6?= =?iso-8859-1?Q?xlw2eWC1UuiRFZeJAg5llD6uw9KofqdUJMVkTxKC3wJnBUDDzFCxTtxGk3?= =?iso-8859-1?Q?5VcgPCnEdUXs2pAcw6rkelUahe8bk5nH3ZCbS9AqM=3D?= Content-Type: multipart/alternative; boundary="_000_AS1P190MB17014CF63D3EDDB0F4F7B66B900CAAS1P190MB1701EURP_" MIME-Version: 1.0 X-OriginatorOrg: sct-15-20-8534-20-msonline-outlook-e6540.templateTenant X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: AS1P190MB1701.EURP190.PROD.OUTLOOK.COM X-MS-Exchange-CrossTenant-RMS-PersistedConsumerOrg: 00000000-0000-0000-0000-000000000000 X-MS-Exchange-CrossTenant-Network-Message-Id: 400eefd7-9147-423a-a4fd-08ddeee8e5ec X-MS-Exchange-CrossTenant-originalarrivaltime: 08 Sep 2025 15:03:41.2705 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 84df9e7f-e9f6-40af-b435-aaaaaaaaaaaa X-MS-Exchange-CrossTenant-rms-persistedconsumerorg: 00000000-0000-0000-0000-000000000000 X-MS-Exchange-Transport-CrossTenantHeadersStamped: DU4P190MB2198 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_AS1P190MB17014CF63D3EDDB0F4F7B66B900CAAS1P190MB1701EURP_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello all the readers, For some projects we need a fast manual switchover to address Near Zero dow= ntime maintenance (not speaking here about automated failover like those provided by HA tools= , but just planned, controlled operations) Database Physical replication switchover itself: - initial replication (before switchover) should be synchronous or replicat= ion LAG should be controlled to prevent data loss. - Switchover duration seems not "compressible" under a few seconds (because= of primary shutdown, promotion, new standby catch up, ...) - Application retry strategy (after disconnection) should be tuned using pr= oper retry delay. Pooler or specific driver may help. May logical replication ( bi-directional, with one instance RW and the othe= r RO) be a better solution ? This solution is more complex because of sequences, DDL, Large Objects, Con= flict resolution (if any) but switchover should be faster ... what could we expect (in term of downtime in both worlds) ? Are there any Logical Replication Manager available, or admin tools (prefer= ably open source) ? any feedback is welcome Thanks in advance Regards PAscal --_000_AS1P190MB17014CF63D3EDDB0F4F7B66B900CAAS1P190MB1701EURP_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hello all the readers,

For some projects we need a fast manual switchover to address N= ear Zero downtime maintenance
(not speaking here about automated failover like those provided by HA tools= , but just planned, controlled operations)


Database Physical replication switchover itself:
- initial replication (before switchover) should be synchronous or replicat= ion LAG should be controlled to prevent data loss.
- Switchover duration seems not "compressible" under a few second= s (because of primary shutdown, promotion, new standby catch up, ...)
- Application retry strategy (after disconnection) should be tuned using pr= oper retry delay. Pooler or specific driver may help.


May logical replication ( bi-directional, with one instance RW and the othe= r RO) be a better solution ?
This solution is more complex because of sequences, DDL, Large Objects, Con= flict resolution (if any)
but switchover should be faster ...

what could we expect (in term of downtime in both worlds) ?
Are there any Logical Replication Manager available, or admin t= ools (preferably open source) ?
any feedback is welcome

Thanks in advance
Regards
PAscal
--_000_AS1P190MB17014CF63D3EDDB0F4F7B66B900CAAS1P190MB1701EURP_--