topic Inserting 1,000,000+ rows into PostgreSQL table on RDS in Talend Studio

Inserting 1,000,000+ rows into PostgreSQL table on RDS

Anonymous — Wed, 06 Jun 2018 16:55:04 GMT

I am trying to insert over 1 million rows into a PostgreSQL table on RDS using the tDBOutputBulkExec but get an error that indicates that I need to be a superuser. Is there another option within Talend to do this? I tried the tDBOutput, but with the number of rows that I'm dealing with, it appears that it will run too long.

Re: Inserting 1,000,000+ rows into PostgreSQL table on RDS

Anonymous — Fri, 08 Jun 2018 02:09:18 GMT

Hi JHohler,

With tPostgresOutput in the advanced setting of the component, you can increase the batch size (avoid commit downtime) and enable parallel insertion.

Alternatively, if your organisation allows it. You could

put your data into S3 bucket with Talend
do import from there using Data Pipeline (create a template into AWS)
then use Talend to kick off the schedule at the end of the copy to S3.

https://stackoverflow.com/questions/45090748/loading-data-from-s3-to-postgresql-rds

Re: Inserting 1,000,000+ rows into PostgreSQL table on RDS

Anonymous — Fri, 08 Jun 2018 15:23:34 GMT

Setting the batch size did the trick. I didn't see that option because it doesn't appear if you have your action on data set to 'Insert or Update' or 'Update or Insert'.

Thank You