Postgres Extensions from Scratch

Setup

Run postgres in a docker container and get a shell into it.

docker pull postgres
docker run -d --name ext-demo --hostname ext-demo -e POSTGRES_HOST_AUTH_METHOD=trust -p 127.0.0.1:5432:5432 postgres
docker exec ext-demo /bin/bash -c "apt-get update && apt-get install -y vim"
docker exec  -it ext-demo /bin/bash

How is a postgres extension packaged?

a .control file
one or more .sql files
[optionally] one or more shared libaries

Where are postgres extensions installed?

The .control file and the .sql files go in the extension directory. You can use the pg_config tool to find this directory. It is nested under the sharedir.

pg_config --sharedir
#/usr/share/postgresql/17

cd `pg_config --sharedir`/extension
pwd
#/usr/share/postgresql/17/extension

ls

Let's make an extension

Create a src directory for us to work in

cd ~
mkdir src
cd src

Put the following in hello--1.0.0.sql. Our extension will define a single function named "hello".

NOTE: the double-dashes are important! A single dash won't work.

create or replace function hello(person text) returns text
as $func$
begin
  return format('hello %s', person);
end
$func$ language plpgsql;

Now, we need a .control file. Put the following in hello.control.

comment='the hello extension'
default_version='1.0.0'

Make our extension available

To make our extension available in the database, we simply copy the .sql and .control files to the extension directory.

cp ~/src/* `pg_config --sharedir`/extension/

Now, it should be available in our database. Let's check

psql -U postgres -c "select * from pg_available_extensions where name = 'hello';"

We should see that the hello extension is available with a default version of 1.0.0 but is not installed.

 name  | default_version | installed_version |       comment
-------+-----------------+-------------------+---------------------
 hello | 1.0.0           |                   | the hello extension
(1 row)

Install the extension

Get a psql shell

psql -U postgres

Run these commands in the shell

create extension hello;

select * from pg_available_extensions where name = 'hello';

select * from pg_extension where extname = 'hello';

\dx+ hello

select hello('bob');

Let's release a new version

Put the following in hello--2.0.0.sql.

create or replace function add_forty_two(num int) returns int
as $func$
  select num + 42
$func$ language sql;

Alter hello.control. Change the default version to 2.0.0

comment='the hello extension'
default_version='2.0.0'

Copy the files into the extension directory

cp ~/src/* `pg_config --sharedir`/extension/

Let's see what versions postgres thinks are available

psql -U postgres -c "select * from pg_available_extension_versions where name = 'hello';"

 name  | version | installed | superuser | trusted | relocatable | schema | requires |       comment
-------+---------+-----------+-----------+---------+-------------+--------+----------+---------------------
 hello | 1.0.0   | t         | t         | f       | f           |        |          | the hello extension
 hello | 2.0.0   | f         | t         | f       | f           |        |          | the hello extension
(2 rows)

Let's update to the 2.0.0 version.

psql -U postgres -c "alter extension hello update;"

ERROR:  extension "hello" has no update path from version "1.0.0" to version "2.0.0"

Uhoh. We can't update to 2.0.0.

Well, let's try installing 2.0.0 directly.

drop extension hello;
create extension hello with version '2.0.0';

\dx+ hello

  Objects in extension "hello"
       Object description
---------------------------------
 function add_forty_two(integer)
(1 row)

Our new add_forty_two function is there, but what happened to our hello function?

Installing version 2.0.0 ONLY runs the hello--2.0.0.sql file. It doesn't also run the hello--1.0.0.sql file. If we want to install directly into 2.0.0 via the hello--2.0.0.sql file, it will need to include everything from version 1.0.0 explicitly.

Update Paths

There's no magic to make updates automatic. We have to explicitly tell postgres how to get from one version of the extension to the next. To do that, we provide another SQL file.

An update path is defined by a SQL file that has two versions in the file name. The first is the prior version, and the second is the target version. Postgres will parse these versions out of the file name to determine what update paths are supported.

Let's rename hello--2.0.0.sql to hello--1.0.0--2.0.0.sql. Again, the double-dashes are important. Single dashes will not work. Then, let's replace our files in the postgres extension directory again.

mv hello--2.0.0.sql hello--1.0.0--2.0.0.sql
rm `pg_config --sharedir`/extension/hello*
cp ~/src/* `pg_config --sharedir`/extension/

Now, ask postgres what update paths it detects.

psql -U postgres -c "select * from pg_extension_update_paths('hello');"

 source | target |     path
--------+--------+--------------
 1.0.0  | 2.0.0  | 1.0.0--2.0.0
 2.0.0  | 1.0.0  |
(2 rows)

Let's try it out

drop extension hello;
create extension hello with version '1.0.0';
alter extension hello update;
\dx hello
\dx+ hello

          List of installed extensions
 Name  | Version | Schema |     Description
-------+---------+--------+---------------------
 hello | 2.0.0   | public | the hello extension
(1 row)

  Objects in extension "hello"
       Object description
---------------------------------
 function add_forty_two(integer)
 function hello(text)
(2 rows)

Ah! We were able to install version 1.0.0, update to 2.0.0, and now the extension has both functions.

If we want to install version 2.0.0 without installing 1.0.0 first, postgres can do that. With the files we have, it will run the 1.0.0 file and then the upgrade path file to get to 2.0.0.

drop extension hello;
create extension hello with version '2.0.0';
\dx hello
\dx+ hello

          List of installed extensions
 Name  | Version | Schema |     Description
-------+---------+--------+---------------------
 hello | 2.0.0   | public | the hello extension
(1 row)

  Objects in extension "hello"
       Object description
---------------------------------
 function add_forty_two(integer)
 function hello(text)
(2 rows)

We can create a hello--2.0.0.sql file that works as expected, too. It needs to be cumulative though.

cat hello--1.0.0.sql > hello--2.0.0.sql
cat hello--1.0.0--2.0.0.sql >> hello--2.0.0.sql
cp ~/src/* `pg_config --sharedir`/extension/
ls `pg_config --sharedir`/extension/hello*

/usr/share/postgresql/17/extension/hello--1.0.0--2.0.0.sql
/usr/share/postgresql/17/extension/hello--1.0.0.sql
/usr/share/postgresql/17/extension/hello--2.0.0.sql
/usr/share/postgresql/17/extension/hello.control

Now, when installing 2.0.0 without updating from 1.0.0, postgres will just execute the hello--2.0.0.sql file.

Enjoy!

jgpruitt/extension-from-scratch.md