Fix db:reset (db:schema:load) for multiple databases

⚠ Warning db:reset destroys your database, so handle with care.

What does this MR do and why?

Fix db:reset for multiple databases. We recommend usage of db:reset in some development docs.

We also use db:reset when setting up GDK. However when GDK is setup with multiple databases, it fails.

Related issue: #349589 (closed)

Why?

Because db:reset is actually db:drop db:create db:schema:load. While the first two commands can run fine on the same database, db:schema:load will fail to load as the structure will have already been loaded the first time.

(Side note: why are we using the same database for main: and ci: ? See FAQ in #354065 (closed))

The fix

We fix this by tracking which database has run db:schema:load already, and skipping if so. This only affects db:schema:load, and not db:schema:load:ci, nor db:schema:load:main.

To reproduce:

1. Setup multiple databases following https://docs.gitlab.com/ee/development/database/multiple_databases.html#development-setup
2. Verify that config/database.yml has main: and ci: has the same database (gitlabhq_test):

development:
  main:
    adapter: postgresql
    encoding: unicode
    database: gitlabhq_development
    host: /Users/tkuah/code/gdk-ee/postgresql
    port: 5432
    pool: 10
    prepared_statements: false
    variables:
      statement_timeout: 120s
  ci:
    adapter: postgresql
    encoding: unicode
    database: gitlabhq_development
    host: /Users/tkuah/code/gdk-ee/postgresql
    port: 5432
    pool: 10
    prepared_statements: false
    variables:
      statement_timeout: 120s

test: &test
  main:
    adapter: postgresql
    encoding: unicode
    database: gitlabhq_test
    host: /Users/tkuah/code/gdk-ee/postgresql
    port: 5432
    pool: 10
    prepared_statements: false
    variables:
      statement_timeout: 120s
  ci:
    adapter: postgresql
    encoding: unicode
    database: gitlabhq_test
    host: /Users/tkuah/code/gdk-ee/postgresql
    port: 5432
    pool: 10
    prepared_statements: false
    variables:
      statement_timeout: 120s

3. Run bundle exec rake db:reset RAILS_ENV=test

Screenshots or screen recordings

Before:

$ bundle exec rake db:reset RAILS_ENV=test
Dropped database 'gitlabhq_test'
Dropped database 'gitlabhq_test'
Created database 'gitlabhq_test'
Database 'gitlabhq_test' already exists
psql:/Users/tkuah/code/gdk-ee/gitlab/db/ci_structure.sql:1: ERROR:  schema "gitlab_partitions_dynamic" already exists
rake aborted!
failed to execute:
psql --set ON_ERROR_STOP=1 --quiet --no-psqlrc --file /Users/tkuah/code/gdk-ee/gitlab/db/ci_structure.sql --single-transaction gitlabhq_test

Please check the output above for any errors and make sure that `psql` is installed in your PATH and has proper permissions.

/Users/tkuah/code/gdk-ee/gitlab/lib/gitlab/database/postgresql_database_tasks/load_schema_versions_mixin.rb:10:in `structure_load'
/Users/tkuah/.rbenv/versions/2.7.5/bin/bundle:23:in `load'
/Users/tkuah/.rbenv/versions/2.7.5/bin/bundle:23:in `<main>'
Tasks: TOP => db:reset => db:setup => db:schema:load
(See full trace by running task with --trace)

After:

$ bundle exec rake db:reset RAILS_ENV=test
Dropped database 'gitlabhq_test'
Dropped database 'gitlabhq_test'
Created database 'gitlabhq_test'
Database 'gitlabhq_test' already exists
Database 'gitlabhq_test' already loaded schema

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

• I have evaluated the MR acceptance checklist for this MR.