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 1tVRjf-008TJv-6k for pgsql-admin@arkaria.postgresql.org; Wed, 08 Jan 2025 08:48:19 +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 1tVRje-00F8a8-AP for pgsql-admin@arkaria.postgresql.org; Wed, 08 Jan 2025 08:48:17 +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 1tVRjd-00F8a0-Tv for pgsql-admin@lists.postgresql.org; Wed, 08 Jan 2025 08:48:17 +0000 Received: from mail-wm1-x336.google.com ([2a00:1450:4864:20::336]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tVRja-000UwY-1a for pgsql-admin@postgresql.org; Wed, 08 Jan 2025 08:48:17 +0000 Received: by mail-wm1-x336.google.com with SMTP id 5b1f17b1804b1-436281c8a38so117648725e9.3 for ; Wed, 08 Jan 2025 00:48:14 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1736326094; x=1736930894; darn=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=ds2f9C7rKQrVoEjr1zU/MNHsGAZitm4H+2UhL3YrvQ8=; b=j2z4YY5HOJxrxRTL/Q796/y/P3ZJrarLG8yCxskyit6IyDuIT9kgMvx7I3NgU6FybE jTgjWZvbgANtS+BnojqeWgOlveAc6pukX5cmb4wI7hlPKqJzbUT4LxjzWNWbDfUh9cbv zZpVUgzj6DGXPC1lynsNk+k6u8Pb9P9Yl3/gW+6IUp1/HH/WHXn+4RV8BP5OZcO18iAT Ys58rxxIvI9lCpghUhG/yzmNhQlyE7Ya/wQCRXXOVMJXNM2lNnp2Tj+Ei9yVNKivzICC 7pUAnSzolehyne7JoZEVivPkAKtJVQ1n+gSCE7s3B49QwV1OukiTDFP0jDYVhJpPycu6 xepA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1736326094; x=1736930894; 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=ds2f9C7rKQrVoEjr1zU/MNHsGAZitm4H+2UhL3YrvQ8=; b=gWfLiV20+4OqVKhiE642DMCgG8EfwjWsX0CMoAPVymy576gMuJyavdEdd8o0LlJveN BYTI6F7iAVvC6gNMwFzFkM593nmWkOdSjmrolZdQEl8XISR2k/tpTjzVZ6SzdAmljcuR 3FbpYEMN+VUxhVwZ9nfNHA3Ex23H7PrUO3iHE1IgpyCN5don7kjwGEaOwnmUFwcTgPo0 acad0An6nirsg3Vt+qyVHZl8z4fm2kZ/+lujCgIV0qY0CoutbxiUuA83lMfi2R0SmVf8 sX4VcSG+JacYAiUgFGvw8iVEmi85C5II1LLuVk/oBq39CDBIslzzP6ncgbPrH2ZDycUi PCKg== X-Forwarded-Encrypted: i=1; AJvYcCXL1HRQQiyyUmqZUPnl/7+thHPc7ZAL/U/uYwWuR9yT4GuZ6JWUHp+gbV1drpJadItdH6GgHOJTTYrUnw==@postgresql.org X-Gm-Message-State: AOJu0YzIqBPKVCpwkk6ndzX6+TAd2Jn7bdW73Xkst44fOBq7cV0XmZYJ Os6t5kL3IGNTbSrqt2W4oVXxOVkbf/jCAurAN0BxifvtS66+a96qz+0XgxLZ4h55koHv6wikJkH zLIiKHDusSBx4R+6q4Q24giKXKuI= X-Gm-Gg: ASbGncvmJMBGeIi5iZG29hOfmnKKGQDvpeNIc4AKIKkeVMDHN/EZD1Iu6NpZdaYmBZA iFNuTQasSM+/Z4WDWTlkS0F96oo68igWfKSDSlg== X-Google-Smtp-Source: AGHT+IFxyJTw9tnqeDT+2xsXCp4gsfd0RDFCMVKIGXaDpen/szedJtLiasBa+4ON8iwzeG2/9sLGR15mFNvrP+d9yHI= X-Received: by 2002:a05:600c:3516:b0:436:5165:f206 with SMTP id 5b1f17b1804b1-436e27075eemr14493505e9.31.1736326093262; Wed, 08 Jan 2025 00:48:13 -0800 (PST) MIME-Version: 1.0 References: <4f6b5b081397ba9de49725f87d9ad3e40bf30dbf.camel@cybertec.at> <5DA36CEB-A299-41C5-8C00-FC1631E6EE8E@elevated-dev.com> <9082A0D6-0C8A-4172-ACB4-0B6ABCF91BF1@elevated-dev.com> <866D2812-D9DE-4920-8C6C-0EF1761970F5@elevated-dev.com> <275d04edb1354fdcab228aaa9067856a@med.uni-muenchen.de> In-Reply-To: From: SOzcn Date: Wed, 8 Jan 2025 11:48:02 +0300 X-Gm-Features: AbW1kvYsvfBc-5ikfEF7AxwYk2zupDfxc2Xk3bFcdLXNrcGQpJUy6uZszrzyfyo Message-ID: Subject: Re: Advice Needed: Simultaneous Upgrade of Two-Node PostgreSQL 11 Cluster To: kasem adel Cc: "Dischner, Anton" , Scott Ribe , pgsql-admin Content-Type: multipart/alternative; boundary="00000000000000d669062b2decb1" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000000d669062b2decb1 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable In this case, I don=E2=80=99t think there=E2=80=99s a need to complicate th= e steps. If I were in your position, I would likely follow the steps outlined below: - Set up a Patroni Cluster, then disable the Patroni Cluster service. - Install the same PostgreSQL version in the new environment and create a replica using pg_basebackup. - Set up a cluster for the new PostgreSQL version you intend to use in the new environment, whether it=E2=80=99s 15, 16, or 17, based on your preferen= ce. - Update the Patroni Cluster configuration to point to the data path of the new version. - When it=E2=80=99s time to upgrade, failover to the old version to make it= the - -- - Primary, then perform the upgrade. Once upgraded, transition to the new version, upgrade it, and enable the Patroni Cluster. - Afterward, to maintain the health of the environment, initiate backup processes. If you are using pgbackrest, start the backup workflows. If not, configure regular backups using pg_basebackup or pg_dump and automate them with crontab. This is a general approach I=E2=80=99ve followed in similar cases. kasem adel , 8 Oca 2025 =C3=87ar, 02:40 tarihinde =C5= =9Funu yazd=C4=B1: > Dear sozcn > > No I mean when take backup to restore it to replica after upgrade what > type of backup I need to take to enable replication after restore backup > in my last email to upgrade. > > Thanks > > > =D9=81=D9=8A =D8=A7=D9=84=D8=AB=D9=84=D8=A7=D8=AB=D8=A7=D8=A1=D8=8C =D9= =A7 =D9=8A=D9=86=D8=A7=D9=8A=D8=B1 =D9=A2=D9=A0=D9=A2=D9=A5 =D9=A1=D9=A1:= =D9=A4=D9=A3 =D9=85 SOzcn =D9=83=D8=AA=D8=A8: > >> >> >> Hello, >> >> Could you first clarify the reason for using 'pg_dump' in the upgrade >> method? >> >> In a PostgreSQL database environment managed by a Patroni cluster, if >> there aren't overly complex extensions in use, you can replicate the >> database to the new environment and then perform a failover during the >> upgrade process. By using the 'pg_upgrade' method, you can first run it >> with the '--check' flag to ensure compatibility, and then proceed with t= he >> upgrade. >> >> Since the PostgreSQL cluster will be initialized with Patroni, there >> should be no issues, and this approach will likely reduce your workload. >> >> If your database is small or involves a highly complex structure, >> 'pg_dump' is indeed an option. However, is it truly necessary? Testing t= his >> approach would provide more clarity. >> >> Dischner, Anton , 7 Oca 2025 Sal, >> 18:41 tarihinde =C5=9Funu yazd=C4=B1: >> >>> Hi, >>> >>> if you are using the same slow internet connection for WAL and >>> data-transfer you might considering to use rsync which has a full set o= f >>> throttling, resuming/ibcremential and as mentioned data-compression opt= ions >>> so that you do not saturate you connection, >>> >>> BTW nice challenge, >>> >>> Best, >>> >>> Anton >>> >>> -----Urspr=C3=BCngliche Nachricht----- >>> Von: Scott Ribe >>> Gesendet: Dienstag, 7. Januar 2025 15:42 >>> An: kasem adel >>> Cc: pgsql-admin >>> Betreff: Re: Advice Needed: Simultaneous Upgrade of Two-Node PostgreSQL >>> 11 Cluster >>> >>> > Are you mean do the following step: >>> > >>> > 1- upgrade primary node >>> > 2- take base backup from new version in primary node and keep wal >>> file for 3 days >>> > 3- move backup by external hard disk to the replica node >>> > 4- restore base backup to replica >>> > 5- start replication to replicate delta from primary node. >>> > >>> > Please confirm if this the best approach and it will work without ris= k. >>> >>> Yes, that's what I meant. It will work, nothing is completely without >>> risk ;-) Main thing is to make absolutely sure you don't lose WAL durin= g >>> that time period. If you could set up WAL archiving to push to the remo= te >>> site, that would be great as then you could configure the remote to pul= l >>> accumulated WAL locally instead of across the slow network link. >>> >>> But, just thought of this: >>> >>> - pg_upgrade both sides >>> - with neither side running, rsync the data directories (bonus points >>> for being paranoid and using -c) >>> - fix up the standby flag on the standby >>> - fix up the postgresql.conf -- for instance, standby config has been >>> moved out of a separate file into the main one, so that you can have co= mmon >>> config both sides now, with the only necessary difference being the sta= ndby >>> flag >>> - bring them up >>> >>> You could even try to figure out where the catalog tables are stored an= d >>> only rsync those, since pg_)upgrade doesn't change the format of your d= ata >>> files. But personally, I wouldn't. I wouldn't want to introduce the >>> possibility of error on my part, and the rsync checksum is a nice check >>> that nothing has gotten corrupted over time from network or disk glitch= . >>> (Excluding disk glitch on the primary...) >>> >>> You may be wondering why not just pg_upgrade both sides? Well, >>> pg_upgrade ourput should be deterministic, right? So if you make sure t= hat >>> clients are disconnected and standby is completely in sync before start= ing, >>> why not? Maybe you could. But because it's not designed nor documented = for >>> that use, so although you likely could make it work, that's a dangerous >>> path. The last thing you want to do is take your server down for this >>> scheduled operation, and wind up at the end with an unusable standby. >>> >>> >>> >>> >>> >>> --00000000000000d669062b2decb1 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
In this case, I don=E2=80=99t think there=E2=80=99s a need= to complicate the steps. If I were in your position, I would likely follow= the steps outlined below:

- Set up a Patroni Cluster, then disable = the Patroni Cluster service.
- Install the same PostgreSQL version in th= e new environment and create a replica using pg_basebackup.
- Set up a c= luster for the new PostgreSQL version you intend to use in the new environm= ent, whether it=E2=80=99s 15, 16, or 17, based on your preference.
- Upd= ate the Patroni Cluster configuration to point to the data path of the new = version.
- When it=E2=80=99s time to upgrade, failover to the old versio= n to make it the - --
- Primary, then perform the upgrade. Once upgraded= , transition to the new version, upgrade it, and enable the Patroni Cluster= .
- Afterward, to maintain the health of the environment, initiate backu= p processes. If you are using pgbackrest, start the backup workflows. If no= t, configure regular backups using pg_basebackup or pg_dump and automate th= em with crontab.

This is a general approach I=E2=80=99ve followed in= similar cases.

<= div dir=3D"ltr" class=3D"gmail_attr">kasem adel <kasemadel8@gmail.com>, 8 Oca 2025 =C3=87ar, 02:40 t= arihinde =C5=9Funu yazd=C4=B1:
Dear sozcn

No I mean when take backup to restore it to replica after upg= rade what type of backup I=C2=A0 need to take to enable replication after r= estore backup in my last email to upgrade.

Thanks


=D9=81=D9=8A= =D8=A7=D9=84=D8=AB=D9=84=D8=A7=D8=AB=D8=A7=D8=A1=D8=8C =D9=A7 =D9=8A=D9=86= =D8=A7=D9=8A=D8=B1 =D9=A2=D9=A0=D9=A2=D9=A5 =D9=A1=D9=A1:=D9=A4=D9=A3 =D9= =85 SOzcn <selahattinozcnma@gmail.com> =D9=83=D8=AA=D8=A8:


Hello,
Could you first clarify the reason for using 'pg_dump' in the= upgrade method?

In a PostgreSQL database environment managed by a = Patroni cluster, if there aren't overly complex extensions in use, you = can replicate the database to the new environment and then perform a failov= er during the upgrade process. By using the 'pg_upgrade' method, yo= u can first run it with the '--check' flag to ensure compatibility,= and then proceed with the upgrade.

Since the PostgreSQL cluster wil= l be initialized with Patroni, there should be no issues, and this approach= will likely reduce your workload.

If your database is small or invo= lves a highly complex structure, 'pg_dump' is indeed an option. How= ever, is it truly necessary? Testing this approach would provide more clari= ty.=C2=A0

Dischner, Anton <Anton.Dischner@med.uni-muenche= n.de>, 7 Oca 2025 Sal, 18:41 tarihinde =C5=9Funu yazd=C4=B1:
Hi,

if you are using the same slow internet connection for WAL and data-transfe= r you might considering to use rsync which has a full set of throttling, re= suming/ibcremential and as mentioned data-compression options so that you d= o not saturate you connection,

BTW nice challenge,

Best,

Anton

-----Urspr=C3=BCngliche Nachricht-----
Von: Scott Ribe <scott_ribe@elevated-dev.com>
Gesendet: Dienstag, 7. Januar 2025 15:42
An: kasem adel <kasemadel8@gmail.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Betreff: Re: Advice Needed: Simultaneous Upgrade of Two-Node PostgreSQL 11 = Cluster

> Are you mean do the following step:
>
> 1- upgrade primary node
> 2- take base backup from new=C2=A0 version in primary node and keep wa= l file for 3 days
> 3- move backup by external hard disk to the replica node
> 4- restore base backup to replica
> 5- start replication to replicate delta from primary node.
>
> Please confirm if this the best approach and it will work without risk= .

Yes, that's what I meant. It will work, nothing is completely without r= isk ;-) Main thing is to make absolutely sure you don't lose WAL during= that time period. If you could set up WAL archiving to push to the remote = site, that would be great as then you could configure the remote to pull ac= cumulated WAL locally instead of across the slow network link.

But, just thought of this:

- pg_upgrade both sides
- with neither side running, rsync the data directories (bonus points for b= eing paranoid and using -c)
- fix up the standby flag on the standby
- fix up the postgresql.conf -- for instance, standby config has been moved= out of a separate file into the main one, so that you can have common conf= ig both sides now, with the only necessary difference being the standby fla= g
- bring them up

You could even try to figure out where the catalog tables are stored and on= ly rsync those, since pg_)upgrade doesn't change the format of your dat= a files. But personally, I wouldn't. I wouldn't want to introduce t= he possibility of error on my part, and the rsync checksum is a nice check = that nothing has gotten corrupted over time from network or disk glitch. (E= xcluding disk glitch on the primary...)

You may be wondering why not just pg_upgrade both sides? Well, pg_upgrade o= urput should be deterministic, right? So if you make sure that clients are = disconnected and standby is completely in sync before starting, why not? Ma= ybe you could. But because it's not designed nor documented for that us= e, so although you likely could make it work, that's a dangerous path. = The last thing you want to do is take your server down for this scheduled o= peration, and wind up at the end with an unusable standby.





--00000000000000d669062b2decb1--