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.
- 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.
CG
--
Achilleas Mantzios
IT DEV - HEAD
IT DEPT
Dynacom Tankers Mgmt
