Eliminating OIDs During PostgreSQL Upgrade from 9.4 to 12

postgresqlupgrade

I'm currently running PostgreSQL 9.4 in my production environment, but we're in the process of doing an OS upgrade on the server so I thought it might be time to upgrade our PostgreSQL version to the latest (12) so that I could potentially make use of some of the new features available. However, several of the tables in the various databases were built quite a while ago (as in, pre-2008, and quite possibly pre-2000 in at least some cases). These databases contain many tables using OIDs (WITH OIDS=TRUE in the definition).

Additionally, I've run a search through a majority of my code base to look for references to OID columns in any table and found a couple of instances where there are queries that explicitly call on the OID columns. Luckily, there aren't too many of these cases, and a majority of them are referencing system tables (i.e., SELECT oid FROM pg_namespace, ...FROM pg_class).

Running the pg_upgrade on the data dumped from our 9.4 database balks at this point in the process and flatly states that it won't continue until the database is free of OIDs. I understand that they've been slowly phasing these out over the past few releases, and that 12 has made them basically irrelevant, but the suggestion from pg_upgrade is to drop the OID columns entirely before proceeding. I consider that to be "overkill" for a mostly irrelevant column.

At this point – and I readily admit that I haven't thought this all the way through yet – I'm wondering if it's sufficient to simply "flip" the WITH OIDS switch in the affected table definitions to FALSE to be able to proceed with the upgrade? Will the existing OID columns remain a part of the database structure if this is flipped (I would think they would, but automated upgrades can do some funny things)?

I realize that, in an ideal situation, I would want to eventually eliminate OIDs from the database completely. However, being a one-man IT department, that's going to have to go "on the list" for later evaluation. For now, I'd simply like to get the database up-and-running on the latest PostgreSQL version while I have the opportunity to do so without immediately affecting operations.

Best Answer

If the oids of the tables are referenced in the code, you cannot simply get rid of them. Probably the old code relies on oid as an auto-generated identifier. Are there any primary keys or other indexes on these columns?

A way to preserve the oids over an upgrade would be:

  • Before upgrade, on the old database:

    ALTER TABLE has_oids ADD newoid bigint NOT NULL;
UPDATE has_oids SET newoid = oid;
ALTER TABLE has_oids SET WITHOUT OIDS;

  • After upgrade, on the new database:

    ALTER TABLE has_oids RENAME newoid TO oid;
CREATE SEQUENCE has_oids_oid_seq OWNED BY has_oids.oid;
ALTER TABLE has_oids ALTER oid SET DEFAULT nextval('has_oids_oid_seq');
SELECT setval('has_oids_oid_seq', ???);

Here, ??? is a number higher than the highest oid present in the table.

You'd also have to take care of indexes or constraints on the oid column.

Related Question