Automating upgrading the DB schema

[MasterWayZ]

Task: automate upgrading the DB schema, so that people who deploy Taskcluster will not have to worry about this. This could be a Kubernetes init pod or something similar.

[lotas]

I was thinking about it. Options are:

1. As it is now: run manually yarn dev:db:upgrade when needed
2. Add it as a init container to the web server deployment
3. Add a new job to be executed once during deployment (yarn dev:apply)

While any of those would work for private and community needs, adding this as a default solution for all deployments might not work for Firefox CI. (afaik it's db is quite big and having locking migrations might have some downsides) But we can always include 2. and 3. under flag, which will be disabled by default to avoid breaking existing workflows.

[djmitche]

Ideally the "production" deployment would not involve yarn dev:..

The upgrade instructions describe how to run this as a one-off docker container. There are a few other notes in that document that may need attention, especially regarding the cronjobs. That may not be an issue for most deployments, but has caused extremely slow upgrades for firefox-ci.

[lotas]

Good point about dev: vs prod.

@MasterWayZ I just realised my options are not really feasible, due to the way permissions in DB are set. I missed that each service is using separate db username (non-admin accounts).

Which makes options 2. and 3. hard to automate this way (unless we place PG admin password into secrets, which will be wrong). So it is better to either run yarn dev:db:upgrade or use advice that Dustin shared.

The only thing worth noting is that will run commands from your local machine. If you want to execute it from within the cluster (if db is not accessible, for example) you can do something like this (keeping in mind that cronjobs and other pods might need to be stopped/restarted to pick up db changes)

kubectl -n $NS run db-upgrade-manual-task \
   --image=taskcluster/taskcluster:v44.7.1 \
   --env="USERNAME_PREFIX=pg-admin" \
   --env="ADMIN_DB_URL=postgresql://....connection..string.." \
   -- \
   node db/src/main.js upgrade

[lotas]

@MasterWayZ do you think we have sufficient info in the docs and we can close this ticket, or you would like to have more info on that topic?

[lotas]

I've added https://github.com/taskcluster/taskcluster/issues/7165 because we see that non-mozilla deployments are running in the same issue and it will be reasonable to have an optional step in helm deployment for this