This repository, which began as a simple Gist, provides a tiny starter kit and instructions for safely performing schema migrations on a PostgreSQL database. It works by providing a PLPGSQL procedure which can execute a chunk of SQL and then note it as having been executed, so that it will not be executed again the next time.
We're actively using this in production over at NEC Smart Cities with great success, so it is now somewhat proven and maintained.
Copy migrations.sql to your project. Add migrations to the end of
that file in the form of calls to apply_migration, as shown in the
examples in that file. To apply all pending migrations, including
bootstrapping the migration function and its migrations table, run
the file against your database in a single transaction with psql, or
via your database connection adaptor.
psql -v ON_ERROR_STOP=1 -1f -- migrations.sql yourdbname
You should generally arrange to run migrations as the database owner (or even the super-user), and your applications should use less-privileged users.
This software was written by Steve Purcell.
MIT license.
Author links: