We use JDBC to export data into csv ,then copy that to Postgres. Multiple sessions working on multiple tables. If not set unlogged , how to make COPY run fast ? possible to start a transaction include all of these “truncate table xxx; copy table xxxx; create index on tables….” With wal_level=minimal, is it ok to make copy and create index without logging ?
James From: Jim Mlodgenski <[email protected]> Sent: Tuesday, July 26, 2022 8:53 PM To: James Pang (chaolpan) <[email protected]> Cc: Tom Lane <[email protected]>; [email protected] Subject: Re: alter table xxx set unlogged take long time On Tue, Jul 26, 2022 at 8:45 AM James Pang (chaolpan) <[email protected]<mailto:[email protected]>> 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 <[email protected]<mailto:[email protected]>> Sent: Tuesday, July 26, 2022 8:43 PM To: James Pang (chaolpan) <[email protected]<mailto:[email protected]>> Cc: Jim Mlodgenski <[email protected]<mailto:[email protected]>>; [email protected]<mailto:[email protected]> Subject: Re: alter table xxx set unlogged take long time "James Pang (chaolpan)" <[email protected]<mailto:[email protected]>> 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
