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 1tVndU-00BYAp-BF for pgsql-admin@arkaria.postgresql.org; Thu, 09 Jan 2025 08:11:24 +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 1tVndT-00F1R5-H0 for pgsql-admin@arkaria.postgresql.org; Thu, 09 Jan 2025 08:11:23 +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 1tVJBE-009qhJ-28 for pgsql-admin@lists.postgresql.org; Tue, 07 Jan 2025 23:40:11 +0000 Received: from mail-lf1-x12a.google.com ([2a00:1450:4864:20::12a]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tVJB8-000PkC-2H for pgsql-admin@postgresql.org; Tue, 07 Jan 2025 23:40:10 +0000 Received: by mail-lf1-x12a.google.com with SMTP id 2adb3069b0e04-540215984f0so16644724e87.1 for ; Tue, 07 Jan 2025 15:40:06 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1736293204; x=1736898004; 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=FCBGZ4vhCMG3GOdyecnzfe5HDu1PL8HhkHGKPLskiS8=; b=Wl+lD/j3rbfKzLfvX2RL1Yj2yAK3Ua9SB/Cno53pfLm1FoFBxWL+GV+BgM9Bzc2xvE gmgxaMmlhtdW8f6fQvOgdXvhkllg8L6efoNLqlUVpQbVjKE6wl5aYCdaVdnPNIBR12hY sMrDUS3L2fp3jKHUtD4Lco4jnPI6QuMEv7KiAipL0SCFCu/UiaOBtcca72Xa9CTF+xET +ED63HIlTj9TaUIVfRdySiR0pHE2eYxSR+CgCeeeFzB1nmDQhNxWyLHBD4zu6oZMJN9b Ax7wwT0uTu954AJOZcTQoTLVYu3g0yH5XC8EGkQ3r7J77Cfsf4f+/eQit0iqS4BM4ylo u/mg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1736293204; x=1736898004; 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=FCBGZ4vhCMG3GOdyecnzfe5HDu1PL8HhkHGKPLskiS8=; b=bTljLRJ7Tnyb6AU26mJpngUZ2MvC7L19jSxz+GDNWk8l4T5TR3gBvOZJlZGeeOC1bw GBv+imFsyO5aBPFhAb3u+ds05HCCerbXO9qMMq+LaRQ77UXwzqv73bs5JHuiflUW6Tee sSFCXkcqEz2w7CJLHVSBkp7Fg+WLb9pZNPYJHGmpZ/SYe14UadvVshzwCw0NZ9UbQx7u sOBuzcXxATpb9MDFJPqLY7xdXF8b1kRZkzfZJ1k18d22AU4mihwsWMn7nh7ddMflDOss tc2AfZdk1Xxg/AOEROpdrxILBBhacR4h+vlY5zrJ1uW+VlMwSYgwjlJ6tg3J5qw8gfDd B9bQ== X-Forwarded-Encrypted: i=1; AJvYcCXxmbbPqL5Pr/RTZLH1LOOo/Q7Ge/djmks5nxq245P9PS7psxZkhdu2q44AjO1TRGDDWQeuQo2aw5KCFw==@postgresql.org X-Gm-Message-State: AOJu0YwDp/zdqzqZjz1Fh756olmPnz+Qqz/LZyOqqCK6JsfQKAPzyjkO TjD04fJhjuC7O7Ld1m7YGCas/oT3l06zpehkKnrsFTZlE8V2t4DlR4Wj403zwXWQfrfm/nlwQpF htbpmI4kuwblNsoUyacxOG50g4ljOOw== X-Gm-Gg: ASbGncuxTZydEsIOPI1JhIBvsBM+1YwVkSlN/vFZ5LQZpT6iGRQUc0aRJa+ysWPJe7d KRn3NTOvqdkBt7/3K+PvFA2Xyy+Oi8xt5sjT24w== X-Google-Smtp-Source: AGHT+IFSy1ausQgWJmWPjfKZbo1DrDXrpn8CLCai/IWRD5HUnf9KP8fS0nBKPBM//Y9x0hHrYcBf4yseKfZO93imhbU= X-Received: by 2002:a05:6512:eaa:b0:542:2871:535c with SMTP id 2adb3069b0e04-542845bf82fmr138058e87.22.1736293203977; Tue, 07 Jan 2025 15:40:03 -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: kasem adel Date: Wed, 8 Jan 2025 01:38:51 +0200 X-Gm-Features: AbW1kvb9qkER32cf5P7q0oC8QYdxhpGV_Q0gd_hXAbaCVwB6lfbYfvRVbw4GJ_c Message-ID: Subject: Re: Advice Needed: Simultaneous Upgrade of Two-Node PostgreSQL 11 Cluster To: SOzcn Cc: "Dischner, Anton" , Scott Ribe , pgsql-admin Content-Type: multipart/alternative; boundary="000000000000a6310a062b2643cd" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000a6310a062b2643cd Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 th= e > 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 th= is > 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 of >> throttling, resuming/ibcremential and as mentioned data-compression opti= ons >> 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 fil= e >> 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 >> risk ;-) 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 remot= e >> site, that would be great as then you could configure the remote to pull >> 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 fo= r >> 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 com= mon >> config both sides now, with the only necessary difference being the stan= dby >> flag >> - bring them up >> >> You could even try to figure out where the catalog tables are stored and >> only rsync those, since pg_)upgrade doesn't change the format of your da= ta >> 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_upgrad= e >> ourput should be deterministic, right? So if you make sure that clients = are >> disconnected and standby is completely in sync before starting, why not? >> Maybe you could. But because it's not designed nor documented for that u= se, >> 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. >> >> >> >> >> >> --000000000000a6310a062b2643cd Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Dear sozcn

N= o I mean when take backup to restore it to replica after upgrade what type = of backup I=C2=A0 need to take to enable replication after restore backup i= n 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 <selahatt= inozcnma@gmail.com> =D9=83=D8=AA=D8=A8:


Hello,

Could you first clarif= y the reason for using 'pg_dump' in the upgrade method?

In = a PostgreSQL database environment managed by a Patroni cluster, if there ar= en't overly complex extensions in use, you can replicate the database t= o the new environment and then perform a failover during the upgrade proces= s. By using the 'pg_upgrade' method, you can first run it with the = '--check' flag to ensure compatibility, and then proceed with the u= pgrade.

Since the PostgreSQL cluster will be initialized with Patron= i, there should be no issues, and this approach will likely reduce your wor= kload.

If your database is small or involves a highly complex struct= ure, 'pg_dump' is indeed an option. However, is it truly necessary?= Testing this approach would provide more clarity.=C2=A0

Dischner, Anton <= ;Anton.Dischner@med.uni-muenchen.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.





--000000000000a6310a062b2643cd--