pgSchemata

pgSchemata is a super simple tool to build and manage both simple and complex PostgreSQL databases schemas in a way that is easy to use with existing SCM systems. It takes a simple approach to storing the schema in individual files enabling easy code review, coupled with a simple build tool to compile and deploy a PostgreSQL database schema.

pgSchemata is designed to be a super simple way to store and build PostgreSQL database schemas in a source code management repository such as Git. pgSchemata approaches this by storing one SQL file per entity of the schema, this makes it very easy to review what is changing per release using standard SCM tools. A very simple build tool then processes these files and assembles a SQL file which can be run against PostgreSQL to install the database schema.

Obviously database schemas are never static and change over time. pgSchemata provides support to run schema (and data) migrations, which will execute only once for the life time of the database. This is achieved by holding a table of migrations applied and using a simple plpgsql function to apply theme, this functionality exists in the _admin schema. Typical migration script include a simple ALTER TABLE statement, but more complex migrations can use the pgSchemata build functions to include entity definitions.

The SQL scripts generated by pgSchemata run the schema installation in a fully transactional manner, with the deployment either succeeding or failing. This script can be built in two modes: create or update. Create mode will install all database entities as currently defined, skipping any migrations. Update mode will ONLY execute database migrations and reinstall all functions. As such when adding tables a migration needs to explicitly add them. It is recommended when you define database entities to always specify IF NOT EXISTS or CREATE OR REPLACE semantics.

Usage Overview

Getting Started

Creating A New pgSchemata Repository

./pgSchemata.sh init <name>

Will create a new directory with the default public schema and pgSchemata installed.

Adding A New Schema

./pgSchemata.sh add <schema_name>

Will create a new schema directory structure.

Building The Database Schema

The database schema can be build using the build command, which will assemble the full database schema installation script to stdout.

Create Mode

./pgSchemata.sh build create

Will generate an installation script intended to be run against a clean database, which only has the _admin schema installed.

Update Mode

./pgSchemata.sh build update

Will generate an installation script intended to b run against an existing database and will perform all database migrations as needed.

Loading Into PostgreSQL

./pgSchemata.sh build create | psql -h <host> -U <deployment_user> <database>

or

./pgSchemata.sh build update | psql -h <host> -U <deployment_user> <database>

To install the schema simply run the schema installation SQL against your PostgreSQL database.

Installing The Admin Schema

./pgSchemata.sh build admin | psql -h <host> -U <deployment_user> <database>

The _admin schema needs to be installed into a database before a pgSchemata generated can be applied, since this schema holds a number of utility functions which pgSchemata relies upon.

Where can I get it

You can find the code on GitLab it is licensed under the PostgreSQL license.