Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.300.87.4.3\))
Subject: Re: Advice Needed: Simultaneous Upgrade of Two-Node PostgreSQL 11
 Cluster
From: Scott Ribe <scott_ribe@elevated-dev.com>
In-Reply-To: 
 <CAP-pBm-_SNXLaOfPbMxs5WWykwfMdJY6Nmgs21oHo4bJFFxXxg@mail.gmail.com>
Date: Tue, 7 Jan 2025 07:10:41 -0700
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <9082A0D6-0C8A-4172-ACB4-0B6ABCF91BF1@elevated-dev.com>
References: 
 <CAP-pBm9YJqrZQgrH1ZsFt9RbTsOTKH1niS5bEhhB_mSsT49B5Q@mail.gmail.com>
 <e122a2f0a0411056585f4b6beb053756a715705b.camel@cybertec.at>
 <CAP-pBm-Y=b6YxHPag0TLertO=7OCo1kN8RVjBg5mvJNx6Y56KA@mail.gmail.com>
 <4f6b5b081397ba9de49725f87d9ad3e40bf30dbf.camel@cybertec.at>
 <5DA36CEB-A299-41C5-8C00-FC1631E6EE8E@elevated-dev.com>
 <CAP-pBm-_SNXLaOfPbMxs5WWykwfMdJY6Nmgs21oHo4bJFFxXxg@mail.gmail.com>
To: kasem adel <kasemadel8@gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/9082A0D6-0C8A-4172-ACB4-0B6ABCF91BF1%40elevated-dev.com>
Precedence: bulk

> On Jan 7, 2025, at 6:50=E2=80=AFAM, kasem adel <kasemadel8@gmail.com> =
wrote:
>=20
> Kindly be informed that database size is 1.8 TB and we have an low =
network bandwidth between two node 10 mbps and to transfer the data from =
scratch that will take from 21 day to 30 day  for this we need solution =
in upgrade to prevent load data from scratch .

So, best case, bzip before transfer might get you down to 3 days, maybe. =
That's one alternative. Others:

- Obviously, if you could get higher network bandwidth temporarily, that =
would help tremendously.
- Express ship DVD/NVMe/SSD with the backup.
- I assume that if 10Mbps keeps up with changes, most of this data is =
inactive, are there inactive tables/partitions?
  - Move inactive data into a separate PG cluster, use foreign data =
wrappers to access it from active so that your users don't see the =
split.
  - Upgrade cluster with inactive data locally.
  - Whatever the process is to move data from active to inactive, you'll =
have to suspend it for the duration.
  -  Whether it's a month or 3 days, transfer the base backup of =
inactive over the slow link (keeping WAL, all the things you have to do =
to make sure that a replica started against this backup will be in sync =
and stream when brought up)
  - Proceed with upgrade of cluster with active data (presuming that it =
is *much* smaller)

Regardless, managing TB+ databases over a 10Mbps link is a challenge.=