Quick PostgreSQL databases with pg_virtualenv

Debian’s postgresql-common package supplies the pg_virtualenv tool, which spins up a PostgreSQL database which can be used for running tests that require a database, testing upgrades, and other purposes. The tool is configured to spin up a database quickly (e.g. using fsync=off) and is thus ideal for these usecases. The database is wiped as soon as the command supplied to pg_virtualenv terminates.

Testing upgrades

You have a PostgreSQL database at major version X and want to upgrade to PostgreSQL major version Y using pg_upgradecluster (or directly using pg_upgrade). The patchnotes mentioned some potential incompatibilities (remember when WITH OIDS was removed?), but you want to verify that the database is not affected by them.

To test whether the new PostgreSQL version plays nice with your database schema, you can use the following:

$ sudo -u postgres pg_dumpall --schema-only \
  | pg_virtualenv -v $TARGET_MAJOR_VERSION psql -v ON_ERROR_STOP=1

…where $TARGET_MAJOR_VERSION is the major version you want to upgrade to, such as 14.

This also works for a full database dump, as long as you have enough disk space, by simply removing --schema-only.

Due to ON_ERROR_STOP=1, psql will abort when it finds any error. In the past, this helped me find some non-obvious incompatibilities and iron them out before taking the main database offline for a smoothly-running pg_upgradecluster.

Unit testing

Note how in the previous command, psql just worked out of the box. This is due to pg_virtualenv automatically exporting environment variables for connecting to PostgreSQL:

$ pg_virtualenv sh -c 'env | egrep ^PG'
Creating new PostgreSQL cluster 14/regress ...
PGPORT=5433
PGDATABASE=postgres
PG_CONFIG=/usr/lib/postgresql/14/bin/pg_config
PGSYSCONFDIR=/tmp/pg_virtualenv.AwdzQt/postgresql-common
PGPASSWORD=94319b9ce9fc93d72cff7bb318d709cf
PGVERSION=14
PG_CLUSTER_CONF_ROOT=/tmp/pg_virtualenv.AwdzQt/postgresql
PGHOST=localhost
PGUSER=jc
Dropping cluster 14/regress ...

This is great for unit testing projects that use PostgreSQL: simply spin up a PostgreSQL database and use the environment variables to connect to it, with zero configuration required. If you are using libpq, you can probably skip manually supplying anything at all.

Toying around

Finally, pg_virtualenv is ideal for creating a new PostgreSQL database to simply play around with new features in the upcoming PostgreSQL version.