Re: Shortest offline window on database migration
> 2019年5月31日(金) 6:53 Haroldo Kerry : >>> On Thu, May 30, 2019 at 12:54 PM Steven Winfield >>> wrote: >> >> >Has anyone been through this type of problem? >> > >> >> You could set up a new, empty db (with checksums enabled, etc.) on the >> new hardware and then use logical replication to sync across all the data >> from the existing cluster. >> (This logical replica could be doing binary replication to hot standbys >> too, if you like). >> >> When the sync has finished you could perhaps gradually shift read-only >> load over to the new db, and finally switch write load too - your downtime >> would then be limited to how long this final cut-over takes. >> > Steve. > Hello Steven, > Unfortunately logical replication is a pg10+ feature. One more reason for > upgrading from 9.6.10 :-) Have you looked at the pglogical extension from 2ndQuadrant? https://github.com/2ndQuadrant/pglogical/tree/REL2_x_STABLE Regards Ian Barwick -- Ian Barwick https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: Shortest offline window on database migration
On Thu, May 30, 2019 at 11:08 AM 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. > As others have noticed, your "trick" won't work. So back to basics. Are you using the best degree of parallelization on each one of these tasks? What is the bottleneck of each one (CPU, disk, network)? how are you creating the replica? Can you share the actual command lines for each one? It seems odd that the dump (which only needs to dump the index and constraint definitions) is so much slower than the restore (which actually needs to build those indexes and validate the constraints). Is that because the dump is happening from the old slow disk and restore a new fast ones? Same with creating the replica, why is that slower than actually doing the restore? It sounds like you are planning on blowing away the old master server on the old array as soon as the upgrade is complete, so you can re-use that space to build the new replica? That doesn't seem very safe to me--what if during the rebuilding of the replica you run into a major problem and have to roll the whole thing back? What will the old array which is holding the current replica server be doing in all of this? Cheers, Jeff >
RE: Shortest offline window on database migration
>Has anyone been through this type of problem? > You could set up a new, empty db (with checksums enabled, etc.) on the new hardware and then use logical replication to sync across all the data from the existing cluster. (This logical replica could be doing binary replication to hot standbys too, if you like). When the sync has finished you could perhaps gradually shift read-only load over to the new db, and finally switch write load too - your downtime would then be limited to how long this final cut-over takes. Steve.
Re: Shortest offline window on database migration
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
Shortest offline window on database migration
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? Has anyone been through this type of problem? Regards, Haroldo Kerry
