Re: alter table xxx set unlogged take long time

At Tue, 26 Jul 2022 12:41:07 +0000, "James Pang (chaolpan)" <chaolpan(at)cisco(dot)com> wrote in
> How to make it fast ? These are our steps about copy large data from Oracle to Postgres
>
> 1. Create table in Postgres 2. Extract data from Oracle to CSV 3. Alter table set xxx unlogged, 4. Run copy command into Postgres db 5. Alter table set xxx logged 6. Create index …
> Step 5 took long time ,especially for large tables.

As others pointed, the step5 inevitably requires WAL emittion. On the
other hand, there is a proposed patch [1]. It lets ALTER TABLE SET
LOGGED/UNLOGGED evade duping the whole target table and could reduce
the amount of WAL to be emitted (caused by the difference of
tuple-based WAL and per-page WAL) (in major cases).

Could you try it and see if it works for you in any extent?

regards.

[1] https://commitfest.postgresql.org/38/3461/

--
Kyotaro Horiguchi
NTT Open Source Software Center