On Wednesday, November 22, 2023 at 12:38:54 PM EST, Achilleas Mantzios <a.mantzios@xxxxxxxxxxxxxxxxxxxx> wrote:
Στις 22/11/23 15:14, ο/η CG έγραψε:
On
Wednesday, November 22, 2023 at 01:20:18 AM EST, Achilleas
Mantzios <a.mantzios@xxxxxxxxxxxxxxxxxxxx> 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.
........Except where does it mention in the pg_repack docs (or source) that it is meant to be used for NO OIDS conversion ?But PostgreSQL still thinks that the table has oids:
mydata=# \d+ wipe_oid_testTable "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
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.
Just Dont!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.
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.
|
This is the same idea as the percona ETL strategy, and essentially 90% of what pg_repack already does (creates new tables, sets up triggers, locks the tables, and swaps new for old at the end of the process)
Why don't just inspect the code pg_repack ?
- 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/
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.
I have, and I have modified pg_repack (modification was shown in my first post) to create and write to a new table without oids, the problem is when the "swap" operation happens the old tabledefs with all the old oid baggage gets mapped on top of the new table that doesn't have oids in it. I need to know what PostgreSQL is seeing in the tabledefs that makes it think this new table, swapped out with the old table, has oids. My thought is if I correct those values in pg_class and elsewhere, the tabledefs will match what is actually on the filesystem after my modified pg_repack has finished processing the tables.
CG
-- Achilleas Mantzios IT DEV - HEAD IT DEPT Dynacom Tankers Mgmt
-- Achilleas Mantzios IT DEV - HEAD IT DEPT Dynacom Tankers Mgmt
