3

I have been using Kettle to import data from a SQL Server to PostgreSQL on AWS RDS and when I was enabling all of my foreign key constraints I realized that data was missing, often in the even hundreds.

But checking the log of the Kettle job, it seems to be doing all read and writes correctly from a table of 1923 entries

2017/02/26 21:36:42 - Table output.5 - Finished processing (I=0, O=240, R=240, W=240, U=0, E=0)
2017/02/26 21:36:42 - Table output.3 - Finished processing (I=0, O=240, R=240, W=240, U=0, E=0)
2017/02/26 21:36:42 - Table output.7 - Finished processing (I=0, O=240, R=240, W=240, U=0, E=0)
2017/02/26 21:36:42 - Table output.6 - Finished processing (I=0, O=240, R=240, W=240, U=0, E=0)
2017/02/26 21:36:42 - Table output.1 - Finished processing (I=0, O=241, R=241, W=241, U=0, E=0)
2017/02/26 21:36:42 - Table output.2 - Finished processing (I=0, O=241, R=241, W=241, U=0, E=0)
2017/02/26 21:36:42 - Table output.4 - Finished processing (I=0, O=240, R=240, W=240, U=0, E=0)
2017/02/26 21:36:42 - Table output.0 - Finished processing (I=0, O=241, R=241, W=241, U=0, E=0)

But when running the query count on Postgres I get 1423 entries, and similar behavior on maybe 10% of all tables I have transferred.

As I don't have any prior experience in neither Kettle nor Postgres, what is it that can cause this issue?

Improve this question
4
  • Check the PostgreSQL logs?
    – Craig Ringer
    Commented Feb 27, 2017 at 6:52
  • I checked the ones i have on RDS, but nothing strange in those
    – Erik Karlsson
    Commented Feb 27, 2017 at 7:27
  • Could you try to disable "Use batch update for inserts" on your table output to test? It might be slower but sometimes reveals a better error message.
    – Tom V
    Commented Feb 27, 2017 at 12:18
  • Thank you both for taking your time craig-ringer and tom-v (can only mention one)!
    – Erik Karlsson
    Commented Feb 28, 2017 at 13:07

1 Answer 1

Reset to default
1

With the help of the author of the script i used (sqlserver2pgsql) we realized it was a (new) issue with how Kettle handled parallel output table truncates.

https://github.com/dalibo/sqlserver2pgsql/issues/57#issuecomment-282675275

What happened was, and the reason i only lacked even hundreds of data, that the output job in some cases did run truncate after one or more workers already had done inserts on the table

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.