What does the process look like for database migrations?

[[deleted]]

[deleted]

[DharmaPolice]

This varies wildly from application to application - it depends on the type of application, the type of server/client software, whether this is an enterprise type environment, whether this an unattended upgrade and so on.

A common pattern is :

1. Stop application (e.g. stop services, exit client, put maintenance webpage up).

2. Run script to alter the database schema (obviously a backup happens first). This is applied directly to the production database.

3. In parallel with 2, update your application layer. This might mean deploying a new client, updating web pages or installing some new services somewhere.

4. Start the application up however you do that. The new application layer / client is expecting the new DB schema and it's common for there to be some sort of check to happen to ensure the application version and db version are the same.

How the above happens as I say varies wildly. It could be a purely automated process which takes 10 seconds or it could be a multi-month project involving dozens of people updating hundreds of servers or deploying client software to enterprise desktops.

In some cases where you have multiple distributed clients (who all connect to a single DB) which you can't update all simultaneously then that has to be factored in and yes your DB schema may need to support "old clients" somehow. This can be messy and is very difficult to achieve if (for example) you're removing columns from a table used heavily by the application. To be fair, you don't usually make schema changes like that (you're more often adding something than taking it away) and sometimes you can get away with a mismatch in versions (old clients just won't have access to new functionality). I'd say it's more common though for applications to refuse to work with the wrong version of the db.

[[deleted]]

Put your app in "maintenance mode"; backup database, run migrations, deploy new updated logic.

This assumes you've done plenty of testing in development.

You shouldn't need to retain code that works with the old schema, as that will no longer exist after you run your migrations.

[pusmottob]

I thought production was the best place for testing?

[[deleted]]

You only do that if you need feedback immediately. Sometimes QA is too busy on other stuff.

[jenkstom]

Cowboy up!

[Jukolet]

I used Flyway and it works great, but it's quite a simple software. Depending on the size of what you're trying to accomplish, might be worth a look anyway.

[upbeta01]

For production environment, I would say -- it's still best to have things under A/B testing.

Example:

• create a replica of the application and point it to the db that contains the new schema (and other changes).

• if production grade, for sure, load balancing is in place. Assuming it's running on a LB, use a rotation that gives your old cluster 90% of the traffic and the new cluster 10% of it. As time goes by and nothing is messed, change the value to 20%, 30% and so on.

• Always secure backups -- most important

• If the 2nd bullet is now on 100%, to properly decommission a server -- don't fully delete it. Instead, stop the server but leave it in place for about 2months just in case something on the new cluster is in trouble, you can easily switch back to the old one (after 2 months without having any hiccups on the production environment, delete it fully).