Create official docker images as part of release cycle

[beargiles]

The releases should include official docker images as part of the release cycle.

There are several reasons:

• people regularly ask for them

• people will write their own, encounter problems, and ask questions that are difficult to answer without having a copy of the docker image. (And maybe hard to answer even if you have the docker image)

• people have been asking about nontrivial examples of the java application using and testing their server-side code.

The last item can be handled via a simple Spring Boot application that uses TestContainers to load an existing docker image. This requires an IDE that supports container-based testing or a docker instance . I'll defer discussion of this until later.

Current work

I had a github project with actions that would poll the official PostgreSQL repo and build new images after a release but it had two limitations since it was implemented in github actions using nothing but ansible. The first was that there was usually a lag between when the official database was release and when the official extensions were released. That confused my simple scripts. The second was that it requires a fair amount of work to extract the pljava version from the repo's package and I had a stop-gap manual method that wasn't sustainable.

That said...

I still have a multistage Dockerfile that can create clean docker images containing a preconfigured pl/java extension. It's ready to go - no need to execute CREATE EXTENSION pljava etc since it's done as part of the container's initialization.

For now we can use my existing Dockerfile, with a few tweaks for running in your environment, and possibly some github actions if you want the builds to be performed there. That has benefits since you can use CI/CD tools in that environment (e.g., for checking for known vulnerabilities) but there will still need to be a local build if a local container is required for the Spring Boot/Test Containers module mentioned above.

This Dockerfile may be split in the future since it starts by building a PGXN docker image and then extending that with the PL/Java docker image. I want to contribute that to the respective project but will probably keep it here because of the use of the official PostgreSQL docker image.

I'll add the current Dockerfile as a comment.

[beargiles]

Notes

• The base image is the official PostgreSQL docker image. It allows us to eliminate the usual initialization tasks while retaining the flexibility of that image.

• The ARG are build-time parameters with default values.

• This is a multistage build and each starts at the FROM statement (or any immediately preceding ARG)

• There are two VOLUME for convenience

  • VOLUME /var/log/postgresql/ allows easy access to the container's logs
  • VOLUME /docker-entrypoint-initdb.d/ allows customization of the database's initialization

  The latter will execute SQL or shell scripts in sorted order. That's normally handled by leading digits. It should be used with care since the docker image may already contain initialization scripts - if so they should be copied to the external location as well.

  Additional volumes can be added as desired. E.g., it's common for PG_DATA directory to be exported so the database is preserved across runs.

The images built are:

• pgxnclient
• pljava
• pljavadev

The first two docker images should be run as daemons.

The final docker image also builds an (unsigned) Debian binary package.

We could also build a RedHat/Fedora binary package but it would require duplicating the stack.

#
# Create image that adds the PostgreSQL extension development tools
# (pgxnclient and pgtap)
#
ARG PG_VERSION=16.0
ARG DEBIAN_RELEASE=bookworm
FROM postgres:${PG_VERSION}-${DEBIAN_RELEASE} AS pgxnclient

LABEL maintainer="Bear Giles <bgiles@coyotesong.com>"
LABEL org.opencontainers.image.authors="bgiles@coyotesong.com"
LABEL org.opencontainers.image.source="https://github.com/coyotesong/postgresql-pgxnclient-docker"
LABEL org.opencontainers.image.description="PostgreSQL with extension dev packages preinstalled (Debian)"

ARG PG_MAJOR=16

ENV DEBIAN_FRONTEND=noninteractive

EXPOSE 5432

VOLUME /var/log/postgresql/
VOLUME /docker-entrypoint-initdb.d/

# add apt source repository (note: error with /usr/bin/sed)
RUN sed -e 's/^deb /deb-src /' </etc/apt/sources.list.d/pgdg.list >/etc/apt/sources.list.d/pgdg-src.list

# DO NOT do a general update - that would also update the postgresql version
# Unfortunately that also means we can't pull debian pljava source later
# RUN /usr/bin/apt-get upgrade -y

# Update apt database then install pgtap, pgxnclient, plus a few packages
# useful for manual tasks later.
RUN /usr/bin/apt-get update && /usr/bin/apt-get install -y \
       pgxnclient \
       postgresql-${PG_MAJOR}-pgtap \
       apt-utils \
       whiptail

# Remove cached files, unnecessary packages, etc.
RUN /usr/bin/apt-get autoclean && /usr/bin/apt-get autoremove

#
# Second image - it adds pl/java
#
FROM pgxnclient as pljava

LABEL maintainer="Bear Giles <bgiles@coyotesong.com>"
LABEL org.opencontainers.image.authors="bgiles@coyotesong.com"
LABEL org.opencontainers.image.description="PostgreSQL with pl/java preinstalled (Debian)"
LABEL org.opencontainers.image.source="https://github.com/beargiles/postgresql-pljava-docker"

ARG PG_MAJOR=16

ENV DEBIAN_FRONTEND=noninteractive

# Install pljava and a few useful java packages
RUN /usr/bin/apt-get install -y \
    postgresql-${PG_MAJOR}-pljava \
    libpostgresql-jdbc-java \
    libsaxon-java \
    libsaxonb-java

# Remove cached files, unnecessary packages, etc.
RUN /usr/bin/apt-get autoclean && apt-get autoremove

# Create 'pljava' extension on launch
COPY docker-entrypoint-initdb.d/ /docker-entrypoint-initdb.d/
RUN /bin/cp /usr/share/postgresql/${PG_MAJOR}/pljava/pljava-examples-1.6.*.jar /tmp/pljava-examples-1.6.jar

RUN /bin/chmod 0755 /docker-entrypoint-initdb.d/0.1.pljava-vars.sh

#
# Third image - it creates and builds pljava extension (for local development)
#
FROM pljava as pljavadev

LABEL maintainer="Bear Giles <bgiles@coyotesong.com>"
LABEL org.opencontainers.image.authors="bgiles@coyotesong.com"
LABEL org.opencontainers.image.description="PostgreSQL with pl/java build environment preinstalled (Debian)"
LABEL org.opencontainers.image.source="https://github.com/beargiles/postgresql-pljava-docker"

ARG PLJAVA_VERSION=1.6.6
ARG PG_MAJOR=16

# ARG JAVA_VERSION=11
ARG JAVA_VERSION=17    # from inspecting upstream docker image

ENV DEBIAN_FRONTEND=noninteractive

# install git, java, C compiler, and libraries
RUN /usr/bin/apt-get update && \
    /usr/bin/apt-get install -y \
      debhelper-compat \
      default-jdk-headless \
      dpkg-dev \
      gcc \
      git \
      libecpg-dev \
      libkrb5-dev \
      maven \
      openjdk-${JAVA_VERSION}-jdk \
      postgresql-all \
      postgresql-server-dev-${PG_MAJOR}

# download source for pl/java
# the latter allows us to build a new deb package with the local changes
WORKDIR /usr/local/src

RUN /usr/bin/apt-get source postgresql-${PG_MAJOR}-pljava

WORKDIR /usr/local/src/postgresql-pljava-${PLJAVA_VERSION}

RUN /usr/bin/dpkg-buildpackage --build=binary --no-sign

[beargiles]

The current contents of /docker-entrypoint-initdb.d are

0.1.pljava-vars.sh

#!/bin/bash

set -e

# see https://tada.github.io/pljava/install/vmoptions.html
# remove first two items if you wish attach java debugger
psql -v ON_ERROR_STOP --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOL
  ALTER DATABASE $POSTGRES_DB SET pljava.vmoptions TO '-Xshare:auto -XX:+DisableAttachMechanism -Xms2m -XX:+UseSerialGC';
EOL

0.2.pljava-setup.sql

-- PL/Java psql installation script
-- See https://tada.github.io/pljava/install/install.html

-- this lets us verify successful installation
-- we should see 'NOTICE: PL/Java loaded'
SET client_min_messages TO NOTICE;

CREATE EXTENSION pljava;
GRANT USAGE ON LANGUAGE java TO public;

0.3.pljava-saxon.sql

--
-- Install saxon and saxonb jars
--
SET client_min_messages TO NOTICE;

--
-- Install saxon jars
--
SELECT sqlj.install_jar('file:///usr/share/java/saxon.jar', 'saxon', true);
SELECT sqlj.install_jar('file:///usr/share/java/saxon-jdom.jar', 'saxon_jdom', true);

--
-- Install saxonb jars
--
SELECT sqlj.install_jar('file:///usr/share/java/dom4j.jar', 'dom4j', true);
SELECT sqlj.install_jar('file:///usr/share/java/jaxen.jar', 'jaxen', true);
SELECT sqlj.install_jar('file:///usr/share/java/jdom1.jar', 'jdom1', true);
-- SELECT sqlj.install_jar('file:///usr/share/java/saxonb.jar', 'saxonb', true);
-- SELECT sqlj.install_jar('file:///usr/share/java/saxonb-ant.jar', 'saxonb_ant', true);
-- SELECT sqlj.install_jar('file:///usr/share/java/saxonb-dom.jar', 'saxonb_dom', true);
-- SELECT sqlj.install_jar('file:///usr/share/java/saxonb-dom4j.jar', 'saxonb_dom4j', true);
-- SELECT sqlj.install_jar('file:///usr/share/java/saxonb-jdom.jar', 'saxonb_jdom', true);
-- SELECT sqlj.install_jar('file:///usr/share/java/saxonb-spl.jar', 'saxonb_spl', true);
-- SELECT sqlj.install_jar('file:///usr/share/java/saxonb-xom.jar', 'saxonb_xom', true);
-- SELECT sqlj.install_jar('file:///usr/share/java/saxonb-xpath.jar', 'saxonb_xpath', true);
SELECT sqlj.install_jar('file:///usr/share/java/xercesImpl.jar', 'xercesImpl', true);
SELECT sqlj.install_jar('file:///usr/share/java/xml-apis-ext.jar', 'xml_apis_ext', true);
SELECT sqlj.install_jar('file:///usr/share/java/xml-commons-external.jar', 'xml_commons_external', true);
SELECT sqlj.install_jar('file:///usr/share/java/xmlParserAPIs.jar', 'xmlParserAPIs', true);
SELECT sqlj.install_jar('file:///usr/share/java/xml-resolver.jar', 'xml_resolver', true);
SELECT sqlj.install_jar('file:///usr/share/java/xom.jar', 'xom', true);

0.4.pljava-test.sqk

--
-- Verify pl/java installation by collecting information required for submission as prebuilt distribution
--
SET client_min_messages TO INFO;

--
-- Install 'examples' jar
--
SET check_function_bodies TO off;
SELECT sqlj.install_jar('file:///tmp/pljava-examples-1.6.jar', 'ex', true);
SET check_function_bodies TO on;

SELECT sqlj.set_classpath('javatest', 'saxon:saxon_jdom:ex');

--
-- perform simple query
--
SELECT array_agg(javatest.java_getsystemproperty(p))
  FROM (values('org.postgresql.pljava.version'),
              ('org.postgresql.version'),
              ('java.version'),
              ('os.name'),
              ('os.arch')) AS props(p);

--
-- do NOT remove jar - reinstallation is broken
--
--SELECT sqlj.remove_jar('ex', false);
--DROP SCHEMA javatest CASCADE;

SET client_min_messages TO NOTICE;

[beargiles]

Finally most of the work is handled by ansible playbooks run as a github action but they can also be run locally. I'm sure this can be converted a conventional build file but there are a lot of benefits for using ansible for this task.

I'll only include a fraction of the files here. The main thing to remember is that ansible uses {{ jinja }} variables set elsewhere.

tasks/actual-build-images.yml

# ------------------------------------------------------
#
# Build and tag local images.
#
# ------------------------------------------------------
#
# Requires:
#  - debian_release - Debian release (bookworm, bullseye)
#  - pg_version - full version of Postgres version
#  - pljava_version - pl/java version (optional)
#
# Provides:
#  - local docker image(s).
#
---
- name: 'Set facts ({{ pg_version }}-{{ debian_release }})'
  ansible.builtin.set_fact:
    pg_major: "{{ pg_version | regex_replace('^([0-9]+).*$', '\\1') }}"
    tag: '{{ pg_version }}-{{ debian_release }}'

- name: 'Build docker image {{ tag }}'
  community.docker.docker_image:
    name: '{{ build.name }}'
    build:
      path: ../docker
      target: '{{ build.target }}'
      args:
        DEBIAN_RELEASE: '{{ debian_release }}'
        POSTGRES_MAJOR: '{{ pg_major }}'
        POSTGRES_VERSION: '{{ pg_version }}'
        PLJAVA_VERSION: '{{ pljava_version | default(omit) }}'
    tag: '{{ tag }}'
    source: build
  loop: '{{ targets }}'
  loop_control:
    loop_var: build

tasks/actual-push-images.yml

This pushes the images to dockerhub but it would be just as easy to push to other repos. (github, AWS, others)

# ------------------------------------------------------
#
# Push local images to dockerhub
#
# ------------------------------------------------------
#
# Requires:
#  - debian_release
#  - pg_version - version to push
#  - pljava_version - pl/java version (optional)
#
# ------------------------------------------------------
#
# Push local images to docker hub
#
---

- name: Set version fact
  ansible.builtin.set_fact:
    pg_major: "{{ pg_version | regex_replace('^([0-9]+).*$', '\\1') }}"
    tag: '{{ pg_version }}-{{ debian_release }}'
    major_tag: '{{ pg_major }}-{{ debian_release }}'

- name: 'Push docker images for ({{ tag }})'
  community.docker.docker_image:
    name: '{{ push1.name }}:{{ tag }}'
    repository: '{{ push1.name }}:{{ tag }}'
    force_tag: true
    push: true
    source: local
  loop: '{{ targets }}'
  loop_control:
    loop_var: push1

- name: 'Push docker images for ({{ major_tag }})'
  community.docker.docker_image:
    name: '{{ push2.name }}:{{ major_tag }}'
    repository: '{{ push2.name }}:{{ major_tag }}'
    force_tag: true
    push: true
    source: local
  loop: '{{ targets }}'
  loop_control:
    loop_var: push2

vars/main.yml

This is a little more complex - the existing project planned to create new docker images for very released version of the database. It actually scanned the official repo to see if any new files had appeared. The seed_tags as an initial cache of previously downloaded versions.

This is the bit that was problematic, but that was mostly due to the delay between when the different packages were pushed and the difficulty determining the correct version of pljava. I noticed that the oldest releases used earlier versions of pl/java so I couldn't make assumptions.

Those concerns go away if the images are built as part of the release (or an ongoing github action) since the version of pljava will be known.

---

debian_releases:
  - bullseye
  - bookworm

debian_latest_release: bookworm

# Docker names (to ensure consistency)
postgres_name: library/postgres

# we can ignore these tags since the release is so old.
seed_tags:
  - '14'
  - '14.0'
  - '14.1'
  - '14.2'
  - '14.3'
  - '14.4'
  - '14.5'
  - '14.6'
  - '14.7'
  - '14.8'
  - '15'
  - '15.0'
  - '15.1'
  - '15.2'
  - '15.3'

[jcflack]

I wonder to what extent this might be covered soon given the work Álvaro is doing and the work David is doing (which seem as if they could fruitfully cross-pollinate).

For now, my most fervent hope is to influence the PGXNv2 design so that something like PL/Java ends up being something it can build, and then integrating with that.

[beargiles]

I've just created a pull request based on my prior local work that used the packages in the official postgresql repo. That made life a lot easier since it already includes a 'postgresql-pljava' package!

Alas that means that there's a small gap that I'll need some feedback on - the best way to handle the SQL scripts called after the extension has been created but before the schema etc are defined. This patch has what I've used in the past but I want to include the correct script from one of the other modules.

[beargiles]

Re: extending PGXNv2...

This is an unusual situation since we have two options:

• use a generic base image (postgres, alpine, fedora, etc.), add PXGN, then add and configure a PostgreSQL database

• use an official docker image from the PostgreSQL team. (https://hub.docker.com/_/postgres)

and then add the pljava bits.

The former has a ton of benefits, esp. if there are official PGXN base images available.

However the former also requires a LOT of extra work by us. Fortunately we can use the geerlingguy/postgresql Ansible Galaxy role to handle this and it has sane defaults. Or we could provide our own via some Ansible maps.

Hmm... if I were to take this approach the actual-build-image playbook would only need to add a few new steps:

• create docker image (existing)
• start a container
• execute the postgresql role
• stop the container
• create an image from the modified container

Bottom line - the current approach is more limited but it's Good Enough for now. The latter approach may be better if there are official PGXN base images.

[beargiles]

I've mentioned github actions in the past. Here's what my existing project (which had to guess the pljava version) has:

.github/workflows/main.yml

name: Docker PostgreSQL pl/java CI

on:
  push:
    branches:
      - 'main'
# schedule:
#   - cron: '15 5 * * *'

defaults:
  run:
    shell: bash

jobs:

  build-and-push-docker-images:
    name: Build and push docker images for PostgreSQL + extensions

    runs-on: ubuntu-24.04

    env:
      DOCKERHUB_USERNAME: ${{ secrets.DOCKERHUB_USERNAME }}
      DOCKERHUB_PASSWORD: ${{ secrets.DOCKERHUB_ACCESS_TOKEN }}

    steps:
      - name: Checkout source
        uses: actions/checkout@v3

      - name: Install ansible and docker sources
        run: /usr/bin/sudo /usr/bin/cp build-resources/apt/sources.list.d/* /etc/apt/sources.list.d

      - name: Run apt-update
        run: /usr/bin/sudo /usr/bin/apt-get update

      - name: Install ansible and docker-ce
        run: /usr/bin/sudo /usr/bin/apt-get install -y ansible ansible-core docker-ce docker-compose-plugin

      - name: Install ansible collections (does not require root)
        run: /usr/bin/ansible-galaxy install -r src/main/ansible/roles/requirements.yml

      - name: Build and push docker images
        run: /usr/bin/ansible-playbook src/main/ansible/playbook.yml

There are numerous ways to trigger the action. I've used both 'push' and regularly scheduled tasks (to poll for updates in a different repo) but there are many others.

Current implementation

The second through fourth blocks set up custom debian repos (to point to the upstream repos instead of often outdated release repo) and installs both ansible and docker. Ansible is a very widely used tool for Intrastructure as Code (IaC) like building custom docker images. They have to be run as root and I know some people would prefer to include that code in the github action for potentially improved security.

The fifth block installs the required ansible "collections". I think it's done automatically when you use the internal pljava role but doing this as a separate step makes it easier to track down problems.

The final block runs the ansible playbook to build and publish the docker images. I know many people would recommend doing this in separate steps - I remember doing this but have forgotten why I flipped back to a single playbook.

Potential implementation

There's at least three potential implementations for a solution similar to my recent PR.

• Install the java build environment, run mvn package, and push the docker images from the build system's docker cache to the remote repos

• Modify pljava-packaging so it calls docker save {{ name }} to a tar file that is then committed. I can't remember if docker lets you push a saved image but you could certainly docker restore {{ name }} and then push the image.

• Modify pljava-packaging so it creates a file with all of the required information to build the docker image. That's not a lot of information - basically just the skeleton, two Dockerfiles, and an ansible list containing the postgresql_version and pg_major values. (The rest are tied to whether the image is based on Debian or Alpine.)

The first approach is pretty heavy unless you're also pushing the jar artifacts to one or more Maven repos. I'm pretty sure I've also seen Maven packages for shared libraries but don't know the details. In this case your Github action can also send notifications that a new release has been pushed.

The second approach is the easiest but it also results in massive commits. When I run docker images I see that the first two (bookworm) images are around 500 MB and the third is nearly 4 GB. That's so large that you need to deal with github's support for large objects.

The final approach may be the cleanest since it eliminates the need install ansible and docker on local machines unless you have a specific need to build local docker images. In my PR the ansible code is only used to move the required resources into a common location and to determine the proper build properties. This could be implemented in java. The Dockerfiles assume everything they need is already present in that common location.

Another benefit to the last approach is that there is much better support for Dockerfiles in both maven and some libraries (e.g., TestContainers).

Next steps

The more I think about it the more I'm convinced that the third approach is worth a closer look. Ansible has a lot of benefits, esp. if you need to be able to have consistent behavior across multiple versions of applications, operating systems releases, etc., but at the moment we know nearly everything is locked down.

That may change with a PGXN base docker image but there's no point in worrying about that yet.

[beargiles]

I did a quick POC yesterday -- the third approach is definitely viable since it's easy to reduce the problem to "generate a set of CSV values that can be used when calling docker build at the command line or shell script."

Unfortunately pljava-packaging produces a pom artifact and it seems iffy whether it can produce a standalone application. At least IntelliJ seems skeptical given how much it fought my efforts.

The POC is currently a standalone class in pljava (main module) since it's overkill to create a new maven module for a single class...

... but adding a Maven plugin is a different story. It might start with generating docker images but it's easy to see how it could be used on other tasks. For instance I would expect everything pushed to the server to be in a separate maven module. The plugin can 'publish' both build artifact and dependencies to the database since plugins have access to the actual dependencies. Or it could push artifacts given only the maven coordinates.

The actual database connection could be problematic in a secured environment since it should use more than just username/password but I can think of a few possibilities. For instance the plugin may default to producing a SQL script and only attempt a database connection if credentials are provided.

The same thing applies to the original question of creating docker images. The default could be producing a script that can be checked in and run by a github action later without requiring a java environment. Set a property and the plugin creates the docker images itself using one of the existing libraries.

Bottom Line

No matter what approach we take it's clear that using ansible was a good solution for my earlier work since it relied on a debian package at the postgresql repo but it's overkill when we're creating the docker images in the build environment.

[jcflack]

The third option is definitely my favorite of the three.

... but adding a Maven plugin is a different story. It might start with generating docker images but it's easy to see how it could be used on other tasks.

Maybe it's something you could do with a scripted-goal for the pljava-pgxs-plugin.

[beargiles]

I've just pushed a large PR - it includes a small script to create a local docker image that has both PL/Java and extensions installed and configured.

See the unit test in the application module - it has NO knowledge of pljava, the 'examples' jarfile, etc. That's all handled by the docker image via the `/entrypoint-init.d mechanism.

The current approach is a little crude but I already know two different libraries that I can use in a small bit of code run during the package stage.

[beargiles]

I've written two new mojos for pljava-pgxn and will submit the PR after adding some tests. The mojos are identical except that one creates a potential 'official' image and the other adds content from the maven project. The latter should be added to pljava-examples.

FWIW I've debated using a single mojo but my concern is usability. I want it to do the 'right thing' if no parameters are provided and with two mojos it's easy to have one copy the maven module's build artifact while the other does not. With a single mojo one of these will require a flag in order to avoid adding content.

Overall implementation

The overall implementation is:

• create a 'working directory'
• create a Docker file based on mojo parameters and environment
• copy maven module build artifact (presumably the server-side jar)
• copy additional files from src/main/docker, if present
• build the docker image
• execute the docker image to verify that be can be launched

The dockerfile assumes that the FROM docker image is either provided by the official PostgreSQL org or independently provides a postgresql-pljava-$version Debian or Alpine package. This OS package handles installing the shared library, etc.

The working directory could be added to a maven build artifact if there's a desire to extend it.

Goals

Goal: createServerImage

This goal creates the 'naked' docker image. It creates the PLJava extension but nothing else.

Goal: createTestImage

This goal extends the prior logic to copy the maven build artifact to the docker image and adds an entrypoint-initdb.d script to load it ( SELECT * from sqlj.install_jar(...). It also copies (some) files from src/main/docker` if it exists.

(Note: I could copy everything in that directory but it's likely to be used in independent testing and may incorporate elements we don't want to include - I'll give this a little more thought since the directory is configurable.)

There are hooks for automatically adding additional jars in the script but I haven't decided on the best approach yet.

Potential goal: listPostgreSQLImages

This goal would query docker hub to retrieve a list of available docker images for the current architecture. It would allow the user to see what's available. Note: there is a small delay between when a new database release is pushed and when the additional package we need is pushed.

Potential future work

Dockerfile template

The implementation currently creates the Dockerfile using standard printf() statements. A template is much more flexible but adds a modest amount of extra testing and cognitive load for the developer.

Custom postgresql-pljava-$pgVersion-$pljavaVersion maven artifacts

The official PostgreSQL images are built using the current pljava release, or the most recent version that works with an older release, and there's no way to install an older pljava release in order for you match your local environment.

There's obviously no OS package for anything you're working on.

This could be handled by creating these maven artifacts. The docker image is built using the local Maven cache so there will be no problem with it using the anything built locally, and the older versions of pljava can be supported by pushing the maven artifact to the standard locations.