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 1r7cxy-0062QT-MW for pgsql-general@arkaria.postgresql.org; Mon, 27 Nov 2023 14:52:06 +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 1r7cxx-00EFIz-Bp for pgsql-general@arkaria.postgresql.org; Mon, 27 Nov 2023 14:52:05 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r7cxw-00EFIl-Cb for pgsql-general@lists.postgresql.org; Mon, 27 Nov 2023 14:52:04 +0000 Received: from sonic302-2.consmr.mail.bf2.yahoo.com ([74.6.135.41]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1r7cxt-007cJb-1p for pgsql-general@lists.postgresql.org; Mon, 27 Nov 2023 14:52:03 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1701096718; bh=HQO+Sme5TZQ3Cvwe1ls8jowLv4QbK2FiVwFT1iUyISA=; h=Date:From:Reply-To:To:In-Reply-To:References:Subject:From:Subject:Reply-To; b=N65eQNqcRm8SUp9/k2JXI7Spxbp7B+AuPqru31J+mg4OypSYJVJ7eX7VEY7mCaF183qTImohvwl0zW7ER//wRfSGGwWq81+0FC42LCct8O7n9HZJ1MjsA4iZd7z64Wvbt2mlaBLSHxkSssssHDI8nc48pTSPptjiKTkMV8epMMRVtl9u1dGylh73jBmyJYFMfeyuFzFO79cwv49JfBXP6rjZcofjQlg9J09TgZPoR/qiKmQ41HasJw836Fbdrpsej2VgzqeDmGWVFsPWP4D1x5uJ0F/QNLwGBpKDtJ0ydD8ZZCJGxX+bzEmvDVCmljVcWaa4E5weGo29RMuIDUFhjg== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1701096718; bh=SCZce9xkIYQJxjy1A8Tl16/FC7H+8fxG+sXEE8fJxI4=; h=X-Sonic-MF:Date:From:To:Subject:From:Subject; b=eEpf+ToczaOfWLre4qGA0BHlQNkFw/u7beMgi/4GomRwDVWE+Cmrqd1Fap3oPxiTvo5Upim08OI4rvjCTRcNPlVyle6KHIVL5jnNyL+rEms4YY5J3LY1vgSczCYYTRTfYTuQuBs0roDUCS4liiRLpuOGvRHDzr+e1xVBLMZuk2toMmqGX5P0KgH+C8DIa+/hTNXIY7GALzBrAzUcKqZn3sWcW7PdIESQd61AgzXSU2dmEPfepi5nRAu4EdHdbFMJfcrSnVTGf/6LpN3ZKb1nHjatIviOLgkERicd5DHjJ/rI4t4bIT8RCORdbLBIr+6/6fvOdYJ+OAAwnS+17QrjGw== X-YMail-OSG: Bnm9CCMVM1mBMXMWimNR_81y.lYVwMvtAVeLyrfVDHW1FEt4BUj7Dt21uCYS56k 7nYUQxjug5MbKU6JhTtmduCohxF_K3Cgm9G.gubMNGJLI2_BB4yoGnkEiY2koYKkgMT9OthPSeca FPY10RzdWjJ2lDYC_L9m.0So6CHq2V.TImzRdrN9kIJXgQsnn7V0o5TA2a0eiz18DaRllURBK6tK .iRj5G.vgTdL09ld8YzYfdFMfUnBbrIlvrMAoI75Q4O6VVzxQGCFKfIyxFAv0yR3coim9LGLHbAt je2iaCLfn2UkL.2iacWbTAOhZis3slYWcxrnQc.8F7KAtVTn3G5.W7yjYznYj2UNECxYIQu696yf YgAr7.hViFpK5pyf46GN.1L.L9qUDQCeqkH8Hd3Rf6nefnvXtCugypxEAmkn.5c9AaIUa2Nwj3Km p7RA5bjEhP9dkL5AIhgJuxggqzX0njO6asm8JgqEgbzT.c73U8Msk5icmPBNlGdCWGFq278Plb0I TKv5JwcZOczZpB8qUlsVs8DKbi5TPWYnFZHnSBNbpmO1Ut6OnlR2w7DHLPSCfZxEGrqZhTDLt65V CHPSaw3G1.0FTK.A2cNW8poYzB5xVdGMNMJLE2XyB6HO0qZnzcwjn0qpMWXXNEhl0dGv7MR73Zvl MtEpkILIJbrTPrhy5YyOD5HjPl1yV4qktymrC46j6ZdMXDCEtX0BmXzjkRwqmlaHhh6.FnB815Sw qiCBKpo9Yt_6rBhr.PjoEYbeSeLFY3NHC6bmCGO3YiUnw73Tk.hJZ9J08WGNhr2Lm9crMArG5lyV i0HzDh3EyTqhwovZBzSD1pViHpzoO6_y3I2t.p98L.9P5_zx75RIRwCorGz.SH8Q5Xbl9Pt8iQV4 eIuzZZgBhQxzVYtavWKfduyari1N6k1L_J6aNLbPodLub4dt7myE_fKH3J3YfLPZW0sGBmnL4SP2 WpptKwyHIWHepLC9EjbpwRHEqeR6LUmWAecgF087ryEmofXBInZoT9P.ZTxOxG0ntEa8j4FsoZgj JpUbZLGfl926sec8V_CqHLuPR9RvFMC7ZqlfdKaDuvyk0a11zf.S3SVn5mocALQFzMncjtQF61aw tm_PWrUlY7l5gg4vOeZ4qaZu.k6NYrJlJ_kw2oOpJwLgLtLOUI.W8wA.UiIYXZx8NWHn2M9SqtGT Y2QhTqpncukEug9Kxu.cCzI.uiXHv0AX1955ewEN4YdK0AtrUe6bKxztfYW6o1ouEoRUEMovBMYH FNvgd6KBDh1E.q2jb9YvnqJoXYEh7IQdj2hqEln04DTx_uDTHuZbTaizzv7lC3ySUOyOrYAk96mZ zf2BBd4JlSsWtqqkoxxn7MNa8xrVvfEuPH2Qg.4DCwrgNFeqsklV2VVdaplOPNQjzTQtnOxxmqyX h3ke6lHwl6D.pjo6gwpJ.K54RRdA.vhE98hmS7.h3SFVctWpmGALjczvYeBKYlXJNXyQyeK2_U5l T7PPZ4etHITrkFftu_T8PFEEuHbcFcpHJKXrhdLkIBjACmkEiEXPCqlRxp1VmRLegkp3uIzsnPq6 UVZ2e_PmdI32tXHWWfyhhesBHYLbLm8vsJoEMvXvDMdDCawOzNgj5hvdQRZAH3w1OPgU42ki3gph Kzfkghl.odSwgryMUsdpnljM.nvD8UGhwNnmd.ATURGREaNhgEA0Ald.9tNomH1YVHEwynwZ0nFy ygN3KTvEsWrh3iIKv_jw.Vm7wSGd1AAN5WCoB_60OCVKKslDc1HLXHpbO3HIrFn1bREjiWJ07.v0 pdDr1KbaYn13jfdYSYbT4Jz0.Ym88_RD68ACes9OlcJ75FDwO56mcp5rOLVNs4jv9xnzuiW7kJp_ sR76t68yyFF7b7zZGXlZHXX6kf3u8EGOD3BBvoda9ouQWjIRB7xGNQk3Abk0cOiFCLh9tZPIemI1 C_ESU9yHnjDmF3.vwmnW9bxpWlGldyjnYTMfBFIXVTQEnN6eMonx2kBI8PR3q68Bf7peUiPVfUXy srzJxabbyOtnB5s7Xnun55vyttV4mLOCnXkmc6E3q3PUsc88PtQ2pGzRN2gvlcRBiqEZOiOt8WMI m7AoP8aG9yUuNlXgLXheX3_Q5oEDONvyVzjafmTM6RSGDW0C76W7wcbD4nqaVsffPHa4jwVQn9ld bIK8yhDETU_VjFS7pCwUyRZ149IJFsVMyvlcyGttQq7DoqiyYbTFToYkuxg3ZaAuatR_6xUzm5Oc IItxa.8_MHWhbMk9KyCC1WY4dBm4NF8L8sD_fPi4uDYvBqYJTOqjUpqQEHE1KJScP9PRvb6Xg9NJ j0j8bWBBMYXTACEiYu_DH29pdOk4HGldVixW_HuXXbE7JntZFIt0VAjuqB9948T07m5fDUdJMCAC musMZh2ZrYvTCQ4eq1.dUiP86JDJsONKOJoU2GZbFfh8uchXKnpx_E4GsVeHFeSrbSBMAwXN1HyE c9xVMMkzTkbtYsI066sbAOs2EyOY4U_2Wsp75TEYQkHhavx0WjfNp7SDNeAaXFTsVUdEE9al70B_ SV153iBu_RXXox08NPiorZLnUjS0SpCs3IdgymRvIsGpsZA-- X-Sonic-MF: X-Sonic-ID: 62ba7aec-f014-4afa-8e00-7ef18475e692 Received: from sonic.gate.mail.ne1.yahoo.com by sonic302.consmr.mail.bf2.yahoo.com with HTTP; Mon, 27 Nov 2023 14:51:58 +0000 Date: Mon, 27 Nov 2023 14:51:56 +0000 (UTC) From: CG Reply-To: CG To: "pgsql-general@lists.postgresql.org" , Achilleas Mantzios Message-ID: <270392623.4500442.1701096716538@mail.yahoo.com> In-Reply-To: <6d98a345-0c20-4c7c-9521-f8074ec9cca5@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> <1411824597.3692573.1700658864193@mail.yahoo.com> <6d98a345-0c20-4c7c-9521-f8074ec9cca5@cloud.gatewaynet.com> Subject: Re: Removing oids with pg_repack MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_4500441_1590342646.1701096716532" X-Mailer: WebService/1.1.21896 YMailNorrin Content-Length: 29919 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_4500441_1590342646.1701096716532 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable =20 On Wednesday, November 22, 2023 at 12:38:54 PM EST, Achilleas Mantzios = wrote: =20 =20 =CE=A3=CF=84=CE=B9=CF=82 22/11/23 15:14, =CE=BF/=CE=B7 CG =CE=AD=CE=B3=CF= =81=CE=B1=CF=88=CE=B5: =20 =20 =20 =20 On Wednesday, November 22, 2023 at 01:20:18 AM EST, Achilleas Mantzio= s wrote: =20 =20 =CE=A3=CF=84=CE=B9=CF=82 21/11/23 20:41, =CE=BF/=CE=B7 CG =CE=AD=CE=B3= =CF=81=CE=B1=CF=88=CE=B5: =20 =20 I have a very large PostgreSQL 9.5 database that still has very large t= ables 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 ha= d a wild idea to mod pg_repack to write a new table without oids. I think i= t almost works.=C2=A0=20 To test out my idea I made a new table wipe_oid_test with oids. I filled = it with a few rows of data. ........ =20 But PostgreSQL still thinks that the table has oids:=20 mydata=3D# \d+ wipe_oid_test =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0Table "public.wipe_oid_test" =C2=A0Column | Type= | Modifiers | Storage=C2=A0 | Stats target | Description=C2=A0 --------+--= ----+-----------+----------+--------------+------------- =C2=A0k=C2=A0 =C2= =A0 =C2=A0 | text | not null=C2=A0 | extended |=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0v=C2=A0 =C2=A0 =C2=A0 | text |=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| extended |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 |=C2=A0 Indexes: =C2=A0 =C2=A0 "wipe_oid_test_pkey" PRIMA= RY KEY, btree (k) Has OIDs: yes =20 Except where does it mention in the pg_repack docs (or source) that it is = meant to be used for NO OIDS conversion ?=20 It does not-- I was trying to leverage and tweak the base functionality o= f pg_repack which sets up triggers and migrates data. I figured if the targ= et table was created without OIDs that when pg_repack did the "swap" operat= ion that the new table would take over with the added bonus of not having o= ids.=20 =20 I can modify pg_class and set relhasoids =3D 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 upda= ting pg_class.=20 =20 Just Dont!=20 Noted. ;)=20 =20 So I'm definitely missing something. I really need a point in the right d= irection.... Please help! ;)=20 =20 =20 There are a few of methods to get rid of OIDs : - ALTER TABLE .. SET WITHOUT OIDS (just mentioning, you already checked th= at) =20 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 some= thing that we can't afford.=20 - Use table copy +=C2=A0 use of a trigger to log changes :=C2=A0https://d= ba.stackexchange.com/questions/259359/eliminating-oids-while-upgrading-post= gresql-from-9-4-to-12=20 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 val= ues after conversion. I simply want to eliminate them without the extraordi= nary downtime the database would experience during ALTER operations. S= orry I meant this one :=C2=A0Stripping OIDs from tables in preparation for = pg_upgrade |=20 |=20 |=20 | | | | | |=20 | |=20 Stripping OIDs from tables in preparation for pg_upgrade I have a postgres database in RDS, file size approaching 1TB. We started in= 2005, using ruby/activerecord/rails... | | | This is the same idea as the percona ETL strategy, and essentially 90% of w= hat pg_repack already does (creates new tables, sets up triggers, locks the= tables, and swaps new for old at the end of the process)=C2=A0 =20 =20 =20 - Use of Inheritance (the most neat solution I have seen, this is what I us= ed for a 2TB table conversion) : https://www.percona.com/blog/performing-et= l-using-inheritance-in-postgresql/ This is closest to the effect I was going for. pg_repack essentially cr= eates a second table and fills it with the data from the first table while = ensuring standard db operations against that table continue to function whi= le 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 i= s work I was hoping to avoid by leveraging 95% of the functionality of pg_r= epack while supplying my own 5% as the resulting table would not have oids = regardless of the source table's configuration.=20 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_re= pack, 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 t= he actual table structure.=C2=A0=20 I have the fallback position for the Percona ETL strategy. But I feel lik= e 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 assist= ance to tap it in the hole. =20 Why don't just inspect the code pg_repack ?=20 I have, and I have modified pg_repack (modification was shown in my first p= ost) to create and write to a new table without oids, the problem is when t= he "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 n= ew table, swapped out with the old table, has oids. My thought is if I corr= ect those values in pg_class and elsewhere, the tabledefs will match what i= s actually on the filesystem after my modified pg_repack has finished proce= ssing the tables. =20 =20 =20 =20 CG =20 =20 =20 =20 =20 --=20 Achilleas Mantzios IT DEV - HEAD IT DEPT Dynacom Tankers Mgmt =20 --=20 Achilleas Mantzios IT DEV - HEAD IT DEPT Dynacom Tankers Mgmt =20 ------=_Part_4500441_1590342646.1701096716532 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable


=20
=20
On Wednesday, November 22, 2023 at 12:38:54 PM EST, Ach= illeas Mantzios <a.mantzios@cloud.gatewaynet.com> wrote:


=CE=A3=CF=84=CE= =B9=CF=82 22/11/23 15:14, =CE=BF/=CE=B7 CG =CE=AD=CE=B3=CF=81=CE=B1=CF=88= =CE=B5:


On Wednesday, November 22, 2023 at 01:20:18 AM EST, Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com> wro= te:


=CE=A3=CF=84=CE=B9=CF=82 21/11/23 20:41, =CE=BF/=CE=B7 CG =CE=AD=CE=B3=CF=81=CE=B1= =CF=88=CE=B5:
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=3D# \d+ wipe_oid_test
            &nbs= p;      Table "public.wipe_oid_test"
 Column | Type | Modifiers | Storage = ; | Stats target | Description 
--------+------+-----------+----------+-------= -------+-------------
 k      | text | not null&= nbsp; | extended |              | 
 v      | text |  &nb= sp;        | 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 =3D 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 da= tabase 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.


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.
=20 Sorry I meant this one : Stripp= ing OIDs from tables in preparation for pg_upgrade

=