I've been asked to write a system which will manage database upgrades for some of our servers. Basically, if a developer needs to apply a change to the database, this change must be pushed out to 2000 servers and must not conflict with any other changes.
Since we use RPC, my thought was, whenever the code is pushed out to the server, the server then makes an RPC call back to a central server (already set up and handling RPC requests), providing its database version and, if the database version is old, the RPC call responds with YAML of successive version numbers and the UPDATE/ALTER SQL to bring the database to the current version required for the code and then tests will run to verify the new schema. This portion of the process can be completely automated.
From the development side, when a developer needs to make a change, he or she will first need to run:
That will reserver a row in the db_upgrade table and write out a
db_upgrade end < some.sql
The SQL file would resemble something like:
ALTER TABLE customer
DROP COLUMN current;
SET value=<% version %>
The version number would be pulled from the
This updates a huge number of servers in data centers which means that this process must not fail, but it requires enough human intervention that I can see plenty of room for failure. Any advice on how to make this more robust would be appreciated.