435.md

# Working with the bundled PgBouncer service

> 原文：[https://docs.gitlab.com/ee/administration/high_availability/pgbouncer.html](https://docs.gitlab.com/ee/administration/high_availability/pgbouncer.html)

*   [Operations](#operations)
    *   [Running PgBouncer as part of an HA GitLab installation](#running-pgbouncer-as-part-of-an-ha-gitlab-installation)
    *   [Running PgBouncer as part of a non-HA GitLab installation](#running-pgbouncer-as-part-of-a-non-ha-gitlab-installation)
*   [Enable Monitoring](#enable-monitoring)
    *   [Interacting with PgBouncer](#interacting-with-pgbouncer)
        *   [Administrative console](#administrative-console)
*   [Troubleshooting](#troubleshooting)
    *   [Message: `LOG: invalid CIDR mask in address`](#message-log--invalid-cidr-mask-in-address)
    *   [Message: `LOG: invalid IP mask "md5": Name or service not known`](#message-log--invalid-ip-mask-md5-name-or-service-not-known)

# Working with the bundled PgBouncer service[](#working-with-the-bundled-pgbouncer-service-premium-only "Permalink")

由于它的高可用性堆栈的一部分，GitLab Premium 包含的捆绑版本[PgBouncer](http://www.pgbouncer.org/)可以通过管理`/etc/gitlab/gitlab.rb` . PgBouncer 用于在故障转移方案中在服务器之间无缝迁移数据库连接. 此外，它可以用于非 HA 设置中以建立连接池，从而加快响​​应时间，同时减少资源使用.

在高可用性设置中，建议为每个数据库节点分别运行一个 PgBouncer 节点，并使用一个内部负载均衡器（TCP）相应地为其服务.

## Operations[](#operations "Permalink")

### Running PgBouncer as part of an HA GitLab installation[](#running-pgbouncer-as-part-of-an-ha-gitlab-installation "Permalink")

该内容已移至[新位置](../postgresql/replication_and_failover.html#configuring-the-pgbouncer-node) .

### Running PgBouncer as part of a non-HA GitLab installation[](#running-pgbouncer-as-part-of-a-non-ha-gitlab-installation "Permalink")

1.  使用命令`gitlab-ctl pg-password-md5 pgbouncer`生成 PGBOUNCER_USER_PASSWORD_HASH

2.  使用命令`gitlab-ctl pg-password-md5 gitlab` . 稍后我们还需要输入纯文本 SQL_USER_PASSWORD

3.  在数据库节点上，确保在`/etc/gitlab/gitlab.rb`设置了以下`/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`

    **注意：**如果数据库已经在运行，则需要在重新配置后通过运行`gitlab-ctl restart postgresql`来重新启动数据库.

5.  在运行 PgBouncer 的节点上，确保在`/etc/gitlab/gitlab.rb`设置了以下`/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.  在运行 Puma 的节点上，确保在`/etc/gitlab/gitlab.rb`设置了以下`/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.  此时，您的实例应通过 PgBouncer 连接到数据库. 如果遇到问题，请参阅" [故障排除"](#troubleshooting)部分

## Enable Monitoring[](#enable-monitoring "Permalink")

在 GitLab 12.0 中[引入](https://gitlab.com/gitlab-org/omnibus-gitlab/-/issues/3786) .

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

1.  创建/编辑`/etc/gitlab/gitlab.rb`并添加以下配置：

    ```
    # 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.  运行`sudo gitlab-ctl reconfigure`来编译配置.

### Interacting with PgBouncer[](#interacting-with-pgbouncer "Permalink")

#### Administrative console[](#administrative-console "Permalink")

作为 Omnibus GitLab 的一部分，我们提供了一个`gitlab-ctl pgb-console`命令，以自动连接到 PgBouncer 管理控制台. 请参阅[PgBouncer 文档](https://www.pgbouncer.org/usage.html#admin-console)以获取有关如何与控制台进行交互的详细说明.

要开始会话，请运行

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

pgbouncer=# 
```

将提示您输入的密码是 PGBOUNCER_USER_PASSWORD

要获取有关实例的一些基本信息，请运行

```
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[](#troubleshooting "Permalink")

如果您遇到通过 PgBouncer 连接的任何问题，则首先检查的始终是日志：

```
# gitlab-ctl tail pgbouncer 
```

另外，您可以在[管理控制台中](#administrative-console)检查`show databases`的输出. 在输出中，您期望在`gitlabhq_production`数据库的`host`字段中看到值. 此外， `current_connections`应该大于 1.

### Message: `LOG: invalid CIDR mask in address`[](#message-log--invalid-cidr-mask-in-address "Permalink")

请参阅[Geo 文档中](../geo/replication/troubleshooting.html#message-log--invalid-cidr-mask-in-address)建议的修复程序.

### Message: `LOG: invalid IP mask "md5": Name or service not known`[](#message-log--invalid-ip-mask-md5-name-or-service-not-known "Permalink")

请参阅[Geo 文档中](../geo/replication/troubleshooting.html#message-log--invalid-ip-mask-md5-name-or-service-not-known)建议的修复程序.