Remote DB setup into GITLAB

nazdaq · June 6, 2020, 4:23pm

I need to setup remote DB into my GitLab v12.10.3. my PostgreSQL v10.12 . My GitLab DB name “gitlabhq_production”, PostgreSQL DB user “gitlab” password “gitlab”. Plz, suggest to me how can I configure and check it.
Thanks

salim-rashid · June 7, 2020, 1:13pm

Hi @nazdaq,

Please go through below documentation from Gitlab website. Start from the section with a title " Using a non-packaged PostgreSQL database management server" you’ll find all the information you want.

Also I’ve seen the PostgreSQL 11 is recommended for Gitlab version 12.10, you might want to check it out. .

https://docs.gitlab.com/omnibus/settings/database.html#using-a-non-packaged-postgresql-database-management-server

Cheers,
Salim

nazdaq · June 10, 2020, 7:38am

I have given this config in gitlab.rb file

Disable the built-in Postgres

postgresql[‘enable’] = false

Fill in the connection details for database.yml

gitlab_rails[‘db_adapter’] = ‘postgresql’
gitlab_rails[‘db_encoding’] = ‘utf8’

gitlab_rails[‘db_host’] = ‘10...*’
gitlab_rails[‘db_port’] = 5432
gitlab_rails[‘db_username’] = ‘gitlab’
gitlab_rails[‘db_password’] = ‘gitlab’

but when I have run gitlab-ctl reconfigure get this error. Could u help me?

Running handlers:
There was an error running gitlab-ctl reconfigure:

bash[migrate gitlab-rails database] (gitlab::database_migrations line 54) had an error: Mixlib::ShellOut::ShellCommandFailed: Expected process to exit with [0], but received ‘1’
---- Begin output of “bash” “/tmp/chef-script20200610-27063-ojhet” ----
STDOUT: psql:/opt/gitlab/embedded/service/gitlab-rails/db/structure.sql:3: NOTICE: extension “plpgsql” already exists, skipping
psql:/opt/gitlab/embedded/service/gitlab-rails/db/structure.sql:5: ERROR: permission denied to create extension “pg_trgm”
HINT: Must be superuser to create this extension.
rake aborted!
failed to execute:
psql -v ON_ERROR_STOP=1 -q -X -f /opt/gitlab/embedded/service/gitlab-rails/db/structure.sql gitlabhq_production

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

/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:53:in block (3 levels) in <top (required)>' /opt/gitlab/embedded/bin/bundle:23:in load’
/opt/gitlab/embedded/bin/bundle:23:in `’
Tasks: TOP => db:structure:load
(See full trace by running task with --trace)
STDERR:
---- End output of “bash” “/tmp/chef-script20200610-27063-ojhet” ----
Ran “bash” “/tmp/chef-script20200610-27063-ojhet” returned 1

salim-rashid · June 10, 2020, 8:16am

Hi @nazdaq,

Please make sure the gitlab.rb file has 0600 permission and you are running the command as sudo user.

nazdaq · June 10, 2020, 8:35am

Still same
Running handlers:
There was an error running gitlab-ctl reconfigure:

bash[migrate gitlab-rails database] (gitlab::database_migrations line 54) had an error: Mixlib::ShellOut::ShellCommandFailed: Expected process to exit with [0], but received ‘1’
---- Begin output of “bash” “/tmp/chef-script20200610-9301-7c4fg” ----
STDOUT: psql:/opt/gitlab/embedded/service/gitlab-rails/db/structure.sql:3: NOTICE: extension “plpgsql” already exists, skipping
psql:/opt/gitlab/embedded/service/gitlab-rails/db/structure.sql:5: ERROR: permission denied to create extension “pg_trgm”
HINT: Must be superuser to create this extension.
rake aborted!
failed to execute:
psql -v ON_ERROR_STOP=1 -q -X -f /opt/gitlab/embedded/service/gitlab-rails/db/structure.sql gitlabhq_production

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

/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:53:in block (3 levels) in <top (required)>' /opt/gitlab/embedded/bin/bundle:23:in load’
/opt/gitlab/embedded/bin/bundle:23:in `’
Tasks: TOP => db:structure:load
(See full trace by running task with --trace)
STDERR:
---- End output of “bash” “/tmp/chef-script20200610-9301-7c4fg” ----
Ran “bash” “/tmp/chef-script20200610-9301-7c4fg” returned 1

Running handlers complete
Chef Client failed. 0 resources updated in 37 seconds
test@ubuntu:~$

salim-rashid · June 10, 2020, 8:57am

@nazdaq to create the pg_trgm the user must have full access to the database (Postgres) and I think the one you are using (Gitlab) does not have that access. You can test this by using a “postgres” user which by default has full access to the Postgres database. This is also documented on the below link…

https://docs.gitlab.com/charts/advanced/external-db/index.html

nazdaq · June 10, 2020, 9:02am

sudo -i -u postgres psql -c “CREATE DATABASE gitlabhq_production;”
sudo -i -u postgres psql -c “CREATE USER gitlab WITH password ‘gitlab’;”
sudo -i -u postgres psql -c “GRANT ALL privileges ON DATABASE gitlabhq_production TO gitlab;”

I have use this command for postgresl and gitlab has full permission. what can I do?

nazdaq · June 10, 2020, 9:05am

global.psql.ssl.secret :
global.psql.ssl.serverCA :
global.psql.ssl.clientCertificate :
global.psql.ssl.clientKey

But I dont know where I can find or generat this ssl files

salim-rashid · June 10, 2020, 9:36am

@nazdaq Please list the roles from postgres and confirm what access the user gitlab has. You can do it with a \du command.

nazdaq · June 10, 2020, 5:08pm

ostgres=# \du
List of roles
Role name | Attributes | Member of
-----------±-----------------------------------------------------------±----------
gitlab | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

salim-rashid · June 11, 2020, 3:59am

@nazdaq assing superuser permission to a gitlab user and run again the command. To assign superuser permission write ```
ALTER USER gitlab WITH SUPERUSER;

Verify that gitlab user has superuser access by running the \du command and again before doing the gitlab-ctl reconfigure and see it ti works.

nazdaq · June 11, 2020, 4:34am

Assalamualaikum brother,

1.Plz see my gitlab.rb config

#####################################

Disable the built-in Postgres

postgresql[‘enable’] = false

Fill in the connection details for database.yml

gitlab_rails[‘db_adapter’] = ‘postgresql’
gitlab_rails[‘db_encoding’] = ‘utf8’
gitlab_rails[‘db_host’] = ‘10...*’
gitlab_rails[‘db_port’] = 5432
gitlab_rails[‘db_username’] = ‘gitlab’
gitlab_rails[‘db_password’] = ‘gitlab’

2.this files has 0600 permission “-rw-------”

3.Postgres
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------±-----------------------------------------------------------±----------
gitlab | Superuser | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

4.After run by sudo user still the facing the error

Running handlers:
There was an error running gitlab-ctl reconfigure:

bash[migrate gitlab-rails database] (gitlab::database_migrations line 54) had an error: Mixlib::ShellOut::ShellCommandFailed: Expected process to exit with [0], but received ‘1’
---- Begin output of “bash” “/tmp/chef-script20200611-2825-1ejeufs” ----
STDOUT: psql:/opt/gitlab/embedded/service/gitlab-rails/db/structure.sql:3: NOTICE: extension “plpgsql” already exists, skipping
psql:/opt/gitlab/embedded/service/gitlab-rails/db/structure.sql:5: ERROR: permission denied to create extension “pg_trgm”
HINT: Must be superuser to create this extension.
rake aborted!
failed to execute:
psql -v ON_ERROR_STOP=1 -q -X -f /opt/gitlab/embedded/service/gitlab-rails/db/structure.sql gitlabhq_production

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

/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:53:in block (3 levels) in <top (required)>' /opt/gitlab/embedded/bin/bundle:23:in load’
/opt/gitlab/embedded/bin/bundle:23:in `’
Tasks: TOP => db:structure:load
(See full trace by running task with --trace)
STDERR:
---- End output of “bash” “/tmp/chef-script20200611-2825-1ejeufs” ----
Ran “bash” “/tmp/chef-script20200611-2825-1ejeufs” returned 1

Running handlers complete
Chef Client failed. 9 resources updated in 01 minutes 04 seconds

Did u see anyting missing from my gitlab.rb file?

Thanks

nazdaq · June 11, 2020, 4:42am

But when I add this line gitlab.rb files “gitlab_rails[‘auto_migrate’] = false” then reconfigure success. sudo gitlab-ctl status command show below and GUI doesn’t appear

test@naztech:~$ sudo gitlab-ctl status
run: alertmanager: (pid 4932) 110s; run: log: (pid 4931) 110s
run: gitaly: (pid 3143) 856s; run: log: (pid 3142) 856s
run: gitlab-exporter: (pid 4867) 113s; run: log: (pid 4866) 113s
run: gitlab-workhorse: (pid 4834) 123s; run: log: (pid 4833) 123s
run: grafana: (pid 4974) 108s; run: log: (pid 4949) 109s
run: logrotate: (pid 4222) 377s; run: log: (pid 1751) 50895s
run: nginx: (pid 4848) 116s; run: log: (pid 4847) 116s
run: node-exporter: (pid 4858) 114s; run: log: (pid 4857) 115s
run: prometheus: (pid 4900) 111s; run: log: (pid 4899) 111s
run: redis: (pid 3094) 862s; run: log: (pid 3093) 862s
run: redis-exporter: (pid 4889) 112s; run: log: (pid 4888) 112s
run: sidekiq: (pid 5338) 0s; run: log: (pid 4824) 125s
run: unicorn: (pid 5315) 10s; run: log: (pid 4804) 127s

salim-rashid · June 11, 2020, 5:02am

@nazdaq the gitlab.rb configs are file. This is so weird!Can you tell me more about your environment for this setup?are doing the installation from source code or using the Omnibus package?

nazdaq · June 11, 2020, 5:45am

I have used omibus. Gitlab.rb is a big file and all lines are comment .Its a fresh install. I have configured only this part rest are in comment

Disable the built-in Postgres

postgresql[‘enable’] = false

Fill in the connection details for database.yml

gitlab_rails[‘db_adapter’] = ‘postgresql’
gitlab_rails[‘db_encoding’] = ‘utf8’
gitlab_rails[‘db_host’] = ‘10.33.255.146’
gitlab_rails[‘db_port’] = 5432
gitlab_rails[‘db_username’] = ‘gitlab’
gitlab_rails[‘db_password’] = ‘gitlab’

Suppose my Omnibus git install v 12.10.3 whose IP 10.x.x.A
postgresql ip 10.x.x.B
PostgreSQL
11.8 (Ubuntu 11.8-1.pgdg18.04+1)
PostgreSQL JDBC Driver
42.1.1

DB: gitlabhq_production
user: gitlab
user: gitlab

salim-rashid · June 11, 2020, 6:13am

@nazdaq ok. Let us do some testing…use a user postgres instead of gitlab to see if It can go through.

nazdaq · June 11, 2020, 6:39am

postgres replace in gitlab?

salim-rashid · June 11, 2020, 6:44am

yes use postgres user and make sure you have the it’s password.

nazdaq · June 11, 2020, 7:09am

By postgres user reconfigure success bt how can I understand gitlab use remote DB? is there any query ?

salim-rashid · June 11, 2020, 7:16am

@nazdaq the config file gitlab.rb tells Gitlab to use that remote DB. I think you can query the database and check which tables have been created. Maybe you can run SELECT * FROM gitlabhq_production