Re: Removing oids with pg_repack

[Achilleas Mantzios <a.mantzios@xxxxxxxxxxxxxxxxxxxx>]

Στις 21/11/23 20:41, ο/η CG έγραψε:

I have a very large PostgreSQL 9.5 database that still has very large tables with oids. I'm trying to get rid of the oids with as little downtime as possible so I can prep the database for upgrade past PostgreSQL 11. I had a wild idea to mod pg_repack to write a new table without oids. I think it almost works. 

To test out my idea I made a new table wipe_oid_test with oids. I filled it with a few rows of data.

........

But PostgreSQL still thinks that the table has oids:

mydata=# \d+ wipe_oid_test

                   Table "public.wipe_oid_test"

 Column | Type | Modifiers | Storage  | Stats target | Description 

--------+------+-----------+----------+--------------+-------------

 k      | text | not null  | extended |              | 

 v      | text |           | extended |              | 

Indexes:

    "wipe_oid_test_pkey" PRIMARY KEY, btree (k)

Has OIDs: yes

Except where does it mention in the pg_repack docs (or source) that it is meant to be used for NO OIDS conversion ?

I can modify pg_class and set relhasoids = false, but it isn't actually eliminating the oid column. `\d+` will report not report that it has oids, but the oid column is still present and returns the same result before updating pg_class.

Just Dont!

So I'm definitely missing something. I really need a point in the right direction.... Please help! ;)

There are a few of methods to get rid of OIDs :

- ALTER TABLE .. SET WITHOUT OIDS (just mentioning, you already checked that)

- Use table copy +  use of a trigger to log changes : https://dba.stackexchange.com/questions/259359/eliminating-oids-while-upgrading-postgresql-from-9-4-to-12

- Use of Inheritance (the most neat solution I have seen, this is what I used for a 2TB table conversion) : https://www.percona.com/blog/performing-etl-using-inheritance-in-postgresql/

CG

-- 
Achilleas Mantzios
 IT DEV - HEAD
 IT DEPT
 Dynacom Tankers Mgmt