Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Migrating 1200 databases from MySQL to Postgres

psyenix.blogspot.com

Post reply on HN
Print view View original

61–70 of 85 posts

Re: Migrating 1200 databases from MySQL to Postgres

#61

Post by richdougherty »

sigi45 wrote: post #58

I like the approach and think something like this is fun but i don't understand the businsess case at all. I would have migrated MySQL 5.5 to 5.6 first, than cleaned the shit out of it, normalized everything, added a few indezes and suddently realize that there is not much left to do. I like to work with postgresql but if you have 1,2k dbs which are more or less not claned up and crappy, what are they used anyway?

I agree. It sounds like they decided to do a cleanup ahead of an investment and not much of a real business case. Your migration path seems much, much simpler - and that's assuming the cleanup is even needed in the first place.

Re: Migrating 1200 databases from MySQL to Postgres

#62

Post by mkez00 »

seanharr11 wrote: post #55

I wrote an open source tool that migrates between any 2 relational databases. https://github.com/seanharr11/etlalchemy This includes automatic migration of the schema, constraints, indexes and obviously data. It leverages fast bulk loading tools like mysqlimport, and PostgreSQL's COPY FROM. It doesn't need any config. Just plug and play. The tool uses SQLAlchemy to abstract column types, and handles various column ty…

Going to give this a try. Thanks for posting!

Re: Migrating 1200 databases from MySQL to Postgres

#63

Post by petergeoghegan »

fauigerzigerk wrote: post #46
fulafel wrote: post #25

Earlier quoted context omitted.

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")

But do these "already working" collations include accent-insensitive variants? I didn't think so, and I don't think they are coming in PostgreSQL 10 either. Do you have other information on this?

They do. The problem is that equality is still strict binary equality for text, for obscure technical reasons. So, while you can get extremely flexible customizations to collation with icu [1], you can't have case insensitive equality, or equality that ignores accents. This is recognized as a problem, and is probably going to be fixed in v11.

[1] https://www.postgresql.org/message-id/CAH2-Wz=bcgmk97YaZ3rs4...

Re: Migrating 1200 databases from MySQL to Postgres

#64

Post by anarazel »

petergeoghegan wrote: post #63
fauigerzigerk wrote: post #46

Earlier quoted context omitted.

But do these "already working" collations include accent-insensitive variants? I didn't think so, and I don't think they are coming in PostgreSQL 10 either. Do you have other information on this?

They do. The problem is that equality is still strict binary equality for text, for obscure technical reasons. So, while you can get extremely flexible customizations to collation with icu [1], you can't have case insensitive equality, or equality that ignores accents. This is recognized as a problem, and is probably going to be fixed in v11. [1] https://www.postgresql.org/message-id/CAH2-Wz=bcgmk97YaZ3rs4...

> The problem is that equality is still strict binary equality for text, for obscure technical reasons

AKA performance.

Re: Migrating 1200 databases from MySQL to Postgres

#65

Post by kondro »

gaius wrote: post #57
Amezarak wrote: post #35

Earlier quoted context omitted.

At least in my experience, joins in 100s of GB databases are not a performance problem. I've always been somewhat baffled by the claims I see pretty regularly that joins are a major performance concern. Was this a big deal in the past?

Was this a big deal in the past? In MySQL, yes. It lacked the concept of foreign keys.

Lack of foreign keys does not effect performance.

In fact, having foreign keys is a net-negative performance-wise because it means that each foreign key must be checked for every insert/update/delete.

Re: Migrating 1200 databases from MySQL to Postgres

#66

Post by ibejoeb »

When talking about replaying live activity on the test system:

>We took the third option because the other two options were quite intrusive and they might interfere at the normal use of our app because in both cases they were going to be in between our users request and our app

One of those two rejected options was goreplay. It is passive, like tcpdump. Right from the docs:

"Gor is not a proxy: you do not need to put 3-rd party tool to your critical path. Instead Gor just silently analyzes the traffic of your application and does not affect it anyhow."

Re: Migrating 1200 databases from MySQL to Postgres

#67

Post by icelancer »

seanharr11 wrote: post #55

I wrote an open source tool that migrates between any 2 relational databases. https://github.com/seanharr11/etlalchemy This includes automatic migration of the schema, constraints, indexes and obviously data. It leverages fast bulk loading tools like mysqlimport, and PostgreSQL's COPY FROM. It doesn't need any config. Just plug and play. The tool uses SQLAlchemy to abstract column types, and handles various column ty…

Thanks much - going to test it between MSSQL and MySQL in a few days here!

Re: Migrating 1200 databases from MySQL to Postgres

#68

Post by beachy »

zimpenfish wrote: post #4
overcast wrote: post #3

Earlier quoted context omitted.

You should see the one here. An old flat file database monstrosity from the 80s, imported into oracle, complete with all of the duplicated data. It's the most non relational, relational database ever.

Would a database stored in an XML file shared between 60+ clients over NFS beat that for monstrositiness?

That made me snort my coffee.

Re: Migrating 1200 databases from MySQL to Postgres

#69

Post by fulafel »

fauigerzigerk wrote: post #46
fulafel wrote: post #25

Earlier quoted context omitted.

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")

But do these "already working" collations include accent-insensitive variants? I didn't think so, and I don't think they are coming in PostgreSQL 10 either. Do you have other information on this?

My misunderstanding seemed to be that "accent-insensitive" turns out to be a precise technical term from MySQL that says "just sort UTF-8 using the American locale and collation, but pretend accented characters are equal to their non-accented versions" which is not a standard collation order in any locale.

Re: Migrating 1200 databases from MySQL to Postgres

#70

Post by gaius »

kondro wrote: post #65
gaius wrote: post #57

Earlier quoted context omitted.

Was this a big deal in the past? In MySQL, yes. It lacked the concept of foreign keys.

Lack of foreign keys does not effect performance. In fact, having foreign keys is a net-negative performance-wise because it means that each foreign key must be checked for every insert/update/delete.

Errm, we're talking about performance of joins
Post reply on HN