pg_tmp(1)

Run tests on an isolated, temporary PostgreSQL database

overview | download 3.3 | man page

Examples

Start a new database listening on a random TCP port and load a predefined schema 

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

Start a database with autovacuum disabled, and keep for at least 10 minutes 

$ uri=$(pg_tmp -w 600 -o "-c autovacuum='off'")
$ 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

  1. Sequential invocation ( initdb, pg_ctl start, pg_ctl stop )
  2. Disabling runtime fsync ( pg_ctl -F )
  3. Spinning to make the first connection (instead of pg_ctl -w )
  4. Background/pre-initialize database ( /tmp/ephemeralpg.XXXXXX )
  5. 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             10.0.0.0/8             trust
EOF

$ 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 127.0.0.1 with the local network address