GitHub Action to Install and Configure PostgreSQL

This action sets up a PostgreSQL server on the GitHub runner VM to enable the automated testing of PGXN extensions against multiple versions of PostgreSQL. It currently supports:

• Ubuntu Runners with PGDG-installed PostgreSQL 8.2-19
• macOS Runners with Homebrew-installed PostreSQL 14-18
• Windows Runners with Chocolatey-installed PostreSQL 10-18¹

In addition, each provides the pgxn client to simplify installing additional extension from PGXN. The Windows images also adds sudo to minimize differences in the commands required to install extension on each OS.

We recommend also using pgxn-matrix-action to generate the test matrix. Example workflow:

name: 🧪 Test
on:
  push:
defaults:
  run: { shell: bash }
jobs:
  matrix:
    # Use pgxn/matrix-action to generate OS/arch/Postgres version matrix.
    name: 📋 Generate Matrix
    runs-on: ubuntu-latest
    outputs:
      matrix: ${{ steps.matrix.outputs.matrix }}
    steps:
      - name: Generate Matrix
        id: matrix
        uses: pgxn/matrix-action@v0
        with:
          min-version: 12
          beta: true
          no-os: windows
  test:
    name: ${{ matrix.pg.emoji }} ${{ matrix.pg.os }}/${{ matrix.pg.arch }} 🐘 v${{ matrix.pg.version }}
    needs: matrix
    runs-on: ${{ matrix.pg.runner }}
    strategy:
      fail-fast: false
      matrix:
        pg: ${{ fromJson(needs.matrix.outputs.matrix) }}
    steps:
      - name: Check out the repo
        uses: actions/checkout@v7
      - name: Start Postgres ${{ matrix.pg.version }}
        uses: pgxn/postgres-action@v0
        with: { version: "${{ matrix.pg.version }}" }
      - name: Build
        run:  make
      - name: Install
        run:  sudo make install
      - name: Test
        id:   test
        run:  make installcheck
      - name: Show Diffs
        if:   failure() && steps.test.outcome == 'failure'
        run:  find . -name regression.diffs -exec cat {} +

Input Parameters

This action takes the following parameters:

Key	Type	Default	Description
version	string	""	PostgreSQL major version to install
port	integer	55432	Port on which PostreSQL should listen for cnnections
packages	string	""	List of additional OS-specific packages to install
start	boolean	true	Start the PostgreSQL server after installing
encoding	string	""	The encoding to use for databases in the cluster
locale	string	""	The locale to use for databases in the cluster

The locale and encoding inputs have no effect if start is false.

For the packages input, use package names specific to the OS packaging system:

• Linux: Debian Packages
• macOS: Homebrew Formulae
• Windows: Chocolatey Packages

Environment Variables

On completion, this action sets the following environment variables:

Variable	Value	Description
PGUSER	"postgres"	The name of the PostgreSQL super user
PGPORT	input.port	The port on which the PostgreSQL server listens
PG_CONFIG	varies	The path pg_config, used to build extensions

Path

On completion, this action adds the path to the PostgreSQLl executables to the PATH environment varaible, so they can be called without needing to know the full, often version-specific path.

Prior Art/Inspirations

• pgxn-tools: Old PGXN Linux/amd64-only OCI image for testing extensions
• petere/pguint: Commit converting to GitHub actions using apt.postgresql.org
• ikalnytskyi/action-setup-postgres: Setup PostgreSQL for Linux, macOS and Windows runner machines

Footnotes

1. Although currently the standard include $(PGXS) pattern in Makefiles appears to work only on Postgres 17 and later, because pg_config --pgxs returns a path with spaces in it on earlier versions. ↩