Using sqitch in production, the main issue is that it effectively has no knowledge of what is happening except for running psql files for you. So it's hard to keep track of what the actual resulting schema should look like after each change has been applied to work out whether it's correct.
Getting people to write deploy/revert scripts are relatively easy. Asking them to write verify scripts that do more than check that a column has been added/removed is hard.
There's the "physical" issues of modifying a schema, and sqitch is great for that. But handling the "logical" issues of schema migration is more than an automated tool for running scripts.
This tool (pgroll) allows you to actually test the modified schema for logical validity without impacting the ongoing operations, which to me, seems like a win.
> Using sqitch in production, the main issue is that it effectively has no knowledge of what is happening except for running psql files for you.
I came to the conclusion that you can not have anything more than that without compromising on how you can change your schema.
> So it's hard to keep track of what the actual resulting schema should look like after each change has been applied to work out whether it's correct.
At first sight, this is a gripe that has to be addressed to SQL itself that the SQL to change a schema can not trivially be inferred from the "before" and "after" schema definitions. But probably there is no way around it, il all generality.
One can store the current version of the schema in the source repository and make sure that the schema extracted after one or several applications of the migration match that.
Asking devs to build their own dependency system for the changes is also part of the issue.
Really the only way I can think of is some sort of DAG of DDL blobs, but I have no idea whether you can express a combination of DDL and DML changes into the one tree so that they can be properly diff'ed.
Changes in production are not just changes in the schema, there are changes to data as well. Really the entire RDBMS needs to have "time" and DDL incorporated into the relations.
Getting people to write deploy/revert scripts are relatively easy. Asking them to write verify scripts that do more than check that a column has been added/removed is hard.
There's the "physical" issues of modifying a schema, and sqitch is great for that. But handling the "logical" issues of schema migration is more than an automated tool for running scripts.
This tool (pgroll) allows you to actually test the modified schema for logical validity without impacting the ongoing operations, which to me, seems like a win.