We developed a mechanism we called "DbAlign" for doing incremental changes to our Redshift development, test and production databases.
- Run using a single command
- The same code used for building in an empty development environment as in a production environment where tables already exist and contain data
SQL in incrementally numbered patch "build" files
Manually creating corresponding numbered patch rollback files
Storing the patch number that the database was built to explicitly in the database, rather than attempting to determine this from the objects in the database
DbAlign mechanism called with a target patch number. DbAlign would inspect the patch history table in the database and compare to the target patch number, then run the relevant build or rollback files in sequential order so that the database would be built or rolled back to the target patch number
No modification to build and rollback files once they had been applied to production
Build and Rollback File Examples
If an example build file contains a CREATE TABLE statement, then the corresponding rollback file would contain a DROP TABLE statement. In the examples below, all of the build files are in a "build" directory, and the rollback files have the same name, in the "rollback" directory.
To make table example_a available for use in all environments, we therefore needed these two files:
build/00001_create_table_example_a.sql:CREATE TABLE example_a (example_column_1 char(1))
rollback/00001_create_table_example_a.sql:DROP TABLE example_a
If we then wanted to add table example_b, we would create the following two files:
build/00002_create_table_example_b.sql:CREATE TABLE example_b (example_column_1 char(1))
rollback/00002_create_table_example_b.sql:DROP TABLE example_b
If we then want to modify table example a, then we would check if build file 1 has already been deployed to production. If it has not, then we could modify build file 1 however we want, and any new version we create will be used when it is actually applied to production.
But if step 1 has already been applied to production, then the table exists on production already, and any changes we make to step 1 are not going to take effect on production - that is why we have design principle 5 above. The table might contain data at this point that we don't want to lose, so dropping and recreating the table is not appropriate. What we then need to do is modify the table, so we create these two new files:
build/00003_alter_table_example_a.sql:ALTER TABLE example_a ADD COLUMN example_column_2 char(1)
rollback/00003_alter_table_example_a.sql:ALTER TABLE example_a DROP COLUMN example_column_2
Applications Beyond Basic DDL
This approach gave us control over the exact SQL executed, which allowed for a lot of flexibility. We created several conventions for making changes like splitting very large tables into multiple tables, making use of RENAME and DML to move only the selected data and minimise the total time necessary. We added a feature to allow us to do substitution of environment specific variables (such as database usernames, S3 buckets) obtained from the configuration files set up by Puppet, allowing us to create users, or do COPY or UNLOAD where we needed. Whenever someone said "I'll just go and do x on production" we made it possible and in most cases easy to do it with the build process instead.
The mechanism was used in some cases for reference data setup too, for example simple DML statements inserting filetype definitions into a control schema table, or country dialcode prefixes into a table used by the CDR transform processing. It was inconvenient for anything involving choices of IDs, because there wasn't anything built in to help with the choice of IDs, other than an automated test that would complain about duplicate IDs. In practice this became particularly time consuming where normalised structures were involved. We introduced a separate mechanism to update the tables from YAML definitions for some of the metadata which gave us significant development time savings particularly when making changes.