pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Michaeldba@sqlexec.com <Michaeldba@sqlexec.com>
To: Alex Balashov <abalashov@evaristesys.com>
Cc: pgsql-admin@lists.postgresql.org
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> (raw)
In-Reply-To: <F804329E-8A76-42F8-BCD0-A32FE6FA243D@evaristesys.com>
References: <F804329E-8A76-42F8-BCD0-A32FE6FA243D@evaristesys.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.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/
> 
> 
> 






view thread (6+ messages)  latest in thread

Message-ID: <9AD73617-C013-4AF6-80C9-B7F1ECCE02A6@sqlexec.com>
Permalink:  ../9AD73617-C013-4AF6-80C9-B7F1ECCE02A6@sqlexec.com/
Also on:    postgresql.org/message-id/9AD73617-C013-4AF6-80C9-B7F1ECCE02A6@sqlexec.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: Michaeldba@sqlexec.com, abalashov@evaristesys.com, pgsql-admin@lists.postgresql.org
  Subject: Re: Differential backup in database migration
  In-Reply-To: <9AD73617-C013-4AF6-80C9-B7F1ECCE02A6@sqlexec.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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