Please welcome dmjwk into the world. This “demo JWK” (or “dumb JWK” if you like) service provides super simple Identity Provider APIs strictly for demo purposes.

Say you’ve written a service that depends on a public JSON Web Key (JWK) set to authenticate JSON Web Tokens (JWT) submitted as OAuth 2 Bearer Tokens. Your users will normally configure the service to use an internal or well-known provider, such as Auth0, Okta, or AWS. Such providers might be too heavyweight for demo purposes, however.

For my own use, I needed nothing more than a Docker Compose file with local-only services. I also wanted some control over the contents of the tokens, since my records the sub field from the JWT in an audit trail, and something like 1a1077e6-3b87-1282-789c-f70e66dab825 (as in Vault JWTs) makes for less-than-friendly text to describe in a demo.

I created dmjwk to scratch this itch. It provides a basic Resource Owner Password Credentials Grant OAuth 2 flow to create custom JWTs, a well-known URL for the public JWK set, and a simple API that validates JWTs. None of it is real, it’s all for show, but the show’s the point.

Quick Start

The simplest way to start dmjwk is with its OCI image (there are binaries for 40 platforms, as well). It starts on port 443, since hosts commonly reserve that port, let’s map it to 4433 instead:

docker run -d -p 4433:443 --name dmjwk --volume .:/etc/dmjwk ghcr.io/theory/dmjwk

This command fires up dmjwk with a self-signed TLS certificate for localhost and creates a root cert bundle, ca.pem, in the current directory. Use it with your favorite HTTP client to make validated requests.

JWK Set

For example, to fetch the JWK set:

curl -s --cacert ca.pem https://localhost:4433/.well-known/jwks.json

By default dmjwk creates a single JWK in the set that looks something like this (JSON reformatted):

{
  "keys": [
    {
      "kty": "EC",
      "crv": "P-256",
      "x": "Ld98DHMIIanlpdOhYf-8GljNHnxHW_i6Bq0iltw9J98",
      "y": "xxyRGhCFIjdQFD-TAs-y6uf18wsPvkq8wH_FsGY1GyU"
    }
  ]
}

Configure services to use this URL, https://localhost:4433/.well-known/jwks.json, to to validate JWTs created by dmjwk.

Authorization

To fetch a JWT signed by the first key in the JWK set (just the one in this example), make an application/x-www-form-urlencoded POST with the required grant_type, username, and password fields:

form='grant_type=password&username=kamala&password=a2FtYWxh'
curl -s --cacert ca.pem -d "$form" https://localhost:4433/authorization

dmjwk stores no actual usernames and passwords; it’s all for show. Provide any username you like and Base64-encode the username, without trailing equal signs, as the password.

Example successful response:

{
  "access_token": "eyJhbGciOiJFUzI1NiIsImtpZCI6IiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiJrYW1hbGEiLCJleHAiOjE3NjY5NDQyNzcsImlhdCI6MTc2Njk0MDY3NywianRpIjoiZ3hhNnNib292aTg5dSJ9.04efdORHDA3GIPMnWErMPy4mXXsBfbnMJlzqZsxGVEc2cRvEWI0Mt_IqHDK4RYK_14BCEu2nTMiEPtgwC2IZ5A",
  "token_type": "Bearer",
  "expires_in": 3600,
  "scope": "read"
}

Parsing the the access_token JWT from the response provides this header:

{
  "alg": "ES256",
  "kid": "",
  "typ": "JWT"
}

And this payload:

{
  "sub": "kamala",
  "exp": 1766944277,
  "iat": 1766940677,
  "jti": "gxa6sboovi89u"
}

We can further customize its contents by passing any of a few additional parameters. To specify an audience and issuer, for example:

form='grant_type=password&username=kamala&password=a2FtYWxh&iss=spacely+sprockets&aud=cogswell.cogs'
curl -s --cacert ca.pem -d "$form" https://localhost:4433/authorization

Which returns something like:

{
  "access_token": "eyJhbGciOiJFUzI1NiIsImtpZCI6IiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzcGFjZWx5IHNwcm9ja2V0cyIsInN1YiI6ImthbWFsYSIsImF1ZCI6WyJjb2dzd2VsbC5jb2dzIl0sImV4cCI6MTc2NzAzNDIyNCwiaWF0IjoxNzY3MDMwNjI0LCJqdGkiOiIxNXZmaDhzYm41YWFxIn0.IGRdD5HGiWLOXggZhb9zPlLK40WWy8R0-HmSuIhaObD6WEwA2WXIBWg_MqtFFQISKLXrjNDHphXtEJsx6FZBOQ",
  "token_type": "Bearer",
  "expires_in": 3600,
  "scope": "read"
}

Now the JWT payload is:

{
  "iss": "spacely sprockets",
  "sub": "kamala",
  "aud": [
    "cogswell.cogs"
  ],
  "exp": 1767034206,
  "iat": 1767030606,
  "jti": "8ri9vfsg5f8mj"
}

This allows customization appropriate for your service, which might determine authorization based on the contents of the various JWT fields.

A request that fails to authenticate the username and password, e.g.:

form='grant_type=password&username=kamala&password=nope'
curl -s --cacert ca.pem -d "$form" https://localhost:4433/authorization

Will return an appropriate response:

{
  "error": "invalid_request",
  "error_description": "incorrect password"
}

Resource

For simple JWT validation, POST a JWT returned from the authorization API as a Bearer token to /resource:

tok=$(curl -s --cacert ca.pem -d "$form" https://localhost:4433/authorization | jq -r .access_token)
curl -s --cacert ca.pem -H "Authorization: Bearer $tok" https://localhost:4433/resource -d 'HELLO WORLD
'

The response simply returns the request body:

HELLO WORLD

A request that fails to authenticate, say with an invalid Bearer token:

curl -s --cacert ca.pem -H "Authorization: Bearer NOT" https://localhost:4433/resource -d 'HELLO WORLD'

Returns an appropriate error response:

{
  "error": "invalid_token",
  "error_description": "token is malformed: token contains an invalid number of segments"
}

That’s It

dmjwk includes a fair number of configuration options, including external certificates, custom host naming (useful with Docker Compose), and multiple key generation. If you find it useful for your demos (but not for production — DON’T DO THAT) — let me know. And if not, that’s fine, too. This is a bit of my pursuit of a thick desire, made mainly for me, but it pleases me if others find it helpful too.

The final PostgresSQL Extension Mini-Summit took place on May 7. Gabriele Bartolini gave an overview of PostgreSQL extension management in CloudNativePG (CNPG). This talk brings together the topics of several previous Mini-Summits — notably Peter Eisentraut on implementing an extension search path — to look at the limitations of extension support in CloudNativePG and the possibilities enabled by the extension search path feature and the Kubernetes 1.33 ImageVolume feature. Check it out:

• Video
• PDF Slides

Or read on for the full transcript with thanks to Floor Drees for putting it together.

Introduction

Floor Drees.

On May 7 we hosted the last of five (5) virtual Mini-Summits that lead up to the big one at the Postgres Development Conference (PGConf.Dev), taking place next week, in Montreal, Canada. Gabriele Bartolini, CloudNativePG maintainer, PostgreSQL Contributor, and VP Cloud Native at EDB, joined to talk about improving the Postgres extensions experience in Kubernetes with CloudNativePG.

The organizers:

• David Wheeler, Principal Architect at Tembo, maintainer of PGXN
• Yurii Rashkovskii, Omnigres
• Keith Fiske, Crunchy Data
• Floor Drees, Principal Program Manager at EDB, PostgreSQL CoCC member, PGDay Lowlands organizer

The stream and the closed captions available for the recording are supported by PGConf.dev and their gold level sponsors, Google, AWS, Huawei, Microsoft, and EDB.

Improving the Postgres extensions experience in Kubernetes with CloudNativePG

Gabriele Bartolini.

Hi everyone. Thanks for this opportunity, and thank you Floor and David for inviting me today.

I normally start every presentation with a question, and this is actually the question that has been hitting me and the other maintainers of CloudNativePG — and some are in this call — from the first day. We know that extensions are important in Kubernetes, in Postgres, and we’ve always been asking how can we deploy extensions, without breaking the immutability of the container.

So today I will be telling basically our story, and hopefully providing good insights in the future about how with CloudNativePG we are trying to improve the experience of Postgres extensions when running databases, including issues.

I’ve been using Postgres for 25 years. I’m one of the co-founders of 2ndQuadrant, which was bought by a EDB in 2020. And because of my contributions, I’ve been recognized as a Postgres contributor and I’m really grateful for that. And I’m also “Data on Kubernetes ambassador”; my role is to promote the usage of stateful workloads in Kubernetes. I’m also DevOps evangelist. I always say this: DevOps is the reason why I encountered Kubernetes, and it will also be the reason why I move away one day from Kubernetes. It’s about culture and I’ll explain this later.

In the past I’ve been working with Barman; I’m one of the creators of Barman. And since 2022, I’m one of the maintainers of CloudNativePG. I want to thank my company, EDB, for being the major contributor in Postgres history in terms of source code. And right now we are also the creators of CloudNativePG. And as we’ll see, the company donated the IP to the CNCF. So it’s something that is quite rare, and I’m really grateful for that.

What I plan to cover tonight is first, set the context and talk about immutable application containers, which have been kind of a dogma for us from day one. Then, how we are handling right now extensions in Kubernetes with CNPG. This is quite similar to the way other operators deal with it. Then the future and key takeaways.

First, we’re talking about Kubernetes. If you’re not familiar, it’s an orchestration system for containers. It’s not just an executor of containers, but it’s a complex system that also manages infrastructure. When it manages infrastructure, it also manages cloud native applications that are also called workloads. When we’re thinking about Postgres in Kubernetes, the database is a workload like the others. That, I think, is the most important mind shift among Postres users that I have faced myself, that I’ve always treated Postgres differently from the rest. Here in Kubernetes is it’s just another workload.

Then of course, it’s not like any other workload, and that’s where operators come into play, and I think the work that we are doing even tonight is in the direction to improve how databases is run in Kubernetes in general, and for everyone.

It was open sourced in 2014, and, it’s owned by the CNCF, and it’s actually the first project that graduated, and graduated is the most advanced stage in the graduation process of the CNCF, which starts with sandbox, then incubation and then graduation.

CloudNativePG is an operator for Postgres. It’s production-ready — what we say is level five. Level five is kind of an utopic, and unbounded level, the highest one as defined by the operator development framework. It’s used by all these players including Tembo, IBM Cloud Paks, Google Cloud, Azure, Akamai, and so on. CNPG is a CNCF project since January. It’s distributed under Apache License 2.0 and the IP — the Intellectual Property — is owned by the community and protected by the CNCF. It therefore is a vendor neutral and openly governed project. This is kind of a guarantee that it will always be free. This is also, in my opinion, a differentiation between CloudNativePG and the rest.

The project was originally created by EDB, but specifically at that time, by 2ndQuadrant. And, as I always like to recall, it was Simon Riggs that put me in charge of the initiative. I’ll always be grateful to Simon, not only for that, but for everything he has done for me and the team.

CNPG can be installed in several ways. As you can see, it’s very popular in terms of stars. There’s more than 4,000 commits. And what’s impressive is the number of downloads in three years, which is 78 million, which means that it’s used the way we wanted it to be used: with CICD pipelines.

This is the CNCF landscape; these are the CNCF projects. As you can see, there are only five projects in the CNCF in the database area, and CloudNativePG is the only one for Postgres. Our aim for 2025 and 2026 is to become incubating. If you’re using CNPG and you want to help with the process, get in touch with me and Floor.

I think to understand again, what, why we’ve done all this process, that led to the patch that, you’ve seen in Postgres 18, it’s important to understand what cloud native has meant to us since we started in 2019. We’ve got our own definition, but I think it still applies. For us it’s three things, Cloud native. It’s people that work following DevOps culture. For example, there are some capabilities that come from DevOps that apply to the cloud native world. I selected some of them like in user infrastructure, infrastructure abstraction, version control. These three form the infrastructure-as-code principle, together with the declarative configuration.

A shift left on security. You’ll see with CloudNativePG, we rarely mention security because it’s pretty much everywhere. It’s part of the process. Then continuous delivery.

The second item is immutable application containers, which kind of led the immutable way of thinking about extensions. And then the third one is that these application containers must be orchestrated via an infrastructure-as-code by an orchestrator, and the standard right now is Kubernetes.

For us it’s these three things, and without any of them, you cannot achieve cloud native.

So what are these immutable application containers? To explain immutability I’d like to talk about immutable infrastructure, which is probably what the majority of people that have historically worked with Postgres are used to. I’m primarily referring to traditional environments like VMs and bare metal where the main ways we deploy Postgres is through packages, maybe even managed by configuration managers, but still, packages are the main artifacts. The infrastructure is seen as a long-term kind of project. Changes happen over time and are incremental updates, updates on an existing infrastructure. So if you want to know the history of the infrastructure over time, you need to check all the changes that have applied. In case of failure of a system, systems are healed. So that’s the pets concept that comes from DevOps.

On the other hand, immutable infrastructure relies on OCI container images. OCI is a standard, the Open Container Initiative and it’s part of the Linux Foundation as well. Immutable infrastructure is founded on continuous delivery, which is the foundation of GitOps practices. In an immutable infrastructure, releasing a new version of an application is not updating the system’s application, it is building a new image and publishing it on a public registry and then deploying it. Changes in the system happen in an atomic way: the new version of a container is pulled from the registry and the existing image is almost instantaneously replaced by the new one. This is true for stateless applications and we’ll see, in the case of stateful applications like Postgres, is not that instantaneous because we need to perform a switchover or restart — in any case, generate a downtime.

When it comes to Kubernetes, the choice was kind of obvious to go towards that immutable infrastructure. So no incremental updates, and in the case of stateful workloads where you cannot change the content of the container, you can use data volumes or persistent volumes. These containers are not changed. If you want to change even a single file or a binary in a container image, you need to create a new one. This is very important for security and change management policies in general.

But what I really like about this way of managing our infrastructure is that, at any time, Kubernetes knows exactly what software is running in your infrastructure. All of this is versioned in an SCM, like Git or whatever. This is something that in the mutable world is less easy to obtain. Again, for security, this is the foundational thing because this is how you can control CVEs, the vulnerabilities in your system. This is a very basic representation of how you build, contain — let’s say the lifecycle of a container image. You create a Dockerfile, you put it in Git, for example, then there’s an action or a pipeline that creates the container image, maybe even run some tests and then pushes it to the container registry.

I walked you through the concepts of mutable and immutable containers, what are, these immutable application containers? If you go back and read what we were rising before CloudNativePG was famous or was even used, we were always putting in immutable application containers as one of the principles we could not lose.

For an immutable application container, it means that there’s only a single application running; that’s why it’s called “application”. If you have been using Docker, you are more familiar with system containers: you run a Debian system, you just connect and then you start treating it like a VM. Application containers are not like that. And then they are immutable — read-only — so you cannot even make any change or perform updates of packages. But in CloudNativePG, because we are managing databases, we need to put the database files in separate persistent volumes. Persistent volumes are standard resources provided by Kubernetes. This is where we put PGDATA and, if you want, a separate volume for WAL files with different storage specifications and even an optional number of table spaces.

CloudNativePG orchestrates what we call “operand images”. These are very important to understand. They contain the Postgres binaries and they’re orchestrated via what we call the “instance manager”. The instance manager is just the process that runs and controlled Postgres; I’ss the PID 1 — or the entry point — of the container.

There’s no other, like SSHD or other, other applications work. There’s just the instance manager that then controls everything else. And this is the project of the operating images. This is one open source project, and every week we rebuild the Postgres containers. We recently made some changes to the flavors of these images and I’ll talk about it shortly.

We mentioned the database, we mentioned the binaries, but what about extensions? This is the problem. Postgres extensions in Kubernetes with CloudNativePG is the next section, and it’s kind of a drama. I’m not hiding this. The way we are managing extensions in Kubernetes right now, in my opinion, is not enough. It works, but it’s got several limitations — mostly limitations in terms of usage.

For example, we cannot place them in the data files or in persistent volumes because these volumes are not read-only in any way. In any case, they cannot be strictly immutable. So we discarded this option to have persistent volume where you could kind of deploy extensions and maybe you can even download on the fly or use the package manager to download them or these kind of operations. We discarded this from the start and we embraced the operand image solution. Essentially what we did was placing these extensions in the same operand image that contains the Postgres binaries. This is a typical approach of also the other operators. If you think about also Zalando we call it “the Spilo way”. Spilo contained all the software that would run with the Zalando operator.

Our approach was a bit different, in that we wanted lighter images, so we created a few flavors of images, and also selected some extensions that we placed in the images. But in general, we recommended to build custom images. We provided instructions and we’ve also provided the requirements to build container images. But as you can see, the complexity of the operational layer is quite high, it’s not reasonable to ask any user or any customer to build their own images.

This is how they look now, although this is changing as I was saying:

You’ve got a base image, for example, the Debian base image. You deploy the Postgres binaries. Then — even right now though it’s changing — CloudNativePG requires Barman Cloud to be installed. And then we install the extensions that we think are needed. For example, I think we distribute pgAudit, if I recall correctly, pgvector and pg_failover_slots. Every layer you add, of course, the image is heavier and we still rely on packages for most extensions.

The problem is, you’ve got a cluster that is already running and you want, for example, to test an extension that’s just come out, or you want to deploy it in production. If that extension is not part of the images that we build, you have to build your own image. Because of the possible combinations of extensions that exist, it’s impossible to build all of these combinations. You could build, for example, a system that allows you to select what extensions you want and then build the image, but in our way of thinking, this was not the right approach. And then you’ve got system dependencies and, if an extension brings a vulnerability that affects the whole image and requires more updates — not just of the cluster, but also of the builds of the image.

We wanted to do something else, but we immediately faced some limitations of the technologies. One was on Postgres, the other one was on Kubernetes. In Postgres, extensions need to be placed in a single folder. It’s not possible to define multiple locations, but thanks to the work that Peter and this team have done, now we’ve got extension_control_path in version 18.

Kubernetes could not allow until, 10 days ago, to mount OCI artifacts as read-only volumes. There’s a new feature that is now part of Kubernetes 1.33 that allows us to do it.

This is the patch that I was talking about, by Peter Eisentraut. I’m really happy that CloudNativePG is mentioned as one of the use cases. And there’s also mentioned for the work that, me, David, and Marco and, primarily Marco and Niccolò from CloudNativePG have done.

This is the patch that introduced VolumeSource in Kubernetes 1.33.

The idea is that with Postgres 18 now we can set in the configuration where we can look up for extensions in the file system. And then, if there are libraries, we can also use the existing dynamic_library_path GUC.

So, you remember, this is where we come from [image above]; the good thing is we have the opportunity to build Postgres images that are minimal, that only contain Postgres.

Instead of recreating them every week — because it’s very likely that something has some dependency, has a CVE, and so recreate them for everyone, forcing everyone to update their Postgres systems — we can now release them maybe once a month, and pretty much follow the Postgres cadence patch releases, and maybe if there are CVEs it’s released more frequently.

The other good thing is that now we are working to remove the dependency on Barman Cloud for CloudNativePG. CloudNativePG has a new plugin interface and with 1.26 with — which is expected in the next weeks — we are suggesting people start moving new workloads to the Barman Cloud plugin solution. What happens is that Barman Cloud will be in that sidecar image. So it will be distributed separately, and so its lifecycle is independent from the rest. But the biggest advantage is that any extension in Postgres can be distributed — right now we’ve got packages — The idea is that they are distributed also as images.

If we start thinking about this approach, if I write an extension for Postgres, until now I’ve been building only packages for Debian or for RPM systems. If I start thinking about also building container images, they could be immediately used by the new way of CloudNativePG to manage extensions. That’s my ultimate goal, let’s put it that way.

This is how things will change at run time without breaking immutability.

There will be no more need to think about all the possible combinations of extensions. There will be the Postgres pod that runs, for example, a primary or standby, that will have the container for Postgres. If you’re using Barman Cloud, the sidecar container managed by the plugin with Barman Cloud. And then, for every extension you have, you will have a different image volume that is read-only, very light, only containing the files distributed in the container image of the extension, and that’s all.

Once you’ve got these, we can then coordinate the settings for external extension_control_path and dynamic_library_path. What we did was, starting a fail fast pilot project within EDB to test the work that Peter was doing on the extension_control_path. For that we used the Postgres Trunk Containers project, which is a very interesting project that we have at CloudNativePG. Every day it rebuilds the latest snapshot of the master branch of Postgres so that we are able to catch, at an early stage, problems with the new version of Postgres in CloudNativePG. But there’s also an action that builds container images for a specific, for example, Commitfest patch. So we use that.

Niccolò wrote a pilot patch, an exploratory patch, for the operator to define the extensions stanza inside the cluster resource. He also built some bare container images for a few extensions. We make sure to include a very simple one and the most complex one, which is PostGIS. This is the patch that — it’s still a draft — and the idea is to have it in the next version, 1.27 for CloudNativePG. This is how it works:

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: postgresql-with-extensions
spec:
  instances: 1
  imageName: ghcr.io/cloudnative-pg/postgresql-trunk:18-devel
  postgresql:
    extensions:
      - name: pgvector
        image:
          reference: ghcr.io/cloudnative-pg/pgvector-18-testing:latest
  storage:
    storageClass: standard
    size: 1Gi

We have the extensions section in the cluster definition. We name the extension. Theoretically we could also define the version and we point to the image. What’s missing in this pilot patch is support for image catalogs, but that’s something else that we can worry about later.

What happens under the hood is that when you update, or when you add a new extension in the cluster definition, a rolling update is initiated. So there’s this short downtime, but the container image is loaded in the replicas first, and then in the primary. n image volume is mounted for each extension in, let’s say, /extensions/$name_of_extension folder and CNPG updates, these two parameters. It’s quite clean, quite neat. It works, but most of the work needs to happen here. So that’s been my call, I mean to call container images as a first class artifacts. If these changes, we have a new way to distribute images.

Just to approach the conclusion, if you want to know more about the whole story, I wrote this blog article that recaps everything, and the key takeaway for me — and then we go more on the patch if you want to, and also address the questions. But what is important for me? Being in the Postgres community for a long time, I think this is a good way, a good moment for us to challenge the status quo of the extension distribution ecosystem.

I think we have an opportunity now to define a standard, which, I just want to be clear, I’m focusing myself primarily on CNPG, but this is in general, even for other operators. I’m sure that this will benefit everyone and overall it will reduce the waste that we collectively create when distributing these extensions in Kubernetes. If this becomes a standard way to distribute extensions, the benefits will be much better operational work for everyone, primarily also easier testing and validation of extensions. I mean, right now, if you see an extension, ideally that extension — and it’s very easy to build — if you’re in GitHub, to build the container images. GitHub, for example, already provides the whole infrastructure for you to easily build container images.

So if we find a standard way to define a GitHub action to build Postgres extensions, I think, if you’re a developer of an extension, you can just use it and then you find a registry in your project directly that continuously publishes or periodically publishes this extension. Any user can just reference that image URL and then without having to build images, they’re just one rolling update away from testing a patch, testing also the upgrade paths.

I think there are some unknown unknowns that kind of scare me, in general, about upgrades, upgrades of extensions. This is, in my opinion, one of the biggest issues. It’s not that they’re not solved, but they require more attention and more testing if you’re using them in an immutable world. All of these will, in my opinion, will be much, much better with the approach we’ve proposed. Images will be lighter, and the lighter image is also safer and more secure, so less prone to have CVEs,lLess prone to require frequent updates, and also they reduce the usage of bandwidth, for an organization in general. What I was saying before, any extension project can be fully independent, have their own way to build images and publish them.

One last point. I keep hearing many signs, that all of the stuff that we are proposing right now seem like a kind of a limitation of Kubernetes. The way I see it, in my view, that it’s not actually a limitation, it’s that these problems have never been addressed before. The biggest mistake we can do is focus on the specific problem of managing extensions without analyzing the benefits that the entire stack brings to an organization. Kubernetes brings a lot of benefits in terms of security, velocity, change management and, operations that any organization must consider right now. Any Postgres DBA, any Postgres user, my advice is, if you haven’t done it yet, start taking Kubernetes, seriously.

Discussion

Floor: I do think that David, you wanted to talk maybe a little bit about the mutable volume pattern?

David: Well, if people are interested, in your early slide where you were looking at alternatives, one you were thinking of was putting extensions on a mutable volume and you decided not to do that. But at Tembo we did do that and I did a bunch of work trying to improve it and try to minimize image size and all that in the last couple months. Tembo Cloud is shutting down now, so I had to stop before I finished it, but I made quite a bit of progress. I’m happy to kind of talk through the ideas there. But I think that this approach is a better long term solution, fundamentally.

Gabriele: I would like if Marco and Niccolò, if you want to talk about the actual work you’ve done. Meanwhile, Peter asks, “why does an installation of an extension require a small downtime?” The reason is that at the moment, the image volume patch, if you add a new image volume, it requires the pod to restart. Nico or Marco, Jonathan, if you want to correct me on that.

Nico or Marco or Jonathan: It provides a rolling update of the cluster right now.

Gabriele: So that’s the reason. That’s the only drawback, but the benefits in my opinion, are…

David: My understanding is that, to add a new extension, it’s mounted it in a different place. And because every single extension is its own mount, you have to add it to both those GUCs. And at least one of them requires a restart.

Gabriele: But then for example, we’ve had this conversation at EDB for example, we’re planning to have flavors of predefined extensions. For example, you can choose a flavor and we distribute those extensions. For example, I dunno, for AI we place some AI kind of extensions in the same image, so it would be different.

But otherwise I’m considering the most extreme case of one extension, one container image, which in my opinion, for the open source world is the way that hopefully will happen. Because this way, think about that – I haven’t mentioned this — if I write an extension, I can then build the image and then run automated tests using Kubernetes to assess my extension on GitHub. If those tests fail, my commit will never be merged on main. This is trunk development, continuous delivery. This is, in my opinion, a far better way of delivering and developing software. This is, again, the reason why we ended up in Kubernetes. It’s not because it’s a technology we like, it’s a toy or so, it’s because it solves bigger problems than database problems.

Even when we talk about databases, there’s still work that needs to be done, needs to be improved. I’m really happy that we have more people that know Postgres nowadays that are joining CloudNativePG, and are elevating the discussions more and more on the database level. Because before it was primarily on Kubernetes level, but now we see people that know Postgres better than me get in CloudNativePG and propose new ideas, which is great. Which is the way it needs to be, in my opinion.

But I remember, Tembo approached us because we actually talked a lot with them. Jonathan, Marco, I’m sure that you recall, when they were evaluating different operators and they chose CloudNativePG. I remember we had these discussions where they asked us to break immutability and we said, “no way”. That’s why I think Tembo had to do the solution you described, because we didn’t want to do it upstream.

I think, to be honest, and to be fair, if image volumes were not added, we would’ve probably gone down that path, because this way of managing extensions, as I was saying, is not scalable, the current one. Because we want to always improve, I think that the approach we need to be critical on what we do. So, I don’t know, Niccolò, Marco, I would like you to, if you want, explain briefly.

[A bit of chatter, opened this Dockerfile.]

FROM ghcr.io/cloudnative-pg/postgresql-trunk:18-devel AS builder

USER 0

COPY . /tmp/pgvector

RUN set -eux; \
	mkdir -p /opt/extension && \
	apt-get update && \
	apt-get install -y --no-install-recommends build-essential clang-16 llvm-16-dev && \
	cd /tmp/pgvector && \
	make clean && \
	make OPTFLAGS="" && \
	make install datadir=/opt/extension/share/ pkglibdir=/opt/extension/lib/

FROM scratch

COPY --from=builder /opt/extension/lib/* /lib/
COPY --from=builder /opt/extension/share/extension/* /share/

Niccolò: I forked, for example, pgvector, That’s what we can do basically for every simple extensions that we can just build. This is a bit more complicated because we have to build from a trunk version of Postgres 18. So we have to compile pgvector from source, and then in a scratch layer we just archive the libraries and every other content that was previously built. But ideally whenever PG 18 comes out as a stable version of Postgres, we just need to apt install pgvector and grab the files from the path. Where it gets a bit more tricky is in the case of PostGIS, or TimescaleDB, or any extension whose library requires third party libraries. For example, PostGIS has a strong requirement on the geometric libraries, so you need to import them as well inside the mount volume. I can link you an example of the PostGIS one.

Gabriele: I think it’s important, we’ve got, I think Peter here, David as well, I mean, for example, if we could get standard ways in Postgres to generate Dockerfiles for extensions, that could be great. And as I said, these extensions can be used by any operator, not only CNPG.

David: That’s my POC does. It’s a patch against the PGXS that would build a trunk image.

Gabriele: This is the work that Niccolò had to do to make PostGIS work in the pilot project: he had to copy everything.

Niccolò: I think we can make it a little bit smoother and dynamically figure out everything from the policies library, so we don’t have to code everything like this, but this is just a proof of concept that it can work.

David: So you installed all those shared libraries that were from packages.

Niccolò: Yeah, they’re being copied in the same MountVolume where the actual extensions are copied as well. And then the pilot patch is able to set up the library path inside the pod so that it makes the libraries available to the system because of course, these libraries are only part of the MountVolume. They’re not injected inside the system libraries of the pod, so we have to set up the library path to make them available to Postgres. That’s how we’re able to use them.

David: So they end up in PKGLIBDIR but they still work.

Niccolò: Yeah.

Gabriele: I mean, there’s better ideas, better ways. As Niccolò also said, it was a concept.

David: Probably a lot of these shared libraries could be shared with other extensions. So you might actually want other OCI images that just have some of the libraries that shared between.

Gabriele: Yeah, absolutely. So we could work on a special kind of, extensions or even metadatas so that we can place, you know…

So, yeah, that’s it.

Jonathan: I think it’s important to invite everyone to try and test this, especially the Postgres trunk containers, when they want to try something new stuff, new like this one, just because we always need people testing. When more people review and test, it’s amazing. Because every time we release something, probably we’ll miss something, some extension like PostGIS missing one of the libraries that wasn’t included in the path. Even if we can try to find a way to include it, it will not be there. So testing, please! Test all the time!

Gabriele: Well, we’ve got this action now, they’re failing. I mean, it’s a bit embarrassing. [Cross talk.] We already have patch to fix it.

But I mean, this is a great project as I mentioned before, because it allows us to test the current version of Postgres, but also if you want to build from a Commitfest or if you’ve got your own Postgres repository with sources, you can compile, you can get the images from using this project.

Floor: Gabriele, did you want to talk about SBOMs?

Gabriele: I forgot to mention Software Bill of Materials. They’re very important. It’s kind of now basic for any container image. There’s also the possibility to add them to these container images too. This is very important. Again, in a change manager for security and all of that — in general supply chain. And signatures too. But we’ve got signature for packages as well. There’s also a attestation of provenance.

Floor: Very good, thanks everyone!

The last Extension Ecosystem Mini-Summit is upon us. How did that happen?

Join us for a virtual conference session featuring Gabriele Bartolini, who will be discussing Extension Management in CNPG. I’m psyched for this one, as the PostgresSQL community has contributed quite a lot to improving extensions management in CloudNativePG in the past year, some of which we covered in previously. If you miss it, the video, slides, and transcript will appear here soon.

Though it may be a week or two to get the transcripts done, considering that PGConf.dev is next week, and featuring the Extension Ecosystem Summit on Tuesday, 13 May in Montreál, CA. Hope to see you there; be sure to say “hi!”

Last Wednesday, March 26, we hosted the second of five virtual Extension Mini-Summits in the lead up to the big one at the Postgres Development Conference (PGConf.dev) on May 13 in Montréal, Canada. Peter Eisentraut gave a very nice presentation on the history, design decisions, and problems solved by “Implementing an Extension Search Path”. That talk, plus another 10-15m of discussion, is now available for your viewing pleasure:

• Video
• Slides

If you’d like to attend any of the next three Mini-Summits, join the Meetup!

Once again, with many thanks again to Floor Drees for the effort, here’s the transcript from the session.

Introduction

Floor Drees introduced the organizers:

• David Wheeler, Principal Architect at Tembo, maintainer of PGXN
• Yurii Rashkovskii, Omnigres
• Keith Fiske, Crunchy Data
• Floor Drees, Principal Program Manager at EDB, PostgreSQL CoCC member, PGDay Lowlands organizer

Peter Eisentraut, contributor to PostgreSQL development since 1999, talked about implementing an extension search path.

The stream and the closed captions available for the recording are supported by PGConf.dev and their gold level sponsors, Google, AWS, Huawei, Microsoft, and EDB.

Implementing an extension search path

Peter: Thank you for having me!

I’m gonna talk about a current project by me and a couple of people I have worked with, and that will hopefully ship with Postgres 18 in a few months.

So, what do I know about extensions? I’m a Postgres core developer, but I’ve developed a few extensions in my time, here’s a list of extensions that I’ve built over the years.

• plsh
• pguint
• pgpcre
• pguri
• plxslt
• pgemailaddr
• pgtrashcan

Some of those are experiments, or sort of one-offs. Some of those are actually used in production.

I’ve also contributed to well-known extensions: orafce; and back in the day, pglogical, BDR, and pg_failover_slots, at EDB, and previously 2ndQuadrant. Those are obviously used widely and in important production environments.

I also wrote an extension installation manager called pex at one point. The point of pex was to do it in one shell script, so you don’t have any dependencies. It’s just a shell script, and you can say pex install orafce and it installs it. This was a proof of concept, in a sense, but was actually quite useful sometimes for development, when you just need an extension and you don’t know where to get it.

And then I wrote, even more experimental, a follow-on project called autopex, which is a plugin module that you load into Postgres that automatically installs an extension if you need it. If you call CREATE EXTENSION orafce, for example, and you don’t have it installed, autopex downloads and installs it. Obviously highly insecure and dubious in terms of modern software distribution practice, but it does work: you can just run CREATE EXTENSION, and it just installs it if you don’t have it. That kind of works.

So anyways, so I’ve worked on these various aspects of these over time. If you’re interested in any of these projects, they’re all under my GitHub account.

In the context of this presentation…this was essentially not my idea. People came to me and asked me to work on this, and as it worked out, multiple people came to me with their problems or questions, and then it turned out it was all the same question. These are the problems I was approached about.

The first one is extension management in the Kubernetes environment. we’ll hear about this in a future talk in this series. Gabriele Bartolini from the CloudNativePG project approached me and said that the issue in a Kubernetes environment is that if you launch a Postgres service, you don’t install packages, you have a pre-baked disk image that contains the software that you need. There’s a Postgres server and maybe some backup software in that image, and if you want to install an extension, and the extension is not in that image, you need to rebuild the image with the extension. That’s very inconvenient.

The ideal scenario would be that you have additional disk images for the extensions and you just somehow attach them. I’m hand waving through the Kubernetes terminology, and again, there will be a presentation about that in more detail. But I think the idea is clear: you want to have these immutable disk images that contain your pieces of software, and if you want to install more of them, you just wanna have these disk images augment ’em together, and that doesn’t work at the moment.

Problem number two is: I was approached by a maintainer of the Postgres.app project, a Mac binary distribution for Postgres. It’s a nice, user-friendly binary distribution for Postgres. This is sort of a similar problem: on macOS you have these .app files to distribute software. They’re this sort of weird hybrid between a zip file with files in it and a directory you can look into, so it’s kind of weird. But it’s basically an archive with software in it. And in this case it has Postgres in it and it integrates nicely into your system. But again, if you want to install an extension, that doesn’t work as easily, because you would need to open up that archive and stick the extension in there somehow, or overwrite files.

And there’s also a tie in with the way these packages are signed by Apple, and if you, mess with the files in the package, then the signature becomes invalid. It’s the way it’s been explained to me. I hope this was approximately accurate, but you already get the idea, right? There’s the same problem where you have this base bundle of software that is immutable or that you want to keep immutable and you want to add things to it, which doesn’t work.

And then the third problem I was asked to solve came from the Debian package maintainer, who will also speak later in this presentation series. What he wanted to do was to run the tests of an extension while the package is being built. That makes sense. You wanna run the tests of the software that you’re building the package for in general. But in order to do that, you have to install the extension into the the normal file system location, right? That seems bad. You don’t want to install the software while you’re into the main system while you’re building it. He actually wrote a custom patch to be able to do that, which then my work was inspired by.

Those are the problems I was approached about.

I had some problems I wanted to solve myself based on my experience working with extensions. While I was working on these various extensions over the years, one thing that never worked is that you could never run make check. It wasn’t supported by the PGXS build system. Again, it’s the same issue.

It’s essentially a subset of the Debian problem: you want to run a test of the software before you install it, but Postgres can only load an extension from a fixed location, and so this doesn’t work. It’s very annoying because it makes the software development cycle much more complicated. You always have to then, then run make all, make install, make sure you have a server running, make installcheck. And then you would want to test it against various different server versions. Usually they have to run this in some weird loop. I’ve written custom scripts and stuff all around this, but it’s was never satisfactory. It should just work.

That’s the problem I definitely wanted to solve. The next problem — and these are are all subsets of each other — that if you have Postgres installed from a package, like an RPM package for example, and then you build the extension locally, you have to install the extension into the directory locations that are controlled by your operating system. If you have Postgres under /usr, then the extensions also have to be installed under /usr, whereas you probably want to install them under /usr/local or somewhere else. You want to keep those locally built things separately, but that’s not possible.

And finally — this is a bit more complicated to explain — I’m mainly using macOS at the moment, and the Homebrew package manager is widely used there. But it doesn’t support extensions very well at all. It’s really weird because the way it works is that each package is essentially installed into a separate subdirectory, and then it’s all symlinked together. And that works just fine. You have a bunch of bin directories, and it’s just a bunch of symlinks to different subdirectories and that works, because then you can just swap these things out and upgrade packages quite easily. That’s just a design choice and it’s fine.

But again, if you wanna install an extension, the extension would be its own package — PostGIS, for example — and it would go into its own directory. But that’s not the directory where Postgres would look for it. You would have to install it into the directory structure that belongs to the other package. And that just doesn’t work. It’s just does not fit with that system at all. There are weird hacks at the moment, but it’s not satisfactory. Doesn’t work at all.

It turned out, all of these things have sort of came up over the years and some of these, people have approached me about them, and I realized these are essentially all the same problem. The extension file location is hard-coded to be inside the Postgres installation tree. Here as an example: it’s usually under something like /usr/share/postgresql/extension/, and you can’t install extensions anywhere else. If you want to keep this location managed by the operating system or managed by your package management or in some kind of immutable disk image, you can’t. And so these are essentially all versions of the same problem. So that’s why I got engaged and tried to find a solution that addresses all of ’em.

I had worked on this already before, a long time ago, and then someone broke it along the way. And now I’m fixing it again. If you go way, way back, before extensions as such existed in Postgres in 9.1, when you wanted to install a piece of software that consists of a shared library object and some SQL, you had to install the shared library object into a predetermined location just like you do now. In addition, you had to run that SQL file by hand, basically, like you run psql -f install_orafce.sql or something like that. Extensions made that a little nicer, but it’s the same idea underneath.

In 2001, I realized this problem already and implemented a configuration setting called dynamic_library_path, which allows you to set a different location for your shared library. Then you can say

dynamic_library_path = '/usr/local/my-stuff/something'

And then Postgres would look there. The SQL file just knows where is because you run it manually. You would then run

psql -f /usr/local/my-stuff/something/something.sql

That fixed that problem at the time. And when extensions were implemented, I was essentially not paying attention or, you know, nobody was paying attention. Extension support were a really super nice feature, of course, but it broke this previously-available feature: then you couldn’t install your extensions anywhere you wanted to; you were tied to this specific file system, location, dynamic_library_path still existed: you could still set it somewhere, but you couldn’t really make much use of it. I mean, you could make use of it for things that are not extensions. If you have some kind of plugin module or modules that install hooks, you could still do that. But not for an extension that consist of a set of SQL scripts and a control file and dynamic_library_path.

As I was being approached about these things, I realized that was just the problem and we should just now fix that. The recent history went as follows.

In April, 2024, just about a year ago now, David Wheeler started a hackers thread suggesting Christoph Berg’s Debian patch as a starting point for discussions. Like, “here’s this thing, shouldn’t we do something about this?”

There was, a fair amount of discussion. I was not really involved at the time. This was just after feature freeze,and so I wasn’t paying much attention to it. But the discussion was quite lively and a lot of people pitched in and had their ideas and thoughts about it. And so a lot of important, filtering work was done at that time.

Later, in September, Gabriele, my colleague from EDB who works on CloudNativePG, approached me about this issue and said like: “hey, this is important, we need this to make extensions useful in the Kubernetes environment.” And he said, “can you work, can you work on this?”

I said, “yeah, sure, in a couple months I might have time.” [Laughs]. But it sort of turns out that, at PGConf.EU we had a big brain trust meeting of various people who basically all came and said, “hey, I heard you’re working on extension_control_path, I also need that!”

Gabriele was there, and Tobias Bussmann from Postgres.app was there ,and Christoph, and I was like, yeah, I really need this extension_control_path to make this work. So I made sure to talk to everybody there and, and make sure that, if we did this, would it work for you? And then we kind of had a good idea of how it should work.

In November the first patch was posted and last week it was committed. I think there’s still a little bit of discussion of some details and, we certainly still have some time before the release to fine tune it, but the main work is hopefully done.

This is the commit I made last week. The fact that this presentation was scheduled gave me additional motivation to get it done. I wanna give some credits to people who reviewed it. Obviously David did a lot of reviews and feedback in general. My colleague Matheus, who I think I saw him earlier, he was also here on the call, did help me quite a bit with sort of finishing the patch. And then Gabriele, Marco and Nicolò, who work on CloudNativePG, did a large amount of testing.

They set up a whole sort of sandbox environment making test images for extensions and, simulating the entire process of attaching these to the main image. Again, I’m butchering the terminology, but I’m just trying to explain it in general terms. They did the whole end-to-end testing of what that would then look like with CloudNativePG. And again, that will, I assume, be discussed when Gabriele presents in a few weeks.

These are the stats from the patch

commit 4f7f7b03758

doc/src/sgml/config.sgml                                     |  68 +++++
doc/src/sgml/extend.sgml                                     |  19 +-
doc/src/sgml/ref/create_extension.sgml                       |   6 +-
src/Makefile.global.in                                       |  19 +-
src/backend/commands/extension.c                             | 403 +++++++++++++++++----------
src/backend/utils/fmgr/dfmgr.c                               |  77 +++--
src/backend/utils/misc/guc_tables.c                          |  13 +
src/backend/utils/misc/postgresql.conf.sample                |   1 +
src/include/commands/extension.h                             |   2 +
src/include/fmgr.h                                           |   3 +
src/test/modules/test_extensions/Makefile                    |   1 +
src/test/modules/test_extensions/meson.build                 |   5 +
.../modules/test_extensions/t/001_extension_control_path.pl  |  80 ++++++

the reason I show this is that, it’s not big! What I did is use the same infrastructure and mechanisms that already existed for the dynamic_library_path. That’s the code in that’s in dfmgr there in the middle. That’s where this little path search is implemented9. And then of course, in extension..c there’s some code that’s basically just a bunch of utility functions, like to list all the extensions and list all the versions of all the extensions. Those utility functions exist and they needed to be updated to do the path search. Everything else is pretty straightforward. There’s just a few configuration settings added to the documentation and the sample files and so on. It’s not that much really.

One thing we also did was add tests for this, Down there in test_extensions. We wrote some tests to make sure this works. Well, it’s one thing to make sure it works, but the other thing is if we wanna make changes or we find problems with it, or we wanna develop this further in the future, we have a record of how it works, which is why you write tests. I just wanted to point that out because we didn’t really have that before and it was quite helpful to build confidence that we know how this works.

So how does it work? Let’s say you have your Postgres installation in a standard Linux file system package controlled location. None of the actual packages look like this, I believe, but it’s a good example. You have your stuff under the /usr/bin/, you have the shared libraries in the /usr/lib/something, you have the extension control files and SQL files in the /usr/share/ or something. That’s your base installation. And then you wanna install your extension into some other place to keep these things separate. So you have /usr/local/mystuff/, for example.

Another thing that this patch implemented is that you can now also do this: when you build an extension, you can write make install prefix=something. Before you couldn’t do that, but there was also no point because if you installed it somewhere else, you couldn’t do anything with it there. Now you can load it from somewhere else, but you can also install it there — which obviously are the two important sides of that.

And then you set these two settings: dynamic_library_path is an existing configuration setting, yYou set that to where your lib directory is, and then the extension_control_path is a new setting. The titular setting of this talk, where you tell it where your extension control files are.

There’s these placeholders, $libdir and $system which mean the system location, and then the other locations are your other locations, and it’s separated by colon (and semi-colon on Windows). We had some arguments about what exactly the extension_control_path placeholder should be called and, people continue to have different opinions. What it does is it looks in the list directories for the control file, and then where it finds the control file from there, it loads all the other files.

And there’s a fairly complicated mechanism. There’s obviously the actual SQL files, but there’s also these auxiliary control files, which I didn’t even know that existed. So you can have version specific control files. It’s a fairly complicated system, so we wanted to be clear that what is happening is the, the main control file is searched for in these directories, and then wherever it’s found, that’s where it looks for the other things. You can’t have the control file in one path and then the SQL files in another part of the path; that’s not how it works.

That solves problem number five. Let’s see what problem number five was. I forgot [Chuckles]. This is the basic problem, that you no longer have to install the extensions in the directories that are ostensibly controlled by the operating system or your package manager.

So then how would Debian packaging use this? I got this information from Christoph. He figured out how to do this. He just said, “Oh, I did this, and that’s how it works.” During packaging, the packaging scripts that built it up in packages that you just pass these:

PKGARGS="--pgoption extension_control_path=$PWD/debian/$PACKAGE/usr/share/postgresql/$v/extension:\$system
--pgoption dynamic_library_path=$PWD/debian/$PACKAGE/usr/lib/postgresql/$v/lib:/usr/lib/postgresql/$v/lib"

These options set the control path and the dynamic_library_path and these versions and then it works. This was confirmed that this addresses his problem. He no longer has to carry his custom patch. This solves problem number three.

The question people ask is, “why do we have two?” Or maybe you’ve asked yourself that. Why do we need two settings. We have the dynamic_library_path, we have the extension_control_path. Isn’t that kind of the same thing? Kind of, yes! But in general, it is not guaranteed that these two things are in a in a fixed relative location.

Let’s go back to our fake example. We have the libraries in /usr/lib/postgresql and the SQL and control files in /usr/share/postgresql, for example. Now you could say, why don’t we just set it to /usr? Or, for example, why don’t we just set the path to /usr/local/mystuff and it should figure out the sub directories. That would be nice, but it doesn’t quite work in general because it’s not guaranteed that those are the subdirectories. There could be, for example. lib64, for example, right? Or some other so architecture-specific subdirectory names. Or people can just name them whatever they want. So, this may be marginal, but it is possible. You need to keep in mind that the subdirectory structure is not necessarily fixed.

So we need two settings. The way I thought about this, if you compile C code, you also have two settings. And if you think about it, it’s exactly the same thing. When you compile C code, you always have to do -I and -L: I for the include files, L for the lib files. This is basically the same thing. The include file is also the text file that describes the interfaces and the libraries are the libraries. Again, you need two options, because you can’t just tell the compiler, oh, look for it in /usr/local because the subdirectories could be different. There could be architecture specific lib directories. That’s a common case. You need those two settings. Usually they go in parallel. If somebody has a plan on how to do it simpler, follow up patches are welcome.

But the main point of why this approach was taken is also to get it done in a few months. I started thinking about this, or I was contacted about this in September and I started thinking about it seriously in the October/November timeframe. That’s quite late in the development cycle to start a feature like this, which I thought would be more controversial! People haven’t really complained that this breaks the security of extensions or anything like that. I was a little bit afraid of that.

So I wanted to really base it on an existing facility that we already had, and that’s why I wanted to make sure it works exactly in parallel to the other path that we already have, and that has existed for a long time, and was designed for this exact purpose. That was also the reason why we chose this path of least resistance, perhaps.

This is the solution progress for the six problems that I described initially. The CloudNativePG folks obviously have accompanied this project actively and have already prototyped the integration solution. And, and presumably we will hear about some of that at the meeting on May 7th, where Gabriele will talk about this.

Postgres.app I haven’t been in touch with, but one of the maintainers is here, maybe you can give feedback later. Debian is done as I described, and they will also be at the next meeting, maybe there will be some comment on that.

One thing that’s not fully implemented is the the make check issue. I did send a follow-up patch about that, which was a really quick prototype hack, and people really liked it. I’m slightly tempted to give it a push and try to get it into Postgres 18. This is a work in progress, but it’s, there’s sort of a way forward. The local install problem I said is done.

Homebrew, I haven’t looked into. It’s more complicated, and I’m also not very closely involved in the development of that. I’ll just be an outsider maybe sending patches or suggestions at some point, maybe when the release is closer and, and we’ve settled everything.

I have some random other thoughts here. I’m not actively working on these right now, but I have worked on it in the past and I plan to work on it again. Basically the conversion of all the building to Meson is on my mind, and other people’s mind.

Right now we have two build systems: the make build system and the Meson build system, and all the production packages, as far as I know, are built with make. Eventually we wanna move all of that over to Meson, but we want to test all the extensions and if it still works. As far as I know, it does work; there’s nothing that really needs to be implemented, but we need to go through all the extensions and test them.

Secondly — this is optional; I’m not saying this is a requirement — but you may wish to also build your own extensions with Meson. But that’s in my mind, not a requirement. You can also use cmake or do whatever you want. But there’s been some prototypes of that. Solutions exist if you’re interested.

And to facilitate the second point, there’s been the proposal — which I think was well received, but it just needs to be fully implemented — to provide a pkg-config file to build against the server, and cmake and Meson would work very well with that. Then you can just say here’s a pkg-config file to build against the server. It’s much easier than setting all the directories yourself or extracting them from pg_config. Maybe that’s something coming for the next release cycle.

That’s what I had. So extension_control_path is coming in Postgres 18. What you can do is test and validate that against your use cases and and help integration into the downstream users. Again, if you’re sort of a package or anything like that, you know, you can make use of that. That is all for me.

Thank you!

Questions, comments

• Reading the comments where several audience members suggested Peter follows Conference Driven Development he confirmed that that’s definitely a thing.

• Someone asked for the “requirements gathering document”. Peter said that that’s just a big word for “just some notes I have”. “It’s not like an actual document. I called it the requirements gathering. That sounds very formal, but it’s just chatting to various people and someone at the next table overheard us talking and it’s like, ‘Hey! I need that too!’”

• Christoph: I tried to get this fixed or implemented or something at least once over the last 10 something-ish years, and was basically shot down on grounds of security issues if people mess up their system. And what happens if you set the extension path to something, install an extension, and then set the path to something else and then you can’t upgrade. And all sorts of weird things that people can do with their system in order to break them. Thanks for ignoring all that bullshit and just getting it done! It’s an administrator-level setting and people can do whatever they want with it.

  So what I then did is just to implement that patch and, admittedly I never got around to even try to put it upstream. So thanks David for pushing that ahead. It was clear that the Debian version of the patch wasn’t acceptable because it was too limited. It made some assumptions about the direct restructure of Debian packages. So it always included the prefix in the path. The feature that Peter implemented solves my problem. It does solve a lot of more problems, so thanks for that.

• Peter: Testing all extensions. What we’ve talked about is doing this through the Debian packaging system because the idea was to maybe make a separate branch or a separate sub-repository of some sort, switch it to build Meson, and rebuild all the extension packages and see what happens. I guess that’s how far we’ve come. I doesn’t actually mean they all work, but I guess that most of them has tests, so we just wanted to test, see if it works.

  There are some really subtle problems. Well, the ones I know of have been fixed, but there’s some things that certain compilation options are not substituted into the Makefiles correctly, so then all your extensions are built without any optimizations, for example, without any -O options. I’m not really sure how to detect those automatically, but at least, just rebuild everything once might be an option. Or just do it manually. There are not thousands of extensions. There are not even hundreds that are relevant. There are several dozens, and I think that’s good coverage.

• Christoph: I realize that doing it on the packaging side makes sense because we all have these tests running. So I was looking into it. The first time I tried, I stopped once I realized that Meson doesn’t support LLVM yet; and the second time I tried, I just diff-ed the generated Makefiles to see if there’s any difference that looks suspicious. At thus point I should just continue and do compilation run and see what the tests are doing and and stuff.

  So my hope would be that I could run diff on the results; the problem is compiling with Postgres with Autoconf once and then with Meson the second time, then see if it has an impact on the extensions compiled. But my idea was that if I’m just running diff on the two compilations and there’s no difference, there’s no point in testing because they’re identical anyway.

• Peter Oooh, you want the actual compilation, for the Makefile output to be the same.

• Christoph: Yeah. I don’t have to run that test, But the diff was a bit too big to be readable. There was lots of white space noise in there. But there were also some actual changes. Some were not really bad, like9 in some points variables were using a fully qualified path for the make directory or something, and then some points not; but, maybe we can just work on making that difference smaller and then arguing about correctness is easier.

• Peter: Yeah, that sounds like a good approach.

• Jakob: Maybe I can give some feedback from Postgres.app. So, thank you very much. I think this solves a lot of problems that we have had with extensions over the years, especially because it allows us to separate the extensions and the main Postgres distribution. For Postgres.app we basically have to decide which extensions to include and we can’t offer additional extensions when people ask for them without shipping them for everyone. So that’s a big win.

  One question I am wondering about is the use case of people building their own extensions. As far as I understand, you have to provide the prefix/ And one thing I’m wondering whether there is there some way to give a default value for the prefix. Like in pg_config or in something like that, so people who just type make install automatically get some path.

• Peter: That might be an interesting follow on. I’m making a note of it. I’m not sure how you’d…

• Jakob: I’m just thinking because a big problem is that a lot of people who try things don’t follow the instructions for the specific Postgres. So for example, if we write documentation how to build extensions and people on a completely different system — like people Google stuff and they get instruction — they’ll just try random paths. Right now, if you just type make install, it works on most systems because it just builds into the standard directories.

• Peter: Yeah, David puts it like, “should there be a different default extension location?” I think that’s probably not an unreasonable direction. I think that’s something we should maybe think about, once this is stabilized. I think for your Postgres.app use case, it, I think you could probably even implement that yourself with a one or two line patch so that at least, if you install Postgres.app, then somebody tries to build an extension, they get a reasonable location.

• David: If I could jump in there, Jakob, my assumption was that Postgres.app would do something like designate the Application Support directory and Preferences in ~/Library as where extensions should be installed. And yeah, there could be some patch to PGXS to put stuff there by default.

• Jakob: Yeah, that would be nice!

• Peter: Robert asked a big question here. What do we think the security consequences of this patch? Well, one of the premises is that we already have dynamic_library_path, which works exactly the same way, and there haven’t been any concerns about that. Well, maybe there have been concerns, but nothing that was acted on. If you set the path to somewhere where anybody can write stuff, then yeah, that’s not so good. But that’s the same as anything. Certainly there were concerns as I read through the discussion.

  I assumed somebody would hav security questions, so I really wanted to base it on this existing mechanism and not invent something completely new. So far nobody has objected to it [Chuckles]. But yeah, of course you can make a mess of it if you go into that extension_control_path = /tmp! That’s probably not good. But don’t do that.

• David: That’s I think in part the xz exploit kind of made people more receptive to this patch because we want to reduce the number of patches that packaging maintainers have to maintain.

• Peter: Obviously this is something people do. Better we have one solution that people then can use and that we at least we understand, as opposed to everybody going out and figuring out their own complicated solutions.

• David: Peter, I think there are still some issues with the behavior of MODULEDIR from PGXS and directory in the control file that this doesn’t quite work with this extension. Do you have some thoughts on how to address those issues?

• Peter: For those who are not following: there’s an existing, I guess, rarely used feature that, in the control file, you can specify directory options, which then specifies where other files are located. And this doesn’t work the way you think it should maybe it’s not clear what that should do if you find it in a path somewhere. I guess it’s so rarely used that we might maybe just get rid of it; that was one of the options.

  In my mental model of how the C compiler works, it sets an rpath on something. If you set an absolute rpath somewhere and you know it’s not gonna work if you move the thing to a different place in the path. I’m not sure if that’s a good analogy, but it sort of has similar consequences. If you hard-code absolute path, then path search is not gonna work. But yeah, that’s on the list I need to look into.

• David: For what it’s worth, I discovered last week that the part of this patch where you’re stripping out $libdir and the extension make file that was in modules, I think? That also needs to be done when you use rpath to install an extension and point to extensions today with Postgres 17. Happy to see that one go.

• Christoph: Thanks for fixing that part. I was always wondering why this was broken. The way it was broken. It looked very weird and it turned out it was just broken and not me not understanding it.

• David: I think it might have been a documentation oversight back when extensions were added at 9.1 to say this is how you list the modules.

  Anyway, this is great! Im super excited for this patch and where it’s going and the promise for stuff in the future. Just from your list of the six issues it addresses, it’s obviously something that covers a variety of pain points. I appreciate you doing that.

• Peter: Thank you!

Many thanks and congratulations wrap up this call.

The next Mini-Summit is on April 9, Christoph Berg (Debian, and also Cybertec) will join us to talk about Apt Extension Packaging.

This Wednesday, March 26 at noon America/New_York (16:00 UTC), Peter Eisentraut has graciously agreed to give a talk at the Extension Mini Summit #2 on the extension search path patch he recently committed to PostgreSQL. I’m personally stoked for this topic, as freeing extensions from the legacy of a single directory opens up a number of new patterns for packaging, installation, and testing extensions. Hit the Meetup to register for this live video conference, and to brainstorm novel uses for this new feature, expected to debut in PostgreSQL 18.

Back on March 12, we hosted the first in a series of PostgreSQL Extensions Mini Summits leading up to the Extension Ecosystem Summit at PGConf.dev on May 13. I once again inaugurated the series with a short talk on the State of the Extension Ecosystem. The talk was followed by 15 minutes or so of discussion. Here are the relevant links:

• Video
• OCI Demo
• Slides

And now, with many thanks to Floor Drees for the effort, the transcript from the session.

Introduction

Floor Drees introduced the organizers:

• David Wheeler, Principal Architect at Tembo, maintainer of PGXN
• Yurii Rashkovskii, Omnigres
• Keith Fiske, Crunchy Data
• Floor Drees, Principal Program Manager at EDB, PostgreSQL CoCC member, PGDay Lowlands organizer

David presented a State of the Extension Ecosystem at this first event, and shared some updates from PGXN land.

The stream and the closed captions available for the recording are supported by PGConf.dev and their gold level sponsors, Google, AWS, Huawei, Microsoft, and EDB.

State of the Extensions Ecosystem

So I wanted to give a brief update on the state of the Postgres extension ecosystem, the past, present, and future. Let’s give a brie history; it’s quite long, actually.

There were originally two approaches back in the day. You could use shared preload libraries to have it preload dynamic shareable libraries into the main process. And then you could do pure SQL stuff using, including procedural languages like PL/Perl, PL/Tcl, and such.

And there were a few intrepid early adopters, including PostGIS, BioPostgres, PL/R, PL/Proxy, and pgTAP, who all made it work. Beginning of Postgres 9.1 Dimitri Fontaine added support for explicit support for extensions in the Postgres core itself. The key features included the ability to compile and install extensions. This is again, pure SQL and shared libraries.

There are CREATE, UPDATE, and DROP EXTENSION commands in SQL that you can use to add extensions to a database, upgrade them to new versions and to remove them. And then pg_dump and pg_restore support so that extensions could be considered a single bundle to be backed up and restored with all of their individual objects being included as part of the backup.

Back then, a number of us, myself included, saw this as an opportunity to have the extensibility of Postgres itself be a fundamental part of the community and distribution. I was a long time user of Perl and used CPAN, and I thought we had something like CPAN for Postgres. So, I proposed PGXN, the PostgreSQL Extension Network, back in 2010. The idea was to do distribution of source code. You would register namespaces for your extensions.

There was discovery via a website for search, documentation published, tags to help you find different kinds of objects, and to support installation through a command line interface. The compile and install stuff that Postgres itself provides, using PGXS and Configure.

This is what PGXN looks like today. It was launched in 2011. There’s a command line client, this website, an API an a registry you can upload your extensions to. The most recent one was pg_task a day or so ago.

In the interim, since that came out in 2011/2012, the cloud providers have come into their own with Postgres, but their support for extensions tends to be rather limited. For non-core extension counts, as of yesterday, Azure provides 38 extensions, GCP provides 44 extensions, and AWS 51. These are the third party extensions that don’t come with Postgres and its contrib itself. Meanwhile, PGXN has 420 extensions available to download, compile, build, and install.

A GitHub project that tracks random extensions on the internet, (joelonsql/PostgreSQL-EXTENSIONs.md), which is pretty comprehensive, has almost 1200 extensions listed. So the question is why is the support not more broad? Why aren’t there a thousand extensions available in every one of these systems?

Rthis has been a fairly common question that’s come up in the last couple years. A number of new projects have tired to fill in the gaps. One is Trusted Language Extensions. They wanted to make it easier to distribute extensions without needing dynamic shared libraries by adding additional features in the database itself.

The idea was to empower app developers to make it easy to install extensions via SQL functions rather than having to access the file system of the database server system itself. It can be portable, so there’s no compilation required, it hooks into the create extension command transparently, supports custom data types, and there have been plans for foreign data wrappers and background workers. I’m not sure how that’s progressed in the past year. The pg_tle extension itself was created by AWS and Supabase.

Another recent entrant in tooling for extensions is pgrx, which is native Rust extensions in Postgres. You build dynamic shared libraries, but write them in pure Rust. The API for pgrx provides full access to Postgres features, and still provides the developer-friendly tooling that Rust developers are used to. There’s been a lot of community excitement the last couple of years around pgrx, and it remains under active development — version 0.13.0 just came out a week or so ago. It’s sponsored and run out of the PgCentral Foundation.

There have also been a several new registries that have come up to try to fill the gap and make extensions available. They have emphasized different things than PGXN. One was ease of use. So, for example, here pgxman says it should be really easy to install a client in a single command, and then it installs something, and then it downloads and installs a binary version of your an extension.

And then there was platform neutrality. They wanted to do binary distribution and support multiple different platform, to know what binary∑ to install for a given platform. They provide stats. PGXN doesn’t provide any stats, but some of them are list stats like how many downloads we had, how many in the last 180 days.

And curation. Trunk is another binary extension registry, from my employer, Tembo. They do categorization of all the extensions on Trunk, which is at 237 now. Quite a few people have come forward to tells us that they don’t necessarily use Trunk to install extensions, but use them to find them, because the categories are really helpful for people to figure out what sorts of things are even available, and an option to use.

So here’s the State of the Ecosystem as I see it today.

• There have been some lost opportunities from the initial excitement around 2010. Extensions remain difficult to find and discover. Some are on PGXN, some are on GitHub, some are on Trunk, some are on GitLab, etc. There’s no like one place to go to find them all.

• They remain under-documented and difficult to understand. It takes effort for developers to write documentation for their extensions, and a lot of them aren’t able to. Some of them do write the documentation, but they might be in a format that something like PGXN doesn’t understand.

• The maturity of extensions can be difficult to gauge. If you look at that list of 1200 extensions on GitHub, which ones are the good ones? Which ones do people care about? That page in particular show the number of stars for each extension, but that the only metric.

• They’re difficult to configure and install. This is something TLE really tried to solve, but the uptake on TLE has not been great so far, and it doesn’t support all the use cases. There are a lot of use cases that need to be able to access the internal APIs of Postgres itself, which means compiling stuff into shared libraries, and writing them in C or Rust or a couple of other compiled languages.

  That makes them difficult to configure. You have ask questions lik: Which build system do I use? Do I install the tooling? How do I install it and configure it? What dependencies does it have? Et cetera.

• There’s no comprehensive binary packaging. The Postgres community’s own packaging systems for Linux — Apt, and YUM — do a remarkably good job of packaging extensions. They probably have more extensions packaged for those platforms than any of the others. If they have the extension you need and you’re using the PGDG repositories, then this stuff is there. But even those are still like a fraction of all the potential available extensions that are out there.

• Dependency management can be pretty painful. It’s difficult to know what you need to install. I was messing around yesterday with the PgSQL HTTP extension, which is a great extension that depends on libcurl. I thought maybe I could build a package that includes libcurl as part of it. But then I realized that libcurl depends on other packages, other dynamic libraries. So I’d have to figure out what all those are to get them all together.

  A lot of that goes away if you use a system like apt or yum. But if you, if you don’t, or you just want to install stuff on your Mac or Windows, it’s much more difficult.

• Centralized source distribution, we’ve found found, is insufficient. Even if all the extensions were available on PGXN, not everybody has the wherewithal or the expertise to find what they need, download it, compile it, and build it. Moreover, you don’t want to have a compiler on your production system, so you don’t want to be building stuff from source on your production system. So then you have to get to the business of building your own packages, which is a whole thing.

But in this state of the extension ecosystem we see new opportunities too. One I’ve been working on for the past year, which we call “PGXN v2”, is made possible by my employer, Tembo. The idea was to consider the emerging patterns — new registries and new ways of building and releasing and developing extensions — and to figure out the deficiencies, and to engage deeply with the community to work up potential solutions, and to design and implement a new architecture. The idea is to serve the community for the next decade really make a PGXN and its infrastructure the source of record for extensions for Postgres.

In the past year, I did a bunch of design work on it. Here’s a high level architectural view. We’d have a root registry, which is still the source code distribution stuff. There’s a web UX over it that would evolve from the current website. And there’s a command line client that knows how to build extensions from the registry.

But in addition to those three parts, which we have today, we would evolve a couple of additional parts.

1. One is “interactions”, so that when somebody releases a new extension on PGXN, some notifications could go out through webhooks or some sort of queue so that downstream systems like the packaging systems could know something new has come out and maybe automate building and updating their packages.

2. There could be “stats and reports”, so we can provide data like how many downloads there are, what binary registries make them available, what kinds of reviews and quality metrics rate them. We can develop these stats and display those on the website.

3. And, ideally, a “packaging registry” for PGXN to provide binary packages for all the major platforms of all the extensions we can, to simplify the installation of extensions for anybody who needs to use them. For extensions that aren’t available through PGDG or if you’re not using that system and you want to install extensions. Late last year, I was focused on figuring out how t build the packaging system.

Another change that went down in the past year was the Extension Ecosystem Summit itself. This took place at PGConf.Dev last May. The idea was for a community of people to come together to collaborate, examine ongoing work in the extension distribution, examine challenges, identify questions, propose solutions, and agree on directions for execution. Let’s take a look at the topics that we covered last year at the summit.

• One was extension metadata, where the topics covered included packaging and discoverability, extension development, compatibility and taxonomies as being important to represent a metadata about extensions — as well as versioning standards. One of the outcomes was an RFC for version two of the PGXN metadata that incorporates a lot of those needs into a new metadata format to describe extensions more broadly.

• Another topic was the binary distribution format and what it should look like, if we were to have major, distribution format. We talked about being able to support multiple versions of an extension at one time. There was some talk about the Python Wheel format as a potential precedent for binary distribution of code.

  There’s also an idea to distribute extensions through Docker containers, also known as the Open Container Initiative. Versioning came up here, as well. One of the outcomes from this session was another PGXN RFC for binary distribution, which was inspired by Python Wheel among other stuff.

  I wanted to give a brief demo build on that format. I hacked some changes into the PGXS Makefile to add a new target, trunk that builds a binary package called a “trunk” and uploads it to an OCI registry for distribution. Here’s what it looks like.

  • On my Mac I was compiling my semver extension. Then I go into a Linux container and compile it again for Linux using the make trunk command. The result is two .trunk files, one for Postgres 16 on Darwin and one for Postgres 16 on Linux.

  • There are also some JSON files that are annotations specifically for OCI. We have a command where we can push these images to an OCI registry.

  • Then we can then use an install command that knows to download and install the version of the build appropriate for this platform (macOS). And then I go into Linux and do the same thing. It also knows, because of the OCI standard, what the platform is, and so it installs the appropriate binary.

• Another topic was ABI and API compatibility. There was some talk at the Summit about what is the definition of an ABI and an API and how do we define internal APIs and their use? Maybe there’s some way to categorize APIs in Postgres core for red, green, or in-between, something like that. There was desire to have more hooks available into different parts of the system.

  One of the outcomes of this session was that I worked with Peter Eisentraut on some stability guidance for the API and ABI that is now committed in the docs. You can read them now on in the developer docs, they’ll be part of the Postgres 18 release. The idea is that minor version releases should be safe to use with other minor versions. If you compiled your extension against one minor version, it should be perfectly compatible with other minor versions of the same major release.

  Interestingly, there was a release earlier this year, like two weeks after Peter committed this, where there was an API break. It’s the first time in like 10 years. Robert Treat and I spent quite a bit of time trying to look for a previous time that happened. I think there was one about 10 years ago, but then this one happened and, notably it broke the Timescale database. The Core Team decided to release a fix just a week later to restore the ABI compatibility.

  So it’s clear that even though there’s guidance, you should in general be able to rely on it, and it was a motivating factor for the a new release to fix an ABI break, there are no guarantees.

  Another thing that might happen is that I proposed a Google Summer of Code project to build an ABI checker service. Peter [embarrassing forgetfulness and misattributed national identity omitted] Geoghegan POC’d an ABI checker in 2023. The project is to take Peter’s POC and build something that could potentially run on every commit or push to the back branches of the project. Maybe it could be integrated into the build farm so that, if there’s a back-patch to an earlier branch and it turns red, they quickly the ABI was broken. This change could potentially provide a higher level of guarantee — even if they don’t end up using the word “guarantee” about the stability of the ABIs and APIs. I’m hoping this happens; a number of people have asked about it, and at least one person has written an application.

• Another topic at the summit last year was including or excluding extensions in core. They’ve talked about when to add something to core, when to remove something from core, whether items in contrib should actually be moved into core itself, and whether to move metadata about extensions into catalog. And once again, support for multiple versions came up; this is a perennial challenge! But I’m not aware of much work on these questions. I’m wondering if it’s time for a revisit,

• As a bonus item — this wasn’t a formal topic at the summit last year, but it came up many times in the mini-summits — is the challenge of packaging and lookup. There’s only one path to extensions in SHAREDIR. This creates a number of difficulties. Christoph Berg has a patch for a PGDG and Debian that adds a second directory. This allowed the PGDG stuff to actually run tests against extensions without changing the core installation of the Postgres service itself. Another one is Cloud Native Postgres immutability. If that directory is part of the image, for your CloudNative Postgres, you can’t install extensions into it.

  It’s a similar issue, for Postgres.app immutability. Postgres.app is a Mac app, and it’s signed by a certificate provided by Apple. But that means that if you install an extension in its SHAREDIR, it changes the signature of the application and it won’t start. They work around this issue through a number of symlink shenanigans, but these issues could be solved by allowing extension to be installed in multiple locations.

  Starting with Christoph’s search path patch and a number of discussions we had at PGConf last year, Peter Eisentraut has been working on a search path patch to the core that would work similar to shared preload libraries, but it’s for finding extension control files. This would allow you to have them in multiple directories and it will find them in path.

  Another interesting development in this line has been, the CloudNativePG project has been using that extension search path patch to prototype a new feature coming to Kubernetes that allows one to mount a volume that’s actually another Docker image. If you have your extension distributed as an OCI image, you can specify that it be mounted and installed via your CNPG cluster configuration. That means when CNPG spins up, it puts the extension in the right place. It updates the search path variables and stuff just works.

  A lot of the thought about the stuff went into a less formal RFC I wrote up in my blog, rather than on PGXN. The idea is to take these improvements and try to more formally specify the organization of extensions separate from how Postgres organizes shared libraries and shared files.

I said, we’re bringing the Extension Summit back! There will be another Extension Summit hosted our team of organizers, myself, Floor, Keith Fiske from Crunchy Data, and Yurii from Omnigres. That will be on May 13th in the morning at PGConf.dev; we appreciate their support.

The idea of these Mini Summits is to bring up a number of topics of interest. Have somebody come and do a 20 or 40 minute talk about it, and then we can have discussion about implications.

Floor mentioned the schedule, but briefly:

• March 12: David Wheeler, PGXN: “State of the Extension Ecosystem”
• March 24: Peter Eisentraut, Core Team: “Implementing an Extension Search Path”
• April 9: Christoph Berg, Debian: “Apt Extension Packaging”
• April 23:
• May 7: Gabriele Bartolini, CNPG “Extension Management in CloudNativePG”

So, what are your interests in extensions and how they can be improved. There are a lot of potential topics to talk about at the Summit or at these Mini Summits: development tools, canonical registry, how easy it is to publish, continuous delivery, yada, yada, yada, security scanning — all sorts of stuff that could go into conceiving, designing, developing, distributing extensions for Postgres.

I hoe you all will participate. I appreciate you taking the time to listen to me for half an hour. So I’d like to turn it over to, discussion, if people would like to join in, talk about implications of stuff. Also, we can get to any questions here.

Questions, comments, shout-outs

Floor: David, at one point you talked about, metadata taxonomy. If you can elaborate on that a little bit, that’s Peter’s question.

David: So one that people told me that they found useful was one provided by Trunk. So it has these limited number of categories, so if you’re interested in machine learning stuff, you could go to the machine learning stuff and it shows you what extensions are potentially available. They have 237 extensions on Trunk now.

PGXN itself allows arbitrary tagging of stuff. It builds this little tag cloud. But if I look at this one here, you can see this one has a bunch of tags. These are arbitrary tags that are applied by the author. The current metadata looks like this. It’s just plain JSON, and it has a list of tags. The PGXN Meta v2 RFC has a bunch of examples. It’s an evolution of that META.json, so the idea is to have a classifications that includes tags as before, but also adds categories, which are a limited list that would be controlled by the core [he means “root”] registry:

{
  "classifications": {
    "tags": [
      "testing",
      "pair",
      "parameter"
    ],
    "categories": [
      "Machine Learning"
    ]
  }
}

Announcements

Yurii made a number of announcements, summarizing:

• There is a new library that they’ve been developing at Omnigres that allows you to develop Postgres extensions in C++. For people who are interested in developing extensions in C++ and gaining the benefits of that and not having to do all the tedious things that we have to do with C extensions: look for Cppgres. Yurii thinks that within a couple of months it will reach parity with pgrx.

  David: So it sounds like it would work more closely to the way PGXS and C works. Whereas pgrx has all these additional Rust crates you have to load and like slow compile times and all these dependencies.

  Yurii: This is just like a layer over the C stuff, an evolution of that. It’s essentially a header only library, so it’s a very common thing in the C++ world. So you don’t have to build anything and you just include a file. And in fact the way I use it, I amalgamate all the header files that we have into one. Whenever I include it in the project, I just copy the amalgamation and it’s just one file. You don’t have any other build chain associated yet. It is C++ 20, which some people consider new, but by the time it’s mature it’s already five years old and most compilers support it. They have decent support of C++ 20 with a few exclusions, but those are relatively minor. So for that reason, it’s not C++ 23, for example, because it’s not very well supported across compilers, but C++ 20 is.

• Yurii is giving a talk about PostgresPM at the Postgres Conference in Orlando. He’ll share the slides and recording with this group. The idea behind PostgresPM is that it takes a lot of heuristics, takes the URLs of packages and of extensions and creates packages for different outputs like for Red Hat, for Debian, perhaps for some other formats in the future. It focuses on the idea that a lot of things can be figured out.

  For example: do we have a new version? Well, we can look at list of tags in the Git repo. Very commonly that works for say 80 percent of extensions. Do we need a C compiler? We can see whether we have C files. We can figure out a lot of stuff without packagers having to specify that manually every time they have a new extension. And they don’t have to repackage every time there is a new release, because we can detect new releases and try to build.

• Yurii is also running an event that, while not affiliated with PGConf.dev, is strategically scheduled to happen one day before PGConf.dev: Postgres Extensions Day. The Call for Speakers is open until April 1st. There’s also an option for people who cannot or would not come to Montréal this year to submit a prerecorded talk. The point of the event is not just to bring people together, but also ti surface content that can be interesting to other people. The event itself is free.

Make sure to join our Meetup group and join us live, March 26, when Peter Eisentraut joins us to talk about implementing an extension search path.

I’m happy to announce that some PostgreSQL colleagues and have once again organized the Extension Ecosystem Summit at PGConf.dev in Montréal on May 13. Floor Drees, Yurii Rashkovskii, Keith Fiske will be on hand to kick off this unconference session:

Participants will collaborate to learn about and explore the ongoing work on PostgreSQL development and distribution, examine challenges, identify questions, propose solutions, and agree on directions for execution.

Going to PGConf.dev? Select it as an “Additional Option” when you register, or update your registration if you’ve already registered. Hope to see you there!

Extension Ecosystem Mini-Summit 2.0

We are also once again hosting a series of virtual gatherings in the lead-up to the Summit, the Postgres Extension Ecosystem Mini-Summit.

Join us for an hour or so every other Wednesday starting March 12 to hear contributors to a variety of community and commercial extension initiatives outline the problems they want to solve, their attempts to so, challenges discovered along the way, and dreams for an ideal extension ecosystem in the future. Tentative speaker lineup (will post updates as the schedule fills in):

• March 12: David Wheeler, PGXN: “State of the Extension Ecosystem”
• March 24: Peter Eisentraut, Core Team: “Implementing an Extension Search Path”
• April 9: Christoph Berg, Debian: “Apt Extension Packaging”
• April 23: Celeste Horgan, Sonia Valeja, and Alexey Palazhchenko: “The User POV”
• May 7: Gabriele Bartolini, CNPG “Extension Management in CloudNativePG”

Join the meetup for details. These sessions will be recorded and Posted to the PGConf.dev YouTube and we’ll have again detailed transcripts. Many thanks to my co-organizers Floor Drees and Yurii Rashkovskii, as well as the PGConf.dev organizers for making this all happen!

Update 2025-04-14: Added the April 23 session topic and panelists.

Released yesterday: Sqitch v1.5.0. This version the MySQL driver DBD::mysql with DBD::MariaDB, both for its better backward compatibility with MySQL as well as MariaDB driver libraries and for its improved Unicode handling. The Docker image likewise switched to the MariaDB mysql client. I expect no compatibility issues, but you never know! Please file an issue should you find any.

V1.5.0 also features a fixes for Yugabyte deployment, Oracle error handling, existing Snowflake schemas, connecting to MySQL/MariaDB without a database name, and omitting the checkit MySQL/MariaDB function when the Sqitch user lacks sufficient permission to create it. Sqitch now will also complain when deploying with --log-only and a deployment file is missing.

Find it in the usual places:

• sqitch.org
• GitHub
• CPAN
• Docker
• Homebrew

Many thanks to everyone who has enjoyed using Sqitch and let me know in person, via email Mastodon, bug reports, and patches. It gratifies me how useful people find it.