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


アトラシアン コミュニティをご利用ください。


プラットフォームについて: サーバーと Data Center のみ。この記事は、サーバーおよび Data Center プラットフォームのアトラシアン製品にのみ適用されます。


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. 


データベースの変更を行う場合は必ず事前にバックアップを取得してください。可能な場合は、まずステージング サーバーで SQL コマンドの変更、挿入、更新、または削除を行うようにします。

To identify and remove OIDs in your database:

  1. データベースをバックアップします。 

  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,
    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. 


    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.


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


Powered by Confluence and Scroll Viewport.