1071720 - [bedrock] Migrate bedrock databases to PostgreSQL + PostGIS

Status: RESOLVED INVALID

(Data & BI Services Team :: DB: MySQL, task)

Description

[Paul [:pmac] McLanahan]

We're fielding requests for increasing numbers of GEO related features. It seems clear that we'll require a full-featured GEO-aware datastore sooner than later. I had a great conversation on IRC with :cyborgshadow about this, and we feel that PostGIS would be optimal for our needs. While MySQL + MyISAM tables could work in the short term for simple features, and MySQL 5.7 possibly adding more features in the future, we still concluded that this move is necessary. And, instead of keeping the rest of the bedrock database on MySQL and requiring the maintenance of 2 database clusters, we'll just move everything to this new cluster.

This will require moving dev, then stage, then prod of course, and we'll need databases on the dev cluster for dev plus the 5 demo instances like we currently have.

We'll also have to coordinate with WebOps to get psycopg2 installed on the webheads and admin nodes. We'll also be preparing the bedrock codebase for the move and data migration in the interim. 

The timeline for this is not urgent, but completion in Q4-2014 would be optimal.

Comment 1

[Brandon Johnson [:cyborgshadow]]

Just to confirm in this. Bedrock is currently using synchronous multi-master replication. Moving to Postgres would lose this feature. According to jgmize and pmac: "that's an acceptable tradeoff".

Comment 2

[Sheeri Cabral [:sheeri]]

Is this still a highly desired item? If so, we can work on this and get it done by mid-to-late January.

Comment 3

[Paul [:pmac] McLanahan]

I think it's not actually. We'll likely revisit when deciding how to deploy when planning the AWS move.

Thanks!