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

  1. pg_dump -s -h dbhost garfield > original_sql
  2. 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.
  3. pg_dump -s -h dbhost garfield > modified_sql
  4. apgdiff original.sql modified.sql > upgrade.sql
  5. apgdiff modified.sql original.sql > downgrade.sql
  6. Hand edit upgrade.sql and downgrade.sql (possibly adding SQL code to migrate the data as well).
  7. Move to a new directory in resources/sql_patches/ and add any necessary dependencies to resources/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

  1. 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.)
  2. 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
  3. 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.
  4. Invoke the migrations tool to initialize the database. sudo is no longer needed. ./garfield-migrate init postgresql:///garfield
  5. Upgrade to the latest schema version. ./garfield-migrate upgrade postgresql:///garfield
  6. 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'