pytest-postgresql: real PostgreSQL databases as pytest fixtures

The problem: a database that behaves like production

pytest-postgresql is a pytest plugin that enables you to test code relying on a running PostgreSQL database. It provides fixtures for managing both the PostgreSQL process and the client connections.

That sentence contains the two halves of the design, and they are the reason the plugin exists. Most teams reach a point where unit tests with mocked database access stop telling them anything, because the behaviour that breaks is the behaviour of PostgreSQL specifically: the way a constraint fires, the semantics of an isolation level, the error code a failed INSERT raises, the way a query planner chooses a plan. SQLite in memory is a different database with different semantics, and a mock is not a database at all.

So the plugin runs an actual PostgreSQL server as part of the test session. The client fixture gives you a connected psycopg.Connection, and after each test it terminates leftover connections and drops the test database, which is the isolation mechanism.

The homepage is the PyPI page for pytest-postgresql, the licence is LGPL-3.0, the primary language is Python, and the last push to the repository was on 2026-10-20. The project is at version 9.1.1, and the classifier in the manifest describes it as Development Status :: 5 - Production/Stable.

Installing the plugin and its async extra

Installation is one pip command:

       pip install pytest-postgresql

The README is explicit that you also need psycopg version 3 installed, and points to the psycopg installation instructions for that. There is a note in the pytest-asyncio configuration section that while this plugin requires psycopg 3 to manage the database, your application code can still use psycopg 2, which is a useful separation for a codebase mid-migration.

For async tests using psycopg.AsyncConnection, there is an optional extra:

       pip install pytest-postgresql[async]

That extra installs pytest-asyncio at 1.4 or newer, required for the @pytest.mark.asyncio marker and the postgresql_async fixtures, and aiofiles at 23.0 or newer, required only when loading SQL files through the async loader. The manifest lists the same two as the async optional dependency group.

The base dependencies are worth reading. They are mirakuru at 2.6.0 or newer, which is the process supervisor the plugin uses to manage the server, port-for at 0.7.3 or newer for picking a free port, packaging, psycopg at 3.0.0 or newer, and pytest at 8.4 or newer. The Python floor is 3.11, and the classifiers list support for 3.11 through 3.15.

The build backend is uv_build, requiring uv-build at 0.11 or newer, and there is a uv.lock at the repository root, so the project develops with uv.

Writing a first test with the postgresql fixture

The test itself is short. You include the postgresql fixture in the test signature and you get a connected connection:

       def test_example(postgresql):
           """Check main postgresql fixture."""
           with postgresql.cursor() as cur:
               cur.execute("CREATE TABLE test (id serial PRIMARY KEY, num integer, data varchar);")
               postgresql.commit()

The fixture is function-scoped, which means a fresh state per test, and the README is explicit that after each test it terminates leftover connections and drops the test database to ensure isolation.

That isolation behaviour has a consequence worth understanding rather than discovering. Because the database is dropped rather than truncated, anything that connects outside pytest's control, a stray connection left open by a thread, or a background job, will be terminated rather than cleaned up. For most code that is exactly what you want. For code that opens connections in a way the plugin cannot see, you will get errors that look like the plugin misbehaving rather than like a leaked connection.

The async path uses a different fixture and the asyncio marker:

       import pytest

       @pytest.mark.asyncio
       async def test_example_async(postgresql_async):
           """Check main async postgresql fixture."""
           async with postgresql_async.cursor() as cur:
               await cur.execute(
                   "CREATE TABLE test (id serial PRIMARY KEY, num integer, data varchar);"
               )
               await postgresql_async.commit()

The postgresql_async fixture returns a connected psycopg.AsyncConnection and requires the async extra, and each async test must be marked with @pytest.mark.asyncio. If pytest-asyncio is missing, fixture setup raises ImportError, which is a clearer failure than a mysterious hang.

pytest-asyncio configuration and the Windows event loop problem

The longest and most useful part of the README is about a Windows-specific event loop problem, and it is worth reading carefully if you develop on Windows.

The reason it exists is that psycopg's async support is incompatible with the default ProactorEventLoop on Windows. Without a fix, postgresql_async tests fail with the message Psycopg cannot use the 'ProactorEventLoop' to run in async mode. The plugin therefore configures a SelectorEventLoop automatically for asyncio tests on Windows when no earlier pytest-asyncio loop factory is registered.

How it does that depends on the version. With pytest-asyncio 1.4 or newer, the plugin registers a selector loop factory via pytest-asyncio's loop-factory hook for all asyncio tests when no prior factory is provided. On Python 3.14 and later, the legacy asyncio policy fallback is not used because that API is deprecated. When an earlier hook implementation already supplies loop factories, those are preserved unchanged, although tests using a prior factory may show different loop names in pytest IDs, for example test_example[custom] rather than test_example[selector].

The older path is the one to know about. With pytest-asyncio older than 1.4 on Windows and Python older than 3.14, the plugin falls back to setting a global WindowsSelectorEventLoopPolicy for the entire test session, not only for the PostgreSQL async tests. The README says plainly that this can change event loop behaviour for unrelated asyncio tests in the same run. The fix is to install the async extra, which pulls pytest-asyncio 1.4 or newer and avoids that legacy path entirely.

For the asyncio mode itself, pytest-asyncio 1.x defaults to asyncio_mode = strict, so each async test needs the marker. If you set asyncio_mode = auto in pytest.ini or pyproject.toml, unmarked async test functions are detected automatically, though postgresql_async still requires the async extra:

       [pytest]
       asyncio_mode = strict

Fixture factories, process fixtures and template databases

Beyond the two headline fixtures, the plugin provides the machinery for more elaborate setups, and the README's How to use section points to a pair of architecture diagrams for the sync and async fixtures.

There are client fixtures, which give you a connection, and there are process fixtures, which manage the server itself. The naming convention is visible in the async SQL file loading section, which refers to process and noproc fixtures.

The key mechanism is a template database. A process fixture populates its template database during session setup via DatabaseJanitor.load(), and the SQL files are then cloned per test rather than re-executed each time, which is much faster when the setup involves schema creation and seed data. The async path is symmetric, with AsyncDatabaseJanitor.load() for the async side.

There is a subtlety in the async file loading that will save you an afternoon if you know it in advance. Process and noproc fixtures always populate their template database synchronously during session setup, via DatabaseJanitor.load(), even when you use postgresql_async as the client fixture. SQL Path entries in a process fixture load list are executed with the sync sql() loader. The async sql_async loader, which requires aiofiles from the extra, is for when you call AsyncDatabaseJanitor.load() directly with a Path. Callable loaders passed to AsyncDatabaseJanitor.load() may be sync or async, and return values that are awaitable are awaited automatically.

There are also custom factories created with factories.postgresql_async, which produce async generator fixtures using pytest_asyncio.fixture, so you can build your own per-test database variants.

The minimum versions when installing the async pieces by hand rather than through the extra are pytest-asyncio 1.4 or newer and aiofiles 23.0 or newer, the latter only for async SQL file loading.

Version constraints, licence and project structure

The README carries a warning that the plugin is tested on PostgreSQL versions 14 and above, with details in the tests. That is the single most important compatibility fact, because a plugin that starts the server binary on your machine will fail in confusing ways if that binary is older.

The licence is LGPL-3.0-or-later, with COPYING and COPYING.lesser at the repository root and the license-files entry pointing at COPYING.lesser. LGPL on a pytest plugin is unusual but not unreasonable, since it lets you use the plugin in a proprietary codebase without the copyleft reaching your own source, while requiring that modifications to the plugin itself stay open.

The repository layout shows a mature project with a proper release process. There are CHANGES.rst and a newsfragments/ directory, a pattern where each change gets its own file rather than an edit to the changelog. CONTRIBUTING.rst, AUTHORS.rst, MANIFEST.in, a Pipfile and a uv.lock are all present, along with a.pre-commit-config.yaml,.coveragerc,.rstcheck.cfg and a.coderabbit.yaml.

The oldest/ directory alongside pyproject.oldest.toml is the interesting one. It is a separate build configuration for the oldest supported dependency set, a pattern used to check that the package still installs and works against minimum versions rather than the latest ones. The pytest plugin itself is registered through the pytest11 entry point, so it is discovered automatically once installed, with no conftest.py changes required.

The plugin module lives in the pytest_postgresql/ directory, and the entry point maps pytest_postgresql to pytest_postgresql.plugin.

Where pytest-postgresql is the wrong tool

Three limits are structural. The first is the dependency on a local PostgreSQL installation. The plugin starts the server itself, using mirakuru as the process supervisor and port-for to find a free port, which means it needs a PostgreSQL binary available on the machine and a way to initialise and start a cluster. On a developer laptop that is usually fine. In a containerised CI image, or a locked-down developer environment, it means provisioning PostgreSQL yourself or using an image that has it.

The second is the single-version limitation. The plugin runs one local server, so it exercises your code against one PostgreSQL version. If your production runs several, or if you specifically need to catch a behaviour change between versions, this does not do it for you. A container-based approach, one image per version with the matrix expressed in CI, is the tool for that, and the difference in approach is that the container approach owns the whole database lifecycle per test rather than managing one server and cloning template databases inside it.

The third is the boundary of what a fixture gives you. The plugin provides a connection and a fresh database. It does not provide migrations, seeding strategies, or a way to run your application against the test database in a multi-process test. For a project whose data model is created by Alembic or Django migrations, you still need to run those migrations in a fixture, and you are back to writing that code yourself.

There is also the psycopg 2 and psycopg 3 split to be aware of. The plugin manages the database with psycopg 3 and requires it, while your application code may still use psycopg 2, which means a project migrating between the two carries both. That is manageable but it is a fact to check before the first test run rather than after.

And on Windows, the legacy event loop path described earlier is a genuine cross-contamination risk, because a plugin that changes the event loop policy for unrelated asyncio tests in the same session will fail tests that have nothing to do with PostgreSQL. The README's own advice, install the async extra, avoids it, but only if you do.

Editorial conclusion

pytest-postgresql suits a Python team whose code has real SQL in it and whose tests would otherwise run against SQLite or a shared staging database, since it gives each test a genuine PostgreSQL instance with the database dropped afterwards. It is the wrong tool if your tests never touch a database, and it is the wrong tool if you need a matrix of PostgreSQL versions, because it tests one local server rather than orchestrating containers. Verify first that a PostgreSQL binary is present and on PATH, since the plugin starts the server itself, that your PostgreSQL is version 14 or newer, and note that the plugin needs Python 3.11 or newer with psycopg 3, while your application code may still use psycopg 2.

Frequently asked questions

Official sources

1. dbfixtures/pytest-postgresql on GitHub
2. Issues
3. License: LGPL-3.0
4. Project website
5. README