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.


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.

  • I'm not asking for a subjective "best" but for some objective "possible" I can dig into the pros and cons as I get further along.
    – Naptha
    Jul 15, 2020 at 8:30

2 Answers 2


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.

  • Sorry, long delay before promised comment. I picked your answer out of the two because you gave two options. Your PostgreSQL answer would also apply to MySQL. I've brought up Snapshots (and Windows Dynamic Disks/Volumes) with some other people who I can trust to get all research happy. This is exactly the sort of thing that required experienced people like you and Rick to find. I was deep in a Google hole based on my initial assumptions. Thanks again!
    – Naptha
    Aug 23, 2020 at 8:10

Are you talking about a single server? Or a set of servers -- web servers + Source database ("Master") and Replicas?

In any case, LVM is probably the best solution. It allows you to capture the entire machine in a matter of minutes, regardless of how big the server is. It requires some initial setup. It works at a very low level, so it does not matter what is on the machine.

COW (Copy On Write) is the underlying secret. The disk becomes two disks, but only the differences cost any effort.

LVM sounds especially good for your job position -- which seems to entail low level activity on many servers. (As opposed to a programmer who would be expected to spend his time on programming, not fiddling with servers.)

  • Very tough call on whether to choose your answer or Laurenz's. I'll put my reasoning in a comment on Laurenz's answer later when not typing on a phone. Thanks. And regarding your question about number of servers: all are instances of webserver + source database. The PostgreSQL ones are standalone and MySQL for redundant. In the redundant systems, both webservers are accessing the primary database during normal operation and the redundant db is just a slave reading the bin logs. We do have enterprise systems with different configuratione, but 95% are as above.
    – Naptha
    Jul 15, 2020 at 22:51
  • Updated my question with a bit of information about LVM.
    – Naptha
    Nov 21, 2020 at 19:56
  • @Naptha - Thanks. Keep the community posted on what you find out. You could even post a Question about the usage of LVM on Windows and immediately post an Answer with the results of your research.
    – Rick James
    Nov 21, 2020 at 23:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.