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-00BYAn-2E 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-00F1R2-Gq for pgsql-admin@arkaria.postgresql.org; Thu, 09 Jan 2025 08:11:23 +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 1tVDHo-005wr0-Qd for pgsql-admin@lists.postgresql.org; Tue, 07 Jan 2025 17:22:36 +0000 Received: from mail-lf1-x12b.google.com ([2a00:1450:4864:20::12b]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tVDHl-000NXF-1g for pgsql-admin@postgresql.org; Tue, 07 Jan 2025 17:22:36 +0000 Received: by mail-lf1-x12b.google.com with SMTP id 2adb3069b0e04-5401bd6ccadso15645881e87.2 for ; Tue, 07 Jan 2025 09:22:33 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1736270552; x=1736875352; 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=25JVlvuvA2MTe1dLTvMQlBs+/lynOqbvzd8qmRMLFzk=; b=U8qrf0hmefs3tGqcPbZJGDPrLx1Dt0UIwSZF5/7V3EN3L7Mie3HZHYQ0fpUSsaH8a0 FRyKGQKvuP1LBTF8oHp0UlPNvkQ9rNNwqjm2Zq4tuD0E05HXv+i9nGbq8L66cO3PfnQk NNSZJjdiT6G02BOxgd/irqlEzPy48DEC4s5BRmPHAZEkApzgsbd2parlGhXSFKZA542K 0+IU1NZf1gpg7p+E/oZZSFTqKsFdzzW7jTKO4VNxm70GGCQ/rQYyKWiSOjHFV1ZQ6ds3 pYMHTKuWYCSGeuFT0gRYwCWzrVg0aT6ltGDpDrG8vkCoF+IEzdgq8/89WIbdiqUClICg gdCA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1736270552; x=1736875352; 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=25JVlvuvA2MTe1dLTvMQlBs+/lynOqbvzd8qmRMLFzk=; b=kLHQs/mY6KFtsxwtIAueKScr3r5gtoSex92DCEC4BrObLdg2jAEFZZO1M5sM0H7DQ7 6g/DgOiJ1mSIHAm3o+2pY5PNnpfaq5MH9UxEhuQho4/BUsxvuqvOM9BfZTTqOStU+QUS /pxtTcxpWWD8PjVkkUIsaro0gnvyDpRHdJVXVrnwezQSsp6m2+arBhSHma/nOVVTYnHH //ptTgtEY6WTmzbA9DP0ouT1/NwCQkga9d46FPuDPaZuSDBRTrWRouWElbFKffitr1Kd bCygALfo55rdYAhs1X76Ml+cV3LGL4Tkw3DCPwPB51dr7QTpbt9FylJYAXaYxZuDogmY 0BfQ== X-Forwarded-Encrypted: i=1; AJvYcCVBhtPI80d68EqYPjVlr/EMjinNObBzmHh4T9OPLhfG3azM52XGDG+7aOMhD9wNLBBqKXsvit1/GZWlHQ==@postgresql.org X-Gm-Message-State: AOJu0Yxh5raZXfNgGKZGQbrESTJpxA7CVghU2aUL5w16U2hdg6uiMgPk 76Rxz1U6AC/oo65uHqbmeFkK1YvBO9d6GZWA/9kHaKPjtT398ItVt5phE6fitaxbHGflv2p2f9w 5DKOEo8PsTHPXjFGwGvxBBODVfs0= X-Gm-Gg: ASbGncsGlzHLuLMu9eY7LdJFKRAcMIdDF095Z1WLPMn4eS1zNo9WHtJfpbDTgAC0O0N wMNKk0ky2pzmRxIg5xieKdcX2/lWSYYtfuMJvxQ== X-Google-Smtp-Source: AGHT+IGW3hhxx/OUh70ymav4uUHyYk55B+KBJMBpGuWA81FMWMLyr/bhJJ74jxsiq2H0dCUaHaZWa/aJ9ZV4HrGV8pU= X-Received: by 2002:a05:6512:398d:b0:542:23a9:bd44 with SMTP id 2adb3069b0e04-5422952e712mr21216595e87.17.1736270552063; Tue, 07 Jan 2025 09:22:32 -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: Tue, 7 Jan 2025 19:22:18 +0200 X-Gm-Features: AbW1kvYawTKWVL3L7iXZ5s1FeROXT_QBYYFiga9aqdSgUTFHLDLAln4UmOxaMhk Message-ID: Subject: Re: Advice Needed: Simultaneous Upgrade of Two-Node PostgreSQL 11 Cluster To: "Dischner, Anton" Cc: Scott Ribe , pgsql-admin Content-Type: multipart/alternative; boundary="0000000000007d68c3062b20fdb2" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000007d68c3062b20fdb2 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Dear Anton For regular backup I mean take dump from specific database that we are use. 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=A6:=D9=A2=D9=A6= =D9=85 kasem adel =D9=83=D8=AA=D8=A8: > Dear Anton > > I need the type of backup that I can take from primary to replica regular > backup or base backup or copy data director. > > 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=A5:=D9=A4= =D9=A1 =D9=85 Dischner, Anton < > Anton.Dischner@med.uni-muenchen.de> =D9=83=D8=AA=D8=A8: > >> 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. >> >> >> >> --0000000000007d68c3062b20fdb2 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Dear Anton=C2=A0

For regular backup I mean take dump from specific database that we are= use.

Thanks=C2=A0
=

=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=A6:=D9=A2=D9=A6 =D9=85 kasem adel <kasemadel8@gmail.com> =D9=83=D8=AA=D8=A8:
Dear Anton

=
I need the type of backup that I can take from prim= ary to replica regular backup or base backup or copy data director.=C2=A0

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=A5:=D9=A4=D9=A1 =D9=85 Disc= hner, Anton <Anton.Dischner@med.uni-muenchen.de> = =D9=83=D8=AA=D8=A8:
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.



--0000000000007d68c3062b20fdb2--