Just a Theory

Trans rights are human rights

Posts about CI/CD

Automate Postgres Extension Releases on GitHub and PGXN

Back in June, I wrote about testing Postgres extensions on multiple versions of Postgres using GitHub Actions. The pattern relies on Docker image, pgxn/pgxn-tools, which contains scripts to build and run any version of PostgreSQL, install additional dependencies, build, test, bundle, and release an extension. I’ve since updated it to support testing on the the latest development release of Postgres, meaning one can test on any major version from 8.4 to (currently) 14. I’ve also created GitHub workflows for all of my PGXN extensions (except for pgTAP, which is complicated). I’m quite happy with it.

But I was never quite satisfied with the release process. Quite a number of Postgres extensions also release on GitHub; indeed, Paul Ramsey told me straight up that he did not want to manually upload extensions like pgsql-http and PostGIS to PGXN, but for PGXN to automatically pull them in when they were published on GitHub. It’s pretty cool that newer packaging systems like pkg.go.dev auto-index any packages on GibHub. Adding such a feature to PGXN would be an interesting exercise.

But since I’m low on TUITs for such a significant undertaking, I decided instead to work out how to automatically publish a release on GitHub and PGXN via GitHub Actions. After experimenting for a few months, I’ve worked out a straightforward method that should meet the needs of most projects. I’ve proven the pattern via the pair extension’s release.yml, which successfully published the v0.1.7 release today on both GitHub and PGXN. With that success, I updated the pgxn/pgxn-tools documentation with a starter example. It looks like this:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
name: Release
on:
  push:
    tags:
      - 'v*' # Push events matching v1.0, v20.15.10, etc.
jobs:
  release:
    name: Release on GitHub and PGXN
    runs-on: ubuntu-latest
    container: pgxn/pgxn-tools
    env:
      # Required to create GitHub release and upload the bundle.
      GITHUB_TOKEN: ${{ secrets.GITHUB_TOKEN }}
    steps:
    - name: Check out the repo
      uses: actions/checkout@v3
    - name: Bundle the Release
      id: bundle
      run: pgxn-bundle
    - name: Release on PGXN
      env:
        # Required to release on PGXN.
        PGXN_USERNAME: ${{ secrets.PGXN_USERNAME }}
        PGXN_USERNAME: ${{ secrets.PGXN_PASSWORD }}
      run: pgxn-release
    - name: Create GitHub Release
      id: release
      uses: actions/create-release@v1
      with:
        tag_name: ${{ github.ref }}
        release_name: Release ${{ github.ref }}
        body: |
          Changes in this Release
          - First Change
          - Second Change          
    - name: Upload Release Asset
      uses: actions/upload-release-asset@v1
      with:
        # Reference the upload URL and bundle name from previous steps.
        upload_url: ${{ steps.release.outputs.upload_url }}
        asset_path: ./${{ steps.bundle.outputs.bundle }}
        asset_name: ${{ steps.bundle.outputs.bundle }}
        asset_content_type: application/zip

Here’s how it works:

  • Lines 4-5 trigger the workflow only when a tag starting with the letter v is pushed to the repository. This follows the common convention of tagging releases with version numbers, such as v0.1.7 or v4.6.0-dev. This assumes that the tag represents the commit for the release.

  • Line 10 specifies that the job run in the pgxn/pgxn-tools container, where we have our tools for building and releasing extensions.

  • Line 13 passes the GITHUB_TOKEN variable into the container. This is the GitHub personal access token that’s automatically set for every build. It lets us call the GitHub API via actions later in the workflow.

  • Step “Bundle the Release”, on Lines 17-19, validates the extension META.json file and creates the release zip file. It does so by simply reading the distribution name and version from the META.json file and archiving the Git repo into a zip file. If your process for creating a release file is more complicated, you can do it yourself here; just be sure to include an id for the step, and emit a line of text so that later actions know what file to release. The output should be appended to the $GITHUB_OUTPUT file like this, with $filename representing the name of the release file, usually $extension-$version.zip:

    echo bundle=$filename >> $GITHUB_OUTPUT
    
  • Step “Release on PGXN”, on lines 20-25, releases the extension on PGXN. We take this step first because it’s the strictest, and therefore the most likely to fail. If it fails, we don’t end up with an orphan GitHub release to clean up once we’ve fixed things for PGXN.

  • With the success of a PGXN release, step “Create GitHub Release”, on lines 26-35, uses the GitHub create-release action to create a release corresponding to the tag. Note the inclusion of id: release, which will be referenced below. You’ll want to customize the body of the release; for the pair extension, I added a simple make target to generate a file, then pass it via the body_path config:

    - name: Generate Release Changes
      run: make latest-changes.md
    - name: Create GitHub Release
      id: release
      uses: actions/create-release@v1
      with:
        tag_name: ${{ github.ref }}
        release_name: Release ${{ github.ref }}
        body_path: latest-changes.md
    
  • Step “Upload Release Asset”, on lines 36-43, adds the release file to the GitHub release, using output of the release step to specify the URL to upload to, and the output of the bundle step to know what file to upload.

Lotta steps, but works nicely. I only wish I could require that the testing workflow finish before doing a release, but I generally tag a release once it has been thoroughly tested in previous commits, so I think it’s acceptable.

Now if you’ll excuse me, I’m off to add this workflow to my other PGXN extensions.

Test Postgres Extensions With GitHub Actions

I first heard about GitHub Actions a couple years ago, but fully embraced them only in the last few weeks. Part of the challenge has been the paucity of simple but realistic examples, and quite a lot of complicated-looking JavaScript-based actions that seem like overkill. But through trial-and-error, I figured out enough to update my Postgres extensions projects to automatically test on multiple versions of Postgres, as well as to bundle and release them on PGXN. The first draft of that effort is pgxn/pgxn-tools1, a Docker image with scripts to build and run any version of PostgreSQL between 8.4 and 12, install additional dependencies, build, test, bundle, and release an extension.

Here’s how I’ve put it to use in a GitHub workflow for semver, the Semantic Version data type:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
name: CI
on: [push, pull_request]
jobs:
  test:
    strategy:
      matrix:
        pg: [12, 11, 10, 9.6, 9.5, 9.4, 9.3, 9.2]
    name: 🐘 PostgreSQL ${{ matrix.pg }}
    runs-on: ubuntu-latest
    container: pgxn/pgxn-tools
    steps:
      - run: pg-start ${{ matrix.pg }}
      - uses: actions/checkout@v3
      - run: pg-build-test

The important bits are in the jobs.test object. Under strategy.matrix, which defines the build matrix, the pg array defines each version to be tested. The job will run once for each version, and can be referenced via ${{ matrix.pg }} elsewhere in the job. Line 10 has the job a pgxn/pgxn-tools container, where the steps run. The are are:

  • Line 12: Install and start the specified version of PostgreSQL
  • Line 13: Clone the semver repository
  • Line 14: Build and test the extension

The intent here is to cover the vast majority of cases for testing Postgres extensions, where a project uses PGXS Makefile. The pg-build-test script does just that.

A few notes on the scripts included in pgxn/pgxn-tools:

  • pg-start installs, initializes, and starts the specified version of Postgres. If you need other dependencies, simply list their Debian package names after the Postgres version.

  • pgxn is a client for PGXN itself. You can use it to install other dependencies required to test your extension.

  • pg-build-test simply builds, installs, and tests a PostgreSQL extension or other code in the current directory. Effectively the equivalent of make && make install && make installcheck.

  • pgxn-bundle validates the PGXN META.json file, reads the distribution name and version, and bundles up the project into a zip file for release to PGXN.

  • pgxn-release uploads a release zip file to PGXN.

In short, use the first three utilities to handle dependencies and test your extension, and the last two to release it on PGXN. Simply set GitHub secrets with your PGXN credentials, pass them in environment variables named PGXN_USERNAME and PGXN_PASSWORD, and the script will handle the rest. Here’s how a release job might look:

15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
  release:
    name: Release on PGXN
    # Release pon push to main when the test job succeeds.
    needs: test
    if: github.ref == 'refs/heads/main' && github.event_name == 'push' && needs.test.result == 'success'
    runs-on: ubuntu-latest
    container:
      image: pgxn/pgxn-tools
      env:
        PGXN_USERNAME: ${{ secrets.PGXN_USERNAME }}
        PGXN_PASSWORD: ${{ secrets.PGXN_PASSWORD }}
    steps:
      - name: Check out the repo
        uses: actions/checkout@v3
      - name: Bundle the Release
        run: pgxn-bundle
      - name: Release on PGXN
        run: pgxn-release

Note that lines 18-19 require that the test job defined above pass, and ensure the job runs only on a push event to the main branch, where we push final releases. We set PGXN_USERNAME and PGXN_PASSWORD from the secrets of the same name, and then, in lines 27-32, check out the project, bundle it into a zip file, and release it on PGXN.

There are a few more features of the image, so read the docs for the details. As a first cut at PGXN CI/CD tools, I think it’s fairly robust. Still, as I gain experience and build and release more extensions in the coming year, I expect to work out integration with publishing GitHub releases, and perhaps build and publish relevant actions on the GitHub Marketplace.


  1. Not a great name, I know, will probably change as I learn more. ↩︎