Pgloader: Pgloader feedback

[jasonab]

This isn't a specific bug report, but feedback for the author based on my experience.

Let me start off with the end result: Pgloader transferred 1.6 million rows (and 350 gigs of data) in just about 5 hours (inside an Amazon VPC), which is amazing. This tool is basically the only way I could have possibly accomplished that feat.

Getting to that point was, unfortunately, a difficult journey.

I started out by installing the latest release using Brew on my local Mac. Unfortunately, that release has a bug that does not support MySQL views, and has not been updated in a while, so I had to build from HEAD. That build failed because of a few different issues related to the code and libraries (some of which are mentioned in other issues), so I reverted to using the Docker images.

Once I got the Docker images up and running (a hassle that is not the fault of pgloader), I had a lot of crashes and heap errors, which led to a lot of tweaking of the settings and switching between CCL and SBCL. Ultimately, I'm pretty sure the problem was that Pgloader tends to choke when the connections to the DBs are not symmetrical, and runs out of memory.

I also ran into the error where "-" in names breaks Pgloader, which forced me to modify my /etc/hosts file to get it to connect.

Once I finally moved my pgloader instance inside the VPC (and got it built on CentOS), things actually went fairly smoothly. It would have been nice for PgLoader to give more feedback about its progress, since the only thing I could do is check the PSQL table size, and make sure that Top looked clean. I really wasn't sure how long the process was going to take.

In the end, this is a great tool that could use some polish:

• Check that builds work on all platforms
• Stabilize the system
• Make more frequent releases when major fixes are made
• More feedback about rows transferred so far

Thanks again for a great tool!

[svantevonerichsen6906]

Thanks for the feedback!

Could you maybe extract specific issues that have a chance of pinning down a cause, fixing it, and closing the issue?

[meglio]

@jasonab may you please share your Dockerfile for this?

Once I finally moved my pgloader instance inside the VPC (and got it built on CentOS), things actually went fairly smoothly.

---

Regarding this:

I also ran into the error where "-" in names breaks Pgloader, which forced me to modify my /etc/hosts file to get it to connect.

My instance connects to host xxx.us-east-1.rds.amazonaws.com with no issues (notice the - in URL).

[meglio]

May you please be so kind and share your experience with this:

I had a lot of crashes and heap errors, which led to a lot of tweaking of the settings and switching between CCL and SBCL. Ultimately, I'm pretty sure the problem was that Pgloader tends to choke when the connections to the DBs are not symmetrical, and runs out of memory.

• What configuration eventually worked for you?
• What do you mean by having "symmetrical" connections?

[jasonab]

@jasonab may you please share your Dockerfile for this?

I just used the standard images that are available, nothing special.

My instance connects to host xxx.us-east-1.rds.amazonaws.com with no issues (notice the - in URL).

This is a bug that has since been fixed (https://github.com/dimitri/pgloader/issues/1082)

[jasonab]

* What configuration eventually worked for you?

I don't have access to my exact config right now, but the one that is listed on the documentation page works ok (https://pgloader.readthedocs.io/en/latest/ref/mysql.html). I tweaked it by adding more "concurrency" to make it process faster. I also noticed that using MySQL views (especially large ones) is a recipe for disaster, and shifted to the base tables, then did the data tweaking on the PostgreSQL side.

* What do you mean by having "symmetrical" connections?

Basically, I noticed that if I was "hosting" the migration on my laptop, using a VPN into AWS, the migration would fill up the heap and crash, while everything worked fine inside of the VPC. My hypothesis is that because the VPN connection is much faster coming down (from AWS) than going back up, that pgloader would tend to pull a ton of data down, then not be able to empty its heap fast enough.

[meglio]

@jasonab Thanks, this is very insightful as I'm getting ready to migrate a 500M+ rows / 50+ tables database.

So, both of your databases were managed, and you could not run pgload right on the destination database server to eliminate data roundtrip?

[jasonab]

So, both of your databases were managed, and you could not run pgload right on the destination database server to eliminate data roundtrip?

Yes, we have a managed MySQL instance, and a PostgreSQL RDS instance. I ended up "hosting" the migration on a small EC2 instance, and that worked just fine.

[dimitri]

Thanks for the feedback, happy to see that pgloader was useful for you. I will think about adding some back pressure mechanism to slow down reads if write can't keep up. In my experience though the problem is a little different than that, it's the size of the buffers we keep around before pushing to the Postgres instance that is a problem, so in many cases playing with the prefetch rows setting is pretty efficient.