Skip to content

Create Praefect database objects with Omnibus

Hossein Pursultani requested to merge 6039-create-praefect-db into master

This MR is blocked by #5897

What does this MR do?

This MR automates Praefect database and user creation.

Testing

For testing on a single node use the following Docker compose file:

version: "3.9"

services:
  postgresql:
    image: '${TEST_IMAGE}:${TEST_TAG}'
    environment:
      GITLAB_OMNIBUS_CONFIG: |
        # Disable all other services
        gitlab_exporter['enable'] = false
        sidekiq['enable'] = false
        unicorn['enable'] = false
        puma['enable'] = false
        registry['enable'] = false
        gitaly['enable'] = false
        gitlab_workhorse['enable'] = false
        nginx['enable'] = false
        prometheus_monitoring['enable'] = false
        redis['enable'] = false

        # Prevent database connections during 'gitlab-ctl reconfigure'
        gitlab_rails['rake_cache_clear'] = false
        gitlab_rails['auto_migrate'] = false

        postgresql['enable'] = true
        postgresql['listen_address'] = '0.0.0.0'

        postgresql['pgbouncer_user'] = '${PGBOUNCER_SQL_USER}'
        postgresql['pgbouncer_user_password'] = '${PGBOUNCER_SQL_PASSWORD_HASH}'
        postgresql['sql_user'] = '${GITLAB_SQL_USER}'
        postgresql['sql_user_password'] = '${GITLAB_SQL_PASSWORD_HASH}'
        postgresql['md5_auth_cidr_addresses'] = %w(0.0.0.0/0)

        praefect['manage_database'] = true
        praefect['pgbouncer_user'] = '${PRAEFECT_SQL_USER}'
        praefect['pgbouncer_user_password'] = '${PRAEFECT_SQL_PASSWORD_HASH}'
        praefect['sql_database'] = 'praefect_production'

  pgbouncer:
    image: '${TEST_IMAGE}:${TEST_TAG}'
    depends_on:
      - postgresql
    environment:
      GITLAB_OMNIBUS_CONFIG: |
        gitlab_exporter['enable'] = false
        sidekiq['enable'] = false
        unicorn['enable'] = false
        puma['enable'] = false
        registry['enable'] = false
        gitaly['enable'] = false
        gitlab_workhorse['enable'] = false
        nginx['enable'] = false
        prometheus_monitoring['enable'] = false
        redis['enable'] = false

        # Prevent database connections during 'gitlab-ctl reconfigure'
        gitlab_rails['rake_cache_clear'] = false
        gitlab_rails['auto_migrate'] = false

        pgbouncer['enable'] = true
        pgbouncer['databases'] = {
          praefect_production: {
            user: '${PGBOUNCER_SQL_USER}',
            password: '${PGBOUNCER_SQL_PASSWORD_HASH}',
            host: 'postgresql'
          },
          praefect_production_direct: {
            user: '${PGBOUNCER_SQL_USER}',
            password: '${PGBOUNCER_SQL_PASSWORD_HASH}',
            host: 'postgresql',
            pool_mode: 'session',
            dbname: 'praefect_production'
          },
          gitlabhq_production: {
            user: '${PGBOUNCER_SQL_USER}',
            password: '${PGBOUNCER_SQL_PASSWORD_HASH}',
            host: 'postgresql'
          }
        }

  gitaly-1:
    image: '${TEST_IMAGE}:${TEST_TAG}'
    environment:
      GITLAB_OMNIBUS_CONFIG: |
        # Disable all other services
        postgresql['enable'] = false
        redis['enable'] = false
        nginx['enable'] = false
        grafana['enable'] = false
        puma['enable'] = false
        sidekiq['enable'] = false
        gitlab_workhorse['enable'] = false
        prometheus_monitoring['enable'] = false

        # Enable only the Gitaly service
        gitaly['enable'] = true

        # Enable Prometheus if needed
        prometheus['enable'] = true

        # Prevent database connections during 'gitlab-ctl reconfigure'
        gitlab_rails['rake_cache_clear'] = false
        gitlab_rails['auto_migrate'] = false

        gitaly['listen_addr'] = '0.0.0.0:8075'
        gitaly['prometheus_listen_addr'] = '0.0.0.0:9236'
        gitaly['auth_token'] = '${PRAEFECT_INTERNAL_TOKEN}'

        gitlab_shell['secret_token'] = '${GITLAB_SHELL_SECRET_TOKEN}'
        gitlab_rails['internal_api_url'] = 'http://web'

        git_data_dirs({
          "gitaly-1" => {
            "path" => "/var/opt/gitlab/git-data"
          },
          "gitaly-2" => {
            "path" => "/var/opt/gitlab/git-data"
          }
        })

  gitaly-2:
    image: '${TEST_IMAGE}:${TEST_TAG}'
    environment:
      GITLAB_OMNIBUS_CONFIG: |
        # Disable all other services
        postgresql['enable'] = false
        redis['enable'] = false
        nginx['enable'] = false
        grafana['enable'] = false
        puma['enable'] = false
        sidekiq['enable'] = false
        gitlab_workhorse['enable'] = false
        prometheus_monitoring['enable'] = false

        # Enable only the Gitaly service
        gitaly['enable'] = true

        # Enable Prometheus if needed
        prometheus['enable'] = true

        # Prevent database connections during 'gitlab-ctl reconfigure'
        gitlab_rails['rake_cache_clear'] = false
        gitlab_rails['auto_migrate'] = false

        gitaly['listen_addr'] = '0.0.0.0:8075'
        gitaly['prometheus_listen_addr'] = '0.0.0.0:9236'
        gitaly['auth_token'] = '${PRAEFECT_INTERNAL_TOKEN}'

        gitlab_shell['secret_token'] = '${GITLAB_SHELL_SECRET_TOKEN}'
        gitlab_rails['internal_api_url'] = 'http://web'

        git_data_dirs({
          "gitaly-1" => {
            "path" => "/var/opt/gitlab/git-data"
          },
          "gitaly-2" => {
            "path" => "/var/opt/gitlab/git-data"
          }
        })

  praefect:
    image: '${TEST_IMAGE}:${TEST_TAG}'
    depends_on:
      - pgbouncer
      - gitaly-1
      - gitaly-2
    environment:
      GITLAB_OMNIBUS_CONFIG: |
        # Disable all other services
        postgresql['enable'] = false
        redis['enable'] = false
        nginx['enable'] = false
        alertmanager['enable'] = false
        prometheus['enable'] = false
        grafana['enable'] = false
        puma['enable'] = false
        sidekiq['enable'] = false
        gitlab_workhorse['enable'] = false
        gitaly['enable'] = false
        
        # Enable only the Praefect service
        praefect['enable'] = true
        
        # Prevent database connections during 'gitlab-ctl reconfigure'
        gitlab_rails['rake_cache_clear'] = false
        gitlab_rails['auto_migrate'] = false

        praefect['listen_addr'] = '0.0.0.0:2305'

        # Enable Prometheus metrics access to Praefect. You must use firewalls
        # to restrict access to this address/port.
        praefect['prometheus_listen_addr'] = '0.0.0.0:9652'

        praefect['auth_token'] = '${PRAEFECT_EXTERNAL_TOKEN}'

        praefect['database_host'] = 'pgbouncer'
        praefect['database_port'] = 6432
        praefect['database_user'] = '${PRAEFECT_SQL_USER}'
        praefect['database_password'] = '${PRAEFECT_SQL_PASSWORD}'
        praefect['database_dbname'] = 'praefect_production'
        praefect['database_direct_dbname'] = 'praefect_production_direct'

        # Disable SSL for test purpose
        praefect['database_sslmode'] = 'disable'

        praefect['virtual_storages'] = {
          'default' => {
            'nodes' => {
              'gitaly-1' => {
                'address' => 'tcp://gitaly-1:8075',
                'token'   => '${PRAEFECT_INTERNAL_TOKEN}',
              },
              'gitaly-2' => {
                'address' => 'tcp://gitaly-2:8075',
                'token'   => '${PRAEFECT_INTERNAL_TOKEN}'
              }
            }
          }
        }

  web:
    image: '${TEST_IMAGE}:${TEST_TAG}'
    environment:
      GITLAB_OMNIBUS_CONFIG: |
        # Disable all other services
        postgresql['enable'] = false
        grafana['enable'] = false
        gitlab_exporter['enable'] = false
        gitaly['enable'] = false

        gitlab_rails['db_host'] = 'pgbouncer'
        gitlab_rails['db_port'] = 6432
        gitlab_rails['db_password'] = '${GITLAB_SQL_PASSWORD}'

        git_data_dirs({
          "default" => {
            "gitaly_address" => "tcp://praefect:2305",
            "gitaly_token" => '${PRAEFECT_EXTERNAL_TOKEN}'
          }
        })

        gitlab_shell['secret_token'] = '${GITLAB_SHELL_SECRET_TOKEN}'

And run the following commands:

alias pg-password-md5="echo 'require \"openssl\"; puts Digest::MD5.hexdigest(\"#{ARGV[1]}#{ARGV[0]}\")' | ruby -- - "

export GITLAB_SQL_USER='gitlab'
export GITLAB_SQL_PASSWORD='secret'
export GITLAB_SQL_PASSWORD_HASH="$(pg-password-md5 ${GITLAB_SQL_USER} ${GITLAB_SQL_PASSWORD})"

export PGBOUNCER_SQL_USER='pgbouncer'
export PGBOUNCER_SQL_PASSWORD='secret'
export PGBOUNCER_SQL_PASSWORD_HASH="$(pg-password-md5 ${PGBOUNCER_SQL_USER} ${PGBOUNCER_SQL_PASSWORD})"

export PRAEFECT_SQL_USER='praefect'
export PRAEFECT_SQL_PASSWORD='secret'
export PRAEFECT_SQL_PASSWORD_HASH="$(pg-password-md5 ${PRAEFECT_SQL_USER} ${PRAEFECT_SQL_PASSWORD})"

export PRAEFECT_INTERNAL_TOKEN='praefect-internal-token'
export PRAEFECT_EXTERNAL_TOKEN='praefect-external-token'
export GITLAB_SHELL_SECRET_TOKEN='gitlab-shell-token'

# The registry requires authentication
export TEST_IMAGE='registry.gitlab.com/gitlab-org/build/omnibus-gitlab-mirror/gitlab-ee'

# Update with the latest tag
export TEST_TAG='13.12.3-rfbranch.316613747.923985ed-0'

docker-compose up -d

# Start praefect container again if needs be.
# Sometimes pgbouncer doesn't start before praefect and it fails.
docker-compose start praefect


# Check pgbouncer
# Enter ${PGBOUNCER_SQL_PASSWORD} on prompt

docker-compose exec pgbouncer gitlab-ctl pgb-console

# Check `show pools; show clients;`

# Check praefect connection pools

# Without session pooling
# Enter ${PRAEFECT_SQL_PASSWORD} on prompt
docker-compose exec praefect /opt/gitlab/embedded/bin/psql \
    -h pgbouncer -p 6432 -U ${PRAEFECT_SQL_USER} -d praefect_production

# With session pooling
# Enter ${PRAEFECT_SQL_PASSWORD} on prompt
docker-compose exec praefect /opt/gitlab/embedded/bin/psql \
    -h pgbouncer -p 6432 -U ${PRAEFECT_SQL_USER} -d praefect_production_direct

# Check praefect database connectivity

docker-compose exec praefect /opt/gitlab/embedded/bin/praefect -config /var/opt/gitlab/praefect/config.toml sql-ping

Try the web application.

Related issues

Related to #6039 (closed)

Checklist

See Definition of done.

For anything in this list which will not be completed, please provide a reason in the MR discussion

Required

  • Merge Request Title, and Description are up to date, accurate, and descriptive
  • MR targeting the appropriate branch
  • MR has a green pipeline on GitLab.com
  • Pipeline is green on dev.gitlab.org if the change is touching anything besides documentation or internal cookbooks
  • trigger-package has a green pipeline running against latest commit

Expected (please provide an explanation if not completing)

  • Test plan indicating conditions for success has been posted and passes
  • Documentation created/updated
  • Tests added
  • Integration tests added to GitLab QA
  • Equivalent MR/issue for the GitLab Chart opened
Edited by Hossein Pursultani

Merge request reports