Working with the bundled PgBouncer service · Gitlab 中文文檔

# 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)建議的修復程序.