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
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. 
.
Cheers,
Salim
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
Hi @nazdaq,
Please make sure the gitlab.rb file has 0600 permission and you are running the command as sudo user.
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:~$
@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
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?
-
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
@nazdaq Please list the roles from postgres and confirm what access the user gitlab has. You can do it with a \du command.
ostgres=# \du
List of roles
Role name | Attributes | Member of
-----------±-----------------------------------------------------------±----------
gitlab | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
@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.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
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
@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?
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
@nazdaq ok. Let us do some testing…use a user postgres instead of gitlab to see if It can go through.
postgres replace in gitlab?
yes use postgres user and make sure you have the it’s password.
1 Like
By postgres user reconfigure success bt how can I understand gitlab use remote DB? is there any query ?
@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 
1 Like