Testing PostgreSQL Extensions

You have written a useful PostgreSQL extension. It works perfectly, includes some tests and has excellent documentation. But can it be tested properly and automatically for multiple PostgreSQL versions?

Single-machine build

First, the best way to build an extension on a single machine is to use the standard PGXS build system. It allows writing a simple declarative Makefile that includes a lot of PGXS machinery:

PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

Only a few Makefile variables with extension metadata must be added, and PGXS does the rest. Just type make, and the platform-specific binary and SQL scripts for extension are built. Target make install install the extension to the current (pointed by pg_config) PostgreSQL installation.

PGXS also provides regression tests with a tool pg_regress and makefile wrappers. It’s enough to put SQL inputs to test/sql/test1.sql and expected text output to test/expected/test1.out. Invoking make installcheck runs all regression tests and provides a report and a status code.

It’s an easy and powerful system.

Testing at scale

But what about testing against multiple PostgreSQL versions? Typical package managers like homebrew and apt don’t allow the installation of a large selection of PostgreSQL binaries because older versions are quickly replaced with newer ones. For a specific Debian installation, only one PostgreSQL version is supported at any moment, which the Debian community chooses. Homebrew allows to have multiple servers installed side-by-side. To select a specific version, pass a PG_CONFIG variable before calling make:

PG_CONFIG=/usr/local/opt/postgresql@14/bin/pg_config make

Another way is to build multiple PostgreSQL versions from the source and install them side-by-side. Usually, it works well because the build system works fine on a range of operating systems versions.

But we don’t want to build everything from scratch. Okay, let’s use official Docker images:

docker run --rm -it postgres:14-bullseye find / -name postgres.h

Oops, no headers. The image is not suitable for building extensions.

How can we avoid building code by ourselves?

It turned out that there are official APT repositories provided not by Debian but by the Postgres community itself. These APT and APT FAQ describe them in details. You can install postgresql-14 and postgresql-server-dev-14 (any version here) on any Debian distribution. Packages provide all needed headers and development files.

Test harness for images

Unfortunately, there aren’t any prebuild Docker images with these packages. With a prebuilt server binary, it’s a much simpler task.

All CI-related files for my parray_gin extension lay inside this directory.

First of all, the image contains everything needed to run actual tests.

Here we specify the build argument with a version.

FROM debian:bullseye

# PostgreSQL version (like 15)
ARG PGVERSION
ARG PGCHANNEL=bullseye-pgdg

We use the same compiler (gcc10 in bullseye) for all different PostgreSQL versions:

ENV DEBIAN_FRONTEND=noninteractive

# Install core packages
RUN apt-get update && \
    apt-get install --no-install-recommends -y sudo curl gnupg tzdata \
    locales lsb-release ca-certificates \
    make gcc libssl-dev libkrb5-dev libicu-dev libdpkg-perl

The APT repository for PostgreSQL must be configured in a modern way. Do not use apt-key anymore because it is deprecated. Remember to raise the priority of new packages because Debian packages could be pulled in instead of the community’s.

# Configure repository
RUN mkdir -p /etc/apt/keyrings && \
    curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | \
    gpg --dearmor -o /etc/apt/keyrings/postgres.gpg && \
    echo "deb [arch=amd64 signed-by=/etc/apt/keyrings/postgres.gpg] http://apt.postgresql.org/pub/repos/apt/ ${PGCHANNEL} main ${PGVERSION}" | \
    tee -a /etc/apt/sources.list.d/postgres.list

# Prefer packages from the Postgres repository
RUN echo "Package: *\nPin: release o=apt.postgresql.org\nPin-Priority: 900" | \
    tee -a /etc/apt/preferences.d/pgdg.pref

# Install PostgreSQL
RUN apt-get update && \
    apt-get install --no-install-recommends -y postgresql-${PGVERSION?} \
      postgresql-server-dev-${PGVERSION?} && \
    rm -rf /var/lib/apt/lists/*

Finally, bootstrap the cluster and prepare the environment for building extension:

# Create cluster
ENV PGBIN=/usr/lib/postgresql/${PGVERSION}/bin
ENV PGDATA="/var/lib/postgresql/${PGVERSION}/test"
ENV PATH="${PATH}:${PGBIN}"

RUN echo "postgres ALL=(ALL) NOPASSWD:ALL" >> /etc/sudoers

COPY docker/entrypoint.sh /entrypoint.sh
COPY docker/test.sh /test.sh
RUN chmod a+x /entrypoint.sh /test.sh

RUN mkdir /src && chown postgres /src

USER postgres

RUN initdb

WORKDIR /src

ENTRYPOINT [ "/entrypoint.sh" ]

The entrypoint launches the cluster with pg_ctl start in the background so the command in a container will be executed with an environment with an already running cluster:

#!/bin/sh
set -e
pg_ctl start
exec "$@"

Images could be prebuilt in advance:

docker build -t pgx:14 -f docker/Dockerfile --build-arg PGVERSION=14 .

Container builds and runs tests for an extension whose source code is in a given directory. The directory is mounted as a read-only volume. Everything from this directory is copied to the intermediate directory and built there.

docker run --rm -v `pwd`:/workspace:ro pgx:14 /test.sh

There is one tricky part. Sometimes at slow CI machines cluster has yet to start when the test is begun. We need to wait until Postgres becomes ready. For newer versions pg_isready command can be used, but for older versions (9.x), the fallback with a blind timeout is used.

if command pg_isready ; then
  timeout 600 bash -c 'until pg_isready; do sleep 10; done'
else
  sleep 30
fi

So the typical local workflow for testing against arbitrary versions is a one-liner:

export PGVERSION=14 && \
  docker build -t pgx:$PGVERSION -f docker/Dockerfile --build-arg PGVERSION=$PGVERSION . && \
  docker run --rm -v `pwd`:/workspace:ro pgx:$PGVERSION /test.sh

Testing on nightly

If you are going to test an extension against unreleased PostgreSQL, there are packages with development nightly snapshots. You don’t need to build a database from the trunk!

My scripts allow specifying a development channel PGCHANNEL=bullseye-pgdg-snapshot instead of standard PGCHANNEL=bullseye-pgdg for the image so the nightly package version will be used for tests.

Wrapping up in GitHub Actions

Since all tests are parametrised with a database version and channel, it’s easy to wrap them in any CI that supports matrix builds.

Here we go with GitHub Actions matrix build:

name: test

on:
  push:
    branches: [ "master" ]
  pull_request:
    branches: [ "master" ]

jobs:
  build:

    runs-on: ubuntu-latest
    strategy:
      matrix:
        pg-version: [ "9.1", "9.5", "9.6", "10", "11", "12", "13", "14", "15" ]

    steps:
      - name: Checkout
        uses: actions/checkout@v3

      - name: Build the Docker image
        run: docker build . --file docker/Dockerfile --tag "pgxtest:$" --build-arg "PGVERSION=$"

      - name: Test
        run: docker run --rm -v `pwd`:/workspace "pgxtest:$" /test.sh

Snapshot workflow is more straightforward and includes the only job for the snapshot.

Summary

The build and testing system for PostgreSQL extensions is stable and useful for local development. More efforts are needed when you need to test it against multiple PostgreSQL versions automatically. Code and approaches introduced in this post could greatly simplify the development experience with local development and public CIs.