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