We are about to roll out some of our new stuff to a production-like staging server, exciting times! One of the lessons we did NOT learn early enough is that if you're not going to automate your database changes, you really need to keep on top of organising them!
As such, we cannot be sure what has and has not been run on the staging DB.
One option for fixing this was to spend a few hours sifting through Jira tickets working out what updates we needed…. or we could work out how to do a diff on the schemas.
The standard library for this is Alembic. It works well with Flask, SQLAlchemy, Postgres… but wow that documentation is dense! Luckily, before I had to do too much hard work, I found a little tool called Migra which does exactly what I wanted and is super simple.
It's a nice little tool command line tool that generates (but does not run..!!) all the updates/alters/etc that you need to turn database_one into database_two. The --unsafe flag means it will output drop commands, so be a bit careful with that…
I just got a pg_dump of both db schemas (using -s -c flags to make sure to include the create database statement, yeh I missed that first time around!), spun them up in docker containers and ran:
migra --unsafe postgresql://[email protected]:1212/database_one postgresql://[email protected]:1111/database_two >> db_migration.sql
Easy!
Except we are still rolling Postgres 9.6, and because of reasons, I had to downgrade Migra to a slightly older version like so:
pip3 uninstall migra schemainspect
pip3 install migra==1.0.1541154795 schemainspect==0.1.1541069836