Ephemeral PostgreSQL

Ephemeral PostgreSQL provides a way to run tests on isolated, temporary PostgreSQL databases using the pg_tmp utility.

Key features include:

• Low Footprint: Temporary databases created with pg_tmp have a limited shared memory footprint.
• Automatic Cleanup: Databases are automatically garbage-collected after a specified number of seconds (controlled by the -w option; default is 60 seconds).
• High Performance: pg_tmp reduces the wait time for a new database to less than one second by initializing a database in the background for subsequent invocations.

To install pg_tmp from source on BSD, Mac OS, or Linux, use make install. You can specify a custom installation directory using the PREFIX environment variable.

# Standard installation
make install

# Install to a specific location
PREFIX=$HOME/local make install

The pg_tmp command accepts an -o flag to pass custom configuration options to the underlying PostgreSQL server. This is useful for loading specific extensions or changing server settings.

Example: Starting a temporary server with a custom extension loaded via shared_preload_libraries:

uri=$(pg_tmp -o "-c shared_preload_libraries=$PWD/auth_hook")
psql $uri -c "SELECT 1"

You can use pg_tmp to obtain a connection URI for a temporary PostgreSQL instance. This is useful for running SQL scripts against an isolated environment.

To create a temporary database and run a script named my.sql:

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