Weblate database switch to PostgreSQL
Upstream only wants to support PostgreSQL and kick out MySQL support. (see https://github.com/WeblateOrg/weblate/issues/2825, https://docs.weblate.org/en/latest/admin/install.html#database-setup)
Related to Tails -
Related to Tails -
|Related to Tails - Bug #17050: Translation platform: design & implement long-term maintenance & support plan||In Progress|
Related to Tails -
#1 Updated by hefee 2019-08-23 17:22:41
Currently PostgrSQL is only recommend for new installation and existing users don’t need to switch atm.
On the bugreport the have a link to a migration guide: https://www.calazan.com/migrating-django-app-from-mysql-to-postgresql/
#7 Updated by zen 2020-01-17 15:50:22
For the record, we’re getting repeated mysql lock timeout errors in the Weblate log (
I am not sure, though, if only switching to PostgreSQL would immediatelly solve those issues as I’m not sure such performance improvements are already implemented in Weblate in the version we’re currently running (3.5.1).
Anyway, I think the best combo to do right now would be (1) upgrade to latest Weblate (Feature #17345), (2) switch to PostgreSQL (this issue), and (3) tune Weblate config and the VM resources to guarantee the service works as expected (
Bug #17445). This way we’d be ready to welcome new performance related features when they are available in upstream.
#11 Updated by zen 2020-04-01 12:14:11
Here is upstream documentation on migrating the database: https://docs.weblate.org/en/weblate-3.11/admin/upgrade.html#database-migration
And these are the rough steps I’m planning to do:
- Put Weblate under maintenance (to avoid modifications during the switch) — probably modifying Nginx config.
- Add PostgreSQL and a database configuration to Puppet codebase.
- Add the new database configuration to Weblate config using Puppet.
- Manually export/import the database.
- Switch the database configuration in Weblate config using Puppet.
- Remove Weblate from maintenance.
- Remove the MariaDB database configuration from Weblate config in Puppet.
#12 Updated by zen 2020-04-02 09:27:36
- Status changed from Confirmed to Resolved
Migration is done, and here are some comments:
- Exporting/importing the database using
manage.py dumpdata/loaddatatook an enormous amount of time and RAM. Exporting needed more than 1 hour and a total of 17G RAM (5 real + 12 SWAP) and importing took 12 hours (!!!) and a total of 35G RAM (5 real + 30 SWAP). Actually, exporting took “only” a bit more than 3 hours of user time, but as it was using SWAP, there was a lot of I/O going on. Looks like the Weblate/Django JSON handler is not optimized, next time we should try with the XML handler (although it may not work depending on the internal models used by Weblate). We are not using latest version Weblate, so properly reporting this behavior to upstream would demand a bit more work/investigation to verify versions, features and bug fixes for later versions.
- Because database dumps took a lot of space (almost 8G without compression, and I needed to deal with more than one at a time) and because of the need to set up a huge amount of SWAP, I needed to increase the disk size of the VM. The root filesystem previously had 45G and I increased it to 90G (doubling as a rule of thumb and understanding that there’s no lack of disk space in our server).
- I left an 8GB SWAP file configured in the VM, just in case.
#13 Updated by zen 2020-04-02 09:36:39
For the record, I keep seeing eventual “504 Gateway Time-out” from Nginx proxy, so looks like we have to take other measures as this migration to PostgreSQL was not enough to get rid of that problem.
Initially, maybe we should increase the timeout time in the proxy, as it’s easy to do and would help us better understand the behavior of Weblate. We also need to upgrade Weblate, and that is already mapped in Feature #17345.