Re: alter table xxx set unlogged take long time

On Tue, Jul 26, 2022 at 8:45 AM James Pang (chaolpan) <chaolpan(at)cisco(dot)com>
wrote:

> Without step 3 , copy data take long time. Use wal_level=minimal can
> help make COPY load data without logging ?
>
>
I assume that you're most concerned with the total time of moving the data
from the source database into the final table so you might get a big win by
not moving the data twice and directly load the table through a Foregin
Data Wrapper and avoid the csv export/import. Something like the oracle_fdw
might help here:
https://github.com/laurenz/oracle_fdw

-----Original Message-----
> From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> Sent: Tuesday, July 26, 2022 8:43 PM
> To: James Pang (chaolpan) <chaolpan(at)cisco(dot)com>
> Cc: Jim Mlodgenski <jimmy76(at)gmail(dot)com>;
> pgsql-performance(at)lists(dot)postgresql(dot)org
> Subject: Re: alter table xxx set unlogged take long time
>
> "James Pang (chaolpan)" <chaolpan(at)cisco(dot)com> writes:
> > 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 …
>
> The easy answer is to skip steps 3 and 5.
>
> regards, tom lane
>