Run tests on an isolated, temporary PostgreSQL database

overview | download 3.2 | man page


Get an interactive shell to a new database with a predefined schema

$ uri=$(pg_tmp)
$ psql $uri -f schema.sql
$ psql $uri

Theory and Operation

pg_tmp is a compact shell script designed to make unit testing, integration testing with PostgreSQL easy in any language.

Sometimes SQLite is used as drop-in replacement for PostgreSQL when running tests in order to make tests self-contained and therefore free of side-effects. This technique seems to suffice for simple interactions, but it does so by reducing the set of features that an application can use to the functionality common to both platforms. PostgreSQL can be thought of as a specialized programming environment.

Performance Techniques

Total Wait Time Strategy
7.0 Sequential invocation ( initdb, pg_ctl start, pg_ctl stop )
6.4 Disabling runtime fsync ( pg_ctl -F )
6.0 Spinning to make the first connection (instead of pg_ctl -w )
2.2 Background/pre-initialize database ( /tmp/ephemeralpg.XXXXXX )
1 second Shut down database asynchronously

Other Utilities

Two utilities are in the contrib/ directory:

urlsed — Alter individual components of a URL

usage: urlsed [component=value ...] < input
components: scheme userinfo host port path query fragment

flattenjs — Print JSON in a line-oriented format using PostgreSQL JSON operator syntax

usage: flattenjs < input.json

To install:

$ cp contrib/{flattenjs,urlsed} $HOME/local/bin/

Access on a Local Network

If you want to spin up a database that is accessible to other hosts on a LAN you can modify pg_hba.conf

$ url=$(pg_tmp -t)
$ datadir=$(psql $url --no-psqlrc -At -c 'show data_directory')
$ cat <<EOF >> $datadir/pg_hba.conf
host    all             all                trust

$ pg_ctl -D $datadir reload
$ echo $url | urlsed host=$(hostname -s)

This works by

  1. Starting up a temporary instances of PostgreSQL
  2. Discover data directory
  3. Modify pg_hba.conf
  4. Signal the server to reload config
  5. Use urlsed to replace with the local network address