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