20

A typical advice before any production deployments is backup the DB first. This way, if the new update has some issue that can lead to potential data loss or logical data corruption, then you still have a backup to compare and correct old records.

However, this can work well till DB size is in few GBs. Once the DB size is huge, backups take a long time complete. What are some best practices that should be followed in such situations, so as to avoid logical data corruption because of logical issues in a code deployment?

  • 11
    Backups are not only for when you do deployments. I mean, your loss of data is just one disk-crash away, and those are unpredictable and can happen today or tomorrow. (Raid arrays are not the answer, they also crash.) – Pieter B Jan 25 '18 at 9:14
  • 10
    I would rephrase this question, the problem is not that backups take a long time, the problem is that in case an update has a desastrous failure, a restore may become necessary, which can block the production for a long time. So what you are really after is a strategy to mitigate the risks of a failure during an update. – Doc Brown Jan 25 '18 at 11:37
  • 1
    I agree with @DocBrown here. Avoiding data corruption and backups taking too long are really two separate questions. – Robbie Dee Jan 25 '18 at 13:30
  • 1
    When you accept quickly you don't get as much input. – paparazzo Jan 25 '18 at 14:02
  • 1
    What do you mean "logical issues in a code deployment"? – paparazzo Jan 25 '18 at 14:05
25

As someone who regularly dealt with updating production database for customers for our software upgrades, I tell you that the best way to minimize errors is to make updates as straightforward as possible.

If you can perform a change to all records rather than specific records, it is preferable.

In other words, if you're given a list of ids of records which need their state changed, you should be asking yourself why the update is being done in the context of the program. It may be that of the 10 records you need to update, the table only has 10 elements. Therefore you should be asking yourself if conceptually all you're doing is updating the state of all records.

If you can insert, it is preferable.

The act of adding a record is self-contained. By this I mean there is only one side effect of adding a record, and that is the existence of a record that didn't exist prior. Therefore unless you're adding a record which shouldn't be there, there should be no issues.

If you can avoid deletion, it is preferable.

If you're performing a deletion, you're removing data which would otherwise be unrecoverable without a backup. If possible, try to organize the data in such a way that you can disable records by changing its state rather than physically deleting the record. The excess of data can be put in a partition or it can be removed entirely in a later moment once you're sure there are no problems.

Have a consistent update policy.

If you need to update a record, one of several things can happen:

  1. Your record doesn't exist.
  2. Your record exists but it has already been changed.
  3. Your record exists and requires the change.

You need to have a policy to determine the course of action should something not go as planned. For simplicity sake, you should be consistent across the board and apply this policy in any situation of this type, not just for specific tables. This makes it easier to be able to recover data later. Generally, my policy is to write the script in such a way as to be able to re-execute it later. Should the script fail, it is nice to know you can make the proper adjustments and re-execute, however you're free to pick your own policy that suits you best.

Backups

This by no means excuses you from performing a backup prior to performing any update in a production environment! Though even with a backup, I consider it a failure to have to use the backup. Losing data cannot be a possibility even in the worst-case scenario.

Conclusion

You're not always going to be able to have it your way. The table schema is not likely going to be determined by you, and as such it means the types of updates you can expect to perform will be both complicated and risky. Though if you have any say-so in the matter, it helps to keep these points in mind as they make any updates straightforward and without significant risk.

Good luck!

  • I agree with everything you said, but I was curious on your thoughts of transactions when there are 10 records that need changing out of 10k and inserts/updating all records are not viable? – I'm here for Winter Hats Jan 25 '18 at 19:07
  • Then you just update the 10 records. I said if you can, do it. I didn't say do it even if it destroys your customer's production database. Take my advice with a grain of salt please. – Neil Jan 30 '18 at 7:56
12

At that point, you should be using a commercial grade DB system that supports snapshots (Oracles calls it Flashback) - that's exactly the kind of thing they are for.

Keep in mind that you need a backup concept anyway - having more data doesn't mean you drop backups because they become difficult, quite the opposite. You need some kind of continuous backup, e.g. based on replication with automatic failover.

  • I am not saying I want to drop backups. Scheduled backups are always there. The questions is more around ad-hoc backups, which are not a problem is small systems. – Pritam Barhate Jan 25 '18 at 10:03
  • To elaborate further, this line of thought came from NoSQL DB as Service platforms. Actually was reading Firestore documentation, when it popped up. If you need offsite logically consistent backups, it seems very expensive. So I was wondering how successful product teams work with such systems and how they ensure that logical data corruption doesn't happen. – Pritam Barhate Jan 25 '18 at 10:20
  • @PritamBarhate: you don't need "more backups" because of updates. On a production database where people work with that data, backups have to done at least daily, with or without updates. Restores are your problem, you want to avoid unnecessary restores under all circumstances. – Doc Brown Jan 25 '18 at 11:43
  • 3
    Replication with automatic failover is redundancy is no more a backup strategy for databases than using RAID is for disks. – Blrfl Jan 25 '18 at 12:38
  • 1
    All good points about backups and snapshots, but cleaning up a botched database operation (if several hours of new data have been added before it is realized) can be very difficult depending on the scenario and the other systems it affects (schedulers, other database entries that rely on it, if it spans several tables, caches, authentication, etc). I always assume I'm going to have to use a backup, but always at least try to never have to. – Anonymous Penguin Jan 25 '18 at 18:43
3

This is a massive area - so expect this question to be closed in fairly short order but, off the top of my head (as a former DBA on yuge databases):

Mart/Repository

You can mitigate some risk if you have a separate database for updates and a separate database that everyone uses. Then it is just a case of copying the data from one DB to the other once various checks have taken place. Mart/repository is how it is sometimes described but you might have primary/secondary, master/slave etc.

Source code

For everything that can change, have a source code which relates to how the data was updated. How many of these you have varies from DB to DB but you might have one for each user, role, data feed, code module etc.

Create/update date

Something that can assist greatly when tracking where things have gone wrong is having a creation and update data for every row. Then you can see at a glance which rows have been updated.

ETL

If the database update takes part as part of a data factory, you may be able to restore a previous vintage from flat files.

Backup

Full backups do of course take lots of space but the usual scenario is for a full backup to happen at regular intervals (says, weekly) and partial ones on a more frequent basis (daily etc).

Point in time recovery

Depending on which RDBMS you are using, some support point in time recovery. This allows you to roll back to the time when a good state was known. This does however require a large amount of storage which increases for how far you want to go back.

Audit

Having audit tables will tell you who (or what) made an update to a row. This can give you a good starting point for investigation.

History

For some critical tables, a copy of the pertinent row is taken at the time of the update so the data can be restored if need be.

Data validation

Ensure basic validation checks are carried out on the data before it is stored - over and above basic data type checks.

Referential integrity

Referential integrity isn't a silver bullet but it can help ensure the data is well structured.

2

Many times if we are doing a "one shot" update we take a back up of production and restore it to a test server. Then we create a suit of tests and run the one shot. We verify the data has changed via the tests and become comfortable the that update will succeed and modify the data in a way that we expect it to. This is called a dry or trial run. I recommend doing this.

This gives everyone a good sense that the one shot will succeed. We cannot guarantee 100% because the data will be updated from the date of the trial run, but we boost confidence and success factors. This also gives a real idea of any issues that will occur since we are using a copy of production. Now if for some reason the update fails, we can always go to the back run prior to restore if needed, but we should have found and remediated any issues with the dry run.

If you can't take the entire database (if really large) try and export a smaller sample size and run the update (small dry run) against the actual data. I'd prefer the entire data set if possible to ensure the test is as complete as possible.

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.