Στις 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/
--
Achilleas Mantzios IT DEV - HEAD IT DEPT Dynacom Tankers Mgmt