GeXiangDong

精通Java、SQL、Spring的拼写，擅长Linux、Windows的开关机

0%

PostgreSQL主从+负载均衡

发表于 更新于

PostgreSQL的读写分离是通过2部分实现的

  1. 主从集群（可以一主多从）
  2. Pgpool-II 代理来分发读写操作到不同服务器

Pgpool-II 功能强大，还可实现一个表/数据库分发到不同服务器上等等，此处不做讨论

环境

三台服务器（可以把以下3个服务安装到一台，此处三台仅仅是为表达清晰）

  • Pgpool-II ， IP： 192.168.1.10
  • PostgreSQL 主服务器， IP: 192.168.1.11
  • PostgreSQL 从服务器, IP: 192.168.1.12

集群环境搭建

安装PostgreSQL

分别在主从服务器（192.168.1.11, 192.168.1.12) 上安装PostgreSQL, apt install postgresql

配置

以下操作主从两个服务器都需要

设置可以从网络访问

修改 postgresql.conf 文件（如果apt安装且版本12，在/etc/postgresql/12/main/目录下）

更改 listen_addresses 修改为*或者自己的IP地址

设置主从复制用户访问

修改 pg_hba.conf，在末尾增加一行：

1
host    replication     all             192.168.1.1/24         trust

这行是信任本地网络上所有复制用操作的连接

在从服务器上设置

在从服务器（192.168.1.12）上配置复制， 修改 postgresql.conf 文件，找到 primary_conninfo, hot_standby, wal_level 并修改他们

1
2
3
primary_conninfo = 'host=192.168.1.11 port=5432 user=postgres password='
hot_standby = on 
wal_level = replica

这几项不设置也行，下面的pg_basebackup命令会在main目录下生成相应的配置

192.168.1.11是主数据库服务器IP，postgres是连接用户名，password是密码，因为我设置了信任所有本地网络用户，所以这里没密码，这行要根据自己的环境修改。

停止从服务器上的postgresql服务 

1
systemctl stop postgresql

进入数据库所在目录（如果ubuntu, apt安装12版，在 /var/lib/postgresql/12），并删除main目录，之后运行pg_basebackup命令从主服务器拷贝数据库文件，最后别忘了把新的main目录改成postgresql的用户

1
2
3
4
cd /var/lib/postgrewsql/12
rm -r main
pg_basebackup -h 192.168.1.11 -p 5432 -U postgres  -Fp -Xs -Pv -R -D ./main
chown -R postgres:postgres main

如果pg_basebackup命令成功，会出现类似提示

pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/4000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_8071"
32514/32514 kB (100%), 1/1 tablespace                                         
pg_basebackup: write-ahead log end point: 0/4000100
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: base backup completed

并且在main目录下有一个 standby.signal 文件

之后启用postgresql

1
systemctl start postgresql

这时，可以用psql连接到从服务器，进行以下测试了，正确的结果应该是查询的sql都没问题，更新的sql不能执行了，会提示事务是只读的。
也可以执行select pg_is_in_recovery();应该返回T，表示服务器处于恢复模式

其他配置（和主从无关）

仅仅为主从可忽略此部分

postgresql.conf

  • max_connections 默认为100，可根据需要增大最大连接数；从服务器的最大连接数不能小于主服务器，否则会启动失败
  • shared_buffers 在独立的数据库服务器上一般设置为物理内存的1/4
  • work_mem增大则对单个SQL的排序等效果明显

pg_hba.conf

可根据需要设置允许网络上某个用户连接及连接方式，例如把本地网络连接都允许且设置为trust，则可免去每次都输入密码的麻烦

负载均衡，Pgpool-II 搭建

以下操作都在 Pgpool-II （192.168.1.10）服务器上操作

安装

1
apt install pgpool2

配置

修改 /etc/pgpool2/pgpool.conf 文件

数据库节点配置

默认代理端口号是5433，可根据需要修改

增加2个节点，找到 backend_hostname0部分，按照下面内容修改

1
2
3
4
5
6
7
8
9
10
11
12
13
backend_hostname0 = '192.168.1.11'
backend_port0 = 5432
backend_weight0 = 1

backend_hostname1 = '192.168.1.12'
backend_port1 = 5432
backend_weight1 = 1

replication_mode = off

load_balance_mode = on

master_slave_mode = on

replication_mode 要设置成off。如果设置on，是由pgpool做复制操作，它会把所有更改SQL发送到每个节点，每个节点都执行一份，当有节点离线时，不会自动在上线后重发，需要设置很多东西，比较麻烦，所以还是用上面的postgresql自己内置的replication机制。

load_balance_mode 可是实现查询操作的负载均衡，如果off，所有sql都在backend0执行，如果on则根据 backend_weight权重比例来分配查询sql

master_slave_mode 指定服务器采用主从模式，backend0为主

pgpool 管理用配置

pgpool通过一系列pcp命令来维护/管理各个backend node（节点），默认管理端口时9898，如需修改在pgpool.conf内修改 pcp_port， 也可修改pcp_listen_addresses来允许远程管理

首先需要给pcp管理创建一个用户，通过修改pcp.conf实现，例如增加一行

1
pcp:ac5c74b64b4b8352ef2f181affb5ac2a

则增加了一个用户，用户名pcp，密码sql（密码存储的是md5值）

backend node 的状态

节点的状态维护并不是默认自动的，需要使用pcp命令来管理

除了backend0外，其他新增节点默认是 unused 状态，需要用pcp_attach_node改变状态

1
pcp_attach_node -U pcp -p 9898 -h 127.0.0.1 1

pcp_attach_node 用于把节点状态变为可用，最后一个1是节点序号，在pgpool.conf设置backend时的需要，也可以用sql命令show pool_nodes查到。

成功执行pcp_attach_node后，如果连接节点没问题，节点状态会变更为 up，否则为down

每个down/unused状态不会自动变为up

up也不会自动变为down，这在分发sql时就会出现问题，把sql分发到了已经停止服务的节点不是我们想要的，为了避免需要配置 health check

在pgpool.conf内做如下配置

1
2
3
4
5
health_check_period = 10    #单位秒
health_check_timeout = 20
health_check_user = 'postgres'
health_check_pass = ''
health_check_database = 'tempdb'

需要把用户名、密码、数据等配制成自己环境所需，之后重启，pgpool就可以检查每个节点的状态是否依旧在线了，如果已经连接不到，则会自动变成down状态

(down, unused) => up 通过 pcp_attach_node

pgpool 配置中的2个参数

num_init_children

num_init_children 这个项目是启动多少个线程来接收pgpool的客户端（一般是我门开发的程序）的连接，当连接数超过这个值时，客户端就会等待直到有连接释放出来或者超时。

如果不使用pgpool时，直接连接postgresql，与postgresql的max_connections类似（在这个数目内都能正常连接，超过这个数就不正常了，区别是超过这个数后，连接postgresql的自动断了，连接pgpool的会进入等待队列）

默认值：100

如果自己的程序中使用了连接池，那么连接池的最大连接数不要超过这个值。

max_pool

这个有点特殊，和postgresql中没有对应的项目。它是每一个连接（num_init_children的每个线程）可以对应的往pgpool的 backend node的连接池数，如果使用多个数据库，这个一般设置成常用的数据库数目。让每个线程都有到每个数据库的连接池，这样速度最快。但要注意 num_init_children * max_pool 应该小于等于 backend node的max_connections

默认值：4

如果服务器上仅仅有一个数据库是用于生产的，那么改成1是个不错的选择。

常见问题

ERROR: canceling statement due to conflict with recovery

ERROR: canceling statement due to conflict with recovery
Detail: User query might have needed to see row versions that must be removed.

原因

产生这个错误的原因是：sql在从服务器上执行，但执行时间较长，执行过程中从服务器从主服务器同步数据，更改了sql中的数据。

解决办法

以下几个办法都行

  1. 优化sql，缩短执行时间，这是最好的办法
  2. 设置 hot_standby_feedback = on
  3. 增大 max_standby_archive_delay max_standby_streaming_delay 两个参数的值
  4. 把查询放到主服务器执行

2和3都有些缺点，2会增加主服务器负担，3会加大从服务器和主服务器之间数据差异。
法4则失去了负载均衡的价值。