# PgBouncer使用最佳实践
#### PgBouncer介绍
PgBouncer是为PostgreSQL提供的轻量级连接池工具，作用如下：
- 能够缓存和PostgreSQL的连接，当有连接请求进来的时候，直接分配空闲进程，而不需要PostgreSQL fork出新进程来建立连接，以节省创建新进程，创建连接的资源消耗。
- 能够有效提高连接的利用率，避免过多的无效连接，导致数据库消耗资源过大，CPU占用过高。
- 对客户端连接进行限制，预防过多或恶意的连接请求。
轻量级体现在：
- 使用libevent进行socket通信，通信效率高。
- C语言编写，效率高，每个连接仅消耗2KB内存。
PgBouncer目前支持三种连接池模型：
- session会话级连接。只有与当客户端的会话结束时，PgBouncer才会收回已分配的连接。
- transaction事务级连接。当事务完成后，PgBouncer会回收已分配的连接。也就是说客户端只是在事务中才能独占此连接，非事务请求没有独享的连接。
- statement语句级连接。任何数据库请求完成后，PgBouncer都会回收连接。此种模式下，客户端不能使用事务，否则会造成数据的不一致。
PgBouncer默认选项是session，建议修改为transaction。
#### 安装配置
在云上部署PgBouncer连接池，需要先[购买弹性云服务器](https://support.huaweicloud.com/qs-ecs/ecs_01_0103.html)。建议选择与后端RDS实例相同VPC、相同子网创建，降低网络通信时延。购买成功后登录ECS进行环境搭建。以Ubuntu操作系统为例，安装PgBouncer步骤如下：
1. 使用Ubuntu官方Apt仓库安装pgbouncer。
   ```
   sudo apt update
   sudo apt install -y pgbouncer
   ```
   
2. 安装完成后，修改pgbouncer配置文件"pgbouncer.ini"。
   配置文件默认位置为 /etc/pgbouncer/pgbouncer.ini。主要配置如下：
   ```
   [databases]
   ; 配置PostgreSQL的连接信息
   * = host=xxx.xxx.xxx.xxx port=5432
   [pgbouncer]
   logfile = /var/log/postgresql/pgbouncer.log
   pidfile = /var/run/postgresql/pgbouncer.pid
   listen_addr = *
   listen_port = 6432
   auth_type = md5
   auth_file = /etc/pgbouncer/userlist.txt
   admin_users = root
   stats_users = stats, postgres                            
   pool_mode = transaction                     
   server_reset_query = DISCARD ALL              
   max_client_conn = 100
   default_pool_size = 20
   ```
   配置文件中各参数含义可参考[PgBouncer官方文档](https://www.pgbouncer.org/config.html)。
   
3. 配置认证用户。 登录PostgreSQL，执行如下SQL语句，查询用户的MD5或SCRAM密文。并写入文件 /etc/pgbouncer/userlist.txt 。
   ```
   SELECT format('"%s" "%s"', usename, passwd) FROM pg_shadow;
   ```
   /etc/pgbouncer/userlist.txt文件内容格式为"username" "password"，示例如下：
   ```
   "root" "****************************" 
   "user1" "****************************"
   ```
   
 
#### 常用操作
- [启动/停止PgBouncer]
  - 启动PgBouncer
    ```
    sudo systemctl restart pgbouncer
    ```
    启动后，可通过**netstat -tunlp \| grep pgbouncer**查看连接池的监听端口，而后进行连接：
    ```
    psql -U root -d postgres -h 127.0.0.1 -p 6432 
    Password for user root: 
    psql (12.13)
    Type "help" for help.
    postgres=> \l
                                       List of databases
       Name    |   Owner   | Encoding |   Collate   |    Ctype    |    Access privileges    
    -----------+-----------+----------+-------------+-------------+-------------------------
     postgres  | pgbouncer | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
     template0 | pgbouncer | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/pgbouncer           
               |           |          |             |             | pgbouncer=CTc/pgbouncer
     template1 | pgbouncer | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/pgbouncer
    ```
    
  
  - 停止PgBouncer
    ```
    sudo systemctl stop pgbouncer
    ```
    
   
- [管理PgBouncer]
  如果修改了一些配置参数，可以不用重启PgBouncer而是通过**reload**使其生效。
  ```
  sudo systemctl reload pgbouncer
  ```
  PgBouncer对外提供了一个虚拟数据库pgbouncer，之所以称为虚拟数据库，是因为它可以提供像PostgreSQL那样的数据库操作界面，但是这个数据库却并不是真实存在的，而是PgBouncer虚拟出来的一个命令行界面。登录虚拟数据库：
  ```
  psql -p 6432 -d pgbouncer
  ```
  登录后可以通过**show help** 查看命令帮助，通过**show clients** 查看客户端连接信息，通过**show pools**查看连接池信息。
#### 实现读写分离示例
PgBouncer并不支持自动解析读写请求，并进行读写分离路由，需要业务侧对读写操作进行区分。
1. 首先，修改配置文件"pgbouncer.ini"配置的数据库信息，添加主库、只读库连接配置。本例中参数设置如下：
   ```
   [databases]
   ;; * = host=127.0.0.1 port=5432
   #配置只读库的连接信息
   mydb_read: host=10.xx.xx.xx port=5432 dbname=postgres user=root password=***
   # 配置主库的连接信息
   mydb_write: host=10.xx.xx.xx port=5432 dbname=postgres user=root password=***
   [pgbouncer]
   logfile = /var/log/pgbouncer/pgbouncer.log
   pidfile = /var/run/pgbouncer/pgbouncer.pid
   listen_addr = *
   listen_port = 6432
   auth_type = md5
   auth_file = /etc/pgbouncer/userlist.txt
   admin_users = postgres
   stats_users = stats, postgres                            
   pool_mode = transaction                     
   server_reset_query = DISCARD ALL 
   max_client_conn = 100
   default_pool_size = 20                    
   ;; resolve: unsupported startup parameter: extra_float_digits
   ;;ignore_startup_parameters = extra_float_digits
   ```
   
2. 验证是否能连接主库和只读数据库。通过psql成功连接只读、主库，可支持业务读写分离。
   ```
   psql -U root -d mydb_write -h 127.0.0.1 -p 6432
   Password for user root:
   psql  (14.6)
   mydb_write=>  SELECT pg_is_in_recovery();
     pg_is_in_recovery
   ----------------------
     f
   (1 row)
   psql -U root -d mydb_read -h 127.0.0.1 -p 6432
   Password for user root:
   psql  (14.6)
   mydb_read=>  SELECT pg_is_in_recovery();
   pg_is_in_recovery
   ----------------------
     t
   (1 row)
   ```