How to check for unique object identifiers (OIDs) before upgrading to PostgreSQL 12 or later

Still need help?

The Atlassian Community is here for you.

Ask the community

Platform Notice: Server and Data Center Only. This article only applies to Atlassian products on the server and data center platforms.

Purpose

In earlier versions of Postgres, each row was given a Unique Object Identifier (OID) regardless of whether you defined any primary keys. PostgreSQL 12 removed the special behavior of OID columns. For more information, see the PostgreSQL 12 release notes

If your Confluence instance previously ran on PostgreSQL 8.x or earlier, it's possible that some of your tables were created with these OIDs. Before upgrading your database we recommend you check your database tables for OIDs, and remove them if present. This is a relatively straightforward process, but as with any database altering activity, we strongly recommend you back up your database and test your changes in a staging environment first. 

 Solution

Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.

To identify and remove OIDs in your database:

  1. Back up your database. 
     

  2. Use the following query to check if OIDs are present in your database.


    SELECT count(*) FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n, pg_catalog.pg_attribute a
    WHERE c.oid = a.attrelid
    AND NOT a.attisdropped
    AND a.atttypid IN ('pg_catalog.regproc'::pg_catalog.regtype,
                       'pg_catalog.regprocedure'::pg_catalog.regtype,
                       'pg_catalog.regoper'::pg_catalog.regtype,
                       'pg_catalog.regoperator'::pg_catalog.regtype,
                       'pg_catalog.regconfig'::pg_catalog.regtype,
                       'pg_catalog.regdictionary'::pg_catalog.regtype)
    AND c.relnamespace = n.oid
    AND n.nspname NOT IN ('pg_catalog', 'information_schema');

    This query will return the names of tables that contain OIDs. 

  3. Use the following query to alter any tables that contain OIDs. Replace <table_name> with the table name. 
     

    ALTER TABLE <table_name> SET WITHOUT OIDS;

    This process can take anywhere from seconds to hours depending on factors such as the number of rows, and the CPU power of your database server. We recommend you try this on a staging environment first. 
     

  4. Once you have completed this process for all tables that contain OIDs, you can proceed with your database upgrade.

More information



DescriptionHow to check for unique object identifiers (OIDs) before upgrading your databse to PostgreSQL 12 or later
ProductConfluence
Last modified on Mar 20, 2022

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.