Migrating database changes from development to live

I don't trust programmatic migrations. If it's a simple change, such as adding a NULLable column, I'll just add it directly to the live server. If it's more complex or requires data changes, I'll write a pair of SQL migration files and test them against a replica database.

When using migrations, always test the rollback migration. It is your emergency "oh shit" button.

The solution I use (originally developed by a friend of mine) is another addendum to yukondude.

1. Create a schema directory under version control and then for each db change you make keep a .sql file with the SQL you want executed along with the sql query to update the db_schema table.
2. Create a database table called "db_schema" with an integer column named version.
3. In the schema directory create two shell scripts, "current" and "update". Executing current tells you which version of the db schema the database you're connected to is currently at. Running update executes each .sql file numbered greater than the version in the db_schema table sequentially until you're up to the greatest numbered file in your schema dir.

Files in the schema dir:

0-init.sql 
1-add-name-to-user.sql
2-add-bio.sql

What a typical file looks like, note the db_schema update at the end of every .sql file:

BEGIN;
-- comment about what this is doing
ALTER TABLE user ADD COLUMN bio text NULL;

UPDATE db_schema SET version = 2;
COMMIT;

The "current" script (for psql):

#!/bin/sh

VERSION=`psql -q -t <<EOF
\set ON_ERROR_STOP on
SELECT version FROM db_schema;
EOF
`

[ $? -eq 0 ] && {
    echo $VERSION
    exit 0
}

echo 0

the update script (also psql):

#!/bin/sh

CURRENT=`./current`
LATEST=`ls -vr *.sql |egrep -o "^[0-9]+" |head -n1`

echo current is $CURRENT
echo latest is $LATEST

[[ $CURRENT -gt $LATEST ]] && {
    echo That seems to be a problem.
    exit 1
}

[[ $CURRENT -eq $LATEST ]] && exit 0

#SCRIPT_SET="-q"
SCRIPT_SET=""

for (( I = $CURRENT + 1 ; I <= $LATEST ; I++ )); do
    SCRIPT=`ls $I-*.sql |head -n1`
    echo "Adding '$SCRIPT'"
    SCRIPT_SET="$SCRIPT_SET $SCRIPT"
done

echo "Applying updates..."
echo $SCRIPT_SET
for S in $SCRIPT_SET ; do
    psql -v ON_ERRO