Migrating 1200 databases from MySQL to Postgres • Hacker News Board

Re: Migrating 1200 databases from MySQL to Postgres

#21

Post by nrmitchi » Wed, Aug 16, 2017, 2:50 PM UTC

> Now is when one of the main reasons to take Postgres as our new database server. Postgres allows the use of Views(include link) supporting INSERT, UPDATE and DELETE meanwhile the relation between views and tables are one to one, ....isn’t it awesome?

Just a heads up, I think you missed a link in there

Re: Migrating 1200 databases from MySQL to Postgres

#22

Post by shrikant » Wed, Aug 16, 2017, 2:54 PM UTC

cntlzw wrote: post #10

noir_lord wrote: post #2

Would love to do this to the monstrosity I inherited in new job but it's a sispheyan task. One day.

One time I saw a database, with a single table, with a single text column that contained the old database.

This sounds similar to what I saw at a client a few years back. All XML responses from a REST API were inserted into a single VARCHAR(MAX) column (one row per response, but a response could have multiple "records") in a SQL Server database.

All interaction with this was through a byzantine web of XPath expressions using the severely neutered dialect of XQuery that shipped with SQL Server 2008.

Apologies for the mini-rant, your comment appears to have triggered some painful memories.

Re: Migrating 1200 databases from MySQL to Postgres

#23

Post by claar » Wed, Aug 16, 2017, 2:56 PM UTC

berns wrote: post #18

One thing that Postgres lacks is accent insensitive collations. Having clients with databases in Spanish, this is one of the reasons I wouldn't consider migrating to Postgres. I know I can use the unaccent extension, but I consider it a poor substitute to proper collations. I guess this isn't a problem for most people because it's never mentioned.

It appears work is active (May 2017) in this area, but accent-insensitive collations are still a ways off: https://blog.2ndquadrant.com/icu-support-postgresql-10/

edit: fulafel corrects me below that you can get accent-insensitive collations working using PostgreSQL 10's new ICU collation support. Note that PostgreSQL 10 is currently pre-release.

Re: Migrating 1200 databases from MySQL to Postgres

#24

Post by fulafel » Wed, Aug 16, 2017, 2:56 PM UTC

berns wrote: post #18

One thing that Postgres lacks is accent insensitive collations. Having clients with databases in Spanish, this is one of the reasons I wouldn't consider migrating to Postgres. I know I can use the unaccent extension, but I consider it a poor substitute to proper collations. I guess this isn't a problem for most people because it's never mentioned.

The docs say that locales and LC_COLLATE work, are you sure?

Re: Migrating 1200 databases from MySQL to Postgres

#25

Post by fulafel » Wed, Aug 16, 2017, 3:01 PM UTC

claar wrote: post #23

berns wrote: post #18

One thing that Postgres lacks is accent insensitive collations. Having clients with databases in Spanish, this is one of the reasons I wouldn't consider migrating to Postgres. I know I can use the unaccent extension, but I consider it a poor substitute to proper collations. I guess this isn't a problem for most people because it's never mentioned.

It appears work is active (May 2017) in this area, but accent-insensitive collations are still a ways off: https://blog.2ndquadrant.com/icu-support-postgresql-10/ edit: fulafel corrects me below that you can get accent-insensitive collations working using PostgreSQL 10's new ICU collation support. Note that PostgreSQL 10 is currently pre-release.

That post says that they currently work, but there is a new os-independent collation method coming.

Edit re your edit: the already working collations are in PG 9.x - the OS independent collations are coming in 10.x. (Hence the post title, "More robust collations with ICU support in PostgreSQL 10")

Re: Migrating 1200 databases from MySQL to Postgres

#26

Post by willcodeforfoo » Wed, Aug 16, 2017, 3:05 PM UTC

I'm working on migrating an app right now (just one MySQL database) but can highly recommend [pgloader](http://pgloader.io/). It has a bunch of built-in and configurable rules to cast different column types to PostgreSQL and the developer has been very responsive in helping me make weird column types work, too.

Re: Migrating 1200 databases from MySQL to Postgres

#27

Post by lbotos » Wed, Aug 16, 2017, 3:07 PM UTC

willcodeforfoo wrote: post #26

I'm working on migrating an app right now (just one MySQL database) but can highly recommend [pgloader]( http://pgloader.io/ ). It has a bunch of built-in and configurable rules to cast different column types to PostgreSQL and the developer has been very responsive in helping me make weird column types work, too.

+1 for PGLoader. Still wrestling with getting it to work for a conversion, but the experience has been a pleasure so far and surprisingly easy.

Re: Migrating 1200 databases from MySQL to Postgres

#28

Post by TekMol » Wed, Aug 16, 2017, 3:11 PM UTC

Is it an universally accepted truth now that it's generally better to use Postgres then to use MySql?

Would anybody here use MySql for a new project? If so, why?

Re: Migrating 1200 databases from MySQL to Postgres

#29

Post by leesalminen » Wed, Aug 16, 2017, 3:13 PM UTC

TekMol wrote: post #28

Is it an universally accepted truth now that it's generally better to use Postgres then to use MySql? Would anybody here use MySql for a new project? If so, why?

I chose MySQL (well, MariaDB) for a project that started in 2013. I chose it because I have the most experience maintaining it and have a pretty good handle on how to tune.

Re: Migrating 1200 databases from MySQL to Postgres

#30

Post by K-Wall » Wed, Aug 16, 2017, 3:17 PM UTC

shoo wrote: post #12

cntlzw wrote: post #7

Earlier quoted context omitted.

How concurrent is that? Can't imagine how writing to the file is even achieved.

maybe there is an email thread shared between all clients for acquiring / releasing the write lock on the xml file

Semaphore as a service Slack Chat bot.