Testing

PostgreSQL has four main flavors of tests, each with its own runner and conventions. New patches are expected to add tests in whichever flavor fits.

1. Core regression tests

SQL scripts under src/test/regress/sql/, with expected output under src/test/regress/expected/. The runner is the pg_regress C program (src/test/regress/pg_regress.c).

make check

make check builds an in-tree temporary cluster, runs the SQL files in a defined order (see src/test/regress/parallel_schedule), and diffs the output against the expected files. A failure produces:

• src/test/regress/regression.diffs — combined diff.
• src/test/regress/results/<test>.out — actual output.
• src/test/regress/log/postmaster.log — the temp server's log.

Adding a regression test:

1. Write <feature>.sql under src/test/regress/sql/.
2. Run make check once with no expected file; it produces an "unexpected" output. Copy results/<feature>.out to expected/<feature>.out after manual review.
3. Add <feature> to src/test/regress/parallel_schedule in an appropriate group.
4. Re-run make check to confirm clean.

Tests should be reproducible across platforms, locales, and time zones. Common pitfalls: depending on row order without an ORDER BY, depending on EXPLAIN cost numbers (use EXPLAIN (COSTS OFF)), depending on a specific OID value.

2. Isolation tests

Under src/test/isolation/. Each spec is a small SQL program with multiple "sessions" and explicit permutations (orderings of session steps). The runner serializes the SQL across the sessions and diffs the output.

make check -C src/test/isolation

Use these when you need to test concurrent behavior — locking, MVCC visibility, deadlock detection, race conditions. A spec looks like:

session "s1"
step "s1a" { BEGIN; INSERT INTO t VALUES (1); }
step "s1b" { COMMIT; }

session "s2"
step "s2a" { BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT * FROM t; }
step "s2b" { SELECT * FROM t; COMMIT; }

permutation "s1a" "s2a" "s1b" "s2b"

The framework parks blocked sessions automatically and resumes them when their lock comes free.

3. TAP tests

Perl scripts under src/test/recovery/t/, src/test/subscription/t/, src/test/authentication/t/, etc., plus per-binary suites under src/bin/<tool>/t/. The harness lives at src/test/perl/PostgreSQL/Test/.

make check -C src/test/recovery
make check -C src/bin/psql
make check PROVE_TESTS='t/001_stream_rep.pl' PROVE_FLAGS='-v'

A TAP test typically:

1. Starts one or more PostgreSQL::Test::Cluster instances.
2. Runs SQL or psql -c against them.
3. Asserts via is/ok/like.
4. Stops the clusters in END {} or via the harness.

A minimal example:

use strict;
use warnings;
use PostgreSQL::Test::Cluster;
use Test::More;

my $node = PostgreSQL::Test::Cluster->new('main');
$node->init;
$node->start;
$node->safe_psql('postgres', 'CREATE TABLE t (i int)');
my $val = $node->safe_psql('postgres', 'SELECT count(*) FROM t');
is($val, '0', 'empty table');
$node->stop;
done_testing();

Use TAP for: replication scenarios, multi-node setups, SSL/auth flows, pg_upgrade, point-in-time recovery, pg_dump round-trips, anything that needs more than one process.

tmp_check/ directories under each test dir hold the temp clusters, logs, and outputs from the last run. They are deleted on success but kept on failure for inspection.

4. Module tests

src/test/modules/ contains test extensions used by other tests — fault injection, custom WAL records, dummy index access methods, etc. They are built only when needed by a test that requires them.

contrib/<module>/sql/ follows the same SQL-script convention as core regression tests but lives next to each contrib module. Each contrib module's Makefile participates in make check-world.

Putting it together: make check-world

make check-world

Runs everything: core regression, isolation, all TAP suites, all module tests, all contrib tests. This is the standard pre-submit gate. It takes anywhere from a few minutes (small machine, parallel disabled) to ~30 seconds (modern machine, full parallelism, in-memory tmpfs).

For Meson:

meson test -C build

Performance and stress

PostgreSQL does not have a built-in benchmark harness in the test suite — pgbench is the canonical workload tool but it is a separate binary, not a regression test. For benchmarking changes, run pgbench outside the test framework and report numbers in the patch email.

For randomized stress tests, the project uses src/test/regress/sql/random.sql and a number of intermittent smoke tests in CI but does not gate commits on them.

Continuous integration

The project's CI is run on Cirrus CI (see .cirrus.yml, .cirrus.tasks.yml, .cirrus.star). It builds and tests on Linux, FreeBSD, macOS, and Windows. make check-world failures in CI are routinely caught at patch-review time.

The pgsql-buildfarm (a separately-administered build farm not stored in this repo) runs continuous builds across many less-common platforms — different libc versions, big-endian, AIX, Solaris, etc. — and reports failures back to the project. New committers learn quickly that "passes on Linux" is not the same as "passes on the buildfarm."