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 1uyh0X-00BSON-SA for pgpool-general@arkaria.postgresql.org; Wed, 17 Sep 2025 01:30:54 +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 1uyh0V-003rRO-0r for pgpool-general@arkaria.postgresql.org; Wed, 17 Sep 2025 01:30:51 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uygo2-003q8v-AC for pgpool-general@lists.postgresql.org; Wed, 17 Sep 2025 01:17:59 +0000 Received: from mail-qk1-x734.google.com ([2607:f8b0:4864:20::734]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uygo0-000ofx-2j for pgpool-general@lists.postgresql.org; Wed, 17 Sep 2025 01:17:58 +0000 Received: by mail-qk1-x734.google.com with SMTP id af79cd13be357-7f04816589bso583238485a.3 for ; Tue, 16 Sep 2025 18:17:57 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1758071876; x=1758676676; 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=HGsd49NeruXo75Hjc8JxrN2sx8+Yx6YcS2WV0jdZE+U=; b=BaitDeIOXQmY4y9Sr+X2NfWJWG9EvFRGCZ+paINj4EWquwSULEQzwFZbmVwsJ25zLu pXmqkt28QN9VUrT8gtQAyVsoWd3tZ2HVbJVk66/ne3c2CLFrhZOV3/PGVfHsfj5kUtM2 +r2w4EGEnBQHdLgq3C1TLnF1pvx+Ynwh3/8UhJ3EpKpGUG9My0EpSAu/JjHOTl3vYKeC UixiNzJWcunAU8r1f2TKnA6TqahvAF7LzGriN1PZYBAZr4cMGIVtriSADiLVkl9JRneK B2ajDa+3DFbsNn/odqNB3PV3pmhVKLQfihE09xdRILhxf0B7tmhT9sxHcSZh/dtyzHqw BjyA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1758071876; x=1758676676; 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=HGsd49NeruXo75Hjc8JxrN2sx8+Yx6YcS2WV0jdZE+U=; b=YUTuWh9GgO6yIp+8tHDgXmp0R3d2Kr2t6ns7GRiKApm6EhpvDFA7BjJIWPKhMqLiiD ofxik3KL81AgBDlTopJPfB+twaNHzdgxmB7cNNTuGK0N4dIrth4gYrqzAthutHWooDRy Jkw4R7aGmdK/qVkgYSyadVlsM5I3OtL2gRD+VmSMjeRRj1/Oi7cvLXse6AN8weBPDiRj y6QvnafiNHqj7ljyIToxMi3CQzxd7coEwFXs8dluv5+S6hQlvAGQHlmKZZS7LKAls2tS 5mbSMk1tDPUKVhPfnc+Qqt0fw92NHkvbvU8MTQOBiDPBQemB0dSVT/gEZzfrHJjOD7iH 05RQ== X-Gm-Message-State: AOJu0YycyTHofSqz/Xm4JFI0b8ShqG39x/Ri6H4YZ38DhNcLpGDJlHFx oIJH55MLao5VbHk47+v5ZJIGh6SSKxnG8zL9vFxiRGa/nQmozVLTsRy+aJPIO08d2l3v7aKBtCu vkmc86YFxBHKlbEKWl0xRT99Zop+pR1xghEls X-Gm-Gg: ASbGncvsk8ogTr4ilk0Fbf4XN5RPJnToxNeVzcps1Vt8YTyHmxW7sE299vQ2j9cD0Lo Qr37OWvTmcYFcCfSdz4/iK7zMiky/Nzk+493Kd5H7xPRigJudj2Vdr8mW+yyIGaoRrXuCIfILMB t2A4YMmFXG7kRG8z1ts04L1HHIT1SkWTRxSut7dpeyM7JCRF8T2AOLBVDuK1g2TWdbTLFii0zz9 jvJ+y/JqSJwyagw32BwNbJcO9nnQkjZKZPnrvdL X-Google-Smtp-Source: AGHT+IEOReEH/ge75ymgUJKd5F4QLCQ+JbV9Q+NwAa+V71cU7B5B2r9fYMI92UJfoPT2Grrw3qeXrHFbaKgKHWGLuvA= X-Received: by 2002:a05:620a:710c:b0:811:bc35:7016 with SMTP id af79cd13be357-83114f2924amr54134185a.58.1758071875742; Tue, 16 Sep 2025 18:17:55 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Macao Tom Date: Wed, 17 Sep 2025 09:17:44 +0800 X-Gm-Features: AS18NWBGvyUigTpF_FTcIwGFf2Fwy8KQbdegicRhOlcbfUsF58-khiQ9qCQ9KAw Message-ID: Subject: Re: About pgpool failover / switchover To: Koshino Taiki Cc: "pgpool-general@lists.postgresql.org" Content-Type: multipart/alternative; boundary="000000000000a4b07e063ef5013d" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000a4b07e063ef5013d Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Dear koshino, I discover that I add =E2=80=9C-R=E2=80=9D in the basebackup command in rec= overy_1st_stage script, this action will lead postgre generate some information into postgre.auto.conf, therefore it success to switch over after I delete it. Thanks for your help. Best regards, Terence On Wed, 17 Sep 2025 at 09:13, Koshino Taiki wrote: > This is because, when a failover or switchover occurs and the primary > changes, Pgpool-II updates $PGDATA/myrecovery.conf instead of > postgresql.auto.conf. > > When a standby connects to the primary, it refers to the information in > postgresql.auto.conf , if the setting exists there. > However, during failover or switchover, Pgpool-II updates > $PGDATA/myrecovery.conf rather than postgresql.auto.conf. > As a result, postgresql.auto.conf still contains outdated connection > information, causing PostgreSQL to attempt to connect using this wrong > information, which leads to connection failures. > > In this case, by deleting postgresql.auto.conf, the primary_conninfo in > $PGDATA/myrecovery.conf was used, which was correct, so the connection > succeeded. > > Since having primary_conninfo in postgresql.auto.conf can sometimes > prevent proper connections, it is generally recommended not to include > primary_conninfo in postgresql.auto.conf. > > Taiki Koshino > SRA OSS K.K. > TEL: 03-5979-2701 FAX: 03-5979-2702 > URL: https://www.sraoss.co.jp/ > > ------------------------------ > *=E5=B7=AE=E5=87=BA=E4=BA=BA:* Macao Tom > *=E9=80=81=E4=BF=A1=E6=97=A5=E6=99=82:* 2025=E5=B9=B49=E6=9C=8812=E6=97= =A5 12:43 > *=E5=AE=9B=E5=85=88:* pgpool-general@lists.postgresql.org < > pgpool-general@lists.postgresql.org> > *=E4=BB=B6=E5=90=8D:* About pgpool failover / switchover > > Dear pgpool, > > I built up a postgresql 17.6.2 3 nodes cluster with 3 pgpool 4.6.2 , when > I try to switch over using =E2=80=9Cpcp_promote_start -s =E2=80=A6..=E2= =80=9D it seems other standy > nodes can not follow the new primary node. However, after I remove the > =E2=80=9Cpostgres.auto.conf=E2=80=9D in each server and switch over using= the same command > again, it works. I have also check what store in =E2=80=9Cpostgres.auto.c= onf=E2=80=9D, it > may store many config like =E2=80=9Cprimary_conn_info=E2=80=9D which conn= ect to the old > primary. > > Do you have any idea about this? Or do you know what mechanism cause > postgres store the old primary info in =E2=80=9Cpostgres.auto.config=E2= =80=9D file? > > Best regards, > Terence > --000000000000a4b07e063ef5013d Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Dear koshino,

I discover that I add =E2=80=9C-R=E2=80=9D in the basebackup comm= and in recovery_1st_stage script, this action will lead postgre generate so= me information into postgre.auto.conf, therefore it success to switch over = after I delete it.

Thank= s for your help.

Best=C2= =A0regards,
Terence

On Wed= , 17 Sep 2025 at 09:13, Koshino Taiki <koshino@sraoss.co.jp> wrote:
This is because, when a failover or switchover occurs and the primary chang= es, Pgpool-II updates $PGDATA/myrecovery.conf instead of postgresql.auto.co= nf.=C2=A0

When a standby connects to the primary, it refers to the information in pos= tgresql.auto.conf , if the setting exists there.
However, during failover or switchover, Pgpool-II updates $PGDATA/myrecover= y.conf rather than postgresql.auto.conf.
As a result, postgresql.auto.conf still contains outdated connection inform= ation, causing PostgreSQL to attempt to connect using this wrong informatio= n, which leads to connection failures.

In this case, by deleting postgresql.auto.conf, the primary_conninfo in $PG= DATA/myrecovery.conf was used, which was correct, so the connection succeed= ed.

Since having primary_conninfo in postgresql.auto.conf can sometimes prevent= proper connections, it is generally recommended not to include primary_con= ninfo in postgresql.auto.conf.

Taiki Koshin= o<= koshino@sraoss.co.jp>
SRA OSS K.= K.
TEL: 03-59= 79-2701 FAX: 03-5979-2702
URL: https://www.sraoss.co.jp/

=E5=B7=AE=E5= =87=BA=E4=BA=BA: Macao Tom <macaotom@gmail.com= >
=E9=80=81=E4=BF=A1=E6=97=A5=E6= =99=82: 2025=E5=B9=B49=E6=9C=8812=E6=97=A5 12:43
=E5=AE=9B=E5=85=88: pgpool-general@lists.postgresql.org <= ;pgpool-general@lists.postgresql.org= >
=E4=BB=B6=E5=90=8D: About p= gpool failover / switchover
=C2=A0
Dear pgpool,

I built up a postgresql 17.6.2 3 nodes cluster with 3 pgp= ool 4.6.2 , when I try to switch over using =E2=80=9Cpcp_promote_start -s = =E2=80=A6..=E2=80=9D it seems other standy nodes can not follow the new pri= mary node. However, after I remove the =E2=80=9Cpostgres.auto.conf=E2=80=9D in each server and switch over using the same command again, it works. I h= ave also check what store in =E2=80=9Cpostgres.auto.conf=E2=80=9D, it may s= tore many config like =E2=80=9Cprimary_conn_info=E2=80=9D which connect to = the old primary.

Do you have any idea about this? Or do you know what mech= anism cause postgres store the old primary info in =E2=80=9Cpostgres.auto.c= onfig=E2=80=9D file?

Best regards,
Terence
--000000000000a4b07e063ef5013d--