Date: Thu, 30 May 2019 17:31:28 +0200
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Haroldo Kerry <hkerry@callix.com.br>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Shortest offline window on database migration
Message-ID: <20190530152914.l2h5f4fomro37ieh@development>
References: 
 <CAHxH9rPuQaXz28Qkgpnp0-kfJgNFArjiba3P2m9iC3C7zBQ5_A@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Disposition: inline
In-Reply-To: 
 <CAHxH9rPuQaXz28Qkgpnp0-kfJgNFArjiba3P2m9iC3C7zBQ5_A@mail.gmail.com>
User-Agent: NeoMutt/20180716-1444-295967-dirty
Precedence: bulk

On Thu, May 30, 2019 at 12:08:04PM -0300, Haroldo Kerry wrote:
>Hello,
>
>We are migrating our PostgreSQL 9.6.10 database (with streaming replication
>active) to a faster disk array.
>We are using this opportunity to enable checksums, so we will have to do a
>full backup-restore.
>The database size is about 500GB, it takes about 2h:30min for a full
>backup, and then about 1h to fully restore it with checksum enabled on the
>new array, plus 2h to recreate the replica on the old array.
>
>Although all synthetic tests (pgbench) indicate the new disk array is
>faster, we will only be 100% confident once we see its performance on
>production, so our backup plan is using our replica database on the older
>array. If the new array performance is poor during production ramp up, we
>can switch to the replica with little impact to our customers.
>
>Problem is the offline window for backup, restore the full database with
>checksum and recreate the replica is about 5h:30m.
>
>One thing that occurred to us to shorten the offline window was restoring
>the database to both the master and replica in parallel (of course we would
>configure the replica as master do restore the database), that would shave
>1h of the total time. Although this is not documented we thought that
>restoring the same database to identical servers would result in binary
>identical data files.
>
>We tried this in lab. As this is not a kosher way to create a replica, we
>ran a checksum comparison of all data files, and we ended up having a lot
>of differences. Bummer. Both master and replica worked (no errors on logs),
>but we ended up insecure about this path because of the binary differences
>on data files.
>But in principle it should work, right?

What should work? Backup using pg_dump and restore certainly won't give
you the same binary files - the commit timestamps will be different,
operations may happen in a different order (esp. with parallel restore),
and so on. And the instances don't start as a copy anyway, so there will
be different system IDs, etc.

So no, this is not a valid way to provision master/standby cluster.

>Has anyone been through this type of problem?
>

Unfortunately, I don't think there's a much better solution that what you
initially described - dump/restore, and then creating a replica.


regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services