Re: Removing oids with pg_repack

Στις 22/11/23 15:14, ο/η CG έγραψε:
>
>
> On Wednesday, November 22, 2023 at 01:20:18 AM EST, Achilleas Mantzios
> <a(dot)mantzios(at)cloud(dot)gatewaynet(dot)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