Page MenuHomeSoftware Heritage
Create Task
Maniphest T2945

Migrate swh-web production database from SQLite to PostgreSQL
Closed, MigratedEdits Locked
Actions

Description

Now that the "Save code now" service gets more and more used, we started to encounter some SQLite concurrency issue, see T2891 and sentry related issue.

This calls for the use of a better Django database backend in production, PostgreSQL being the obvious choice.

This task will track the actions that need to be done to perform that migration process, notably:

  • the creation of a swh-web database in a PostgreSQL server somewhere in swh staging / production environment
  • the adaptation of Django production settings and swh-web configuration to use that database
  • the dump of current swh-web SQLite database content to JSON
  • the load from those JSON data into the new swh-web PostgreSQL database

Plan or action so far:

  • D5392: Migrate swh.web settings to declare the use of postgres
  • D5391: Ensure migration tools and setup works in docker
  • D5393: staging: Prepare db (puppet)
  • production: Deactivate puppet on moma, webapp1
  • D5402: staging: Prepare swh-site setup change to webapps
  • Land, tag package swh-web (so the migration tools is available on nodes to migrate)
  • Check everything is fine in webapp.staging.swh.network
  • production: Prepare swh-site setup change to webapps
  • production: Prepare new swh-web db (manual creation)
  • production: Run swh-web upgrade and migrate previous data dump on new db
  • production: Run upgrade on moma (reactivate puppet)

Revisions and Commits

rDWAPPS Web applications
D5392rDWAPPS07f06cba53b1 swh-web: Migrate from sqlite to postgresql
D4830rDWAPPSb71d14deebe8 django: Use valid identifiers for app labels
rDENV Development environment
D5391rDENV482fa7d1068e swh-web: Migrate from sqlite to postgresql
rSPSITE puppet-swh-site
D5406rSPSITE6671513b73cb prod/webapp: Deploy new production_db configuration
D5402rSPSITEb79aa1370c07 staging/webapp: Deploy new production_db configuration
D5393rSPSITEff4940c92eeb staging: Install new swh-web db

Related Objects
Search...

Event Timeline

anlambert triaged this task as Normal priority.Jan 8 2021, 2:52 PM
anlambert created this task.
anlambert mentioned this in T2891: "Save code now" may fail with "OperationalError: database is locked".
anlambert added a revision: D4830: django: Use valid identifiers for application labels.Jan 8 2021, 6:24 PM
anlambert added a commit: rDWAPPSb71d14deebe8: django: Use valid identifiers for app labels.Jan 21 2021, 3:28 PM
ardumont moved this task from Backlog to Weekly backlog on the System administration board.Mar 31 2021, 9:49 AM
ardumont added a revision: D5391: swh-web: Migrate from sqlite to postgresql.Mar 31 2021, 3:28 PM
ardumont added a revision: D5392: swh-web: Migrate from sqlite to postgresql.Mar 31 2021, 3:31 PM
ardumont mentioned this in rSPPRIVCa927a5b01fa8: Update censored credentials.Apr 1 2021, 10:29 AM
ardumont added a revision: D5393: staging: Install new swh-web db.Apr 1 2021, 10:30 AM
ardumont added a commit: rSPSITEff4940c92eeb: staging: Install new swh-web db.Apr 1 2021, 11:00 AM
ardumont added a subscriber: ardumont.Apr 1 2021, 4:42 PM
This comment was removed by ardumont.
ardumont updated the task description. (Show Details)Apr 1 2021, 4:42 PM

This comment was removed by ardumont.

I've edited the description instead.

ardumont changed the task status from Open to Work in Progress.Apr 1 2021, 4:43 PM
ardumont moved this task from Weekly backlog to in-progress on the System administration board.
ardumont added a revision: D5402: staging/webapp: Deploy new production_db configuration.Apr 1 2021, 5:15 PM
ardumont updated the task description. (Show Details)Apr 1 2021, 5:24 PM
ardumont added a comment.Apr 1 2021, 5:37 PM

First, deactivate puppet on webapp nodes (puppet install the new webapp version which would starts a migration):

root@moma:~# puppet agent --disable "Migrate to postgresql, staging first"
root@webapp1:~# puppet agent --disable "Migrate to postgresql, staging first"

we do not want that because we want to try said migration to staging.

ardumont updated the task description. (Show Details)Apr 1 2021, 5:40 PM
ardumont added a commit: rDWAPPS07f06cba53b1: swh-web: Migrate from sqlite to postgresql.Apr 1 2021, 5:42 PM
ardumont added a comment.Apr 1 2021, 6:03 PM

while the jenkins is building the webapp package, we can create the production db:

postgres@belvedere:~$ createuser -p 5434 -P swh-web
Enter password for new role:
Enter it again:
postgres@belvedere:~$ createdb -p 5434 \
>      --lc-ctype=C.UTF-8 \
>      -T template1 \
>      -O swh-web \
>      swh-web
ardumont mentioned this in rDAUTH99479f96c2cf: d/changelog: Bump release.Apr 1 2021, 6:18 PM
ardumont mentioned this in rDAUTHa631cedc0956: d/control: Add missing dependencies.
ardumont added a comment.Apr 1 2021, 6:19 PM

Unstucking the debian builds now (swh.auth got stuck so unstuck it, then unstuck the swh.web one)

ardumont added a commit: rSPSITEb79aa1370c07: staging/webapp: Deploy new production_db configuration.Apr 1 2021, 6:28 PM
ardumont added a comment.EditedApr 1 2021, 6:36 PM
  • Unstuck swh-web buid done.
  • Activate puppet back on webapp.staging
  • run puppet did update the configuration file appropriately
  • And updated the webapp version which triggered the migration script
  • Now running the actual migration from sqlite to postgresql:
swhwebapp@webapp:~$ django-admin loaddata --settings swh.web.settings.production /tmp/webapp-schema.staging.json
Installed 132 object(s) from 1 fixture(s)
  • forgotten part, make the db readable by the guest user:
psql service=admin-staging-swh-web
psql (12.6)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

swh-web=> grant select on all tables in schema public to guest;
GRANT
swh-web=> \q
ardumont added a commit: rDENV482fa7d1068e: swh-web: Migrate from sqlite to postgresql.Apr 1 2021, 6:48 PM
anlambert mentioned this in T3195: Some SWHIDs provided by the WebApp produce 404 when resolved.Apr 1 2021, 7:02 PM
ardumont updated the task description. (Show Details)Apr 1 2021, 7:06 PM
ardumont added a revision: D5406: prod/webapp: Deploy new production_db configuration.Apr 2 2021, 10:15 AM
ardumont added a commit: rSPSITE6671513b73cb: prod/webapp: Deploy new production_db configuration.Apr 2 2021, 10:25 AM
ardumont updated the task description. (Show Details)Apr 2 2021, 10:57 AM
ardumont added a comment.Apr 2 2021, 11:01 AM

As planned, out of webapp1:

  • update webapp settings
  • activate puppet and let it update (webapp settings, upgrade swh.web package, migrate the schema)
  • Trigger the data dump migration:
swhwebapp@webapp1:~$ django-admin loaddata --settings swh.web.settings.production /tmp/swh-web.production.json
Installed 72268 object(s) from 1 fixture(s)

There is actually more than yesterday since a listing of ~1000 got done since.

Then do the same on moma (without the need of the last step to actually load any data
since they are using the same db now and it got already done through webapp1)

ardumont moved this task from in-progress to deployed/landed/monitoring on the System administration board.Apr 2 2021, 11:01 AM
ardumont closed this task as Resolved.Apr 2 2021, 11:04 AM
ardumont claimed this task.
ardumont moved this task from deployed/landed/monitoring to done on the System administration board.
anlambert mentioned this in T3601: Use PostgreSQL backend for django database in tests.Sep 23 2021, 11:08 AM
gitlab-migration changed the task status from Resolved to Migrated.Oct 19 2022, 6:00 PM
gitlab-migration claimed this task.
gitlab-migration added a subscriber: gitlab-migration.

This task has been migrated to GitLab.

About · Developer information · Legal