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 type conversions between any flavor of SQL.
The one thing it needs are tests, and scalability support for tables bigger than a few GB!
We run MySQL on product and am in the process of de-normalizing it. If I have compelling reasons, I might push for migration to Postgres but I never found any genuine reasons. Uber even migrated the opposite way from Postgres to MySQL (Although I think reddit engineers disagreed with their reasons for move).
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?
Might have been worthy of testing this to see if it was worth it to materialize certain views and refresh them every 15-30 minutes or so.
Also, it's officially "PostgreSQL" or informally "Postgres". Not "PostGres".
In MySQL, yes. It lacked the concept of foreign keys.
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.
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")
So what if the functionality is not in core, but in an extension? You make it sound as if it does not exist at all and all we can do is gnash our teeth in frustration.
http://www.public-software-group.org/pg_collkey
first release in 2006 for PostgreSQL 8
I used AWS Schema Conversion Tool for initial PG schema. I customized the generated schema for my specific needs.
We ended up with a frankenstein mixture of bash scripts, a sequence of sql transformations, some python and some php to decode some of the data that had been stored in pickled / php encoded packages.
It's not something I want to repeat any time soon :-)
I can't remember exactly but the whole project was done in a real hurry, about 4 weeks I think. It included a rewrite of the application / website (about 20k lines of backend code, html and js / css), migration of all the data, creation of new transcoders, migration of a lot of data asset/video data from another server.
No idea how we got through it all in retrospect, but I had to. A few days after we finished I got married and went off on my honeymoon, so I guess I decided it was stable enough the day I released it...! :-)
This is of course only when you set a case insensitive collation (_ci).
But I agree this can be a problem because case insensitive collations are used a lot in MySQL.
Also other collations can be a trouble. For example the collation that returns results for both 'ß' and 'ss' (German)
One day.
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.
Every single table has either a uniqid() or a UUID() which isn't the primary key (they all have incrementing integer keys) but is 'joined' on in code.
When he needs to 'join' he used coalesce() on selects.
Referential integrity doesn't exist.
Oh and the cherry on the shit sandwhich, we have four seperate systems that talk to this database written in 3 different languages (Java (Android scanners), C# (Factory scanners) and PHP (the main system).
So there is no way to accurately know which SP's are used by what without grepping the entire codebase looking for call <foo>.
The PHP is written badly in the old PHP pure procedural style running on an outdated version of PHP/Debian/MySQL.
Basically if you took the absolute worst approach to everything this would be the end result.
It's good fun!.
Would anybody here use MySql for a new project? If so, why?
They are both plenty feature complete to work well for relational data type problems. Grab the one you have more exposure to and you'll be fine.
That said, I'm still using mysql-innodb (latest percona) because the built-in replication has had more time to bake and its concurrency model (think 100k qps+) is plain better. For further reading see:
Ultimately, I think familiarity is and will always be the biggest driver for experienced devs who choose a DB.
MySQL is fast enough, usually has enough features, I know how to tune it and queries, and I normally only bump into 2-3 things in a project which would've been easier with PostgreSQL. And can work around them.
The backwards-incompatible SQL_MODE changes between MySQL 5.6 and 5.7 made me reconsider, as legacy apps needed updating.
tl;dr: yes. But PostgreSQL would be easier for some items
http://www.brightball.com/articles/why-should-you-learn-post...
1. Did you use magento/Drupal/WordPress/etc PHP framework
2. Did you host on a 20$ per month server with unlimited bandwidth and free database ?
Apparently postgresql is difficult to run in a properly restricted bsd jail. Other than that, I'd be hard pressed to come up with reasons to prefer mysql on technical merit over either postgresql, or, say redis or something.
Is there anything else that's needed to properly restrict the jail?
That's essentially my reasons for preferring PostgreSQL.
Just a heads up, I think you missed a link in there
>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."
