Working with the bundled PgBouncer service (PREMIUM SELF)

PgBouncer is used to seamlessly migrate database connections between servers in a failover scenario. Additionally, it can be used in a non-fault-tolerant setup to pool connections, speeding up response time while reducing resource usage.

GitLab Premium includes a bundled version of PgBouncer that can be managed through /etc/gitlab/gitlab.rb.

PgBouncer as part of a fault-tolerant GitLab installation

This content has been moved to a new location.

PgBouncer as part of a non-fault-tolerant GitLab installation

Generate PGBOUNCER_USER_PASSWORD_HASH with the command gitlab-ctl pg-password-md5 pgbouncer
Generate SQL_USER_PASSWORD_HASH with the command gitlab-ctl pg-password-md5 gitlab. Enter the plaintext SQL_USER_PASSWORD later.

On your database node, ensure the following is set in your /etc/gitlab/gitlab.rb

postgresql['pgbouncer_user_password'] = 'PGBOUNCER_USER_PASSWORD_HASH'
postgresql['sql_user_password'] = 'SQL_USER_PASSWORD_HASH'
postgresql['listen_address'] = 'XX.XX.XX.Y' # Where XX.XX.XX.Y is the ip address on the node postgresql should listen on
postgresql['md5_auth_cidr_addresses'] = %w(AA.AA.AA.B/32) # Where AA.AA.AA.B is the IP address of the pgbouncer node

Run gitlab-ctl reconfigure

NOTE: If the database was already running, it needs to be restarted after reconfigure by running gitlab-ctl restart postgresql.

On the node you are running PgBouncer on, make sure the following is set in /etc/gitlab/gitlab.rb

pgbouncer['enable'] = true
pgbouncer['databases'] = {
  gitlabhq_production: {
    host: 'DATABASE_HOST',
    user: 'pgbouncer',
    password: 'PGBOUNCER_USER_PASSWORD_HASH'
  }
}

Run gitlab-ctl reconfigure

On the node running Puma, make sure the following is set in /etc/gitlab/gitlab.rb

gitlab_rails['db_host'] = 'PGBOUNCER_HOST'
gitlab_rails['db_port'] = '6432'
gitlab_rails['db_password'] = 'SQL_USER_PASSWORD'

Run gitlab-ctl reconfigure
At this point, your instance should connect to the database through PgBouncer. If you are having issues, see the Troubleshooting section

Backups

Do not backup or restore GitLab through a PgBouncer connection: it causes a GitLab outage.

Enable Monitoring

Introduced in GitLab 12.0.

If you enable Monitoring, it must be enabled on all PgBouncer servers.

Create/edit /etc/gitlab/gitlab.rb and add the following configuration:

# Enable service discovery for Prometheus
consul['enable'] = true
consul['monitoring_service_discovery'] =  true

# Replace placeholders
# Y.Y.Y.Y consul1.gitlab.example.com Z.Z.Z.Z
# with the addresses of the Consul server nodes
consul['configuration'] = {
   retry_join: %w(Y.Y.Y.Y consul1.gitlab.example.com Z.Z.Z.Z),
}

# Set the network addresses that the exporters will listen on
node_exporter['listen_address'] = '0.0.0.0:9100'
pgbouncer_exporter['listen_address'] = '0.0.0.0:9188'

Run sudo gitlab-ctl reconfigure to compile the configuration.

Administrative console

As part of Omnibus GitLab, a command is provided to automatically connect to the PgBouncer administrative console. See the PgBouncer documentation for detailed instructions on how to interact with the console.

To start a session run the following and provide the password for the pgbouncer user:

sudo gitlab-ctl pgb-console

To get some basic information about the instance:

pgbouncer=# show databases; show clients; show servers;
        name         |   host    | port |      database       | force_user | pool_size | reserve_pool | pool_mode | max_connections | current_connections
---------------------+-----------+------+---------------------+------------+-----------+--------------+-----------+-----------------+---------------------
 gitlabhq_production | 127.0.0.1 | 5432 | gitlabhq_production |            |       100 |            5 |           |               0 |                   1
 pgbouncer           |           | 6432 | pgbouncer           | pgbouncer  |         2 |            0 | statement |               0 |                   0
(2 rows)

 type |   user    |      database       | state  |   addr    | port  | local_addr | local_port |    connect_time     |    request_time     |    ptr    | link
| remote_pid | tls
------+-----------+---------------------+--------+-----------+-------+------------+------------+---------------------+---------------------+-----------+------
+------------+-----
 C    | gitlab    | gitlabhq_production | active | 127.0.0.1 | 44590 | 127.0.0.1  |       6432 | 2018-04-24 22:13:10 | 2018-04-24 22:17:10 | 0x12444c0 |
|          0 |
 C    | gitlab    | gitlabhq_production | active | 127.0.0.1 | 44592 | 127.0.0.1  |       6432 | 2018-04-24 22:13:10 | 2018-04-24 22:17:10 | 0x12447c0 |
|          0 |
 C    | gitlab    | gitlabhq_production | active | 127.0.0.1 | 44594 | 127.0.0.1  |       6432 | 2018-04-24 22:13:10 | 2018-04-24 22:17:10 | 0x1244940 |
|          0 |
 C    | gitlab    | gitlabhq_production | active | 127.0.0.1 | 44706 | 127.0.0.1  |       6432 | 2018-04-24 22:14:22 | 2018-04-24 22:16:31 | 0x1244ac0 |
|          0 |
 C    | gitlab    | gitlabhq_production | active | 127.0.0.1 | 44708 | 127.0.0.1  |       6432 | 2018-04-24 22:14:22 | 2018-04-24 22:15:15 | 0x1244c40 |
|          0 |
 C    | gitlab    | gitlabhq_production | active | 127.0.0.1 | 44794 | 127.0.0.1  |       6432 | 2018-04-24 22:15:15 | 2018-04-24 22:15:15 | 0x1244dc0 |
|          0 |
 C    | gitlab    | gitlabhq_production | active | 127.0.0.1 | 44798 | 127.0.0.1  |       6432 | 2018-04-24 22:15:15 | 2018-04-24 22:16:31 | 0x1244f40 |
|          0 |
 C    | pgbouncer | pgbouncer           | active | 127.0.0.1 | 44660 | 127.0.0.1  |       6432 | 2018-04-24 22:13:51 | 2018-04-24 22:17:12 | 0x1244640 |
|          0 |
(8 rows)

 type |  user  |      database       | state |   addr    | port | local_addr | local_port |    connect_time     |    request_time     |    ptr    | link | rem
ote_pid | tls
------+--------+---------------------+-------+-----------+------+------------+------------+---------------------+---------------------+-----------+------+----
--------+-----
 S    | gitlab | gitlabhq_production | idle  | 127.0.0.1 | 5432 | 127.0.0.1  |      35646 | 2018-04-24 22:15:15 | 2018-04-24 22:17:10 | 0x124dca0 |      |
  19980 |
(1 row)

Procedure for bypassing PgBouncer

Some database changes have to be done directly, and not through PgBouncer.

Read more about the affected tasks: database restores and GitLab upgrades.

To find the primary node, run the following on a database node:
```
sudo gitlab-ctl repmgr cluster show
```
Edit /etc/gitlab/gitlab.rb on the application node you're performing the task on, and update gitlab_rails['db_host'] and gitlab_rails['db_port'] with the database primary's host and port.
Run reconfigure:
```
sudo gitlab-ctl reconfigure
```

Once you've performed the tasks or procedure, switch back to using PgBouncer:

Change back /etc/gitlab/gitlab.rb to point to PgBouncer.
Run reconfigure:
```
sudo gitlab-ctl reconfigure
```

Troubleshooting

In case you are experiencing any issues connecting through PgBouncer, the first place to check is always the logs:

sudo gitlab-ctl tail pgbouncer

Additionally, you can check the output from show databases in the administrative console. In the output, you would expect to see values in the host field for the gitlabhq_production database. Additionally, current_connections should be greater than 1.

Message: `LOG: invalid CIDR mask in address`

See the suggested fix in Geo documentation.

Message: `LOG: invalid IP mask "md5": Name or service not known`