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 1vBqae-005uhV-Io for pgpool-general@arkaria.postgresql.org; Thu, 23 Oct 2025 08:22:31 +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 1vBqad-007EwR-HV for pgpool-general@arkaria.postgresql.org; Thu, 23 Oct 2025 08:22:30 +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 1vBpVA-006P17-Gw for pgpool-general@lists.postgresql.org; Thu, 23 Oct 2025 07:12:47 +0000 Received: from mail-ej1-x629.google.com ([2a00:1450:4864:20::629]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vBpV6-003l8Y-30 for pgpool-general@lists.postgresql.org; Thu, 23 Oct 2025 07:12:47 +0000 Received: by mail-ej1-x629.google.com with SMTP id a640c23a62f3a-b3b27b50090so93843966b.0 for ; Thu, 23 Oct 2025 00:12:44 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1761203563; x=1761808363; 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=gFj0Ekw388+bXr72ICfIuot7lvd9eh4E9xDKEcAPy0w=; b=FoU+6B1mM7TYjhrFxnkdDKA2Qdhmc2ffkdN2DHZqdw6o5+YucMkKG+mB8s6hOoZI1h dcrg4Zf2gSvKjX+xSmz1w00+r+rDroZUlx/uQNMvHKkbcekj17SOtmdmX7qfhCZySz7c iteNiD3xVM+2uGZ4c7U2wh7eXY3D68uxhECAjIfDdOGXrLVNS2DaZum0bEjB7+HUsXVq Ln7pSdprpiMM8m7vZ32hjRUJXOO7ZdvPHMcMLSo5PsL7UH3WjiL3ZhUtQTOvahSCrTIk OcQ/ZXlGibnEwr8LY9ZBgbRve5ijur6xrEI7iQ6/nPO1c350FLACr39YmIhwXxO+gVLE YZdg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1761203563; x=1761808363; 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=gFj0Ekw388+bXr72ICfIuot7lvd9eh4E9xDKEcAPy0w=; b=lmWCfPEBQhq8uwli8qkR821CYt3LM3FKhtQI97Bei6jZu2LQfs4o7IGIVllVAXG0Rw TwlouGjJC9GSdndBcXb888Mges35klv667yBU/LFJQ5Q7swuB4MHPardFAfELQsWf6BA i2z9L7NrbeHKgtYrg4xO0MDT9wYmg/lgxfqd2Uq6IswmHHQtVkPgvkt/2QMlifwYXITP japQjU2wdWXaibZRncdamabNhAF2Tpm2Dh9Iv9wg0k3n1DiI2Tf04ylLoh300ryAbx9Z tmRnDmKdfk2PPNlVXbNaYcZCB09knLaf1ipNNXSSEufbjHdm4VcGWvbMN/w/U9Ob5c6B GPGw== X-Gm-Message-State: AOJu0YzPknrQlkFE6W/6KiQfj71/no7BowhP1N22z7y/BjL07X/tjcgQ 7hQI287K1pRo2EJSskijjmm2JjFhgmWMo4naGl5TDdjSfv/DLsHjKQF/CY/m+81fudvDsy901Ft HHFK/WH+m3kkXo4ilJQIixq4NrBKop3+A+g== X-Gm-Gg: ASbGnctz5oLl0u7AvU9kdnaXaeIjR4yOZw4Mf1tBRuzFpBNHP7/NIGPybwihPCoCy38 GTolrf5Ak3mt+iSOyWDqZ2igw8UiUPEQP+nofq4pIJmTbAiZKd6dP9+Y40Cltxa8SjLSbzttrb8 spwOX1TRxUcHOmZETSybaiAB6hPh/uefcllKPaHLlgFG6c1IKAI/hMW+3AUNdvNZVO6CgEwynhn fC5ENg2Jcg0G6lHktKRryaRoSfSHjLDIRhXwnliLUTDxU+btr+8mAZg9js5 X-Google-Smtp-Source: AGHT+IERrHqsz13R1jSv82dGFKk79mbKU2WPV+RYEZyGRTQmPqb6F4NqUer3J5CoxTSo181WKFch15tqHFwAFT2Dc80= X-Received: by 2002:a17:906:fd87:b0:b2b:63a9:223b with SMTP id a640c23a62f3a-b6474b365eamr3131610266b.31.1761203562859; Thu, 23 Oct 2025 00:12:42 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: VASUKI M Date: Thu, 23 Oct 2025 12:43:04 +0530 X-Gm-Features: AWmQ_bmohX07fip21v-zqraDMkcylQ8IsAmiqXW3QGQ4FC1xQXcdjzwCiCUTUY0 Message-ID: Subject: Fwd: Automating Failover Resync & Re-Attach in pgpool2 To: pgpool-general@lists.postgresql.org Cc: bharatdb@cdac.in Content-Type: multipart/alternative; boundary="000000000000bdd0ae0641ce2887" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000bdd0ae0641ce2887 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi Bo, Thank you very much for your clarification and the helpful links on follow_primary_command and auto_failback. I went through those sections in the documentation, and I now understand that Pgpool-II can automatically follow the new primary and reattach a standby node once it becomes available again. However, my idea was aimed at handling cases where the *old primary diverges in timeline or LSN* after a failover =E2=80=94 for example, when t= he new primary executes additional writes before the old primary rejoins. In such cases, the existing auto-failback or follow-primary mechanisms can=E2=80=99= t directly reattach the old node because its data is no longer in sync with the current primary. To address that, I was exploring a built-in *auto-resync enhancement* where Pgpool-II could internally perform the following before reattaching: 1. *Detect timeline mismatch* between the new primary and the returning node. 2. *Automatically run pg_rewind* (or WAL-based replay) to synchronize the old node=E2=80=99s data directory. 3. *Restart and reattach the node* to the pool automatically once the resync is complete. This would essentially extend the existing auto_failback behavior to include *automated resynchronization*, reducing manual intervention and ensuring consistent cluster recovery even in timeline divergence scenarios. I=E2=80=99m thinking of something like a new configuration section in pgpoo= l.conf: auto_resync =3D on resync_method =3D 'pg_rewind' resync_user =3D 'replicator' The feature could hook into the existing failback workflow (perhaps in failover.c or recovery.c), so that Pgpool performs resync + reattach seamlessly when the failed node returns. Would this be something the Pgpool team would consider as an enhancement? Thanks again for your time and guidance. Best regards, *Vasuki M* CDAC, Chennai vasukim1992002@gmail.com On Fri, 17 Oct 2025 at 13:30, Bo Peng wrote: > Hi, > > Thank you for your question. > > > While working with PostgreSQL failover scenarios, I noticed that the > process of re-attaching a standby node > > after a failover can be somewhat manual and prone to delays, especially > in production environments. > > After a failover, the standby nodes can be automatically attached to the > new primary by setting "follow_primary_command". > > > https://www.pgpool.net/docs/latest/en/html/runtime-config-failover.html#R= UNTIME-CONFIG-FAILOVER-SETTINGS > > You can also automatically reattach a failed standby node by setting > "auto_failback =3D on". > > > https://www.pgpool.net/docs/latest/ja/html/runtime-config-failover.html#G= UC-AUTO-FAILBACK > > --- > Bo Peng > SRA OSS K.K. > TEL: 03-5979-2701 FAX: 03-5979-2702 > Mobile: 080-7752-0749 > URL: https://www.sraoss.co.jp/ > > > ________________________________________ > =E5=B7=AE=E5=87=BA=E4=BA=BA: VASUKI M > =E9=80=81=E4=BF=A1: 2025 =E5=B9=B4 10 =E6=9C=88 10 =E6=97=A5 (=E9=87=91= =E6=9B=9C=E6=97=A5) 21:17 > =E5=AE=9B=E5=85=88: pgsql-bugs@lists.postgresql.org > Cc: bharatdb@cdac.in ; > pgpool-general@lists.postgresql.org > =E4=BB=B6=E5=90=8D: Automating Failover Resync & Re-Attach in pgpool2 > > Dear PostgreSQL and Pgpool Communities,While working with PostgreSQL > failover scenarios, I noticed that the process of re-attaching a standby > node after a failover can be somewhat manual and prone to delays, > especially in production environments.I explored automating this process > using a combination of pg_rewind and WAL replay, which allows a standby > node to resynchronize and re-attach to the primary automatically after a > failover. This could reduce downtime and simplify management of failover > nodes in high-availability setups.Automatically resynchronize after > failoverReduce downtime and ensure quicker recoveryMinimize manual > operations and errorsMaintain consistent cluster state with less > administrative overheadI believe that integrating such an automated resyn= c > and re-attach feature into Pgpool-II could be very valuable for PostgreSQ= L > users, potentially as an enhancement in a future release.I wanted to shar= e > this idea with the community to get feedback, suggestions, or any pointer= s > on existing work that may align with this. I am happy to contribute more > details --000000000000bdd0ae0641ce2887 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Hi Bo,

Thank you very much for your clarification and the helpful links on follow_primary_command and auto_failback. I went thro= ugh those sections in the documentation, and I now understand that Pgpool-I= I can automatically follow the new primary and reattach a standby node once= it becomes available again.

However, my idea was aimed at handling cases where the old prima= ry diverges in timeline or LSN after a failover =E2=80=94 for exam= ple, when the new primary executes additional writes before the old primary= rejoins. In such cases, the existing auto-failback or follow-primary mecha= nisms can=E2=80=99t directly reattach the old node because its data is no l= onger in sync with the current primary.

To address that, I was exploring a built-in auto-resync enhancem= ent where Pgpool-II could internally perform the following before = reattaching:

  1. Detect timeline mismatch between the new primary and th= e returning node.

  2. Automatically run pg_rewind (or WAL-based = replay) to synchronize the old node=E2=80=99s data directory.

  3. Restart and reattach the node to the pool automatically= once the resync is complete.

This would essentially extend the existing auto_failback be= havior to include automated resynchronization, reducing ma= nual intervention and ensuring consistent cluster recovery even in timeline= divergence scenarios.

I=E2=80=99m thinking of something like a new configuration section in pgpool.conf:

auto_resync =3D on
resync_method =3D 'pg_rewind'
resync_user =3D 'replicator'

The feature could hook into the existing failback workflow (perhaps in <= code>failover.c or recovery.c), so that Pgpool performs= resync + reattach seamlessly when the failed node returns.

Would this be something the Pgpool team would consider as an enhancement= ?

Thanks again for your time and guidance.

Best regards,
Vasuki M
CDAC, Chennai
vasukim199200= 2@gmail.com


On Fri, 17 Oct 2025 at 13:30, Bo Peng <pengbo@sraoss.co.jp> wrot= e:
Hi,

Thank you for your question.

> While working with PostgreSQL failover scenarios, I noticed that the p= rocess of re-attaching a standby node
> after a failover can be somewhat manual and prone to delays, especiall= y in production environments.

After a failover, the standby nodes can be automatically attached to the ne= w primary by setting "follow_primary_command".

=C2=A0 =C2=A0 https://www.pgpool.net/docs/latest/en/html/runtime-config= -failover.html#RUNTIME-CONFIG-FAILOVER-SETTINGS

You can also automatically reattach a failed standby node by setting "= auto_failback =3D on".

=C2=A0 =C2=A0 https://www.pgpool.net/docs/latest/ja/html/runtime-config-failover.html#= GUC-AUTO-FAILBACK

---
Bo Peng <pengbo= @sraoss.co.jp>
SRA OSS K.K.
TEL: 03-5979-2701 FAX: 03-5979-2702
Mobile: 080-7752-0749
URL: https://www.sraoss.co.jp/


________________________________________
=E5=B7=AE=E5=87=BA=E4=BA=BA: VASUKI M <vasukim1992002@gmail.com>
=E9=80=81=E4=BF=A1: 2025 =E5=B9=B4 10 =E6=9C=88 10 =E6=97=A5 (=E9=87=91=E6= =9B=9C=E6=97=A5) 21:17
=E5=AE=9B=E5=85=88: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql= .org>
Cc: bharatdb@cdac.in<= /a> <bharatdb@cdac= .in>; pgpool-general@lists.postgresql.org <pgpool-general@lists.= postgresql.org>
=E4=BB=B6=E5=90=8D: Automating Failover Resync & Re-Attach in pgpool2
Dear PostgreSQL and Pgpool Communities,While working with PostgreSQL failov= er scenarios, I noticed that the process of re-attaching a standby node aft= er a failover can be somewhat manual and prone to delays, especially in pro= duction environments.I explored automating this process using a combination= of pg_rewind and WAL replay, which allows a standby node to resynchronize = and re-attach to the primary automatically after a failover. This could red= uce downtime and simplify management of failover nodes in high-availability= setups.Automatically resynchronize after failoverReduce downtime and ensur= e quicker recoveryMinimize manual operations and errorsMaintain consistent = cluster state with less administrative overheadI believe that integrating s= uch an automated resync and re-attach feature into Pgpool-II could be very = valuable for PostgreSQL users, potentially as an enhancement in a future re= lease.I wanted to share this idea with the community to get feedback, sugge= stions, or any pointers on existing work that may align with this. I am hap= py to contribute more details
--000000000000bdd0ae0641ce2887--