Bug #16994

Weblate database switch to PostgreSQL

Added by hefee 2019-08-23 17:21:00 . Updated 2020-04-02 09:36:39 .

Status:
Resolved
Priority:
Normal
Assignee:
Category:
Target version:
Start date:
2020-04-01
Due date:
2020-04-01
% Done:

0%

Feature Branch:
Type of work:
Sysadmin
Blueprint:

Starter:
Affected tool:
Translation Platform
Deliverable for:

Description

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)

Subtasks

Related issues

Related to Tails - Bug #16995: translate.lizard: cron.sh fail with 'Lock wait timeout exceeded; try restarting transaction' Resolved
Related to Tails - Bug #16943: Upgrade to Weblate 3.x Resolved
Related to Tails - Bug #17050: Translation platform: design & implement long-term maintenance & support plan In Progress
Related to Tails - Bug #17445: Weblate: tune VM resources and service config for better performance Rejected

History

#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/

#2 Updated by hefee 2019-08-23 17:34:06

  • related to Bug #16995: translate.lizard: cron.sh fail with 'Lock wait timeout exceeded; try restarting transaction' added

#3 Updated by intrigeri 2019-08-24 07:07:35

  • related to Bug #16943: Upgrade to Weblate 3.x added

#4 Updated by intrigeri 2019-08-24 07:14:28

> Upstream only wants to support PostgreSQL and kick out MySQL support. (see https://github.com/WeblateOrg/weblate/issues/2825

Interesting! I’ve subscribed to this upstream issue so we have a heads up wrt. upstream’s decision.

#5 Updated by Anonymous 2019-09-13 09:31:26

Sounds like a good (long-term) plan!

#6 Updated by intrigeri 2019-09-13 09:31:32

  • related to Bug #17050: Translation platform: design & implement long-term maintenance & support plan added

#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 (Bug #17445).

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.

#8 Updated by zen 2020-01-17 15:51:04

  • related to Bug #17445: Weblate: tune VM resources and service config for better performance added

#9 Updated by zen 2020-02-21 18:50:42

  • Due date set to 2020-03-20
  • Start date set to 2020-03-18

#10 Updated by zen 2020-03-26 17:24:42

  • Due date changed from 2020-03-20 to 2020-04-01
  • Start date changed from 2020-03-18 to 2020-04-01

Pandemic context made me had to do something else on the day I had organized to do this, so I’m rescheduling for April 1st 2020.

#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/loaddata took 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.