How to check for unique object identifiers (OIDs) before upgrading to PostgreSQL 12 or later
Platform Notice: Server and Data Center Only. This article only applies to Atlassian products on the server and data center platforms.
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.
Alwaysyour 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:
Back up your database.
- 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.
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.
Once you have completed this process for all tables that contain OIDs, you can proceed with your database upgrade.
- Upgrading the PostgreSQL DB engine for Amazon RDS
- Why you should make your PostgreSQL tables without OIDs