You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
In my upgrades from PostgreSQL 14.7 -> 15.2 and PostgreSQL 15.5 -> 16.1 on NixOS, I noticed that having pg_ivm in the database prevents pg_upgrade from succeeding. The upgrade succeeds only after dropping the extension from the old cluster.
The relevant part in the second log below is:
pg_restore: creating TABLE "pg_catalog.pg_ivm_immv"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 229; 1259 18065 TABLE pg_ivm_immv postgres
pg_restore: error: could not execute query: ERROR: permission denied to create "pg_catalog.pg_ivm_immv"
DETAIL: System catalog modifications are currently disallowed.
/var/lib/postgresql# pg_upgrade -d 15 -D 16 -b /nix/store/dln4b6ss9q6mhkgygwfri48f479f7wi8-postgresql-and-plugins-15.5/bin -B /nix/store/wzwsnhxxb91gw2bwr5w7w2g8nz4f4cff-postgresql-and-plugins-16.1/bin
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
ivan
*failure*
Consult the last few lines of "16/pg_upgrade_output.d/20231117T143344.919/log/pg_upgrade_dump_16584.log" for
the probable cause of the failure.
Failure, exiting
command: "/nix/store/wzwsnhxxb91gw2bwr5w7w2g8nz4f4cff-postgresql-and-plugins-16.1/bin/pg_dump" --host /var/lib/postgresql --port 50432 --username postgres --schema-only --quote-all-identifiers --binary-upgrade --format=custom --file="16/pg_upgrade_output.d/20231117T143344.919/dump/pg_upgrade_dump_16584.custom" 'dbname=ivan' >> "16/pg_upgrade_output.d/20231117T143344.919/log/pg_upgrade_dump_16584.log" 2>&1
command: "/nix/store/wzwsnhxxb91gw2bwr5w7w2g8nz4f4cff-postgresql-and-plugins-16.1/bin/pg_restore" --host /var/lib/postgresql --port 50432 --username postgres --create --exit-on-error --verbose --dbname template1 "16/pg_upgrade_output.d/20231117T143344.919/dump/pg_upgrade_dump_16584.custom" >> "16/pg_upgrade_output.d/20231117T143344.919/log/pg_upgrade_dump_16584.log" 2>&1
pg_restore: connecting to database for restore
pg_restore: creating DATABASE "ivan"
pg_restore: connecting to new database "ivan"
pg_restore: creating DATABASE PROPERTIES "ivan"
pg_restore: connecting to new database "ivan"
pg_restore: creating pg_largeobject "pg_largeobject"
pg_restore: creating EXTENSION "pg_ivm"
pg_restore: creating COMMENT "EXTENSION "pg_ivm""
pg_restore: creating SCHEMA "__pg_ivm__"
pg_restore: creating SCHEMA "ivan"
[...]
pg_restore: creating FUNCTION "pg_catalog.IVM_immediate_before()"
pg_restore: creating FUNCTION "pg_catalog.IVM_immediate_maintenance()"
pg_restore: creating FUNCTION "pg_catalog.IVM_prevent_immv_change()"
pg_restore: creating FUNCTION "pg_catalog.create_immv("text", "text")"
pg_restore: creating FUNCTION "pg_catalog.get_immv_def("regclass")"
pg_restore: creating FUNCTION "pg_catalog.ivm_visible_in_prestate("oid", "tid", "oid")"
pg_restore: creating FUNCTION "pg_catalog.refresh_immv("text", boolean)"
[...]
pg_restore: creating TABLE "pg_catalog.pg_ivm_immv"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 229; 1259 18065 TABLE pg_ivm_immv postgres
pg_restore: error: could not execute query: ERROR: permission denied to create "pg_catalog.pg_ivm_immv"
DETAIL: System catalog modifications are currently disallowed.
Command was:
-- For binary upgrade, must preserve pg_type oid
SELECT pg_catalog.binary_upgrade_set_next_pg_type_oid('18067'::pg_catalog.oid);
-- For binary upgrade, must preserve pg_type array oid
SELECT pg_catalog.binary_upgrade_set_next_array_pg_type_oid('18066'::pg_catalog.oid);
-- For binary upgrade, must preserve pg_class oids and relfilenodes
SELECT pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('18065'::pg_catalog.oid);
SELECT pg_catalog.binary_upgrade_set_next_heap_relfilenode('18065'::pg_catalog.oid);
SELECT pg_catalog.binary_upgrade_set_next_toast_pg_class_oid('18068'::pg_catalog.oid);
SELECT pg_catalog.binary_upgrade_set_next_toast_relfilenode('18068'::pg_catalog.oid);
SELECT pg_catalog.binary_upgrade_set_next_index_pg_class_oid('18069'::pg_catalog.oid);
SELECT pg_catalog.binary_upgrade_set_next_index_relfilenode('18069'::pg_catalog.oid);
CREATE TABLE "pg_catalog"."pg_ivm_immv" (
"immvrelid" "regclass" NOT NULL,
"viewdef" "text" NOT NULL,
"ispopulated" boolean NOT NULL
);
-- For binary upgrade, set heap's relfrozenxid and relminmxid
UPDATE pg_catalog.pg_class
SET relfrozenxid = '840', relminmxid = '1'
WHERE oid = '"pg_catalog"."pg_ivm_immv"'::pg_catalog.regclass;
-- For binary upgrade, set toast's relfrozenxid and relminmxid
UPDATE pg_catalog.pg_class
SET relfrozenxid = '840', relminmxid = '1'
WHERE oid = '18068';
-- For binary upgrade, handle extension membership the hard way
ALTER EXTENSION "pg_ivm" ADD TABLE "pg_catalog"."pg_ivm_immv";
The text was updated successfully, but these errors were encountered:
Creating the catalog table pg_ivm_immv in pg_catalog is useful in the sense that it can be easily accessed without considering search_path, but I've found some problem. In addition to the permission problem you reported, data in pg_catalog are not copied by pg_upgrade. Technically, we can allow pg_upgrade to create a table in pg_catalog, and provide some method to copy data at upgrading, but I might be better to place the catalog table in other schema in future.
Is the best way to continue the upgrade to drop the extension and then create it again after the upgrade? Or is there some modification we can make to the schemas that is safe for upgrading (e.g. drop the catalog table?)
In my upgrades from PostgreSQL 14.7 -> 15.2 and PostgreSQL 15.5 -> 16.1 on NixOS, I noticed that having pg_ivm in the database prevents
pg_upgrade
from succeeding. The upgrade succeeds only after dropping the extension from the old cluster.The relevant part in the second log below is:
The text was updated successfully, but these errors were encountered: