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).

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.

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.