Investigate database migration options

[mrchrisadams]

Significant parts of the TGWF database rely on MySQL and MyISAM tables.

Investigate our options for switching away.

If we stay with MySQL

Possible drawbacks migrating to postgres

• https://wiki.postgresql.org/wiki/How_to_make_a_proper_migration_from_MySQL_to_PostgreSQL
• Possible changes to the php application code?
• We need more disk space to execute the migration.

are the trade-offs, and possible pitfalls

If we consider other database options

• like Postgres, (timescale db, etc)

Migration guide

• https://www.digitalocean.com/community/tutorials/how-to-migrate-mysql-database-to-postgres-using-pgloader
• https://pgloader.io/blog/migrating-to-postgresql-the-white-paper/

Why this issue appeared and which needs to be resolved.

We tried creating a new table Hostingcommunication (innodb) that references hostingproviders (MyISAM). That foreign key constraint couldn't be created because the two tables were different.

The change from MyISAM to InnoDB can be done by altering the table to use InnoDB instead. See the following answer: https://stackoverflow.com/a/30648414/

Migrating from MyISAM to InnoDB requires us to be aware of the following issues described here: https://mariadb.com/kb/en/library/converting-tables-from-myisam-to-innodb/

Parts of the TGWF API app we would need to change

(AJ, I'm using the wrong terminology, but can you list the bits we'd need, and any open PRs for them? I'll look over them, comment and merge if appropriate)

• [ ] logger
• [ ] worker
• [ ] main resource

[mrchrisadams]

Hey @arendjantetteroo - if we're using postgres, then timescale db is the ideal extension to use for our use case.

It'll compress the huge greencheck table, and give us all the nice postgres features like materialised views, and a good way to back up data.

https://docs.timescale.com/latest/getting-started/installation/ubuntu/installation-apt-ubuntu

[arendjantetteroo]

52 has the first steps into allowing postgres, the greencheck package which is used in the worker code is now also tested with postgres on travis so we can switch them based on a config change only.

Next step would be testing the databaseQueries file in the api application to see if that supports all postgress queries.

[arendjantetteroo]

Postgres database is setup on the server with 400GB of disk space and the timescaledb extension.

We can already migrate the logger to push stuff into a hyper table on postgres indepedent on the rest of the migration. It might also be best to do these migration in several steps if we can so we don't need to do a big bang migration.