http://eradman.com/ephemeralpg/ 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 Total Wait Speedup Strategy Time 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 Space Optimization 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. Other Utilities 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/ Schema Comparison (experimental) 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 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 News & Discussion April 19, 2016 Overcoming First Principles: A guide for accessing the features of PostgreSQL in test-driven development >> pgconf.us video November 17, 2015 Database testing in Python >> stackoverflow.com/ July 29, 2015 Minimal postgres instance for testing in Java >> stackoverflow.com/ --------------------------------------------------------------------- Last updated on October 26, 2020 Send questions or comments to ericshane@eradman.com