1

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.

  • 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 at 8:30
1

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.

| improve this answer | |
  • 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 at 8:10
1

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.)

| improve this answer | |
  • 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 at 22:51
  • Updated my question with a bit of information about LVM. – Naptha yesterday
  • @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 yesterday

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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