Run Isolated PostgreSQL Tests with pg_virtualenv

You will finish with a repeatable command that creates a temporary PostgreSQL cluster, runs a test or query against it, and removes the cluster when the command exits. The examples use pg_virtualenv from postgresql-common 257build1.1, with PostgreSQL 16 installed on this machine.

Allow about fifteen minutes. You need a shell, postgresql-common, at least one installed PostgreSQL server version, and a command that can run without changing your permanent database clusters. The normal workflow needs no elevated privileges. Do not point test code at a production database while checking this guide.

1. Check the installed command

Start by confirming the command and package version. This is read-only and does not need sudo:

$ command -v pg_virtualenv
/usr/bin/pg_virtualenv
$ dpkg-query -W -f='${Package} ${Version}\n' postgresql-common
postgresql-common 257build1.1
$ pg_virtualenv --help
pg_virtualenv: Create throw-away PostgreSQL environment for regression tests
Syntax: /usr/bin/pg_virtualenv [options] [command]

The help output shows the supported options. The installed command treats --help as an invalid long option before printing its help, so use the short -h if you want to avoid that diagnostic:

$ pg_virtualenv -h
pg_virtualenv: Create throw-away PostgreSQL environment for regression tests

Checkpoint: command -v must identify the executable you intend to test, and the package query must identify the version whose behaviour you are documenting.

2. Run one harmless query

Put the command to run after pg_virtualenv. A shell command is useful for inspecting the connection variables without modifying the database:

$ pg_virtualenv sh -c 'printf "PGHOST=%s\nPGPORT=%s\nPGDATABASE=%s\nPGUSER=%s\nPGVERSION=%s\n" "$PGHOST" "$PGPORT" "$PGDATABASE" "$PGUSER" "$PGVERSION"; psql -Atqc "select current_database(), current_user, current_setting('''server_version''')"'
Creating new PostgreSQL cluster 16/regress ...
PGHOST=localhost
PGPORT=5434
PGDATABASE=postgres
PGUSER=andy
PGVERSION=16
postgres|andy|16.15
Dropping cluster 16/regress ...

Your port, user name and PostgreSQL patch version can differ. The first available port is selected starting at 5432; on a host where 5432 is occupied, a higher port is normal. The command creates one cluster using the newest installed server version by default. It sets PGHOST, PGPORT, PGDATABASE, PGUSER, PGPASSWORD and, in postgresql-common 219 or newer, PGVERSION for the child command.

The password is generated for the temporary cluster. Avoid printing PGPASSWORD in shared terminal logs, CI output or failure reports. It exists only for this run and the cluster is dropped afterwards.

Checkpoint: the query returns a row and the final line says that the temporary cluster is being dropped. If the shell command exits successfully, no persistent PostgreSQL cluster was changed.

3. Run a test suite against the temporary server

Replace the example command with your test runner. The command inherits the connection variables, so PostgreSQL client tools normally connect to the throw-away server without extra host or port arguments:

$ pg_virtualenv make check
Creating new PostgreSQL cluster 16/regress ...
... test runner output ...
Dropping cluster 16/regress ...

Use a real test command that returns a meaningful exit status. A successful make check does not make changes to an existing cluster, but the test itself may create tables, roles or extensions inside the temporary one. Those objects disappear when the command finishes.

Do not assume that an empty command is a useful test. If your runner needs a database name, user or extension, arrange those prerequisites in the test workflow rather than changing a permanent cluster to make the temporary run pass.

4. Select the server versions to test

List the installed clusters before choosing a version. This is read-only:

$ pg_lsclusters --no-header
16  main  5432 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log

Use -v with a space-separated, quoted list when the test must run against particular major versions:

$ pg_virtualenv -v '16' psql -Atqc "select current_setting('server_version_num')"
Creating new PostgreSQL cluster 16/regress ...
160015
Dropping cluster 16/regress ...

For every installed version, use -a:

$ pg_virtualenv -a make check

With more than one temporary cluster, the clusters use different ports and the wrapper does not set PGPORT or PGVERSION. The clusters are named version/regress. Set PGCLUSTER=version/regress when a command needs to select one, or use the generated service entry, such as psql service=16. Check the actual startup output before relying on a port in a script.

5. Set temporary PostgreSQL configuration

Pass a PostgreSQL configuration value with -o. This is useful for a test that needs a different server setting:

$ pg_virtualenv -o log_statement=all psql -Atqc "select 1"
Creating new PostgreSQL cluster 16/regress ...
1
Dropping cluster 16/regress ...

The setting is written to the temporary cluster configuration through pg_createcluster. It does not edit the permanent cluster. Keep logging settings narrow: log_statement=all can expose SQL values in the temporary server log and can make a noisy test much larger. Remove the option once the diagnostic run is complete.

The related -c option passes extra pg_createcluster options, while -i passes extra initdb options. Read those commands' local help before composing a value; their options are not interchangeable.

6. Diagnose a failed command

If the child command returns non-zero, pg_virtualenv prints the tail of the PostgreSQL server log before removing the cluster. Preserve the child status when wrapping it in a shell:

$ pg_virtualenv sh -c 'psql -v ON_ERROR_STOP=1 -c "select * from missing_table"'
Creating new PostgreSQL cluster 16/regress ...
ERROR:  relation "missing_table" does not exist
... tail of the PostgreSQL server log ...
Dropping cluster 16/regress ...
$ printf 'exit status: %s\n' "$?"
exit status: 1

The exact log lines vary with the PostgreSQL version and failure. If gdb is installed and the server produced a core dump, the wrapper can also show a backtrace. Treat the log as sensitive: SQL text, usernames and file paths may be present.

Use -s when a failed command needs an interactive shell inside the same virtual environment:

$ pg_virtualenv -s make check
... failed test output ...
Starting shell in virtual environment. Type exit to continue cleanup.

Exit that shell to allow cleanup to continue. If the test process is killed abruptly, check for a leftover 16/regress cluster with pg_lsclusters and remove only a cluster that you have confirmed belongs to this test run. Do not delete a permanent cluster directory by guessing its name.

7. Understand root and temporary paths

As an ordinary user, pg_virtualenv places cluster configuration and data under a temporary directory and sets PG_CLUSTER_CONF_ROOT and PGSYSCONFDIR for the child. As root, it normally creates the cluster under /etc/postgresql like other Debian clusters. Use -t to request a temporary directory even when running as root.

Prefer the unprivileged mode for local tests. If a test genuinely requires root, review the command before running it: a temporary database is disposable, but the test program still has root's access to the host. fakeroot is not equivalent to root here; the manual says it falls back to non-root operation, and running fakeroot pg_virtualenv as root fails.