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

Consider pglogical with hardly any downtime and no need to catch any changes=
 during the cutover.

Sent from my iPad

> On Feb 16, 2022, at 6:24 AM, Alex Balashov <abalashov@evaristesys.com> wro=
te:
>=20
> =EF=BB=BFHi,
>=20
> I sincerely apologise if this is a stupid question, in that it has been an=
swered enough times to enter an FAQ canon:
>=20
> I=E2=80=99m faced with the daunting prospect of upgrading a PG 9.6 install=
ation to 14, in the process migrating a ~400 GB database. Due to the large j=
ump in major versions, the on-disk block storage of course will not be in th=
e slightest bit binary-compatible, so I will have to dump out the DB & impor=
t 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 t=
he old 9.6 DB. The issue, of course, is that the 9.6 DB will change during t=
he time this snapshot is being dumped out and imported. Ultimately, the targ=
et database will need to be brought into consistency with the incumbent data=
base (more or less) at the point of a cut-over.
>=20
> I suppose the most intuitive solution to me would be to import the snapsho=
t to the target DB, then take another, smaller differential backup of some k=
ind, and in turn import that. But perhaps there=E2=80=99s another solution a=
ltogether, i.e. a much faster backup & restore? I see that pgBackRest makes t=
his 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 routine=
ly.
>=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