We do not mention postgres admin is required, leading to confusing migration issues

[dadlerj]

• Sourcegraph version: Sourcegraph 3.4.2
• Platform information: K8s cluster

Reported by a customer:

• https://app.hubspot.com/contacts/2762526/company/1455993560

Steps to reproduce:

I am trying to install Sourcegraph on Kubernetes using an external RDS DB and keep getting the error message: "Fatal error connecting to Postgres DB: Failed to migrate the DB. Please contact support@sourcegraph.com for further assistance: Dirty database version 1503574972. Fix and force version."

I am using the cloned repo from here: https://github.com/sourcegraph/deploy-sourcegraph.

And repro steps:

I had originally deployed Sourcegraph entirely on Kubernetes and everything worked well. Then, I wanted to move the DB to RDS and so I created an RDS instance with a sourcegraph DB and user and reconfigured all the yaml files. I then deleted all the deployed resources, including the pgsql pod and redeployed with the new configuration.

The only other thing I did was remove the pgsql directory from base/ as I did not want the script (kubectl-apply-all.sh) to redeploy the pgsql pod and service.

[slimsag]

Looking at the error, it appears we partially initialized the DB schema and failed on the first migration we run. I don't know how that could possibly occur, so it is quite bizarre.

One possibility I can think of, if you copied an existing DB into RDS, is that the copy was incomplete in some way (e.g. perhaps the schema_migrations table was omitted in the copy by accident?)

If it was a fresh DB creation (which it sounds like it was?), then it would seem Sourcegraph began initializing the DB and was interrupted and got stuck here for some reason. That is odd, but we can correct it. This seems likely because I note the error message indicates it is failing on the very first schema migration we would normally apply -- indicating the DB schema isn't initialized at all yet.

To correct this, please try:

1. Confirm the logs still show 1503574972 as the problematic DB version. If not, don't continue with these steps.
2. Get a psql prompt connected to your RDS instance, with the Sourcegraph DB name and user.
3. Confirm that running \d+ shows only a few tables. If it shows a fuller list like this then don't continue with these steps.
4. Assuming the above output showed only a few tables, run the problematic migration manually, by entering the following:

CREATE EXTENSION IF NOT EXISTS citext;
CREATE EXTENSION IF NOT EXISTS hstore;
CREATE EXTENSION IF NOT EXISTS pg_trgm;

5. Force the DB schema version by running the following:

UPDATE schema_migrations SET version=1503574972, dirty=false;

6. Restart (kubectl delete pod $POD_ID) the frontend/management-console/repo-updater pods so they more quickly retry.

After this, my expectation is that the error will go away after about 5 minutes (once the first restarted pod has had a chance to initialize the DB schema).

[slimsag]

Update: The user discovered that the problem was that the sourcegraph user role in postgres had been created without admin privileges (for security reasons) and that prevented the first migration's CREATE EXTENSIONs from working.

We should update our external DB docs to include mention of this.

[beyang]

Taking this out of SWAT as the updating of the docs is not a p0

[slimsag]

This didn't make 3.8.

[uwedeportivo]

Dear all,

This is your release captain speaking. 🚂🚂🚂

Branch cut for the 3.16 release is scheduled for tomorrow.

Is this issue / PR going to make it in time? Please change the milestone accordingly.
When in doubt, reach out!

Thank you