Auto-Release PostgreSQL Extensions on PGXN
I last wrote about auto-releasing PostgreSQL extensions on PGXN back in 2020, but I thought it worthwhile, following my Postgres Extensions Day talk last week, to return again to the basics. With the goal to get as many extensions distributed on PGXN as possible, this post provides step-by-step instructions to help the author of any extension or Postgres utility to quickly and easily publish every release.
TL;DR
- Create a PGXN Manager account
- Add a
META.jsonfile to your project - Add a pgxn-tools powered CI/CD pipeline to publish on tag push
- Fully-document your extensions
Release your extensions on PGXN
PGXN aims to become the defacto source for all open-source PostgreSQL extensions and tools, in order to help users quickly find and learn how to use extensions to meet their needs. Currently, PGXN distributes source releases for around 400 extensions (stats on the about page), a fraction of the ca. 1200 known extensions. Anyone looking for an extension might exist to solve some problem must rely on search engines to find potential solutions between PGXN, GitHub, GitLab, blogs, social media posts, and more. Without a single trusted source for extensions, and with the proliferation of AI Slop in search engine results, finding extensions aside from a few well-known solutions proves a challenge.
By publishing releases and full documentation — all fully indexed by its search index — PGXN aims to be that trusted source. Extension authors provide all the documentation, which PGXN formats for legibility and linking. See, for example, the pgvector docs.
If you want to make it easier for users to find your extensions, to read your documentation — not to mention provide sources for binary packaging systems — publish every release on PGXN.
Here’s how.
Create an Account
Step one: create a PGXN Manager account. The Email, Nickname, and Why fields are required. The form asks “why” as a simple filter for bad actors. Write a sentence describing what you’d like to release — ideally with a link to the source repository — and submit. We’ll get the account approved forthwith, which will send a confirmation email to your address. Follow the link in the email and you’ll be good to go.
Anatomy of a Distribution
A PostgreSQL extension source tree generally looks something like this (taken from the pair repository):
pair
├── Changes
├── doc
│ └── pair.md
├── Makefile
├── META.json
├── pair.control
├── README.md
├── sql
│ ├── pair--unpackaged--0.1.2.sql
│ └── pair.sql
└── test
├── expected
│ └── base.out
└── sql
└── base.sql
Extension authors will recognize the standard PGXS (or pgrx) source
distribution files; only META.json file needs explaining. The META.json
file is, frankly, the only file that PGXN requires in a release. It contains
the metadata to describe the release, following the PGXN Meta Spec.
This example contains only the required fields:
{
"name": "pair",
"version": "0.1.0",
"abstract": "A key/value pair data type",
"maintainer": "David E. Wheeler <[email protected]>",
"license": "postgresql",
"provides": {
"pair": {
"file": "sql/pair.sql",
"version": "0.1.0"
}
},
"meta-spec": {
"version": "1.0.0"
}
}
Presumably these fields contain no surprises, but a couple of details:
- It starts with the name of the distribution,
pair, and the release version,0.1.0. - The
abstractprovides a brief description of the extension, while themaintainercontains contact information. - The
licensestipulates the distribution license, of course, usually one of a few known, but may be customized. - The
providesobject lists the extensions or tools provided, each named by an object key that points to details about the extension, including main file, version, and potentially an abstract and documentation file. - The
meta-specobject identifies the meta spec version used for theMETA.jsonitself.
Release It!
This file with these fields is all you need to make a release. Assuming Git, package up the extension source files like so (replacing your extension name and version as appropriate).
git archive --format zip --prefix=pair-0.1.0 -o pair-0.1.0.zip HEAD
Then navigate to the release page, authenticate, and upload the resulting
.zip file.
And that’s it! Your release will appear on pgxn.org and on Mastodon within five minutes.
Let’s Automate it!
All those steps would be a pain in the ass to follow for every release. Let’s automate it using pgxn-tools! This OCI image contains the tools necessary to package and upload an extension release to PGXN. Ideally, use a CI/CD pipeline like a GitHub Workflow to publish a release on every version tag.
Set up Secrets
pgxn-tools uses your PGXN credentials to publish releases. To keep them
safe, use the secrets feature of your preferred CI/CD tool. This figure shows
the “Secrets and variables” configuration for a GitHub repository, with two
repository secrets: PGXN_USERNAME and PGXN_PASSWORD:
Create a Pipeline
Use those secrets and pgxn-tools in CI/CD pipeline. Here, for example, is a minimal GitHub workflow to publish a release for every SemVer tag:
|
|
Details:
- Line 3 configures the workflow to run on a SemVer tag push, typically used to denote a release.
- Line 8 configures the workflow job to run inside a pgxn-tools container.
- Lines 10-11 set environment variables with the credentials from the secrets.
- Line 16 bundles the release using either
git archiveorzip. - Line 18 publishes the release on PGXN.
Now publishing a new release is as simple as pushing a SemVer tag, like so:
git tag v0.1.0 -sm 'Tag v0.1.0'
git push --follow-tags
That’s it! The workflow will automatically publish the extension for every release, ensuring the latest and greatest always make it to PGXN where users and packagers will find them.
The pgxn-tools image also provides tools to easily test a PGXS or pgrx extension on supported PostgreSQL versions (going back as far as 8.2), also super useful in a CI/CD pipeline. See Test Postgres Extensions With GitHub Actions for instructions. Depending on your CI/CD tool of choice, you might take additional steps, such as publishing a release on GitHub, as previously described.
Optimizing for PGXN
But let’s dig deeper into how to optimize extensions for maximum discoverability and user visibility on PGXN.
Add More Metadata
The META.json file supports many more fields that PGXN indexes and
references. These improve the chances users will find what they’re looking
for. This detailed example demonstrates how a PostGIS META.json file might
start to provide additional metadata:
|
|
- Line 4 contains a longer description of the distribution.
- Lines 6-9 show how to list multiple maintainers as an array.
- Line 10 demonstrates support for an array of licenses.
- Lines 11-24 list multiple extensions included in the distribution, with abstracts and documentation files for each.
- Lines 25-37 identify dependencies for various phases of the distribution lifecycle, including configure, build, test, runtime, and develop. Each contains an object identifying PostgreSQL or extension dependencies.
- Lines 38-47 lists resources for the distribution, including issue tracking and source code repository.
- Lines 53-60 contains an array of tags, an arbitrary list of keywords for a distribution used both in the search index and the PGXN tag cloud.
Admittedly the PGXN Meta Spec provides a great deal of information.
Perhaps the simplest way to manage it is to copy an existing META.json from
another project (or above) and edit it. In general, only the version fields
require updating for each release.
Write Killer Docs
The most successful extensions provide ample descriptive and reference documentation, as well as examples. Most extensions feature a README, of course, which contains basic information, build and install instructions, and contact info. But as the pair tree, illustrates, PGXN also supports extension-specific documentation in a variety of formats, including:
Some examples:
- jsonschema (Markdown)
- semver (MultiMarkdown)
PGXN will also index and format additional documentation files in any of the above formats. See, for example, all the files formatted for orafce.
Exclude Files from Release
Use gitattributes to exclude files from the release. For example,
distributions don’t generally include .gitignore or the contents of the
.github directory. Exclude them from the archive created by git archive by
assigning export-ignore to each path to exclude in the .gitattributes
file, like so:
.gitignore export-ignore
.gitattributes export-ignore
.github export-ignore
What’s It All For?
PGXN aims to be the trusted system of record for open-source PostgreSQL extensions. Of course that requires that it contain all (or nearly all) of said extensions. Hence this post.
Please help make it so by adding your extensions, both to help users find the extensions they need, and to improve the discoverability of your extensions. Over time, we aim to feed downstream extension distribution systems, such as Yum, APT, CloudNativePG, OCI, and more.
Let’s make extensions available everywhere to everyone.
