upgrade to Postgres 12 from 11

[George Kowalski Sr]

We're trying a dry run of an upgrade from PG 11 to PG 12 for our xnat server with : 

/usr/pgsql-12/bin/pg_upgrade --old-datadir /var/lib/pgsql/11/data/ --new-datadir /var/lib/pgsql/12/data/ --old-bindir /usr/pgsql-11/bin/ --new-bindir /usr/pgsql-12/bin/ --check

 

and we're seeing : 

Performing Consistency Checks on Old Live Server

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 tables WITH OIDS fatal

Your installation contains tables declared WITH OIDS, which is not supported anymore. Consider removing the oid column using ALTER TABLE ... SET WITHOUT OIDS; A list of tables with the problem is in the file: tables_with_oids.txt

Failure, exiting [postgres ~]$ cat tables_with_oids.txt Database: xnat public.arc_archivespecification_history public.arc_archivespecification_notification_type public.arc_archivespecification_notification_type_meta_data public.arc_fieldspecification public.arc_archivespecification_notification_type_history public.arc_archivespecification_meta_data public.arc_archivespecification public.arc_fieldspecification_history public.arc_fieldspecification_meta_data public.arc_pathinfo public.arc_pathinfo_history public.arc_pipelinedata_meta_data public.arc_pathinfo_meta_data public.arc_pipelinedata_history

... 

Is this to be expected ? 

[Rick Herrick]

Yes, it’s expected. XNAT itself deals with the deprecation of OIDs without issue, but to migrate the database our devops team came up with the attached SQL script. If you run this script, you’ll get the removeOidsFromTables() function installed on the database. This is in the comments in the script file, but you can test the function with this call:

SELECT removeoidsfromtables FROM removeOidsFromTables() ORDER BY removeoidsfromtables;

Run the actual migration with this call:

SELECT removeoidsfromtables FROM removeOidsFromTables(true) ORDER BY removeoidsfromtables;

Rick Herrick

Senior Software Developer

rickh...@flywheel.io

He/him/his/hey you

Sign up to receive our monthly updates

------ Original Message ------

From "George Kowalski Sr" <george....@gmail.com>

To "xnat_discussion" <xnat_di...@googlegroups.com>

Date 11/8/23, 8:15:44 AM

Subject [XNAT Discussion] upgrade to Postgres 12 from 11

--
You received this message because you are subscribed to the Google Groups "xnat_discussion" group.
To unsubscribe from this group and stop receiving emails from it, send an email to xnat_discussi...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/xnat_discussion/17578ec5-304b-4be4-821d-eae197a778bcn%40googlegroups.com.

[George Kowalski Sr]

Thanks , we'll give it a try .