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.