how to upgrade from 15 to 16

[realies]

is there a process which would work for upgrading the appdata from v15 to v16?

repos like https://github.com/tianon/docker-postgres-upgrade do not seem to help even after installing postgis inside,

command: "/usr/lib/postgresql/16/bin/pg_dump" --host /var/lib/postgresql --port 50432 --username postgres --schema-only --quote-all-identifiers --binary-upgrade --format=custom  --file="/var/lib/postgresql/16/data/pg_upgrade_output.d/20240222T215008.060/dump/pg_upgrade_dump_5.custom" 'dbname=postgres' >> "/var/lib/postgresql/16/data/pg_upgrade_output.d/20240222T215008.060/log/pg_upgrade_dump_5.log" 2>&1
WARNING:  database "postgres" has a collation version mismatch
DETAIL:  The database was created using collation version 2.31, but the operating system provides version 2.36.
HINT:  Rebuild all objects in this database that use the default collation and run ALTER DATABASE postgres REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
pg_dump: error: query failed: ERROR:  could not access file "$libdir/postgis-3": No such file or directory
pg_dump: detail: Query was: SELECT
a.attrelid,
a.attnum,
a.attname,
a.attstattarget,
a.attstorage,
t.typstorage,
a.attnotnull,
a.atthasdef,
a.attisdropped,
a.attlen,
a.attalign,
a.attislocal,
pg_catalog.format_type(t.oid, a.atttypmod) AS atttypname,
array_to_string(a.attoptions, ', ') AS attoptions,
CASE WHEN a.attcollation <> t.typcollation THEN a.attcollation ELSE 0 END AS attcollation,
pg_catalog.array_to_string(ARRAY(SELECT pg_catalog.quote_ident(option_name) || ' ' || pg_catalog.quote_literal(option_value) FROM pg_catalog.pg_options_to_table(attfdwoptions) ORDER BY option_name), E',
    ') AS attfdwoptions,
a.attcompression AS attcompression,
a.attidentity,
CASE WHEN a.atthasmissing AND NOT a.attisdropped THEN a.attmissingval ELSE null END AS attmissingval,
a.attgenerated
FROM unnest('{18318,18720,18856,19054,19066,19232,19239,19248,19254,19260,19271,19278,19285,19292,19299,19302,19307,19313,19328,19345,19359,19364,19370,19385,19400,19416,19431,19441,19450,19465,19474,19482,19489,19510,19522,19534,19564,19574,19584}'::pg_catalog.oid[]) AS src(tbloid)
JOIN pg_catalog.pg_attribute a ON (src.tbloid = a.attrelid) LEFT JOIN pg_catalog.pg_type t ON (a.atttypid = t.oid)
WHERE a.attnum > 0::pg_catalog.int2
ORDER BY a.attrelid, a.attnum

[ImreSamu]

Hello @realies ,

repos like https://github.com/tianon/docker-postgres-upgrade do not seem to help even after installing postgis inside,

I adapted the Dockerfile from
https://github.com/tianon/docker-postgres-upgrade/blob/master/15-to-16/ for PostGIS, and my minimal test worked.

It's important to always have a backup
and not to practice on the live data you're planning to upgrade initially.

I've also attached a minimal test upgrade (adapted from https://github.com/tianon/docker-postgres-upgrade ) ; I hope it's useful.

other related infos:

• https://github.com/tianon/docker-postgres-upgrade/blob/master/README.md
• https://postgis.net/workshops/postgis-intro/upgrades.html#pg-upgrade
• https://postgis.net/docs/PostGIS_Extensions_Upgrade.html
• https://www.postgresql.org/docs/16/pgupgrade.html

Dockerfile

( adapted from https://github.com/tianon/docker-postgres-upgrade/blob/master/15-to-16/Dockerfile )

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"]

adapted - minimal test

mkdir upgrade-test
cd upgrade-test

# create the adapted  Dockerfile ..
# create docker-upgrade from https://github.com/tianon/docker-postgres-upgrade/blob/master/15-to-16/docker-upgrade
# chmod +x  docker-upgrade

# build the adapted upgrade image
docker pull postgis/postgis:16-3.4
docker build -t  postgis-upgrade15-to-16 .


#  create old-pg15 data
docker pull postgis/postgis:15-3.4
docker run -d \
	--name postgis15-upgrade-testing \
	-e POSTGRES_PASSWORD=password \
	-v "$PWD/15/data":/var/lib/postgresql/data \
	postgis/postgis:15-3.4

sleep 5
docker logs postgis15-upgrade-testing

# let's get some testing data in there
docker exec -it \
	-u postgres \
	postgis15-upgrade-testing \
	pgbench -i -s 10

# create a geometry table
wget https://postgis.net/extra/test-data/countries.sql.bz2
bzcat countries.sql.bz2 | docker exec -i -u postgres postgis15-upgrade-testing psql

# check ...
docker exec -i -u postgres postgis15-upgrade-testing psql -c "\dt+"
docker exec -i -u postgres postgis15-upgrade-testing psql -c "\d+ countries"

# stop 
docker stop postgis15-upgrade-testing
docker rm   postgis15-upgrade-testing


# run the upgrade with --link
docker run --rm \
	-v "$PWD":/var/lib/postgresql \
	postgis-upgrade15-to-16 --link

# pull and start the new postgis service
docker pull postgis/postgis:16-3.4
docker run -dit \
	--name postgis16-3.4-upgrade-testing \
	-e POSTGRES_PASSWORD=password \
	-v "$PWD/16/data":/var/lib/postgresql/data \
	postgis/postgis:16-3.4

sleep 5
docker logs --tail 100 postgis16-3.4-upgrade-testing


# upgrade postgis
docker exec -i -u postgres postgis16-3.4-upgrade-testing psql -c "SELECT postgis_extensions_upgrade();"
docker exec -i -u postgres postgis16-3.4-upgrade-testing psql -c "SELECT postgis_full_version();"
docker exec -i -u postgres postgis16-3.4-upgrade-testing psql -c "\dx"

# vacuumdb ..
docker exec -i -u postgres postgis16-3.4-upgrade-testing vacuumdb --all --analyze-in-stages

# check ..
docker exec -i -u postgres postgis16-3.4-upgrade-testing psql -c "\dt+"
docker exec -i -u postgres postgis16-3.4-upgrade-testing psql -c "\d+ countries"

log ...

# docker run --rm \
> -v "$PWD":/var/lib/postgresql \
> postgis-upgrade15-to-16 --link
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/16/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 ... 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.
ok


Success. You can now start the database server using:

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

Performing Consistency Checks
-----------------------------
Checking cluster versions                                     ok
Checking database user is the install user                    ok
Checking database connection settings                         ok
Checking for prepared transactions                            ok
Checking for system-defined composite types in user tables    ok
Checking for reg* data types in user tables                   ok
Checking for contrib/isn with bigint-passing mismatch         ok
Checking for incompatible "aclitem" data type in user tables  ok
Creating dump of global objects                               ok
Creating dump of database schemas                             ok
Checking for presence of required libraries                   ok
Checking database user is the install user                    ok
Checking for prepared transactions                            ok
Checking for new cluster tablespace directories               ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Setting locale and encoding for new cluster                   ok
Analyzing all rows in the new cluster                         ok
Freezing all rows in the new cluster                          ok
Deleting files from new pg_xact                               ok
Copying old pg_xact to new server                             ok
Setting oldest XID for new cluster                            ok
Setting next transaction ID and epoch for new cluster         ok
Deleting files from new pg_multixact/offsets                  ok
Copying old pg_multixact/offsets to new server                ok
Deleting files from new pg_multixact/members                  ok
Copying old pg_multixact/members to new server                ok
Setting next multixact ID and offset for new cluster          ok
Resetting WAL archives                                        ok
Setting frozenxid and minmxid counters in new cluster         ok
Restoring global objects in the new cluster                   ok
Restoring database schemas in the new cluster                 ok
Adding ".old" suffix to old global/pg_control                 ok

If you want to start the old cluster, you will need to remove
the ".old" suffix from /var/lib/postgresql/15/data/global/pg_control.old.
Because "link" mode was used, the old cluster cannot be safely
started once the new cluster has been started.
Linking user relation files                                   ok
Setting next OID for new cluster                              ok
Sync data directory to disk                                   ok
Creating script to delete old cluster                         ok
Checking for extension updates                                notice

Your installation contains extensions that should be updated
with the ALTER EXTENSION command.  The file
    update_extensions.sql
when executed by psql by the database superuser will update
these extensions.

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade.
Once you start the new server, consider running:
    /usr/lib/postgresql/16/bin/vacuumdb --all --analyze-in-stages
Running this script will delete the old cluster's data files:
    ./delete_old_cluster.sh

# 

[realies]

Thanks for the alternative approach, it works great!