Thread: pg_upgrade and generated column
Hi,
O/S is
Rocky Linux release 9.5
Command is
nohup time /usr/pgsql-16/bin/pg_upgrade -d /pgdata/pgsql/15/data -D /pgdata/pgsql/16/data -b /usr/pgsql-15/bin -B /usr/pgsql-16/bin --retain 2>&1&
and
pg_upgrade fails with error:
...
pg_restore: from TOC entry 226; 1259 17061 TABLE m_access_cert_campaign iamappadmin
pg_restore: error: could not execute query: ERROR: column "objecttype" in child table must not be a generated column
Command was:
-- For binary upgrade, must preserve pg_type oid
SELECT pg_catalog.binary_upgrade_set_next_pg_type_oid('17063'::pg_catalog.oid);
...
I can find some descriptions of the problems from internet,
but there are no instructions or recommendations on postgres.org,
what is the "correct" way to upgrade a db if it has a "generated column".
Some suggestion pls. Can i use pg_upgrade at all?
br
Kaido
O/S is
Rocky Linux release 9.5
Command is
nohup time /usr/pgsql-16/bin/pg_upgrade -d /pgdata/pgsql/15/data -D /pgdata/pgsql/16/data -b /usr/pgsql-15/bin -B /usr/pgsql-16/bin --retain 2>&1&
and
pg_upgrade fails with error:
...
pg_restore: from TOC entry 226; 1259 17061 TABLE m_access_cert_campaign iamappadmin
pg_restore: error: could not execute query: ERROR: column "objecttype" in child table must not be a generated column
Command was:
-- For binary upgrade, must preserve pg_type oid
SELECT pg_catalog.binary_upgrade_set_next_pg_type_oid('17063'::pg_catalog.oid);
...
I can find some descriptions of the problems from internet,
but there are no instructions or recommendations on postgres.org,
what is the "correct" way to upgrade a db if it has a "generated column".
Some suggestion pls. Can i use pg_upgrade at all?
br
Kaido
Hi,
Why are you trying to retain? What does --check gives?
Thanks,
Imran
On Mon, Mar 3, 2025, 4:50 PM kaido vaikla <kaido.vaikla@gmail.com> wrote:
Hi,
O/S is
Rocky Linux release 9.5
Command is
nohup time /usr/pgsql-16/bin/pg_upgrade -d /pgdata/pgsql/15/data -D /pgdata/pgsql/16/data -b /usr/pgsql-15/bin -B /usr/pgsql-16/bin --retain 2>&1&
and
pg_upgrade fails with error:
...
pg_restore: from TOC entry 226; 1259 17061 TABLE m_access_cert_campaign iamappadmin
pg_restore: error: could not execute query: ERROR: column "objecttype" in child table must not be a generated column
Command was:
-- For binary upgrade, must preserve pg_type oid
SELECT pg_catalog.binary_upgrade_set_next_pg_type_oid('17063'::pg_catalog.oid);
...
I can find some descriptions of the problems from internet,
but there are no instructions or recommendations on postgres.org,
what is the "correct" way to upgrade a db if it has a "generated column".
Some suggestion pls. Can i use pg_upgrade at all?
br
Kaido
On Monday, March 3, 2025, kaido vaikla <kaido.vaikla@gmail.com> wrote:
...
pg_restore: from TOC entry 226; 1259 17061 TABLE m_access_cert_campaign iamappadmin
pg_restore: error: could not execute query: ERROR: column "objecttype" in child table must not be a generated column
Command was:
-- For binary upgrade, must preserve pg_type oid
SELECT pg_catalog.binary_upgrade_set_next_pg_type_oid('17063'::pg_ catalog.oid);
...
I can find some descriptions of the problems from internet,
but there are no instructions or recommendations on postgres.org,
what is the "correct" way to upgrade a db if it has a "generated column".
Some suggestion pls. Can i use pg_upgrade at all?
The release notes indicate that the parent and child must agree on whether a column is generated or not. To upgrade to v16 you must ensure your tartitioned table follows the new rule in v15 first.
David J.