5 ms·

Pgloader – migrate your data from any SQL database into PostgreSQL

127 points by humility 7y ago

dfsegoat 7y ago
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#mysql-database-casting-rules https://pgloader.readthedocs.io/en/latest/ref/mysql.html#mys...
- humility 7y ago
  We're using it for migration from sqlite to postgres. Never found a more useful single line command.
jjice 7y ago
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?
- sosodev 7y ago
  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.
- goatinaboat 7y ago
  Postgres really improved in performance? It’s not the only reason but yes, Postgres has recently made significant advances in parallelism and indexing.
- kjeetgill 7y ago
  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.
- barrkel 7y ago
  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.
- dano 7y ago
  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.
lukastr0 7y ago
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.
ilikepi 7y ago
The author of this tool also appears to maintain a website[1] with information about migrating from MySQL to Postgres. [1]: http://mysqltopgsql.com/ http://mysqltopgsql.com/
deleted 7y ago
[deleted]
aidenn0 7y ago
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/ https://theartofpostgresql.com/
mrtweetyhack 7y ago
would be more useful if this was "migrate your data from any database into any database"
poweredbylisp 7y ago
Nice to see that it's Common Lisp 96.7% Makefile 1.9% Other 1.4%

127 points by humility 7y ago

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#mysql-database-casting-rules https://pgloader.readthedocs.io/en/latest/ref/mysql.html#mys...

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

jjice 7y ago

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?

sosodev 7y ago
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.
goatinaboat 7y ago
Postgres really improved in performance? It’s not the only reason but yes, Postgres has recently made significant advances in parallelism and indexing.
kjeetgill 7y ago
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.
barrkel 7y ago
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.
dano 7y ago
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.

lukastr0 7y ago

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.

ilikepi 7y ago

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

deleted 7y ago

[deleted]

aidenn0 7y ago

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/ https://theartofpostgresql.com/

mrtweetyhack 7y ago

would be more useful if this was "migrate your data from any database into any database"

poweredbylisp 7y ago

Nice to see that it's Common Lisp 96.7% Makefile 1.9% Other 1.4%