pg_tmp(1)

Run tests on an isolated, temporary PostgreSQL database

overview | download 3.1 | man page

Usage

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.

The engineering genius of unit tests is mainly the ability they have to shape the design of the internal interfaces used to build an application. To ensure that we are testing the module and not the environment we mock APIs to external resources, but never the runtime or the language itself.

Performance Techniques

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/

Schema Comparison

Starting with the 3.0 release, a new utilty ddl_compare(1) is included. This utiliity generates a representation of two schemas that is suitable for diff

$ ddl_compare -g roles.sql test.sql prod.sql
----
/home/eradman/localharvest/{a,b}/products    |  18 ------------------
/home/eradman/localharvest/{a,b}/farmer      |   5 ++---
2 files changed, 2 insertions(+), 21 deletions(-)

This works by

1. Starting up two temporary instances of PostgreSQL
2. Optionally applying global changes to both databases
3. Loading a patched version of the ddlx extension
4. Using ddlx to generate a complete definition of each table and it's dependencies
5. Executing git diff to display a summary of chnages

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

Last updated on April 27, 2021
Send questions or comments to ericshane@eradman.com