View on
Dave Rolsky > Pg-DatabaseManager-0.05 > Pg::DatabaseManager



Annotate this POD


Open  1
View/Report Bugs
Module Version: 0.05   Source   Latest Release: Pg-DatabaseManager-0.06


Pg::DatabaseManager - Manage installation and migration of an application's (Postgres) database


version 0.05


  use Pg::DatabaseManager;


or subclass it ...

  package MyApp::DatabaseManager;

  use Moose;

  extends 'Pg::DatabaseManager';

  has '+app_name' => ( default => 'MyApp' );

  has '+contrib_files => ( default => [ 'citext.sql' ] );


This class provides an object which can be used to drop, create, and migrate an application's Postgres database.

It uses MooseX::Getopt so that it can be invoked easily from a command-line script, but it is also designed to be usable from another module.


In order to perform migrations, your application database must define a table to store the database version. By default, this class expects this table to be named "Version", but you can override this by setting the appropriate attribute.

However, the structure of the table is fixed, and must look like this:

  CREATE TABLE "Version" (
      version  INTEGER  PRIMARY KEY

This table should never contain more than one row (for obvious reasons).

You must include the version table in your database SQL DDL file.

You must also include a single line which inserts the current database version into this table:

  INSERT INTO "Version" (version) VALUES (6);

This class will parse the database SQL file to find this line in order to determine the current database version.


Migrations are defined in their own directory. That directory in turn contains one directory per database version (except version 1). The per-version directories should each contain one or more files. The files will be executed in the order that Perl's sort returns them. You can number them (01-do-x.sql, 02-do-y.sql) to ensure a clear ordering.

The files can either contain SQL or Perl code. Any file ending in ".sql" is assumed to contain SQL (duh) and is executed using the psql utility.

Otherwise, the file should contain Perl code which defines an anonymous subroutine. That subroutine will be called with the Pg::DatabaseManager object as its only argument.

Allowing Perl migration files lets you do things like import contrib SQL files as part of a migration, for example:

      use strict;
      use warnings;

      return sub {
          my $manager = shift;

This is the entire migration file.

This module always dumps the existing database (with data) to a file in the temp directory before running migrations.

Testing Migrations

See the Pg::DatabaseManager::TestMigrations module for a tool which you can use to test your migrations as part of your test suite.


This class provides the following methods:

Pg::DatabaseManager->new( ... )

This method accepts the following parameters:


This is the one public "do it" method. It will update or install the database as needed. If the drop parameter was true, then it will drop any existing database first, meaning it will always install a new database from scratch.

This method tests whether it can connect to the database server's template1 database, and dies if it cannot connect.

$manager->import_contrib_file( $file )

Given a contrib file name such as "citext.sql" or "pgxml.sql", this method finds the file and imports it into the database. If it cannot find the named file, it dies.


This module is designed to be subclassed. In particular, it may make sense for a subclass to provide defaults for various attributes. Please see Silki::DatabaseManager in the Silki distribution's inc dir for an example.

In future versions of this module, I plan to document more of the internals as a stable subclassing interface. For now, if you subclass this module, please let me know what parts of the interface you overrode.


Please report any bugs or feature requests to, or through the web interface at I will be notified, and then you'll automatically be notified of progress on your bug as I make changes.


If you'd like to thank me for the work I've done on this module, please consider making a "donation" to me via PayPal. I spend a lot of free time creating free software, and would appreciate any support you'd care to offer.

Please note that I am not suggesting that you must do this in order for me to continue working on this particular software. I will continue to do so, inasmuch as I have in the past, for as long as it interests me.

Similarly, a donation made in this way will probably not make me work on this software much more, unless I get so many donations that I can consider working on free software full time, which seems unlikely at best.

To donate, log into PayPal and send money to or use the button on this page:


Dave Rolsky <>


This software is Copyright (c) 2011 by Dave Rolsky.

This is free software, licensed under:

  The Artistic License 2.0 (GPL Compatible)
syntax highlighting: