PostGIS breaks after soft upgrade.

[CHOWY]

Hi, I've been stuck with this problem for more than a week now, and I hope someone could help me out here.

I was running Postgresql 12 with PostGIS 3.1.1 extension. More than a week ago, I `accidentally` performed a PostGIS upgrade by running this query

`SELECT PostGIS_Extensions_Upgrade();` which returned "Upgrade completed, run SELECT postgis_full_version(); for details". 

Although it seemed the upgrade went on successfully, while I check with `SELECT postgis_full_version();` it returned `POSTGIS="3.2.0 c3e3cc0" [EXTENSION] PGSQL="120" GEOS="3.9.1-CAPI-1.14.2" PROJ="6.3.1" GDAL="GDAL 3.0.4, released 2020/01/28 GDAL_DATA not found" LIBXML="2.9.10" LIBJSON="0.13.1" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)" TOPOLOGY RASTER`. Note that GDAL_DATA is no longer found. 

This causes functions like ST_Transform breaks with error as following: `SQL Error [XX000]: ERROR: could not form projection (LWPROJ) from 'srid=4326' to 'srid=4269'`. 

So far I have tried reinstalled postgresql-12, postgresql-client-12, postgis, postgresql-12-postgis-3 and postgresql-12-postgis-3-scripts on my Ubuntu 20.04 system, to no avail. 

I tried downgrading to PostGIS to 3.1.4, which returned `POSTGIS="3.1.4 ded6c34" [EXTENSION] PGSQL="120" GEOS="3.9.1-CAPI-1.14.2" PROJ="7.2.1" GDAL="GDAL 3.3.2, released 2021/09/01" LIBXML="2.9.10" LIBJSON="0.13.1" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)" (core procs from "3.2.0 c3e3cc0" need upgrade) TOPOLOGY (topology procs from "3.2.0 c3e3cc0" need upgrade) RASTER (raster procs from "3.2.0 c3e3cc0" need upgrade)` when querying `SELECT postgis_full_version();`. There didn't seem any error but the ST_Transform query still gave the same error above. 

I am also tried to use the pg_upgrade function to upgrade the database to Postgresql 14. While running with the `--check` option, the results says "Clusters are compatible".  But when running the actual upgrade, it breaks at the "Creating dump of database schemas" of the `postgres` database. Dumps of other databases went through just fine. The "pg_upgrade_dump_13427.log" log shows the following.  

```
pg_dump: error: query was: SELECT classid, objid, refobjid FROM pg_depend WHERE refclassid = 'pg_extension'::regclass AND deptype = 'e' ORDER BY 3
command: "/usr/lib/postgresql/14/bin/pg_dump" --host /var/lib/postgresql --port 50432 --username postgres --schema-only --quote-all-identifiers --binary-upgrade --format=custom  --file="pg_upgrade_dump_13427.custom" 'dbname=postgres' >> "pg_upgrade_dump_13427.log" 2>&1
pg_dump: WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
pg_dump: error: query failed: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
pg_dump: error: query was: SELECT c.tableoid, c.oid, c.relname, (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(coalesce(c.relacl,pg_catalog.acldefault(CASE WHEN c.relkind = 'S' THEN 's' ELSE 'r' END::"char",c.relowner))) WITH ORDINALITY AS perm(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault(CASE WHEN c.relkind = 'S' THEN 's' ELSE 'r' END::"char",c.relowner))) AS init(init_acl) WHERE acl = init_acl)) as foo) AS relacl, (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault(CASE WHEN c.relkind = 'S' THEN 's' ELSE 'r' END::"char",c.relowner))) WITH ORDINALITY AS initp(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(c.relacl,pg_catalog.acldefault(CASE WHEN c.relkind = 'S' THEN 's' ELSE 'r' END::"char",c.relowner))) AS permp(orig_acl) WHERE acl = orig_acl)) as foo) as rrelacl, CASE WHEN privtype = 'e' THEN (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(pip.initprivs) WITH ORDINALITY AS initp(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(pg_catalog.acldefault(CASE WHEN c.relkind = 'S' THEN 's' ELSE 'r' END::"char",c.relowner)) AS privm(orig_acl) WHERE acl = orig_acl)) as foo) END AS initrelacl, CASE WHEN privtype = 'e' THEN (SELECT pg_catalog.array_agg(acl) FROM (SELECT acl, row_n FROM pg_catalog.unnest(pg_catalog.acldefault(CASE WHEN c.relkind = 'S' THEN 's' ELSE 'r' END::"char",c.relowner)) WITH ORDINALITY AS privp(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(pip.initprivs) AS initp(init_acl) WHERE acl = init_acl)) as foo) END as initrrelacl, c.relkind, c.relnamespace, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = c.relowner) AS rolname, c.relchecks, c.relhastriggers, c.relhasindex, c.relhasrules, 'f'::bool AS relhasoids, c.relrowsecurity, c.relforcerowsecurity, c.relfrozenxid, c.relminmxid, tc.oid AS toid, tc.relfrozenxid AS tfrozenxid, tc.relminmxid AS tminmxid, c.relpersistence, c.relispopulated, c.relreplident, c.relpages, am.amname, CASE WHEN c.relkind = 'f' THEN (SELECT ftserver FROM pg_catalog.pg_foreign_table WHERE ftrelid = c.oid) ELSE 0 END AS foreignserver, CASE WHEN c.reloftype <> 0 THEN c.reloftype::pg_catalog.regtype ELSE NULL END AS reloftype, d.refobjid AS owning_tab, d.refobjsubid AS owning_col, (SELECT spcname FROM pg_tablespace t WHERE t.oid = c.reltablespace) AS reltablespace, array_remove(array_remove(c.reloptions,'check_option=local'),'check_option=cascaded') AS reloptions, CASE WHEN 'check_option=local' = ANY (c.reloptions) THEN 'LOCAL'::text WHEN 'check_option=cascaded' = ANY (c.reloptions) THEN 'CASCADED'::text ELSE NULL END AS checkoption, tc.reloptions AS toast_reloptions, c.relkind = 'S' AND EXISTS (SELECT 1 FROM pg_depend WHERE classid = 'pg_class'::regclass AND objid = c.oid AND objsubid = 0 AND refclassid = 'pg_class'::regclass AND deptype = 'i') AS is_identity_sequence, EXISTS (SELECT 1 FROM pg_attribute at LEFT JOIN pg_init_privs pip ON (c.oid = pip.objoid AND pip.classoid = 'pg_class'::regclass AND pip.objsubid = at.attnum)WHERE at.attrelid = c.oid AND ((SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(coalesce(at.attacl,pg_catalog.acldefault('c',c.relowner))) WITH ORDINALITY AS perm(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('c',c.relowner))) AS init(init_acl) WHERE acl = init_acl)) as foo) IS NOT NULL OR (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('c',c.relowner))) WITH ORDINALITY AS initp(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(at.attacl,pg_catalog.acldefault('c',c.relowner))) AS permp(orig_acl) WHERE acl = orig_acl)) as foo) IS NOT NULL OR CASE WHEN privtype = 'e' THEN (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(pip.initprivs) WITH ORDINALITY AS initp(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(pg_catalog.acldefault('c',c.relowner)) AS privm(orig_acl) WHERE acl = orig_acl)) as foo) END IS NOT NULL OR CASE WHEN privtype = 'e' THEN (SELECT pg_catalog.array_agg(acl) FROM (SELECT acl, row_n FROM pg_catalog.unnest(pg_catalog.acldefault('c',c.relowner)) WITH ORDINALITY AS privp(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(pip.initprivs) AS initp(init_acl) WHERE acl = init_acl)) as foo) END IS NOT NULL))AS changed_acl, pg_get_partkeydef(c.oid) AS partkeydef, c.relispartition AS ispartition, pg_get_expr(c.relpartbound, c.oid) AS partbound FROM pg_class c LEFT JOIN pg_depend d ON (c.relkind = 'S' AND d.classid = c.tableoid AND d.objid = c.oid AND d.objsubid = 0 AND d.refclassid = c.tableoid AND d.deptype IN ('a', 'i')) LEFT JOIN pg_class tc ON (c.reltoastrelid = tc.oid AND c.relkind <> 'p') LEFT JOIN pg_am am ON (c.relam = am.oid) LEFT JOIN pg_init_privs pip ON (c.oid = pip.objoid AND pip.classoid = 'pg_class'::regclass AND pip.objsubid = 0) WHERE c.relkind in ('r', 'S', 'v', 'c', 'm', 'f', 'p') ORDER BY c.oid
command: "/usr/lib/postgresql/13/bin/pg_dump" --host /var/lib/postgresql --port 50432 --username postgres --schema-only --quote-all-identifiers --binary-upgrade --format=custom  --file="pg_upgrade_dump_13427.custom" 'dbname=postgres' >> "pg_upgrade_dump_13427.log" 2>&1
pg_dump: WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
pg_dump: error: query failed: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
pg_dump: error: query was: SELECT classid, objid, refobjid FROM pg_depend WHERE refclassid = 'pg_extension'::regclass AND deptype = 'e' ORDER BY 3
```
It seems the dependencies of postgres extensions are broken. How do I fix that? Can someone please help? 

Sincerely begging,
CHOWY

[CHOWY]

Hi, I am trying again to see if there is anyone who could help me here. 

As illustrated earlier, PostGIS broke after I performed the `SELECT PostGIS_Extensions_Upgrade();`. I tried to keep the system up to date and currently the `SELECT PostGIS_Full_Version();` returns `POSTGIS="3.2.1 5fae8e5" [EXTENSION] PGSQL="120" GEOS="3.10.1-CAPI-1.16.0" PROJ="6.3.1" GDAL="GDAL 3.0.4, released 2020/01/28 GDAL_DATA not found" LIBXML="2.9.10" LIBJSON="0.13.1" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)" RASTER`. 

Apart from the "GDAL_DATA not found" error message, the versions of the underlying libraries is also out of sync with the versions installed on the Ubuntu 20.04 OS, such that:

- `sudo apt-cache policy gdal-bin`: 3.4.0+dfsg-1~focal0 500

- `sudo apt-cache policy proj-bin`: 8.2.0-1~focal0 500

- `sudo apt-cache policy geos-bin`: 3.10.1-1~focal0 500

The OS versions are of later versions, yet not detected by PostGIS in Postgresql-12. 

If anyone know how I could fix this, please help. Thanks very much in advance. 

CHOW

[Chris]

I have the same issue, except SELECT PostGIS_Full_Version();  doesn't even return any mention of GDAL