ImreSamu
commented
7 months ago 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
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,