Thread: Differential backup in database migration
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 DBwill change during the time this snapshot is being dumped out and imported. Ultimately, the target database will need tobe 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, smallerdifferential backup of some kind, and in turn import that. But perhaps there’s another solution altogether, i.e. amuch 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/
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.6DB will change during the time this snapshot is being dumped out and imported. Ultimately, the target database will needto 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, smallerdifferential backup of some kind, and in turn import that. But perhaps there’s another solution altogether, i.e. amuch 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/ > > >
Ah, interesting. I did not realise there is any replication scheme, whether logical or physical, which would be binary-compatibleacross 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.6DB will change during the time this snapshot is being dumped out and imported. Ultimately, the target database will needto 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, smallerdifferential backup of some kind, and in turn import that. But perhaps there’s another solution altogether, i.e. amuch 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/
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
Huh! I suppose I don’t know; I just assumed it would take a bunch of fragile, error-prone upgrade jumps across major releasesto 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 afew 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/
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