Στις 22/11/23 15:14, ο/η CG έγραψε:
On Wednesday, November 22, 2023 at 01:20:18 AM EST, Achilleas Mantzios
<a.mantz...@cloud.gatewaynet.com> wrote:
Στις 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 ?
It does not-- I was trying to leverage and tweak the base
functionality of pg_repack which sets up triggers and migrates data. I
figured if the target table was created without OIDs that when
pg_repack did the "swap" operation that the new table would take over
with the added bonus of not having oids.
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!
Noted. ;)
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)
This makes the database unusable for hours and hours and hours because
it locks the table entirely while it performs the operation. That's
just something that we can't afford.
- 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
That SO is not quite the effect I'm going for. The poster of that SO
was using OIDS in their application and needed a solution to maintain
those values after conversion. I simply want to eliminate them without
the extraordinary downtime the database would experience during ALTER
operations.
Sorry I meant this one :
https://dba.stackexchange.com/questions/286453/stripping-oids-from-tables-in-preparation-for-pg-upgrade
- 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/
<https://www.percona.com/blog/performing-etl-using-inheritance-in-postgresql/>
This is closest to the effect I was going for. pg_repack essentially
creates a second table and fills it with the data from the first table
while ensuring standard db operations against that table continue to
function while the data is being moved from the old table to the new
table. The process outlined in the Percona ETL strategy has to be
repeated per-table, which is work I was hoping to avoid by leveraging
95% of the functionality of pg_repack while supplying my own 5% as the
resulting table would not have oids regardless of the source table's
configuration.
For my experiment, Table A did have oids. Table B (created by
pg_repack) did not (at least at creation). When the "swap" operation
happened in pg_repack, the metadata for Table A was assigned to Table
B. I'm just trying to figure out what metadata I need to change in the
system tables to reflect the actual table structure.
I have the fallback position for the Percona ETL strategy. But I feel
like I'm REALLY close with pg_repack and I just don't understand
enough about the system internals to nudge it to correctness and need
some expert assistance to tap it in the hole.
Why don't just inspect the code pg_repack ?
CG
--
Achilleas Mantzios
IT DEV - HEAD
IT DEPT
Dynacom Tankers Mgmt
--
Achilleas Mantzios
IT DEV - HEAD
IT DEPT
Dynacom Tankers Mgmt
