One Star

How to insert data to Amazon RDS(PostgreSQL) from local flat file?

I  would like to insert large number of rows from flat file on my local drive to AWS RDS database (PostgreSQL).
I used tFileInput and tPostgresqlOutputBulkExec but the latter component gave me following errors.
Is there any component that can insert data into AWS RDS and doesn't require superuser priviledge (You can't have it in RDS) ?
------------------------------------------------------------------------------
Exception in component tPostgresqlOutputBulkExec_1_tPBE
org.postgresql.util.PSQLException: ERROR: must be superuser to COPY to or from a file
4 REPLIES
Four Stars

Re: How to insert data to Amazon RDS(PostgreSQL) from local flat file?

How many number of rows you have in a source file, can you insert using tPostgresqlOutput component?
Vaibhav
One Star

Re: How to insert data to Amazon RDS(PostgreSQL) from local flat file?

Thank you for the suggestion. It's abount 20,000,000 rows (2.5GB) in total, devided into equally sized 5 files. I had thought it wasn't an option to insert this much of rows.
But anyway, I tried it with one file and found out that although it takes an hour or two for all the data at hand, it can work. I still prefer bulk copy without superuser ( According to Amazon documentation "\copy" command instead of "copy" command ), insert can be an alternative if I run the job over night.
Four Stars

Re: How to insert data to Amazon RDS(PostgreSQL) from local flat file?

You can even think of optimizing the record insert by playing with memory settings, commit size, batch size etc...
Vaibhav
One Star

Re: How to insert data to Amazon RDS(PostgreSQL) from local flat file?

In the end, it turns out that row insert into Amazon RDS(MySQL) using specialized to AWS compornent is an option for over night process (about 1500-2000 lines / second) but insert into Amazon RDS (PostgreSQL) is not (60 lines / second ).
I decided to leave the talend just with fixed to delimited file conversion and use pgadminIII to do /copy.