[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: Postgres upgrade 12 - issues with OIDs
From:       Venkata B Nagothi <nag1010 () gmail ! com>
Date:       2023-01-30 8:54:11
Message-ID: CAEyp7J_zvGtjAQC7NXXe-2x_OhpMGjyTr=f0jka+ytD3ZH3pwg () mail ! gmail ! com
[Download RAW message or body]

Thanks all for the ideas, we have chosen to resolve this using Logical
Replication as we cannot use any other methods due to various constraints.

Regards,

Venkata B N
Database Consultant



On Mon, Nov 28, 2022 at 11:16 AM David Rowley <dgrowleyml@gmail.com> wrote:

> On Mon, 28 Nov 2022 at 12:46, Venkata B Nagothi <nag1010@gmail.com> wrote:
> > Coming back to this thread after a while.. we have to remove OID on a 6
> TB (5 TB of indexes) table and ALTER TABLE is gonna block the table and is
> gonna take hours...
>
> You may want to look into exploiting table inheritance for this.
> Something like:
>
> create table tab (a int, b int) with oids; -- the existing table
>
> begin; -- do make the following atomic
> alter table tab rename to old_tab;
> create table tab (a int, b int) without oids; -- new version of the
> table, without oids
> alter table old_tab inherit tab; -- make it so querying the new table
> also gets rows from the old table.
> commit;
>
> -- do this a bunch of times over the course of a few days until
> old_tab is empty.
> with del as (delete from old_tab where a in (select a from old_tab
> limit 1000) returning *) insert into tab select * from del;
>
> you can then drop the old table.
>
> You'll need to think carefully about unique constraints and any other
> constraints which are on the table in question. You'll want to do a
> lot of testing before committing to doing this too.
>
> David
>

[Attachment #3 (text/html)]

<div dir="ltr">Thanks all for the ideas, we have chosen to resolve this using Logical \
Replication as we cannot use any other methods due to various constraints.<br \
clear="all"><div><div dir="ltr" class="gmail_signature" \
data-smartmail="gmail_signature"><div dir="ltr"><div><div dir="ltr"><div \
dir="ltr"><div dir="ltr"><div \
dir="ltr"><div><br></div>Regards,<div><br></div><div>Venkata B N</div><div><span \
style="font-size:12.8px">Database Consultant</span></div><div>  \
</div></div></div></div></div></div></div></div></div><br></div><br><div \
class="gmail_quote"><div dir="ltr" class="gmail_attr">On Mon, Nov 28, 2022 at 11:16 \
AM David Rowley &lt;<a \
href="mailto:dgrowleyml@gmail.com">dgrowleyml@gmail.com</a>&gt; \
wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">On Mon, 28 Nov 2022 at \
12:46, Venkata B Nagothi &lt;<a href="mailto:nag1010@gmail.com" \
target="_blank">nag1010@gmail.com</a>&gt; wrote:<br> &gt; Coming back to this thread \
after a while.. we have to remove OID on a 6 TB (5 TB of indexes) table and ALTER \
TABLE is gonna block the table and is gonna take hours...<br> <br>
You may want to look into exploiting table inheritance for this.<br>
Something like:<br>
<br>
create table tab (a int, b int) with oids; -- the existing table<br>
<br>
begin; -- do make the following atomic<br>
alter table tab rename to old_tab;<br>
create table tab (a int, b int) without oids; -- new version of the<br>
table, without oids<br>
alter table old_tab inherit tab; -- make it so querying the new table<br>
also gets rows from the old table.<br>
commit;<br>
<br>
-- do this a bunch of times over the course of a few days until<br>
old_tab is empty.<br>
with del as (delete from old_tab where a in (select a from old_tab<br>
limit 1000) returning *) insert into tab select * from del;<br>
<br>
you can then drop the old table.<br>
<br>
You&#39;ll need to think carefully about unique constraints and any other<br>
constraints which are on the table in question. You&#39;ll want to do a<br>
lot of testing before committing to doing this too.<br>
<br>
David<br>
</blockquote></div>



[prev in list] [next in list] [prev in thread] [next in thread]