云芯一号教程 - PostgreSQL集群搭建教程 - 极术社区

Pgpool-II是PostgreSQL集群开源实现中比较成功的项目，它是一个位于PostgreSQL服务器和PostgreSQL数据库客户端之间的中间件。对于PostgreSQL客户端来说，Pgpool-II就相当于PostgreSQL服务器；对PostgreSQL服务器来说，Pgpool-II相当于PostgreSQL客户端。
Pgpool-II提供了连接池（Connection Pooling）、复制（Replication）、负载均衡（Load Balancing）、缓存（In Memory Query Cache）、看门狗（Watchdog）、超出限制链接（Limiting Exceeding Connections）等功能，并且与PostgreSQL是解耦合的，因此Pgpool-II可用于搭建任意版本的PostgreSQL数据库主从结构集群。

1.配置新实例
PostgreSQL数据库主从结构集群需要2个实例，PostgreSQL安装时会默认生成main实例，还需要创建一个standby实例，使用5433端口。

  jishu@Jishu:~$ sudo pg_createcluster -e utf8 -p 5433 12 standby 
  Creating new PostgreSQL cluster 12/standby ...
  /usr/lib/postgresql/12/bin/initdb -D /var/lib/postgresql/12/standby --auth-local peer --auth-host md5 --encoding utf8
  ......
  
  fixing permissions on existing directory /var/lib/postgresql/12/standby ... ok
  creating subdirectories ... ok
  selecting dynamic shared memory implementation ... posix
  selecting default max_connections ... 100
  selecting default shared_buffers ... 128MB
  selecting default time zone ... Asia/Chongqing
  creating configuration files ... ok
  running bootstrap script ... ok
  performing post-bootstrap initialization ... ok
  syncing data to disk ... ok
  
  Success. You can now start the database server using:
  
  pg_ctlcluster 12 standby start
  
  Ver Cluster Port Status Owner    Data directory                 Log file
  12  standby 5433 down   postgres /var/lib/postgresql/12/standby /var/log/postgresql/postgresql-12-standby.log

standby实例创建好后，来查看一下实例目录。
先看一下配置文件目录。

  jishu@Jishu:~$ ls /etc/postgresql/12/
  main  standby

standby实例的配置文件已经生成。

再看一下配置数据库目录。

  jishu@Jishu:~$ ls /var/lib/postgresql/12/
  main  standby

standby实例的数据库目录也已经生成。

2.主从配置流复制
流复制是PostgreSQL同步数据的常用方法，其原理为：备库不断的从主库同步相应的数据，并在备库apply每个WAL record。
2.1 修改主库配置
流复制通过修改postgresql.conf里面的参数来设置。

  jishu@Jishu:$ sudo vim /etc/postgresql/12/main/postgresql.conf
  ......      
  wal_level = replica                     
  synchronous_commit = on            #打开流复制模式
  ......
  max_wal_senders = 2                #最多有2个流复制连接
  wal_keep_segments = 16
  wal_sender_timeout = 60s
  synchronous_standby_names = '*'

主库配置文件修改完成后，需要重启主库生效。

  jishu@Jishu:/$ sudo pg_ctlcluster 12 main restart

2.2 配置从库
从库的配置很简单，只需要把主库的数据库文件直接拷贝复制即可。
拷贝前需要把从库停掉，并切换到postgres用户拷贝数据库。

  jishu@Jishu:/$ sudo pg_ctlcluster 12 standby stop  
  jishu@Jishu:/$ su postgres
  postgres@Jishu:/$ pg_basebackup -h 127.0.0.1 -p 5432 -U postgres -Fp -Xs -Pv -R -D /var/lib/postgresql/12/standby

拷贝完成后，在从库的数据库目录下生成standby.signal标志文件，这个文件表示此库为备库，同时生成了postgresql.auto.conf文件中, 该文件的优先级是大于postgresql.conf的。
重启从库。

  postgres@Jishu:~/12$ pg_ctlcluster 12 standby restart

2.3 验证流复制
登录主库，查看流复制情况。

  jishu@Jishu:~$ sudo psql -U postgres -h 127.0.0.1
  psql (12.2 (Ubuntu 12.2-3.pgdg18.04+1), server 12.3 (Ubuntu 12.3-1.pgdg18.04+1))
  ......
  postgres=# select * from pg_stat_replication;
  -[ RECORD 1 ]----+------------------------------
  pid              | 8063
  usesysid         | 10
  usename          | postgres
  application_name | 12/standby
  client_addr      | 127.0.0.1
  client_hostname  | 
  client_port      | 46936
  backend_start    | 2020-06-25 22:03:29.068658+08
  backend_xmin     | 
  state            | streaming
  sent_lsn         | 0/3000148
  write_lsn        | 0/3000148
  flush_lsn        | 0/3000148
  replay_lsn       | 0/3000148
  write_lag        | 
  flush_lag        | 
  replay_lag       | 
  sync_priority    | 1
  sync_state       | sync
  reply_time       | 2020-06-25 22:05:51.300534+08

可以看到从库standby已经连接上, 并且使用的是sync同步模式。
在主库上创建数据来验证流复制功能。

  postgres=# create database testdb;
  CREATE DATABASE
  postgres=# \c testdb
  ......
  You are now connected to database "testdb" as user "postgres".
  testdb=# create table test_table(name text);
  CREATE TABLE
  testdb=# insert into test_table(name) values ('china');
  INSERT 0 1

登录从库查看。

  jishu@Jishu:~$ sudo psql -U postgres -h 127.0.0.1 -p 5433
  Password for user postgres: 
  ......
  
  postgres=# \l
                          List of databases
     Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges   
  -----------+----------+----------+---------+---------+-----------------------
   postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 | 
   template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
             |          |          |         |         | postgres=CTc/postgres
   template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
             |          |          |         |         | postgres=CTc/postgres
   testdb    | postgres | UTF8     | C.UTF-8 | C.UTF-8 | 
  (5 rows)
  
  postgres=# \c testdb
  psql (12.2 (Ubuntu 12.2-3.pgdg18.04+1), server 12.3 (Ubuntu 12.3-1.pgdg18.04+1))
  SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
  You are now connected to database "testdb" as user "postgres".
  testdb=# \d
             List of relations
   Schema |    Name    | Type  |  Owner   
  --------+------------+-------+----------
   public | test_table | table | postgres
  (1 row)
  
  testdb=# select * from test_table;
   name  
  -------
   china
  (1 row)

可以看到主库上创建的数据，已经同步到从库。

3.安装和配置Pgpool-II
3.1 安装Pgpool-II
Pgpool-II依赖postgresql-server-dev，安装非常简单。

  jishu@Jishu:$ sudo apt-get install postgresql-server-dev-12
  jishu@Jishu:$ sudo apt-get install pgpool2

安装完成后，先了解一下Pgpool-II的配置目录/etc/pgpool2下的4个配置文件。


  pcp.conf             集群节点密码配置文件
  pgpool.conf          pgpool2主配置文件
  pool_hba.conf        用户访问验证策略trust/md5配置文件
  pool_passwd          数据库密码管理文件

3.2 配置Pgpool-II
用户访问的验证策略需要和PostgreSQL保持一致，在pool_hba.conf配置文件中修改验证策略。

  jishu@Jishu:/etc/pgpool2$ sudo vim pool_hba.conf 
  .......
  host    all         all         127.0.0.1/32          md5
  host    all         all         0.0.0.0/0             md5

添加Pgpool-II管理器的用户名和密码，用于管理集群。

  #先用md5加密密码   
  jishu@Jishu:/etc/pgpool2$ pg_md5 123456   
  e10adc3949ba59abbe56e057f20f883e
  jishu@Jishu:/etc/pgpool2$ sudo vim pcp.conf 
  ......
  #在pcp.conf文件中添加
  postgres:e10adc3949ba59abbe56e057f20f883e

在Pgpool-II中添加PostgreSQL数据库的用户名和密码，Pgpool-II使用该用户密码来访问真正的数据库。

  jishu@Jishu:/etc/pgpool2$ sudo pg_md5 -p -m -u postgres pool_passwd
  123456

命令执行后，在pool_passwd文件中会添加用户名密码。

修改主配置文件pgpool.conf。

  jishu@Jishu:$ sudo vim  /etc/pgpool2/pgpool.conf
  ......
  #监听端口
  listen_addresses = '*'
  port = 9999
  pcp_listen_addresses = '*'
  pcp_port = 9898
  
  #后台数据库连接信息
  backend_hostname0 = 'localhost'
  backend_port0 = 5432
  backend_weight0 = 1
  backend_data_directory0 = '/var/lib/postgresql/12/main'
  backend_flag0 = 'ALLOW_TO_FAILOVER'
  backend_application_name0 = 'server0'
  
  backend_hostname1 = 'localhost'
  backend_port1 = 5433
  backend_weight1 = 1
  backend_data_directory1 = '/var/lib/postgresql/12/standby'
  backend_flag1 = 'ALLOW_TO_FAILOVER'
  backend_application_name1 = 'server1'
              
  #开启hba验证
  enable_pool_hba = on
  pool_passwd = 'pool_passwd'
  
  #流复制相关配置
  replication_mode = off               #关闭Pgpool-II中复制模式
  load_balance_mode = on               #打开负载均衡 
  master_slave_mode = on               #打开主从模式
  master_slave_sub_mode = 'stream'     #主从之间模式为流传输
  sr_check_period = 5 
  sr_check_user = 'postgres'
  sr_check_password = '123456'
  sr_check_database = 'postgres'
  
  #数据库运行状况检查，以便Pgpool-II执行数据库的主备切换
  health_check_period = 10
  health_check_timeout = 20
  health_check_user = 'postgres'
  health_check_password = '123456'
  health_check_database = 'postgres'
       
  failover_command = '可配置脚本用于处理故障'
 
  #当有多个Pgpool-II节点时，可配置watchdog用于监测Pgpool-II节点状态，发生故障时，其他节点会执行仲裁, 选择从节点成为主节点。
  use_watchdog = off

3.3 添加log文件目录
为Pgpool-II添加log目录。

  jishu@Jishu:/etc/pgpool2$ sudo mkdir -p /var/log/pgpool
  jishu@Jishu:/etc/pgpool2$ sudo touch /var/log/pgpool/pgpool.log
  jishu@Jishu:/etc/pgpool2$ sudo chown -R postgres.postgres /var/log/pgpool/

3.4 验证Pgpool-II
重新启动Pgpool-II，并连接Pgpool-II查看集群状态。

  jishu@Jishu:/etc/pgpool2$ sudo service pgpool2 restart 
  jishu@Jishu:/etc/pgpool2$ sudo psql -h localhost -p 9999 -Upostgres -d postgres
  Password for user postgres: 
  psql (12.2 (Ubuntu 12.2-3.pgdg18.04+1), server 12.3 (Ubuntu 12.3-1.pgdg18.04+1))
  Type "help" for help.
  
  postgres=# show pool_nodes;
   node_id | hostname  | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change  
  ---------+-----------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
   0       | localhost | 5432 | up     | 0.500000  | primary | 0          | true              | 0                 |                   |                        | 2020-06-27 14:55:25
   1       | localhost | 5433 | up     | 0.500000  | standby | 0          | false             | 0                 |                   |                        | 2020-06-27 14:55:25
  (2 rows)

PostgreSQL主从服务器查询正常。配置完成。

云芯一号教程 - PostgreSQL集群搭建教程

推荐阅读

目录