SQL migrations¶
How to use migrations¶
The DBURL needs to point to an existing database where the user has superuser powers (at least initially, in later revisions, it suffices to be the database owner, i.e. garfield_superuser).
DBURL=postgresql:///garfield
To place a fresh database under version control:
./garfield-migrate init $DBURL
If the database is not fresh, but already contains all patches:
./garfield-migrate init --all-patches $DBURL
To upgrade a database to the current version:
./garfield-migrate upgrade $DBURL
To downgrade the database to emptiness:
./garfield-migrate downgrade $DBURL
To remove specific patch from the database:
./garfield-migrate downgrade PATCH_NAME $DBURL
To test whether a specific patch would successfully upgrade and downgrade repeatedly:
./garfield-migrate test PATCH_NAME $DBURL
To test whether all unapplied patches would successfully upgrade and downgrade repeatedly:
./garfield-migrate test $DBURL
To see which patches are currently applied / unapplied:
./garfield-migrate status $DBURL
The patches are stored in a directory tree, typically
resources/sql_patches/
. For local patches that add site specific data you
can create one or more additional repositories and make garfield-migrate aware
of them by passing --add-repo REPO
as a parameter to all
garfield-migrate
calls:
./garfield-migrate --add-repo contrib/sample_data upgrade $DBURL
How to create a migration patch set¶
pg_dump -s -h dbhost garfield > original_sql
- Perform changes to DB schema and app as usual. Test app. Tweak schema some more, and so on... When you’re done with your changes go to step 3.
pg_dump -s -h dbhost garfield > modified_sql
apgdiff original.sql modified.sql > upgrade.sql
apgdiff modified.sql original.sql > downgrade.sql
- Hand edit upgrade.sql and downgrade.sql (possibly adding SQL code to migrate the data as well).
- Move to a new directory in
resources/sql_patches/
and add any necessary dependencies toresources/sql_patches/«DIR»/depends_on
.
- Note:
- apgdiff does not output role creations, so you need to add those manually to the patches.
How to setup a development database¶
- Install the PostgreSQL server. Version 8.4 or higher is required.
(On Debian-based system you can
sudo apt-get install postgresql-8.4
or the concrete version available.) - Switch to the postgres user and create a superuser role for you.
The username specified needs to be identical to your regular
username.
sudo -u postgres createuser -s username
- Create a database for Garfield. Let’s use the postgres system
user once again for this:
sudo -u postgres createdb -E UTF8 -O username garfield
Replace username with your username once again. - Invoke the migrations tool to initialize the database. sudo is no
longer needed.
./garfield-migrate init postgresql:///garfield
- Upgrade to the latest schema version.
./garfield-migrate upgrade postgresql:///garfield
- Import the sample data.
cat garfield_basic_data.sql garfield_sample_data.sql | psql garfield
How to work with test_nightly
¶
test_nightly is a postgresql cluster that’s reset every night. If you want to develop garfield, you should be added as garfield_superuser on that cluster. That way you can do migrations as above without disrupting the production setup. The easiest way is from a shell on fsmi-db. You do not need any sudo powers.
Please note: Some cooperation is advised, given that you can reset another developer’s state on the way.
To upgrade the database to the most current version in your repository just execute:
./garfield-migrate upgrade postgresql://:5435/garfield
As above you might want to add --add-repo contrib/sample_data
to
the command-line to include optional local data patches.
To add a user as garfield_superuser, an admin needs to modify
/var/lib/postgresql/test_nightly-snippets.d/04-garfield_superusers.sql
and re-run sudo -u postgres pg_clonecluster --src-ver=8.4 --dst-ver=8.4
--src-name=main --dst-name=test_nightly --force --quiet
.
To actually run garfield against the test database you should invoke the scripts while being in the garfield source top-level directory and modify garfield.conf to specify the port:
[database]
dbname = garfield
server = fsmi-db
port = 5435
How to migrate from old-style garfield-migrate
¶
Drop the migrate_version table
and convert to the new migration system:
$ sudo -u postgres ./garfield-migrate convert-init postgresql:///garfield
notice: migration_version table exists
notice: detected migration version 11
notice: dropping migration_version table
notice: creating new migration metadata
notice: conversion complete
Apply the missing patches:
$ sudo -u postgres ./garfield-migrate upgrade postgresql:///garfield
applying patch '013_snacks_with_authoring_metadata'
applying patch '014_fix_print_account_log_view'
applying patch '015_print_user_account_log'
applying patch '016_add_user_trans_cancel_log'
applying patch '017_cancel_snack_buy'