Re: Removing oids with pg_repack - Mailing list pgsql-general

From Achilleas Mantzios
Subject Re: Removing oids with pg_repack
Date
Msg-id 164bcbcf-73a5-4c8c-9016-7a185584d96e@cloud.gatewaynet.com
Whole thread Raw
In response to Removing oids with pg_repack  (CG <cgg007@yahoo.com>)
Responses Re: Removing oids with pg_repack
List pgsql-general
Στις 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

pgsql-general by date:

Previous
From: "Johnson, Bruce E - (bjohnson)"
Date:
Subject: Connection fails on one system in a address range allowed to connect
Next
From: Inzamam Shafiq
Date:
Subject: Re: Connection fails on one system in a address range allowed to connect