May 22, 2022
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.
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
.
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.
Finally, pg_virtualenv
is ideal for creating a new
PostgreSQL database to simply play around with new features in the
upcoming PostgreSQL version.