What is the recommend process to upgrade to new major PG version? #680
Description
Describe the bug
What is the recommend process to upgrade to new major PG version?
What is the recommend process to upgrade the mobility extension?
All extensions are install from the postgresql docker image and using the postgresql repository.
MobilityDB is install from the postgresql repository using package postgresql-16-mobilitydb and postgresql-17-mobilitydb.
My opensource project use PG16 and MobilityDB and trying to upgrade PG17.
However during the pg_upgrade process its failed.
pg_restore: creating OPERATOR "public.#>"
pg_restore: creating OPERATOR "public.#>"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 10309; 2617 25135 OPERATOR #> username
pg_restore: error: could not execute query: ERROR: commutator operator #<= is already the commutator of operator #>=
Command was: CREATE OPERATOR "public".#> (
FUNCTION = "public"."temporal_tgt",
LEFTARG = "public"."ttext",
RIGHTARG = "text",
COMMUTATOR = OPERATOR("public".#<=)
);
-- For binary upgrade, handle extension membership the hard way
ALTER EXTENSION "mobilitydb" ADD OPERATOR "public".#> ("public"."ttext", "text");
Specifications:
PG16 version:
# SELECT version();
version
---------------------------------------------------------------------------------------------------------------------------
PostgreSQL 16.9 (Debian 16.9-1.pgdg120+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
(1 row)
postgres=# SELECT postgis_full_version();
postgis_full_version
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
POSTGIS="3.5.3 aab5f55" [EXTENSION] PGSQL="160" GEOS="3.11.1-CAPI-1.17.1" PROJ="9.1.1 NETWORK_ENABLED=OFF URL_ENDPOINT=https://cdn.proj.org USER_WRITABLE_DIRECTORY=/var/lib/postgresql/.local/share/proj DATABASE_PATH=/usr/share/proj/proj.db" (compiled against PROJ 9.1.1) LIBXML="2.9.14" LIBJSON="0.16" LIBPROTOBUF="1.4.1" WAGYU="0.5.0 (Internal)"
(1 row)
postgres=# SELECT mobilitydb_full_version();
mobilitydb_full_version
-----------------------------------------------------------------------------------------------------------------------------------------
MobilityDB 1.2.0, PostgreSQL 16.4 (Debian 16.4-1.pgdg120+2), PostGIS 3.5.0, GEOS 3.11.1-CAPI-1.17.1, PROJ 9.1.1, JSON-C 0.16, GSL 2.7.1
(1 row)PG17 version:
# SELECT version();
version
---------------------------------------------------------------------------------------------------------------------------
PostgreSQL 17.5 (Debian 17.5-1.pgdg120+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
(1 row)
SELECT postgis_full_version();
postgis_full_version
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
POSTGIS="3.5.3 aab5f55" [EXTENSION] PGSQL="170" GEOS="3.11.1-CAPI-1.17.1" PROJ="9.1.1 NETWORK_ENABLED=OFF URL_ENDPOINT=https://cdn.proj.org USER_WRITABLE_DIRECTORY=/var/lib/postgresql/.local/share/proj DATABASE_PATH=/usr/share/proj/proj.db" (compiled against PROJ 9.1.1) LIBXML="2.9.14" LIBJSON="0.16" LIBPROTOBUF="1.4.1" WAGYU="0.5.0 (Internal)"
(1 row)
# SELECT mobilitydb_full_version();
mobilitydb_full_version
---------------------------------------------------------------------------------------------------------------------------------------------------------------
MobilityDB 1.2.0, PostgreSQL 17.0 (Debian 17.0-2.pgdg120+~20241004.1428.gb52007c), PostGIS 3.5.0, GEOS 3.11.1-CAPI-1.17.1, PROJ 9.1.1, JSON-C 0.16, GSL 2.7.1
(1 row)
Additional context