I work in Tech Support and the some of the products I support are heavily database dependent (PostgreSQL and MySQL, but most of the big ones are MySQL). Before we do an upgrade, we always take a full backup of the database, in case we need to roll back. Upgrades often perform modifications to the schema and there is no mechanism to reverse them if problems are discovered after upgrade.
The problem is that most of our customers have short windows to take the backup, do the upgrade, test the heck out of it, and potentially roll back. There are some long-term customers where the amount of time required to take a backup is now taking up a significant portion of the upgrade window.
Much of the data is no longer updated. A lot of it is not even used but, because reasons (let's just assume they're all justified), it is not purged from the database.
Is there a method where a large portion of the database could be set as read-only and treated separately? It would still need to have schema massage done when required but could be done to a copy outside of the upgrade window.
From what I've read of partitioning, it seems like a candidate. If I understand correctly, large tables can be split and remain the same table. Or does it make more sense to move the old data to different "archive" tables that are unchanging, except for when more data is added.
Would differential backups also do what I want?
I'd like a more fully formed pitch than what I have now before I suggest it to R&D. I'm fine with doing more research, but lack the practical experience to know what avenues to investigate.
Thanks.
2020-11-21 UPDATE: LVM and Storage Spaces
With some research complete, Storage Spaces is Microsoft's current (fairly well reviewed) implementation of what Linux has been good at for a while, LVM. We have both Windows and Linux servers in use.
The nice thing about that approach is that it shouldn't require any modification to or accommodation by our applications; the magic is done at a level below their awareness. Of course, everything would still need to be tested, but I haven't encountered anything discouraging.
Best Answer
Concerning PostgreSQL, I can think of two options:
Low level solution:
Use a storage system or a file system that supports snapshots and take a snapshot before the upgrade.
After the test, either discard the snapshot or restore to it.
Database level solution:
Build up a streaming replication standby database and stop replication before you upgrade.
If the test is fine, discard the standby. If the test fails, discard the master database, promote the standby and continue using that.