Re: Differential backup in database migration

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(at)sqlexec(dot)com wrote:
>
> 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(at)evaristesys(dot)com> wrote:
>>
>> Hi,
>>
>> I sincerely apologise if this is a stupid question, in that it has been answered enough times to enter an FAQ canon:
>>
>> I’m 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’t 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’s 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!
>>
>> Thank you in advance, and apologies again if the question is asked routinely.
>>
>> — Alex
>>
>> --
>> 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/
>>
>>
>>
>
>
>

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