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
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
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
$TARGET_MAJOR_VERSION is the major version you want to upgrade to,
This also works for a full database dump, as long as you have enough disk space,
by simply removing
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
Note how in the previous command,
psql just worked out of the box. This is due
pg_virtualenv automatically exporting environment variables for connecting
$ pg_virtualenv sh -c 'env | egrep ^PG'
Creating new PostgreSQL cluster 14/regress ...
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
pg_virtualenv is ideal for creating a new PostgreSQL database to
simply play around with new features in the upcoming PostgreSQL version.