Presented at Postgres Open 2013, Sept 16-18 in Chicago
Working with developers on schema migrations is a perennial challenge for DBAs and developers. Devs tend to like a "set it and forget it" tool. Very few of those tools work well with distributed teams, continuous integration or situations which require raw SQL.
This talk discusses the strategies used to move from a pure SQL and shell migration system to using an ORM and alembic for maximum DBA and developer happiness.
I'll share tips for DBAs and developers in bringing schema elements into the mainstream revision control system, and making it reasonable and easy to get code review on stored procedures from the entire team. Whether you're a Python shop looking for a better migration tool, or a Java-based team looking for something more native to Postgres, you'll get useful insight into the key features your next schema migration system should support.
Our environment is a 2+TB PostgreSQL cluster running 9.2. We have a rapidly evolving schema and reporting system, and over 150 user defined functions in plpgsql. We also support custom types, DOMAINs and data types not supported yet by SQLAlchemy.
Migrating from pure SQL to Python models was the first challenge. Creating a reasonable system for managing the UDFs was the second. The final challenge has been training developers to create and manage their own migrations (called "revisions" in alembic).