Upgrade postgresql with postgis extension

[bellini666]

I'm trying to upgrade a database that has postgis installed in it. Since the container doing the upgrade doesn't have postgis installed in it it gives this error:

pg_dump: error: query failed: ERROR:  could not access file "$libdir/postgis-3": No such file or directory

[SuperSandro2000]

Install postgis in the container. You can create a Dockerfile which depends on the right container and built that.

[bellini666]

Yeah, I ended up doing that! This is the dockerfile I used, with another extension that I use there too.

FROM tianon/postgres-upgrade:12-to-13

RUN apt-get update
RUN apt-get install -y \
  postgresql-12-postgis-3 \
  postgresql-12-postgis-3-scripts \
  postgresql-12-first-last-agg \
  postgresql-13-postgis-3 \
  postgresql-13-postgis-3-scripts \
  postgresql-13-first-last-agg \
  libproj-dev \
  gdal-bin

Maybe it would be nice to have this documented somewhere. Or maybe it is something so obvious that shouldn't need any doc?

[tianon]

I wouldn't say that it's "so obvious", but also this repository isn't actually meant to be an end-all solution. The point is that performing this upgrade automatically requires both instances of PostgreSQL (including any necessary/used extensions) installed simultaneously in order to use the PostgreSQL-native upgrade functionality, which is definitely really complicated. 😞

Given the documentation we have is already pretty complex (and difficult to follow IMO), I'm not sure where we could add this that wouldn't just lead to more confusion instead of less. 😬

[theAkito]

Fully agree with @tianon here. The upgrade process itself is already not very straight-forward. Then add Docker and all its implications on top of it. Additionally, Docker is often used with an Orchestrator. Another level of arduousness on top of that.

Now, when this gets even more features for special cases, it would get even more complicated. It's probably best to keep this project as a basic (haha) upgrade tool, just for upgrading plain Postgres to Postgres.
Users with higher demands could make a version of this tool with the features others usually wouldn't need. This way the users have a choice, everyone is happy and the normal user with normal demands does not have to deal with potential issues arising from advanced features.

[ImreSamu]

(2024-feb)
I adapted the tianon/docker-postgres-upgrade solution and test example to the postgis/postgis Debian images.

This resolves the confusing warning resulting from the bullseye vs bookworm discrepancy, as the tianon/docker-postgres-upgrade is already on bookworm, but postgis/postgis is still on bullseye. (WARNING: database 'postgres' has a collation version mismatch.)

Essentially, only the Dockerfile needed to small modification: (15-to-16 example)

• The base image should be the new postgis image (FROM postgis/postgis:16-3.4)
  • Later, we can expect both postgis/postgis:16-3.4-bookworm and postgis/postgis:16-3.4-bullseye versions, meaning that here we will need to set the targeted new version accordingly.
• And the extra postgis image also needs to be installed (postgresql-15-postgis-3).

FROM postgis/postgis:16-3.4

RUN sed -i 's/$/ 15/' /etc/apt/sources.list.d/pgdg.list

RUN set -eux; \
	apt-get update; \
	apt-get install -y --no-install-recommends \
		postgresql-15 \
                postgresql-15-postgis-3 \
	; \
	rm -rf /var/lib/apt/lists/*

ENV PGBINOLD /usr/lib/postgresql/15/bin
ENV PGBINNEW /usr/lib/postgresql/16/bin

ENV PGDATAOLD /var/lib/postgresql/15/data
ENV PGDATANEW /var/lib/postgresql/16/data

RUN set -eux; \
	mkdir -p "$PGDATAOLD" "$PGDATANEW"; \
	chown -R postgres:postgres /var/lib/postgresql

WORKDIR /var/lib/postgresql

COPY docker-upgrade /usr/local/bin/

ENTRYPOINT ["docker-upgrade"]

# recommended: --link
CMD ["pg_upgrade"]

See more for the adapted test example:

• how to upgrade from 15 to 16 postgis/docker-postgis#377 (comment)
• and don't forget the final SELECT postgis_extensions_upgrade();