Skip to content

Working with the bundle PgBouncer service

As part of its High Availability stack, GitLab Premium includes a bundled version of PgBouncer that can be managed through /etc/gitlab/gitlab.rb. PgBouncer is used to seamlessly migrate database connections between servers in a failover scenario. Additionally, it can be used in a non-HA setup to pool connections, speeding up response time while reducing resource usage.

In a HA setup, it's recommended to run a PgBouncer node separately for each database node with an internal load balancer (TCP) serving each accordingly.

Operations

Running PgBouncer as part of an HA GitLab installation

  1. Make sure you collect CONSUL_SERVER_NODES, CONSUL_PASSWORD_HASH, and PGBOUNCER_PASSWORD_HASH before executing the next step.

  2. One each node, edit the /etc/gitlab/gitlab.rb config file and replace values noted in the # START user configuration section as below:

    # Disable all components except PgBouncer and Consul agent
roles ['pgbouncer_role']

# Configure PgBouncer
pgbouncer['admin_users'] = %w(pgbouncer gitlab-consul)

# Configure Consul agent
consul['watchers'] = %w(postgresql)

# START user configuration
# Please set the real values as explained in Required Information section
# Replace CONSUL_PASSWORD_HASH with with a generated md5 value
# Replace PGBOUNCER_PASSWORD_HASH with with a generated md5 value
pgbouncer['users'] = {
  'gitlab-consul': {
    password: 'CONSUL_PASSWORD_HASH'
  },
  'pgbouncer': {
    password: 'PGBOUNCER_PASSWORD_HASH'
  }
}
# Replace placeholders:
#
# Y.Y.Y.Y consul1.gitlab.example.com Z.Z.Z.Z
# with the addresses gathered for CONSUL_SERVER_NODES
consul['configuration'] = {
  retry_join: %w(Y.Y.Y.Y consul1.gitlab.example.com Z.Z.Z.Z)
}
#
# END user configuration

    NOTE: Note: pgbouncer_role was introduced with GitLab 10.3.

  3. Run gitlab-ctl reconfigure

  4. Create a .pgpass file so Consul is able to reload PgBouncer. Enter the PGBOUNCER_PASSWORD twice when asked:

    gitlab-ctl write-pgpass --host 127.0.0.1 --database pgbouncer --user pgbouncer --hostuser gitlab-consul

PgBouncer Checkpoint

  1. Ensure each node is talking to the current master:

    gitlab-ctl pgb-console # You will be prompted for PGBOUNCER_PASSWORD

    If there is an error psql: ERROR: Auth failed after typing in the password, ensure you previously generated the MD5 password hashes with the correct format. The correct format is to concatenate the password and the username: PASSWORDUSERNAME. For example, Sup3rS3cr3tpgbouncer would be the text needed to generate an MD5 password hash for the pgbouncer user.

  2. Once the console prompt is available, run the following queries:

    show databases ; show clients ;

    The output should be similar to the following:

            name         |  host       | port |      database       | force_user | pool_size | reserve_pool | pool_mode | max_connections | current_connections
---------------------+-------------+------+---------------------+------------+-----------+--------------+-----------+-----------------+---------------------
 gitlabhq_production | MASTER_HOST | 5432 | gitlabhq_production |            |        20 |            0 |           |               0 |                   0
 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    | pgbouncer | pgbouncer           | active  | 127.0.0.1      | 56846 | 127.0.0.1  |       6432 | 2017-08-21 18:09:59 | 2017-08-21 18:10:48 | 0x22b3880 |      |          0 |
(2 rows)

Configure the internal load balancer

If you're running more than one PgBouncer node as recommended, then at this time you'll need to set up a TCP internal load balancer to serve each correctly. This can be done with any reputable TCP load balancer.

As an example here's how you could do it with HAProxy:

global
    log /dev/log local0
    log localhost local1 notice
    log stdout format raw local0

defaults
    log global
    default-server inter 10s fall 3 rise 2
    balance leastconn

frontend internal-pgbouncer-tcp-in
    bind *:6432
    mode tcp
    option tcplog

    default_backend pgbouncer

backend pgbouncer
    mode tcp
    option tcp-check

    server pgbouncer1 <ip>:6432 check
    server pgbouncer2 <ip>:6432 check
    server pgbouncer3 <ip>:6432 check

Refer to your preferred Load Balancer's documentation for further guidance.

Running PgBouncer as part of a non-HA GitLab installation

  1. Generate PGBOUNCER_USER_PASSWORD_HASH with the command gitlab-ctl pg-password-md5 pgbouncer

  2. Generate SQL_USER_PASSWORD_HASH with the command gitlab-ctl pg-password-md5 gitlab. We'll also need to enter the plaintext SQL_USER_PASSWORD later

  3. 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

  4. Run gitlab-ctl reconfigure

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

  5. 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'
  }
}

  6. Run gitlab-ctl reconfigure

  7. On the node running Unicorn, 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'

  8. Run gitlab-ctl reconfigure

  9. At this point, your instance should connect to the database through PgBouncer. If you are having issues, see the Troubleshooting section

Enable Monitoring

Introduced in GitLab 12.0.

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

  1. 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'

  2. Run sudo gitlab-ctl reconfigure to compile the configuration.

Interacting with PgBouncer

Administrative console

As part of Omnibus GitLab, we provide a command gitlab-ctl pgb-console to automatically connect to the PgBouncer administrative console. Please see the PgBouncer documentation for detailed instructions on how to interact with the console.

To start a session, run

# gitlab-ctl pgb-console
Password for user pgbouncer:
psql (9.6.8, server 1.7.2/bouncer)
Type "help" for help.

pgbouncer=#

The password you will be prompted for is the PGBOUNCER_USER_PASSWORD

To get some basic information about the instance, run

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)

Troubleshooting

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

# 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.