Migrating data from sql server to postgre

General
1

Hi Team,

i have tried migrating data from sql server to postgre. i have succeeded in it but could not load bulk loads like more than 4 million records data in 4 tables( each 1million).I ahve increased heap size too.

Could you suggest ANY ALTERBATIVES in migrating the data.

2

Use appache airflow 2 or 3. Node and node-red aren't suitable for this type of job.

3

Hi Allan,
But the use case is to do in node red

4
  • Break the whole migration per table and pages.
  • Export the whole db into .csv files of size N. Since we are going to read these files per line (streaming api), you wont use much memory during read operations, but some memory will still he used while creating the transaction with all inserts. So, the smaller the size N, the less likely memory issues you will have while creating transactions of size N. Don't break the transaction into size M, where M < N because you will make the process more complicated.
  • Read the .csv using streaming api instead of loading it as a whole in memory. If you buffer the whole file in memory, you will have to reduce the size of N because you will have N records in memory (file) + N records in the db transaction.
  • Once the whole file is processed and all records are in the db transaction, commit the transaction.
  • if it was successfull move the processed file to a different folder
  • repeat the above process untill no more files are available to be processed.

you can try processing many files using many node-red processes but you will need a lock service to lock the file that is being processed by any of the other node-red processes. This lock service can be abother node-red process.

5

And be prepared for the process to take a loooooong time.

6

This response timed out.