Validate and document comparing DB structure dumps

Problem to solve

We've seen some cases where for whatever reason a database index is missing or incorrect, or some other structural problems exists with a database. We've had a need to be able to compare a database schema/structure against a known good/clean database (from a new install, for example).

I think parts of the support team have toyed with different approaches. Let's bring those all together and documented a tested strategy.

Further details

A proposal I have for comparing the schema is to use gitlab-rake db:structure:dump. This will output a database-specific SQL file with the schema. This file gets written to /opt/gitlab/embedded/service/gitlab-rails/db/structure.sql for Omnibus. The full set of commands needed is:

root@gitlab:~# touch /opt/gitlab/embedded/service/gitlab-rails/db/structure.sql
root@gitlab:~# chown git:git /opt/gitlab/embedded/service/gitlab-rails/db/structure.sql
root@gitlab:~# gitlab-rake db:structure:dump

We have to precreate and assign ownership of the file so git can write to it. If we do this once on the customer system and once on a fresh install of their exact version we should be able to get a decent comparison.

• Does this work in practice?
• What other approach might work?

@gl-support