Run tests on an isolated, temporary PostgreSQL database
overview | download 3.1 | man page
Get an interactive shell to a new database with a predefined schema
$ uri=$(pg_tmp) $ psql $uri -f schema.sql $ psql $uri
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.
Total Wait Time | Speedup | Strategy |
---|---|---|
7.2 | — | Naive in-process creation and teardown |
6.4 | 0.8 |
Disabling runtime fsync (
pg_ctl -F
)
|
6.0 | 0.4 |
Spinning to make the first connection (instead of
pg_ctl -w
)
|
2.2 | 3.8 |
Background database initialization (
initdb --nosync
)
|
1 second | 1.3 | Shut down database asynchronously |
On a production server, generous space for
pg_wal
is always a good idea, but an ephemeral instance has reason to limit space used on
/tmp
.
To this end, the configuration installed by
pg_tmp
allows the Postgres server clean up quickly by writing checkpoint after 64MB of
WAL (the default is 1GB).
In some cases it is useful to use
pg_tmp
to construct a snapshot of larger database that is populated by
pg_dump | pg_restore
. Since PostgreSQL is already trimming WAL the on-disk representation is already
reasonably compact.
There is one notable utility provided in the
contrib/
directory.
flattenjs
is line-oriented way of
viewing JSON data structures
that is also compatible with PostgreSQL
JSON operators.
To install:
$ cp contrib/flattenjs $HOME/local/bin/
The 3.0 release will include a new utility
ddl_compare
for generating 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
ddlx
to generate a complete definition of each table and it's dependencies
git diff
to display a summary of chnages
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
pg_hba.conf
urlsed
to replace
127.0.0.1
with the local network address
Last updated on October 26, 2020
Send questions or comments to
ericshane@eradman.com