Skip to content
This repository

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP

PostgreSQL Change Management Tool

tag: 0.0.6

Fetching latest commit…

Cannot retrieve the latest commit at this time

README.md

PostgreSQL Change Management Tool

What psql-cm is

This project is a tool to assist with an ITIL like change management process for database schemas within a PostgreSQL database cluster.

Specifically psql-cm is a tool which encodes one change management process for a complex multi database, multi schema PostgreSQL system.

This means that psql-cm may be much more than you need for a simple single database system. Please take the time to understand the process and what problems it solves. In order for psql-cm to be effective it must be combined with complimentary process and adherence.

What psql-cm is not

psql-cm is not intended on being a solution for data backup. For backup of data instead use the pg_dump command line utility for backing up data in addition to a repliaction technique tailored to your needs.

The process

Using psql-cm

Prerequisites

Installation

Once the prerequisites have been satisfied on your system, using the 'gem' command from Ruby 1.9.3 do:

$ gem install psql-scm

Setup

Setup the psql_cm control tables on the target databases, use a comma (',') to separate multiple database names.

$ psql-cm --databases psqlcm_test --uri "postgres://127.0.0.1:5432" setup

Dump

Dump the current database schema to the specified --sql-path directory, if none specified it dumps to $PWD/sql

$ psql-cm --databases psqlcm_test --uri "postgres://127.0.0.1:5432" dump

Restore

Restore a previously psql-cm dumped database schema into a brand new postgresql database cluster.

$ psql-cm --databases psqlcm_test --uri "postgres://127.0.0.1:5432" restore

Change

TODO: Document how to commit a change.

Command line parameters

--databases argument may take multiple database targets, to do this pass them in ',' separated format, no spaces. Specifically the format is,

$ psql-cm --databases adatabase,anotherdatabase,... ...

--uri has the format,

$ psql-cm --uri "postgres://{user}:{password}@{host}:{port}/{database}?{sslmode}={mode}"

user, password, port, the ? and everything after it (the query) are all optional.

sslmode mode may be one of disable, allow, prefer, require if used.

Walkthrough

First let's create a PostgreSQL database for us to work with,

$ createdb psqlcm_test

Next let's create two schemas in addition to the public schema (which is added by default when the database is created) and a table for each schema for our database.

$ psql psqlcm_test -c '
    SET search_path = public;
    CREATE SCHEMA schema_one;
    CREATE TABLE a_bool(a BOOL);

    SET search_path = schema_one;
    CREATE TABLE an_integer(an INTEGER);

    CREATE SCHEMA schema_two;
    SET search_path = schema_two;
    CREATE TABLE a_varchar(a VARCHAR);'

Now that we have a base set of database(s) and schemas that we wish to apply change management process to we can setup the psql-cm control tables.

The setup action adds one table called 'pg_psql_cm' to each of the target database schemas.

$ psql-cm --databases psqlcm_test --uri "postgres://127.0.0.1:5432" setup

Use a PostgreSQL client tool (psql/pgAdmin/Navicat/...) and examine the schemas for the psqlcm_test database for which there should be three:

public
schema_one
schema_two

each with two tables, the pg_psql_cm control table and one other table.

Next we'll dump the schema to sql/ within our working directory

$ psql-cm --databases psqlcm_test --uri "postgres://127.0.0.1:5432" dump

At this point we have the base schema for the psqlcm_test database recorded to the filesystem. You can see the filesystem structure and contents with a find command on *nix:

$ find sql/psqlcm_test
sql/psqlcm_test
sql/psqlcm_test/public
sql/psqlcm_test/public/base.sql
sql/psqlcm_test/public/cm.sql
sql/psqlcm_test/schema_one
sql/psqlcm_test/schema_one/base.sql
sql/psqlcm_test/schema_one/cm.sql
sql/psqlcm_test/schema_two
sql/psqlcm_test/schema_two/base.sql
sql/psqlcm_test/schema_two/cm.sql

We can now do a restore restore by droping the database and then running the psql-cm restore action.

$ dropdb psqlcm_test
$ psql-cm --databases psqlcm_test --uri "postgres://127.0.0.1:5432" restore

Once again useing a client tool and verify that the schema is inded what it was after setup was run.

NOTE: one caveat is that psql-cm does not handle ROLEs and USERs so these will have to be accounted for after doing a restore.

Debugging

Debugging output can be enabled by exporting DEBUG=true in the environment before calling the psql-cm command:

$ export debug=true

Development

To play around inside of a running psql-cm Ruby environment use the console:

rake console    # Development console, builds installs then runs console

The 'Walkthrough' from above is encoded as rake tasks, each step can be seen including all debugging output by running:

rake clean      # Remove the sql/ directory in the current working directory.
rake create     # Create the development database psqlcm_development, including two schemas.
rake debug      # Enable debugging using environment variable DEBUG
rake drop       # Drop the development database psqlcm_development
rake dump       # Remove sql/ from CWD and then run the psql-cm dump action on psqlcm_development
rake build      # Build the psql-cm gem.
rake install    # Build then install the psql-cm gem.
rake restore    # Create psqlcm_development, run psql-cm actions {setup, dump, restore} in order.
rake setup      # Create psqlcm_development and run psql-cm setup on it
Something went wrong with that request. Please try again.