Move over to using postgres as the database for the web application

openaustralia / morph

Take the hassle out of web scraping

https://morph.io

GNU Affero General Public License v3.0

463 stars 74 forks source link

Move over to using postgres as the database for the web application #1058

Open mlandauer opened 7 years ago

mlandauer commented 7 years ago

There are a few reasons why I think this is a good idea:

Migrations don't lock the database - we have a few big tables which are really painful to do migrations on. It would be nice to be able to do migrations without locking the whole site up. This is the main reason why we migrated to postgres for cuttlefish.io.
The production database on morph.io has an overgrown /var/lib/mysql/ibdata1 file currently standing at 57G while the per-table files in /var/lib/mysql/morph currently stand at 24G. This is despite using mysql 5.6 which should have innodb_file_per_table enabled by default. The only way to fix this as far as I can tell is to export the database, delete everything, and reimport the database. If we're going to do this we might as well as just migrate to postgres!

@henare thoughts?

henare commented 7 years ago

@mlandauer totally agree :+1:

mlandauer commented 7 years ago

Breaking this rather large task into smaller bits

In development

[x] Setup dev postgres using docker compose
[x] Put docker volume into directory inside repo
[x] Run unit tests against fresh install of db on postgress
[x] Test app by hand with postgres setup
[x] Commit commented out postgres database.yml
[ ] Setup migration tool recommended by heroku
[ ] Test migration tool on existing MySQL dev data
[ ] Script to backup postgres data locally on server
[ ] Modify backup script for backing up postgres data to S3
[ ] Setup docker compose with ansible to get pg going in production
[ ] Update capistrano to put docker volume data in shared directory

In production

[ ] Test migration of production data
[ ] Put site into read-only mode. Do real migration. Switch database to use Postgres. Turn off read only mode.
[ ] Cleanup old mysql production data
[ ] Remove references to mysql in codebase
[ ] Update README to refer to Postgres

mlandauer commented 7 years ago

After discussing this with @henare we decided to put this bit of work on hold as I have a relatively short amount of time left and this is not the most sensible use of my time. Instead I've done the simpler task of reducing the ibdata1 file size by dumping the database, dropping the database, stopping the server, deleting the file (and the log files) in mysql, restarting the database and then reloading the data from the dump.