1

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 extension. I am trying 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".

postgres@ual:~$ /usr/lib/postgresql/13/bin/pg_upgrade   --old-datadir=/bnas/pgdata/postgresql/12/main   --new-datadir=/bnas/pgdata/postgresql/13/main   --old-bindir=/usr/lib/postgresql/12/bin   --new-bindir=/usr/lib/postgresql/13/bin   --old-options '-c config_file=/etc/postgresql/12/main/postgresql.conf'   --new-options '-c config_file=/etc/postgresql/13/main/postgresql.conf'   --check
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 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

*Clusters are compatible*

However, when running without the --check option to perform the actual update, it keeps failing at the "Creating dump of database schemas" of postgres database. Dumps of other databases went through just fine.


postgres@ual:~$ /usr/lib/postgresql/13/bin/pg_upgrade   --old-datadir=/bnas/pgdata/postgresql/12/main   --new-datadir=/bnas/pgdata/postgresql/13/main   --old-bindir=/usr/lib/postgresql/12/bin   --new-bindir=/usr/lib/postgresql/13/bin   --old-options '-c config_file=/etc/postgresql/12/main/postgresql.conf'   --new-options '-c config_file=/etc/postgresql/13/main/postgresql.conf'
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
Creating dump of global objects                             ok
Creating dump of database schemas
  postgres
*failure*

Consult the last few lines of "pg_upgrade_dump_13427.log" for
the probable cause of the failure.
Failure, exiting

Consulting the "pg_upgrade_dump_13427.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

I had previously updated the PostGIS extension following this guide and didn't encounter any error.

Has the PostGIS update broke the postgres database? Anyone had this experience before and what is the solution to this? Please help.

CHOWY
  • 41
  • 5
  • Look into the PostgreSQL log file. It looks very much like there is some data corruption. You'll have to get rid of that before you can upgrade. – Laurenz Albe Feb 11 '22 at 07:21
  • I certainly don't understand what this line is doing there "/usr/lib/postgresql/14/bin/pg_dump" Why would upgrading from 12 to 13 ever invoke something from 14? – jjanes Feb 11 '22 at 22:16
  • pg_dump's log says the server crashed. Look in the server's log, probably named 'pg_upgrade_server.log', to figure out why. – jjanes Feb 11 '22 at 22:24
  • @LaurenzAlbe Thanks, I checked the query. The exact same query where it crashed, could run without error on its own. Any advice on how to track down where the data is corrupted on `postgres` database? – CHOWY Feb 13 '22 at 02:38
  • @jjanes I tried and failed at upgrade of Postgresql-12 to Postgresql-14, then backed down to try the more conservative Postgresql-12 to Postgresql-13. Still failed at the same pg_dump of `postgres`. I have pasted the log [here](https://pastebin.com/jLCD6fPi). It is beyond me as I am a beginner at database administration. Please help shade some light. – CHOWY Feb 13 '22 at 03:26
  • OK, that explains part of it, the pg_upgrade_dump_13427.log contains the concatenation of two different attempts. Your pasted server log file looks like it is on the right trail, but what you show starts in the middle of the crash, you need to go back further, if there is any more. Having log_min_messages cranked up so high is also probably not helpful--it just generates more log to sift through. – jjanes Feb 13 '22 at 04:38
  • @jjanes, thank you. what would be a reasonable level of log_min_messages? I can set it there and retry the upgrade attempts to get better logs. – CHOWY Feb 14 '22 at 05:01
  • I think the default of "warning" would be fine for this.. – jjanes Feb 14 '22 at 15:22
  • @jjanes, I repeated the pg_upgrade process, and here are the logs: [pg_upgrade_server.log](https://pastebin.com/ePDpmheE), [pg_upgrade_dump_13427.log](https://pastebin.com/YTsU4hYh) and [tail_postgresql-12-2022-02-16_190344.log](https://pastebin.com/KP8ePVct). Hope these are helpful to debug my issues. – CHOWY Feb 16 '22 at 11:40
  • I am facing similar issues. What is the locations of pg_upgrade_dump and pg_upgrade_internal log files? @CHOWY – darecoder Jun 07 '22 at 07:21

1 Answers1

0

The following text is from : https://www.postgresql.org/docs/current/release-14.html

Specifically, array_append(), array_prepend(), array_cat(), array_position(), array_positions(), array_remove(), array_replace(), and width_bucket() used to take anyarray arguments but now take anycompatiblearray. Therefore, user-defined objects like aggregates and operators that reference those array function signatures must be dropped before upgrading, and recreated once the upgrade completes.

Postgres 12 has few inbuilt functions and in later versions the parameter signature for these functions were changed. You need to drop these functions (if referred in your source i.e. postgres 12 database) right before you start the pg_upgrade operation.

I faced similar issue while upgrading from 12.8 to 14.3. Dropping these inbuilt functions before triggering the upgrade fixed it.

darecoder
  • 1,478
  • 2
  • 14
  • 29