Working with the bundled PgBouncer service

原文：https://docs.gitlab.com/ee/administration/high_availability/pgbouncer.html

Working with the bundled PgBouncer service

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

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

Operations

Running PgBouncer as part of an HA GitLab installation

该内容已移至新位置 .

Running PgBouncer as part of a non-HA GitLab installation

  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

    1. postgresql['pgbouncer_user_password'] = 'PGBOUNCER_USER_PASSWORD_HASH'
    2. postgresql['sql_user_password'] = 'SQL_USER_PASSWORD_HASH'
    3. postgresql['listen_address'] = 'XX.XX.XX.Y' # Where XX.XX.XX.Y is the ip address on the node postgresql should listen on
    4. 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

    1. pgbouncer['enable'] = true
    2. pgbouncer['databases'] = {
    3.   gitlabhq_production: {
    4.     host: 'DATABASE_HOST',
    5.     user: 'pgbouncer',
    6.     password: 'PGBOUNCER_USER_PASSWORD_HASH'
    7.   }
    8. }

  6. Run gitlab-ctl reconfigure

  7. 在运行 Puma 的节点上，确保在/etc/gitlab/gitlab.rb设置了以下/etc/gitlab/gitlab.rb

    1. gitlab_rails['db_host'] = 'PGBOUNCER_HOST'
    2. gitlab_rails['db_port'] = '6432'
    3. gitlab_rails['db_password'] = 'SQL_USER_PASSWORD'

  8. Run gitlab-ctl reconfigure

  9. 此时，您的实例应通过 PgBouncer 连接到数据库. 如果遇到问题，请参阅” 故障排除”部分

Enable Monitoring

在 GitLab 12.0 中引入 .

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

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

    1. # Enable service discovery for Prometheus
    2. consul['enable'] = true
    3. consul['monitoring_service_discovery'] =  true
    5. # Replace placeholders
    6. # Y.Y.Y.Y consul1.gitlab.example.com Z.Z.Z.Z
    7. # with the addresses of the Consul server nodes
    8. consul['configuration'] = {
    9.    retry_join: %w(Y.Y.Y.Y consul1.gitlab.example.com Z.Z.Z.Z),
    10. }
    12. # Set the network addresses that the exporters will listen on
    13. node_exporter['listen_address'] = '0.0.0.0:9100'
    14. pgbouncer_exporter['listen_address'] = '0.0.0.0:9188'

  2. 运行sudo gitlab-ctl reconfigure来编译配置.

Interacting with PgBouncer

Administrative console

作为 Omnibus GitLab 的一部分，我们提供了一个gitlab-ctl pgb-console命令，以自动连接到 PgBouncer 管理控制台. 请参阅PgBouncer 文档以获取有关如何与控制台进行交互的详细说明.

要开始会话，请运行

  1. # gitlab-ctl pgb-console
  2. Password for user pgbouncer:
  3. psql (11.7, server 1.7.2/bouncer)
  4. Type "help" for help.
  6. pgbouncer=#

将提示您输入的密码是 PGBOUNCER_USER_PASSWORD

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

  1. pgbouncer=# show databases; show clients; show servers;
  2.         name         |   host    | port |      database       | force_user | pool_size | reserve_pool | pool_mode | max_connections | current_connections
  3. ---------------------+-----------+------+---------------------+------------+-----------+--------------+-----------+-----------------+---------------------
  4.  gitlabhq_production | 127.0.0.1 | 5432 | gitlabhq_production |            |       100 |            5 |           |               0 |                   1
  5.  pgbouncer           |           | 6432 | pgbouncer           | pgbouncer  |         2 |            0 | statement |               0 |                   0
  6. (2 rows)
  8.  type |   user    |      database       | state  |   addr    | port  | local_addr | local_port |    connect_time     |    request_time     |    ptr    | link
  9. | remote_pid | tls
  10. ------+-----------+---------------------+--------+-----------+-------+------------+------------+---------------------+---------------------+-----------+------
  11. +------------+-----
  12.  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 |
  13. |          0 |
  14.  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 |
  15. |          0 |
  16.  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 |
  17. |          0 |
  18.  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 |
  19. |          0 |
  20.  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 |
  21. |          0 |
  22.  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 |
  23. |          0 |
  24.  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 |
  25. |          0 |
  26.  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 |
  27. |          0 |
  28. (8 rows)
  30.  type |  user  |      database       | state |   addr    | port | local_addr | local_port |    connect_time     |    request_time     |    ptr    | link | rem
  31. ote_pid | tls
  32. ------+--------+---------------------+-------+-----------+------+------------+------------+---------------------+---------------------+-----------+------+----
  33. --------+-----
  34.  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 |      |
  35.   19980 |
  36. (1 row)

Troubleshooting

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

  1. # gitlab-ctl tail pgbouncer

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

Message: LOG: invalid CIDR mask in address

请参阅Geo 文档中建议的修复程序.

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

请参阅Geo 文档中建议的修复程序.