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.

Improve this question
3
  • 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
    Commented Dec 17, 2018 at 23:26
  • Instead of copying from prod db directly, I'd suggest using one of standby servers for that purpose.
    – a1ex07
    Commented Dec 18, 2018 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
    Commented Dec 18, 2018 at 18:43

1 Answer 1

Reset to default
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.

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.