pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedDifferential backup in database migration
6+ messages / 4 participants
[nested] [flat]
* Differential backup in database migration
@ 2022-02-16 11:24 Alex Balashov <abalashov@evaristesys.com>
0 siblings, 3 replies; 6+ messages in thread
From: Alex Balashov @ 2022-02-16 11:24 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Differential backup in database migration
@ 2022-02-16 11:29 Michaeldba@sqlexec.com <Michaeldba@sqlexec.com>
parent: Alex Balashov <abalashov@evaristesys.com>
2 siblings, 1 reply; 6+ messages in thread
From: Michaeldba@sqlexec.com @ 2022-02-16 11:29 UTC (permalink / raw)
To: Alex Balashov <abalashov@evaristesys.com>; +Cc: pgsql-admin@lists.postgresql.org
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> 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/
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Differential backup in database migration
@ 2022-02-16 11:36 Alex Balashov <abalashov@evaristesys.com>
parent: Michaeldba@sqlexec.com <Michaeldba@sqlexec.com>
0 siblings, 0 replies; 6+ messages in thread
From: Alex Balashov @ 2022-02-16 11:36 UTC (permalink / raw)
To: Michaeldba@sqlexec.com <Michaeldba@sqlexec.com>; +Cc: pgsql-admin@lists.postgresql.org
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:
>
> 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> 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/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Differential backup in database migration
@ 2022-02-16 12:08 Paul Smith <paul@pscs.co.uk>
parent: Alex Balashov <abalashov@evaristesys.com>
2 siblings, 1 reply; 6+ messages in thread
From: Paul Smith @ 2022-02-16 12:08 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 16/02/2022 11:24, Alex Balashov wrote:
> 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,
How do you know it will not be binary compatible?
I'm fairly sure I've upgraded 9.5 to 14 using pg_upgrade... If you use
the 'link' option in pg_upgrade it just takes a few minutes with
databases that big.
I'd take a backup, put it on a test machine with 9.5 and try to upgrade
it. You may be pleasantly surprised
Paul
--
Paul Smith Computer Services
Tel: 01484 855800
Vat No: GB 685 6987 53
Sign up for news & updates at http://www.pscs.co.uk/go/subscribe
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Differential backup in database migration
@ 2022-02-16 12:36 Alex Balashov <abalashov@evaristesys.com>
parent: Paul Smith <paul@pscs.co.uk>
0 siblings, 0 replies; 6+ messages in thread
From: Alex Balashov @ 2022-02-16 12:36 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Huh! I suppose I don’t know; I just assumed it would take a bunch of fragile, error-prone upgrade jumps across major releases to get five major releases forward.
I’ll definitely follow up on your suggestion, as it is by far the simplest!
> On Feb 16, 2022, at 7:08 AM, Paul Smith <paul@pscs.co.uk> wrote:
>
> On 16/02/2022 11:24, Alex Balashov wrote:
>> 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,
>
> How do you know it will not be binary compatible?
>
> I'm fairly sure I've upgraded 9.5 to 14 using pg_upgrade... If you use the 'link' option in pg_upgrade it just takes a few minutes with databases that big.
>
> I'd take a backup, put it on a test machine with 9.5 and try to upgrade it. You may be pleasantly surprised
>
>
> Paul
>
>
> --
>
>
> Paul Smith Computer Services
> Tel: 01484 855800
> Vat No: GB 685 6987 53
>
> Sign up for news & updates at http://www.pscs.co.uk/go/subscribe
>
>
--
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/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Differential backup in database migration
@ 2022-02-16 13:42 Thomas Kellerer <shammat@gmx.net>
parent: Alex Balashov <abalashov@evaristesys.com>
2 siblings, 0 replies; 6+ messages in thread
From: Thomas Kellerer @ 2022-02-16 13:42 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Alex Balashov schrieb am 16.02.2022 um 12:24:
> 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.
pg_upgrade will happily do the migration from 9.6 directly to 14
If the length of the downtime is a concern, the --link option will make this quite fast.
Thomas
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2022-02-16 13:42 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-02-16 11:24 Differential backup in database migration Alex Balashov <abalashov@evaristesys.com>
2022-02-16 11:29 ` Michaeldba@sqlexec.com <Michaeldba@sqlexec.com>
2022-02-16 11:36 ` Alex Balashov <abalashov@evaristesys.com>
2022-02-16 12:08 ` Paul Smith <paul@pscs.co.uk>
2022-02-16 12:36 ` Alex Balashov <abalashov@evaristesys.com>
2022-02-16 13:42 ` Thomas Kellerer <shammat@gmx.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox