Including Extensions (PostGIS)

Adding an extension to the container image

• Some common extensions are already bundled with pgautoupgrade.
• However, many extensions, such as PostGIS or TimescaleDB require additional work to be included in the upgrade.
• This example will build an extended pgautoupgrade image, including the PostGIS extension.
• A similar process could be followed to install any additional extension you may need (as long as a pre-built container image exists including the extension).

Example for Alpine based images

FROM docker.io/postgis/postgis:14-3.4-alpine as pg14
FROM docker.io/postgis/postgis:15-3.4-alpine as pg15
FROM docker.io/postgis/postgis:16-3.4-alpine as pg16
FROM docker.io/pgautoupgrade/pgautoupgrade:16-alpine as pgautoupgrade

# Original entrypoint overwritten in /usr/local/bin, so copy elsewhere
RUN cp /usr/local/bin/docker-entrypoint.sh /docker-entrypoint.sh

# Remove libs for Postgres < v14
RUN rm -rf \
    /usr/local-pg9.5 \
    /usr/local-pg9.6 \
    /usr/local-pg10 \
    /usr/local-pg11 \
    /usr/local-pg12 \
    /usr/local-pg13

# Copy in PostGIS libs / bins
COPY --from=pg16 /_pgis*.* /
COPY --from=pg16 /usr/lib /usr/lib

# Copy extensions for postgresql 16
COPY --from=pg16 /usr/local /usr/local
# Copy all extensions for postgresql 15
COPY --from=pg15 /usr/local/lib/postgresql /usr/local-pg15/lib/postgresql
# Copy all extensions for postgresql 14
COPY --from=pg14 /usr/local/lib/postgresql /usr/local-pg14/lib/postgresql

# Squash image to reduce size
FROM scratch
COPY --from=pgautoupgrade / /
ENV \
    PGTARGET=16 \
    PGDATA=/var/lib/postgresql/data
WORKDIR /var/lib/postgresql
ENTRYPOINT ["/docker-entrypoint.sh"]
CMD ["postgres"]

Note that the steps to remove versions of Postgres < v14 and to squash the image at the end are not mandatory and can be removed.

Example for Debian based images

# The official postgis/postgis image doesn't support ARM64, so we are using baosystems/postgis instead.
FROM ghcr.io/baosystems/postgis:14-3.5 AS pg14
FROM ghcr.io/baosystems/postgis:15-3.5 AS pg15
FROM ghcr.io/baosystems/postgis:16-3.5 AS pg16
FROM ghcr.io/baosystems/postgis:17-3.5 AS pg17
FROM docker.io/pgautoupgrade/pgautoupgrade:17-debian AS pgautoupgrade

# Copy in PostGIS libs / bins
RUN --mount=type=bind,from=pg17,source=/usr/lib,target=/mnt/pg17_lib \
    cp -rn /mnt/pg17_lib/* /usr/lib/

RUN --mount=type=bind,from=pg17,source=/etc/alternatives,target=/mnt/pg17_etc_alternatives \
    cp -rn /mnt/pg17_etc_alternatives/postgresql-17-* /etc/alternatives/ || true

# Copy extensions and binaries for postgresql 17
COPY --from=pg17 /usr/lib/postgresql/17 /usr/lib/postgresql/17
COPY --from=pg17 /usr/share/postgresql/17 /usr/share/postgresql/17
# Copy all extensions and binaries for postgresql 16
COPY --from=pg16 /usr/lib/postgresql/16 /usr/lib/postgresql/16
COPY --from=pg16 /usr/share/postgresql/16 /usr/share/postgresql/16
# Copy all extensions and binaries for postgresql 15
COPY --from=pg15 /usr/lib/postgresql/15 /usr/lib/postgresql/15
COPY --from=pg15 /usr/share/postgresql/15 /usr/share/postgresql/15
# Copy all extensions and binaries for postgresql 14
COPY --from=pg14 /usr/lib/postgresql/14 /usr/lib/postgresql/14
COPY --from=pg14 /usr/share/postgresql/14 /usr/share/postgresql/14

# Create symlinks for each version (except latest)
RUN rm -rf /usr/local-pg16 /usr/local-pg15 /usr/local-pg14 /usr/local-pg17 \
    && ln -s /usr/lib/postgresql/16 /usr/local-pg16 \
    && ln -s /usr/lib/postgresql/15 /usr/local-pg15 \
    && ln -s /usr/lib/postgresql/14 /usr/local-pg14

ENV \
    PGTARGET=17 \
    PGDATA=/var/lib/postgresql/data
WORKDIR /var/lib/postgresql
ENTRYPOINT ["/usr/local/bin/docker-entrypoint.sh"]
CMD ["postgres"]

Using the image

• Create a file named Dockerfile with the content above.
• Build the image: docker build . -t pgautoupgrade/pgautoupgrade:16-3.4-alpine (Postgres=16, PostGIS=3.4).
• Use the image name as a drop in replacement for the pgautoupgrade workflows already described in this repo.

Warning

Please ensure you are consistent with your usage of Alpine or Debian based images. The image above is built on Alpine, but could be swapped out for a Debian based image. Attempting to upgrade a database on one OS, using pgautoupgrade on another OS, may result in data corruption or issues due to differing C libraries (musl vs glibc). The issue will likely manifest as collation version conflicts.

Missing functionality from the postgres build

• The standard postgres binary may not have been compiled with all the functionality you need.
• An example is TimescaleDB, which by default builds Postgres with lz4 compression enabled (--with-lz4).
• A possible workaround is to bundle the compiled version of Postgres included in the containers for the extension, such as TimescaleDB, or compiling your own Postgres binaries with the required flags.