Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r5gal-00EVWN-MK for pgsql-general@arkaria.postgresql.org; Wed, 22 Nov 2023 06:20:07 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1r5gaj-0058C6-Vk for pgsql-general@arkaria.postgresql.org; Wed, 22 Nov 2023 06:20:05 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r5gaj-0058Bx-Je for pgsql-general@lists.postgresql.org; Wed, 22 Nov 2023 06:20:05 +0000 Received: from cloud.gatewaynet.com ([185.90.37.94]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r5gac-007jUj-A8 for pgsql-general@lists.postgresql.org; Wed, 22 Nov 2023 06:20:04 +0000 Content-Type: multipart/alternative; boundary="------------CiKIZH3xs1xLh31z3ZlJBu0B" Message-ID: <164bcbcf-73a5-4c8c-9016-7a185584d96e@cloud.gatewaynet.com> Date: Wed, 22 Nov 2023 08:19:54 +0200 MIME-Version: 1.0 Subject: Re: Removing oids with pg_repack Content-Language: en-US To: pgsql-general@lists.postgresql.org References: <1375662821.3508272.1700592084461.ref@mail.yahoo.com> <1375662821.3508272.1700592084461@mail.yahoo.com> From: Achilleas Mantzios In-Reply-To: <1375662821.3508272.1700592084461@mail.yahoo.com> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------CiKIZH3xs1xLh31z3ZlJBu0B Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Στις 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-to-12 - 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 --------------CiKIZH3xs1xLh31z3ZlJBu0B Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
Στις 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-to-12

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