pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: Removing oids with pg_repack
Date: Wed, 22 Nov 2023 08:19:54 +0200
Message-ID: <164bcbcf-73a5-4c8c-9016-7a185584d96e@cloud.gatewaynet.com> (raw)
In-Reply-To: <1375662821.3508272.1700592084461@mail.yahoo.com>
References: <1375662821.3508272.1700592084461.ref@mail.yahoo.com>
	<1375662821.3508272.1700592084461@mail.yahoo.com>

Στις 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-...

- 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/


> CG
>
>
>
>
-- 
Achilleas Mantzios
  IT DEV - HEAD
  IT DEPT
  Dynacom Tankers Mgmt

view thread (8+ messages)  latest in thread

Message-ID: <164bcbcf-73a5-4c8c-9016-7a185584d96e@cloud.gatewaynet.com>
Permalink:  ../164bcbcf-73a5-4c8c-9016-7a185584d96e@cloud.gatewaynet.com/
Also on:    postgresql.org/message-id/164bcbcf-73a5-4c8c-9016-7a185584d96e@cloud.gatewaynet.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: a.mantzios@cloud.gatewaynet.com
  Subject: Re: Removing oids with pg_repack
  In-Reply-To: <164bcbcf-73a5-4c8c-9016-7a185584d96e@cloud.gatewaynet.com>
  References: <1375662821.3508272.1700592084461.ref@mail.yahoo.com> <1375662821.3508272.1700592084461@mail.yahoo.com> <164bcbcf-73a5-4c8c-9016-7a185584d96e@cloud.gatewaynet.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox