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 <dgrowle...@gmail.com> wrote: > On Mon, 28 Nov 2022 at 12:46, Venkata B Nagothi <nag1...@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 >
