Pgloader: A High-Speed PostgreSQL Swiss Army Knife, Written in Lisp | Hacker News Reader

Pgloader: A High-Speed PostgreSQL Swiss Army Knife, Written in Lisp

pgloader.io

133 pointskesor12 comments

A few years back I had some experience using this tool to migrate a MySQL database to a PostgreSQL. As far as I remember it was quite pleasant.

I had the some trouble with MySQL accepting invalid dates (like 0000-00-00) while Postgres rejects such dates. However, there was an option to convert such dates to NULL which saved the day.

Also you have to manually rewrite your views. Since we had a lot and also very complicated views I ended up materializing each MySQL view, manually porting it to Postgres and then comparing the result. That worked quite well.

Are you me?

In 2013 I was migrating a mysql database to postgres, for a tech demo as I'm a bit of a fan of postgres.

What followed was pgloader spitting out millions of errors, I assumed I had used the tool wrong or it was broken; but the more I looked at it the more I realised that we'd been suffering silent data corruption for years- and postgresql wouldn't validate the invalid data.

Oh, and the datetime thing, that was annoying. Fun things used to happen with mysql when you had a NOT NULL constraint but didn't specify a default.

This is from the same person that wrote The Art of PostgreSQL.

I’m working through the book now and a lot of chapters say something like “Download this public MySQL dataset from the internet and convert it into Postgres using pgloader”. The whole time I was wondering why the author didn’t just find public Postgres datasets but it makes a lot of sense now

I liked this tool. Just be sure that if you want to do stuff like transformations on the data that you are migrating that if you don't already know a dialect of lisp that you're going to have to pick that up real fast.

Might be viable if you have time to do that but if not strongly consider writing your own migration scripts in python or whatever you already know.

I had a great experience with pg_loader in 2014 but I had to use SBCL to make it work reliably. I'm curious if someone can inform my experience there -- I'm not a regular CLisper, I just followed some advice I found on a forum.

I wanted to use it as well on windows the other day. Because I wanted to migrate a mssql database to PostgreSQL. However AFAIR there were no builds I could find and from what I read in the issues it is quite discouraging, a pity

You have two options in this case, tacking on an mssql or general db connect library is asking a lot of a tool made by one guy in lisp.

First is writing your own tool with the connector libraries working fine on windows sql server in the lang of your choice which is great if you have to do transformation on the data for whatever reason while you are importing - the second is dumping from mssql to csv, then loading it in, which is honestly the KISS way to go as long as that is all you need to do.

Best of luck!

I've just loaded pgloader successfully on 64-bit Windows into SBCL 2.1.8 straight from Quicklisp. The only snag was that I needed sybdb.dll (which I got from https://github.com/ramiro/freetds/releases), and that in turn required OpenSSL (which I got from https://www.npcglib.org/~stathis/blog/precompiled-openssl/). Then, with sysdb.dll on the path, it loaded correctly. Haven't tried how it works, though. I don't even have an MSSQL installation, so there's that.

The best way I have found to work with the tool is the docker image. This brings up an entirely new issue of docker on windows and bridging localhost access, so replacing one problem with another.

Use the Docker image, here's a tutorial written by someone I helped migrate MySQL to Postgres entirely in Docker (because they also had troubles with running it locally):

https://dev.to/narayandreamer/mysql-to-postgres-migration-us...

I recommend the Docker image over downloading and running it locally anyways tbh -- you can just prune Docker images to remove stuff without needing to remember to delete it, and you don't have to copy it to your bin dir in PATH.

https://news.ycombinator.com/item?id=8924270 7 years ago|23 comments

Reply on news.ycombinator.com