Page MenuHomeSoftware Heritage

Migrate swh-web production database from SQLite to PostgreSQL
Closed, ResolvedPublic


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

  • 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)

Event Timeline

This comment was removed by ardumont.

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.

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.

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

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

  • 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;
swh-web=> \q

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 claimed this task.
ardumont moved this task from deployed/landed to done on the System administration board.