RE: alter table xxx set unlogged take long time

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.

Thank,

James

From: Jim Mlodgenski <jimmy76(at)gmail(dot)com>
Sent: Tuesday, July 26, 2022 8:21 PM
To: James Pang (chaolpan) <chaolpan(at)cisco(dot)com>
Cc: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: Re: alter table xxx set unlogged take long time

On Tue, Jul 26, 2022 at 4:53 AM James Pang (chaolpan) <chaolpan(at)cisco(dot)com<mailto:chaolpan(at)cisco(dot)com>> wrote:
Hi ,
We have PG v13.6 in RHEL8.4, we try to set table unlogged before load data. There are a lot of existing data in this table, when ‘alter table xxx set unlogged’, we found it take long time and spend time on IO datafileread. Is it expected?

Yes, the whole table needs to be written to WAL so this could take a long time for a large table