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
