Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 15.0 \(3693.40.0.1.81\))
Subject: Re: Differential backup in database migration
From: Alex Balashov <abalashov@evaristesys.com>
In-Reply-To: <9AD73617-C013-4AF6-80C9-B7F1ECCE02A6@sqlexec.com>
Date: Wed, 16 Feb 2022 06:36:05 -0500
Cc: pgsql-admin@lists.postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <6AC5B740-2A61-4B95-A56E-AA1EB32CF5C4@evaristesys.com>
References: <F804329E-8A76-42F8-BCD0-A32FE6FA243D@evaristesys.com>
 <9AD73617-C013-4AF6-80C9-B7F1ECCE02A6@sqlexec.com>
To: "Michaeldba@sqlexec.com" <Michaeldba@sqlexec.com>
Sender: sasha@evaristesys.com
Archived-At: 
 <https://www.postgresql.org/message-id/6AC5B740-2A61-4B95-A56E-AA1EB32CF5C4%40evaristesys.com>
Precedence: bulk

Ah, interesting. I did not realise there is any replication scheme, =
whether logical or physical, which would be binary-compatible across =
such a large release gap.

> On Feb 16, 2022, at 6:29 AM, Michaeldba@sqlexec.com wrote:
>=20
> Consider pglogical with hardly any downtime and no need to catch any =
changes during the cutover.
>=20
> Sent from my iPad
>=20
>> On Feb 16, 2022, at 6:24 AM, Alex Balashov =
<abalashov@evaristesys.com> wrote:
>>=20
>> =EF=BB=BFHi,
>>=20
>> I sincerely apologise if this is a stupid question, in that it has =
been answered enough times to enter an FAQ canon:
>>=20
>> I=E2=80=99m faced with the daunting prospect of upgrading a PG 9.6 =
installation to 14, in the process migrating a ~400 GB database. Due to =
the large jump in major versions, the on-disk block storage of course =
will not be in the slightest bit binary-compatible, so I will have to =
dump out the DB & import it into the target DB, live via a network pipe.
>>=20
>> This process is time-consuming, and can=E2=80=99t afford the downtime =
on the old 9.6 DB. The issue, of course, is that the 9.6 DB will change =
during the time this snapshot is being dumped out and imported. =
Ultimately, the target database will need to be brought into consistency =
with the incumbent database (more or less) at the point of a cut-over.
>>=20
>> I suppose the most intuitive solution to me would be to import the =
snapshot to the target DB, then take another, smaller differential =
backup of some kind, and in turn import that. But perhaps there=E2=80=99s =
another solution altogether, i.e. a much faster backup & restore? I see =
that pgBackRest makes this argument to the prospective user.
>>=20
>> I have no experience with pgBackRest or any such tool, so would =
appreciate any advice on how to best go about this!=20
>>=20
>> Thank you in advance, and apologies again if the question is asked =
routinely.
>>=20
>> =E2=80=94 Alex
>>=20
>> --=20
>> Alex Balashov | Principal | Evariste Systems LLC
>>=20
>> Tel: +1-706-510-6800 / +1-800-250-5920 (toll-free)
>> Web: http://www.evaristesys.com/, http://www.csrpswitch.com/
>>=20
>>=20
>>=20
>=20
>=20
>=20

--=20
Alex Balashov | Principal | Evariste Systems LLC

Tel: +1-706-510-6800 / +1-800-250-5920 (toll-free)
Web: http://www.evaristesys.com/, http://www.csrpswitch.com/