Pgloader – migrate your data from any SQL database into PostgreSQL | Hacker News Reader

Pgloader – migrate your data from any SQL database into PostgreSQL

github.com

127 pointshumility12 comments

We've been using pgloader as part of our production workflows between RDBMS' for about a year now. Overall I am super impressed with the speed and reliability of it.

Some gotchas:

- MySQL to Postgres needs some massaging for int-types. Pgloader will convert MySQL bigint to type "numeric" by default - which is a bit of overkill.

- For issues like the above, they have a handy syntax for the pgloader template files which lets you do datatype casts on the fly. [1]

1 - https://pgloader.readthedocs.io/en/latest/ref/mysql.html#mys...

We're using it for migration from sqlite to postgres. Never found a more useful single line command.

Pgloader is a great project, and I planned to use it for database migration of a small commercial product (from SQL Server).

Unfortunately, I never go it to work. Tried three different distros (Ubuntu / Debian / Amazon Linux). Sometimes installation would fail, other times it would crash on first run - even when following instructions to the letter.

When I finally got it to run, it could not connect to SQL Server, and no one on Github could help with my particular error message.

Ended up writing the migration code myself, which took only a few hours. The lesson: for a very simple migration, pgloader was actually overkill and a self-made solution did the job quicker.

I've noticed recently that Postgres is really gaining in popularity, especially as a MySQL alternative. Why is that? Is it because of Oracle's involvement in MySQL or has Postgres really improved in performance?

I tend to reach for postgres on all of my projects just because it’s the simplest option. It’s ridiculously easy to get running (you can just start the docker container), every framework seems to support it by default, and its types and query syntax just seem a little more intuitive than all the others. I can’t speak to performance since I don’t have anything particularly intense running on it.

Postgres really improved in performance?

It’s not the only reason but yes, Postgres has recently made significant advances in parallelism and indexing.

Postgres has earned the reputation for doing thinking features through, rolling improvements out reliably, and is pretty committed to doing things right (tm). This has meant fewer/slower hot features in the past, but solid when they arrive. It feels like they've pulled ahead in many features now. My favorite is all the indexing options.

It may be undeserved now, but MySQL had/has a reputation for a lot of sloppy work for good enough features or for performance. Mysql dropped MyISAM for InnoDB in an effort to undo some of that damage so it's hard for me to say they're not committed to quality now.

But reputations take time to rebuild.

PostgreSQL is way more featureful than MySQL, and the recent parallelization optimizations do add a significant competitive advantage over MySQL. What kept MySQL in the popularity lead for so long, though, was its solid async replication story. It wasn't until fairly recently that Postgres had that natively, rather than by third parties.

I think the larger programming community is coming around to the stability and good engineering present in the Postgresql development team and code base.

Over the past decade we've seen a lot of different data storage options presented that have exchanged critical data integrity features for speed, architecture, "document storage", or some other fad factor of the day.

I've been using Postgresql in a production environment since 2004, having converted from MySQL and never looked back. I'm not bagging on the other technologies, they're great for certain tasks, its just that Postgresql is great data storage and query engine and I think you'll grow to love it. I recommend the #postgresql community on Freenode as they are helpful and generous with their time.

Someone on HN once said "Postgres is the answer until proven otherwise" and that's a pretty good sentiment.

Nice to see that it's

    Common Lisp 96.7%
    Makefile 1.9%
    Other 1.4%

For those interested in learning more about PostreSQL, I'd recommend the book by the author of this tool[1]. It's bee a great resource for me. It's a great introduction to SQL with a focus on postgres.

(FWIW, it was called "Mastering PostgreSQL" when I bought it, but as far as I can tell, the site I linked is just an updated version).

1: https://theartofpostgresql.com/

The author of this tool also appears to maintain a website[1] with information about migrating from MySQL to Postgres.

[1]: http://mysqltopgsql.com/

Reply on news.ycombinator.com