From: | Venkata B Nagothi <nag1010(at)gmail(dot)com> |
---|---|
To: | David Rowley <dgrowleyml(at)gmail(dot)com> |
Cc: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Postgres upgrade 12 - issues with OIDs |
Date: | 2023-01-30 08:54:11 |
Message-ID: | CAEyp7J_zvGtjAQC7NXXe-2x_OhpMGjyTr=f0jka+ytD3ZH3pwg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)gmail(dot)com> wrote:
> On Mon, 28 Nov 2022 at 12:46, Venkata B Nagothi <nag1010(at)gmail(dot)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
>
From | Date | Subject | |
---|---|---|---|
Next Message | qihua wu | 2023-01-30 09:10:40 | Does statement_timeout apply to commit? |
Previous Message | Venkata B Nagothi | 2023-01-30 08:53:30 | Question on Logical Replication |