Re: alter table xxx set unlogged take long time

On Tue, Jul 26, 2022 at 8:45 AM James Pang (chaolpan) <chaolpan@cisco.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@sss.pgh.pa.us>
> Sent: Tuesday, July 26, 2022 8:43 PM
> To: James Pang (chaolpan) <chaolpan@cisco.com>
> Cc: Jim Mlodgenski <jimmy76@gmail.com>;
> pgsql-performance@lists.postgresql.org
> Subject: Re: alter table xxx set unlogged take long time
>
> "James Pang (chaolpan)" <chaolpan@cisco.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
>