PG Migrator

Gem Version Build Status

  • This tool provides a set of Rake tasks to apply changes on different Postgres environments.
  • Every change, upgrade or rollback, is versionned.
  • Each environment is described by a configuration file.
  • Host, user, database name and migration SQL scripts location are defined in the configuration file.
  • For example, 'config/uat' is a 'User Aceptance Testing' environment.
  • 'config/dev' is the default environment used for development.

Installation

  • gem install pg_migrator

Getting started

  • Create a minimal Rakefile as below, in an empty directory
  • 'rake -T' displays the available tasks
  • 'rake config' displays current configuration and creates minimal 'config' file if none
  • Fill in correct values in 'config/dev' file
  • Add other 'config' files as you need

Minimal Rakefile

    require 'pg_migrator'
    PGMigrator.new

Configuration

  • See 'config/dev' file.
  • Every environment configuration file is built on the same template.
  • The name of each environment is its configuration file name.
  • Environment is select by the option 'env=name'. See examples below.

Migrations script files

Migration SQL scripts are stored in a directory compliant with two configuration parameters, upgrade and downgrade. Default directory is 'migrations'. Upgrade and downgrade SQL script file names must be compliant with upgrade and downgrade parameters in the configuration file. Version number is text between the last character '/' and next character '_' in script file name. An empty newly created database is in version set by version0 parameter. Several files can have the same version number.

Version number can be 000, 0.1.1, 0.09, 1.2a or any other text. Next version number is given by #next method of String object. Beware, next version of 1.9 is 2.0 and next version of 1.09 is 1.10

Example: version 0.02

  • Update parameter upgrade = migrations/\*_up_\*.sql

  • Script file: migrations/0.02_up_add_id_3_in_tablename.sql INSERT INTO tablename (id, caption) VALUES (3,'something');

  • Downgrade parameter downgrade = migrations/\*_down_\*.sql

  • Script file: migrations/0.02_down_add_id_3_in_tablename.sql DELETE FROM tablename WHERE id = 3;

Database requirements

  • For now, database engine MUST BE Postgres.
  • The database MUST NOT have a table called 'migrations'.
  • Database version is the version of the last database upgrade, stored in the 'migrations' table.
  • Downgrade are not logged in migrations table.

Options

  • '-s' or '-q' Rake options also suppresse pg_migrator messages.
  • '-v' verbose is default mode

Usage examples

Show configuration of the 'uat' environment

rake config env=uat

Migrate the 'uat' environment in silent mode

rake db:migrate env=uat -s

Show the list of migrations of the 'dev' environment

rake migrations