From: | David Rowley <dgrowleyml(at)gmail(dot)com> |
---|---|
To: | Venkata B Nagothi <nag1010(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: | 2022-11-28 00:16:29 |
Message-ID: | CAApHDvq9morQa05adc1SYdWrDvUU-e9UAjYW=dF7L7r4Fcv0wg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 | li jie | 2022-11-28 03:27:03 | Re: Support logical replication of DDLs |
Previous Message | Venkata B Nagothi | 2022-11-27 23:46:01 | Re: Postgres upgrade 12 - issues with OIDs |