How to use migrator operation drift

WARNING: When running the drift command, the version supplied (or inferred from your instance's state) is meant to indicate the most recently running version of the instance (not the target version during an upgrade process). Drift output is meant to show you the difference between the schema expected by Sourcegraph during operation and upgrades against your database's actual schema so that the database can be put in a healthy, known state. Following the instructions provided when supplying a different version will move your database schema further out of sync.

During an upgrade you may run into the following message.

SHELL
✱ Sourcegraph migrator 7.7.359
 ⨯ Schema drift detected for frontend
💡 Before continuing with this operation, run the migrator's drift command and follow instructions to repair the schema to the expected current state. See https://sourcegraph.com/docs/admin/updates/migrator/schema-drift for additional instructions.

This error indicates that migrator has detected some difference between the state of the schema in your database and the expected schema for the database in the -from or current version of your Sourcegraph instance.

When the schema drift command is run you'll see a set of diffs representing the areas where your instance schema has diverged from the expected state as well as the SQL operations to fix these examples of drift. For example:

SHELL
 ⨯ Drift detected!

 ⨯ Missing index "external_service_repos"."external_service_repos_repo_id_external_service_id_unique"
💡 Suggested action: define the index.
ALTER TABLE external_service_repos ADD CONSTRAINT external_service_repos_repo_id_external_service_id_unique UNIQUE (repo_id, external_service_id);

 ⨯ Unexpected properties of column batch_spec_resolution_jobs."batch_spec_id"
💡 Suggested action: alter the column.
ALTER TABLE batch_spec_resolution_jobs ALTER COLUMN batch_spec_id SET NOT NULL;

To correct these errors in the database run the suggested SQL queries via psql in internal databases, or via the tools provided by your cloud database provider.

docker example

SHELL
docker exec -it pgsql psql -U sg -c 'ALTER TABLE external_service_repos ADD CONSTRAINT external_service_repos_repo_id_external_service_id_unique UNIQUE (repo_id, external_service_id);'

kubernetes example

SHELL
kubectl -n ns-sourcegraph exec -it pgsql -- psql -U sg -c 'ALTER TABLE external_service_repos ADD CONSTRAINT external_service_repos_repo_id_external_service_id_unique UNIQUE (repo_id, external_service_id);'

Then check the database again with the drift command and proceed with your multiversion upgrade.

If migrator drift suggests SQL queries which don't make sense please report to support@sourcegraph.com. You may proceed with a migrator upgrade command using the -skip-drift-check=true flag.