ERROR: extension "pg_uuidv7" is not available

[austinm911]

Hi, I am quite new to docker so please bear with me.

I am trying to create temporary docker containers with testcontainers node to test using vitest my Postgres 15 database. I am using pg_uuidv7 on Neon.tech so trying to replicate the extension.

For example tables in my db are like (defined using drizzle)

CREATE TABLE IF NOT EXISTS "invitations" (
    "invitation_id" uuid PRIMARY KEY DEFAULT uuid_generate_v7() NOT NULL,
    "inviter_team_id" uuid NOT NULL,
    "inviter_user_id" uuid NOT NULL,
    "invitee_email" varchar(100),
    "created_at" timestamp DEFAULT now() NOT NULL,
    "updated_at" timestamp,
    "deleted_at" timestamp,
    CONSTRAINT "invitations_invitation_token_unique" UNIQUE("invitation_token")
);

I forked the repo and built the image

❯ docker build --no-cache  . --tag pg_uuidv7
[+] Building 32.8s (11/11) FINISHED                                                              docker:desktop-linux
 => [internal] load .dockerignore                                                                                0.0s
 => => transferring context: 2B                                                                                  0.0s
 => [internal] load build definition from Dockerfile                                                             0.0s
 => => transferring dockerfile: 400B                                                                             0.0s
 => [internal] load metadata for docker.io/library/postgres:15-bullseye                                          0.0s
 => CACHED [1/6] FROM docker.io/library/postgres:15-bullseye                                                     0.0s
 => [internal] load build context                                                                                0.0s
 => => transferring context: 2.47kB                                                                              0.0s
 => [2/6] RUN apt-get update && apt-get -y upgrade   && apt-get install -y build-essential libpq-dev postgresq  30.0s
 => [3/6] WORKDIR /srv                                                                                           0.0s
 => [4/6] COPY . /srv                                                                                            0.0s
 => [5/6] RUN make                                                                                               0.4s
 => [6/6] RUN tar -czvf pg_uuidv7.tar.gz pg_uuidv7--1.2.sql pg_uuidv7.control pg_uuidv7.so   && sha256sum pg_uu  0.3s
 => exporting to image                                                                                           2.0s
 => => exporting layers                                                                                          2.0s
 => => writing image sha256:fcdac1e74592953d86d900a443125920b3f72ec3ad6ae28f329628283013651b                     0.0s
 => => naming to docker.io/library/pg_uuidv7                                                                     0.0s

Started the container

❯ docker run -p 49153:5432 -e POSTGRES_USER=testuser -e POSTGRES_PASSWORD=testpassword -e POSTGRES_DB=mydatabase pg_uuidv7

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    pg_ctl -D /var/lib/postgresql/data -l logfile start

initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.
waiting for server to start....2023-09-19 06:17:20.308 UTC [49] LOG:  starting PostgreSQL 15.4 (Debian 15.4-2.pgdg110+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
2023-09-19 06:17:20.309 UTC [49] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-09-19 06:17:20.312 UTC [52] LOG:  database system was shut down at 2023-09-19 06:17:20 UTC
2023-09-19 06:17:20.316 UTC [49] LOG:  database system is ready to accept connections
 done
server started
CREATE DATABASE

/usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*

waiting for server to shut down...2023-09-19 06:17:20.470 UTC [49] LOG:  received fast shutdown request
.2023-09-19 06:17:20.471 UTC [49] LOG:  aborting any active transactions
2023-09-19 06:17:20.472 UTC [49] LOG:  background worker "logical replication launcher" (PID 55) exited with exit code 1
2023-09-19 06:17:20.473 UTC [50] LOG:  shutting down
2023-09-19 06:17:20.473 UTC [50] LOG:  checkpoint starting: shutdown immediate
2023-09-19 06:17:20.504 UTC [50] LOG:  checkpoint complete: wrote 918 buffers (5.6%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.013 s, sync=0.017 s, total=0.032 s; sync files=301, longest=0.005 s, average=0.001 s; distance=4217 kB, estimate=4217 kB
2023-09-19 06:17:20.510 UTC [49] LOG:  database system is shut down
 done
server stopped

PostgreSQL init process complete; ready for start up.

2023-09-19 06:17:20.589 UTC [1] LOG:  starting PostgreSQL 15.4 (Debian 15.4-2.pgdg110+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
2023-09-19 06:17:20.589 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2023-09-19 06:17:20.590 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2023-09-19 06:17:20.592 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-09-19 06:17:20.594 UTC [65] LOG:  database system was shut down at 2023-09-19 06:17:20 UTC
2023-09-19 06:17:20.597 UTC [1] LOG:  database system is ready to accept connections

Then I connected to the database using beekeeper and ran the SQL statement CREATE EXTENSION pg_uuidv7

2023-09-19 06:18:57.539 UTC [83] ERROR:  extension "pg_uuidv7" is not available
2023-09-19 06:18:57.539 UTC [83] DETAIL:  Could not open extension control file "/usr/share/postgresql/15/extension/pg_uuidv7.control": No such file or directory.
2023-09-19 06:18:57.539 UTC [83] HINT:  The extension must first be installed on the system where PostgreSQL is running.
2023-09-19 06:18:57.539 UTC [83] STATEMENT:  CREATE EXTENSION pg_uuidv7

I am not seeing the pg_uuidv7 files in the container. Not sure if i am missing something or did something wrong. Thanks!

[fboulnois]

The Dockerfile is mainly for building the code, but I could make it a functional Postgres instance too. In the meantime, you'll probably want to have your own Dockerfile that does something like this:

FROM postgres:15

COPY --from=pg_uuidv7 /srv/pg_uuidv7.so /usr/lib/postgresql/${PG_MAJOR}/lib
COPY --from=pg_uuidv7 /srv/pg_uuidv7.control /usr/share/postgresql/${PG_MAJOR}/extension
COPY --from=pg_uuidv7 /srv/pg_uuidv7--1.2.sql /usr/share/postgresql/${PG_MAJOR}/extension

[austinm911]

The Dockerfile is mainly for building the code, but I could make it a functional Postgres instance too. In the meantime, you'll probably want to have your own Dockerfile that does something like this:

FROM postgres:15

COPY --from=pg_uuidv7 /srv/pg_uuidv7.so /usr/lib/postgresql/${PG_MAJOR}/lib
COPY --from=pg_uuidv7 /srv/pg_uuidv7.control /usr/share/postgresql/${PG_MAJOR}/extension
COPY --from=pg_uuidv7 /srv/pg_uuidv7--1.2.sql /usr/share/postgresql/${PG_MAJOR}/extension

Thanks for the reply. I was able to get this working below to make it all work in one step. Still learning docker but now I understand a bit more on using it as a base image. Thanks again

FROM postgres:15-bullseye

# Install packages for building the extension
RUN apt-get update && apt-get -y upgrade && \
    apt-get install -y build-essential libpq-dev postgresql-server-dev-all

# Set the working directory and copy source files
WORKDIR /srv
COPY . /srv

# Build the extension
RUN make

# Package the extension and create checksums
RUN tar -czvf pg_uuidv7.tar.gz pg_uuidv7--1.2.sql pg_uuidv7.control pg_uuidv7.so && \
    sha256sum pg_uuidv7--1.2.sql pg_uuidv7.control pg_uuidv7.so pg_uuidv7.tar.gz > SHA256SUMS

# Install the extension
RUN cp pg_uuidv7.so /usr/lib/postgresql/15/lib/ && \
    cp pg_uuidv7--1.2.sql pg_uuidv7.control /usr/share/postgresql/15/extension/

Makefile

MODULES = pg_uuidv7
EXTENSION = pg_uuidv7
DATA = pg_uuidv7--1.2.sql

PG_CONFIG = /usr/lib/postgresql/15/bin/pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

[fboulnois]

If you don't want to modify the Makefile, you could also do the following (assumes you're using v1.3.0 of the extension which now builds for multiple Postgres versions):

RUN cp ${PG_MAJOR}/pg_uuidv7.so /usr/lib/postgresql/${PG_MAJOR}/lib \
  && cp pg_uuidv7.control /usr/share/postgresql/${PG_MAJOR}/extension \
  && cp pg_uuidv7--1.3.sql /usr/share/postgresql/${PG_MAJOR}/extension

I'll likely set this in a future version of the extension.

[floristenhove]

I have followed these steps, and I feel I almost got it working, except for this last error when running CREATE EXTENSION pg_uuidv7;:

ERROR: could not load library "/usr/lib/postgresql/15/lib/pg_uuidv7.so": /usr/lib/postgresql/15/lib/pg_uuidv7.so: cannot open shared object file: No such file or directory

I see the file does exist at this path when I manually go into my container. I have tried to explicitly set permissions to postgres:postgres but this doesn't seem to help. Do you have any other ideas here? Any help is much appreciated.

[fboulnois]

This issue is resolved as of v1.4.0.