From: Alex Balashov <abalashov@evaristesys.com>
Content-Type: text/plain;
	charset=utf-8
Content-Transfer-Encoding: quoted-printable
Mime-Version: 1.0 (Mac OS X Mail 15.0 \(3693.40.0.1.81\))
Subject: Differential backup in database migration
Message-Id: <F804329E-8A76-42F8-BCD0-A32FE6FA243D@evaristesys.com>
Date: Wed, 16 Feb 2022 06:24:17 -0500
To: pgsql-admin@lists.postgresql.org
Sender: sasha@evaristesys.com
Archived-At: 
 <https://www.postgresql.org/message-id/F804329E-8A76-42F8-BCD0-A32FE6FA243D%40evaristesys.com>
Precedence: bulk

Hi,

I sincerely apologise if this is a stupid question, in that it has been =
answered enough times to enter an FAQ canon:

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.

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.

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.

I have no experience with pgBackRest or any such tool, so would =
appreciate any advice on how to best go about this!=20

Thank you in advance, and apologies again if the question is asked =
routinely.

=E2=80=94 Alex

--=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/