Four Stars

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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Six Stars

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

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

 

2 REPLIES
Six Stars

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

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

 

Four Stars

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

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