0

Is there a way to copy or export a large table from MySQL server to postgres without affecting servers performance.

I mean the table is very large and using mysqldump or any select query has bad consequences.

Overall my question is: if there is a way to get the copy of the production database or table without performance problems on production.

  • 1
    Is a consistent snapshot required? Is mysqldump causing "bad consequences" due to IO saturation? history length growth? or something else. If its IO saturation, using mysqldump ... | pv you could use the bandwidth limits of pv when piping mysqldump to file/postgresql server which limits the mysqldump rate. – danblack Dec 17 '18 at 23:26
  • Instead of copying from prod db directly, I'd suggest using one of standby servers for that purpose. – a1ex07 Dec 18 '18 at 16:30
  • @danblack no consistent snapshot is not required. I'll probably try pv or write some script to extract chunks of data. – Tornike Dec 18 '18 at 18:43
0

If you can ignore changes that might happen while the copy is taking place...

Use the PRIMARY KEY to walk through the source table, fetch chunks of 100-1000 rows, copy them. More on chunking, but adapt for just fetching, not deleting or updating.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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