pgpool-II的安装及配置
1.源码安装pgpool-II
tar -xvf pgpool-II-4.2.2.tar.gz
cd pgpool-II-4.2.2
./configure --prefix=/usr/package/pgpool-II-4.2.2
make && make install
或者直接yum安装:
# 每台机器安装yum 源
yum install -y https://www.pgpool.net/yum/rpms/4.2/redhat/rhel-7-x86_64/pgpool-II-release-4.2-1.noarch.rpm
# 安装pgpool
yum install -y pgpool-II-pg11-devel.x86_64
systemctl enable pgpool.service //使用服务
# Pgpool-II 4.2 开始所有配置将一样通过/etc/pgpool/下面的pgpool_node_id 区分节点
cat /etc/pgpool-II/pgpool_node_id
2.postgresql 配置流同步
2.1 安装postgresql。
链接: postgresql安装.
2.2 配置修改及开启流同步
主机:
2.2.1 启动并创建同步的用户:
create user repl REPLICATION LOGIN password 'qgzhdc@123';
2.2.2 创建测试数据库
CREATE DATABASE pgpool ;
\c pgpool
CREATE TABLE pgpool (id serial,age bigint,insertTime timestamp default now());
insert into pgpool (age) values (1);
select * from pgpool;
2.2.3 查询数据库是否为主库
select * from pg_is_in_recovery();
如果结果为f,则为主库。
vi /data/pgsql/zhdc01/postgresql.conf
postgresql.conf:
wal_level = replica # 之前版本可能是 hot_standby
max_wal_senders = 1
wal_keep_segments = 500
hot_standby = on
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
pg_hba.conf:
增加备份机的ip
host replication all 172.16.106.62/32 trust
开启备份:select pg_start_backup('backup0001')
关闭备份: select pg_stop_backup()
2.3 备份机初始化
2.3.1 手动复制库
将主机的data目录传到备库的data目录下。
将data目录下的数据远程拷贝到备库的data目录下
备份机:
postgresql.conf等文件是从主机复制过来的,不需要单独配置。
删除postmaster.pid文件 rm -rf postmaster.pid
增加recovery.conf文件
standby_mode='on'
recovery_target_timeline = 'latest'
primary_conninfo='host=172.16.106.61 port=5432 user=standby password=standby'
trigger_file='/data/pgsql/qgzhdc01/trigger_node'
常见错误:
1.启动数据库报错,没有权限。
data directory “/data/pgsql/zhdc01” has invalid permissions
2021-02-25 05:04:53.316 EST [12501] DETAIL: Permissions should be
u=rwx (0700) or u=rwx,g=rx (0750).
解决方法:
cd /data/pgsql/
chown -R postgres:postgres zhdc01
chmod -R 0700 zhdc01
2.3.2 使用 pg_basebackup 命令
去各个从节点执行命令:
pg_basebackup -h 主节点ip -p 5432 -U repl -Fp -Xs -Pv -R -D /data/pgsql/qgzhdc
#-h 启动的主库数据库地址 -p 主库数据库端口
#-U 流复制用户 -w 不使用密码验证
#-Fp 备份输出正常的数据库目录 -Xs 使用流复制的方式进行复制
#-Pv 输出复制过程的详细信息 -R 为备库创建recovery.conf文件
#-D 指定创建的备库的数据库目录
修改/data/pgsql/zhdc01/recovery.conf,在primary_conninfo 中增加
application_name=slave1 (slave1、slave2)
2.4 postgres 免密互信
在postgres用户新建配置文件
vim /home/postgres/.pgpass
172.16.106.61:5432:replication:repl:123456
172.16.106.62:5432:replication:repl:123456
172.16.106.63:5432:replication:repl:123456
172.16.106.61:5432:postgres:postgres:123456
172.16.106.62:5432:postgres:postgres:123456
172.16.106.63:5432:postgres:postgres:123456
chown -R postgres:postgres /home/postgres/
chmod 700 /home/postgres/.pgpass
2.5 linux postgres用户免密登录
做免密登录需要先给postgres用户设置密码:
passwd postgres
# 然后输入密码
root、postgres用户 所有服务器节点在
/root/.ssh(root用户) /home/postgres/.ssh (postgres用户) 下执行:
ssh-keygen -t rsa -f id_rsa_pgpool
ssh-copy-id -i id_rsa_pgpool.pub postgres@172.16.106.60
ssh-copy-id -i id_rsa_pgpool.pub postgres@172.16.106.61
ssh-copy-id -i id_rsa_pgpool.pub postgres@172.16.106.62
3.postgres 插件安装
3.1 pgpool-regclass
如果你在使用 PostgreSQL 8.0 或之后的版本,强烈推荐在需要访问的 PostgreSQL 中安装 pgpool_regclass 函数,因为它被 pgpool-II 内部使用。 如果不这样做,在不同的 schema 中处理相同的表名会出现问题(临时表不会出问题)。
cd /usr/local/pgpool-II-4.1.4/src/sql/pgpool-regclass
make && make install
psql -U postgres -f pgpool-regclass.sql template1
# 下面的没有执行测试
ln -s /usr/package/pgpool-II-4.1.4/src/sql/pgpool-regclass/* /usr/package/pgsql/11/lib/
3.2 pgpool-recovery
pgpool在线恢复会使用该函数。
cd /usr/package/pgpool-II-4.2.2/src/sql/pgpool-recovery
make && make install
psql -U postgres -f pgpool-recovery.sql template1
cp /usr/package/pgpool-II-4.2.2/etc/recovery_1st_stage.sample /data/pgsql/qgzhdc/recovery_1st_stage
修改其中配置:
PRIMARY_NODE_HOST=$(/usr/bin/sudo /usr/sbin/ifconfig eth0 | grep "inet " | awk '{print $2}')
PGHOME=/usr/package/pgsql/11
ARCHIVEDIR=/data/pgsql/archivedir
REPLUSER=repl
cp /usr/package/pgpool-II-4.2.2/etc/pgpool_remote_start.sample /data/pgsql/qgzhdc/pgpool_remote_start
# 修改其中的
PGHOME=/usr/package/pgsql/11
3.3 insert_lock
如果你在复制模式中使用了 insert_lock ,强烈推荐建立 pgpool_catalog.insert_lock 表,用于互斥。 到现在为止,insert_lock 还能够工作。但是,在这种情况下,pgpool-II 需要锁定插入的目标表。 这种行为和 pgpool-II 2.2 和 2.3 系列类似。由于表锁与 VACUUM 冲突,所以 INSERT 操作可能因而等待很长时间。
cd /usr/package/pgpool-II-4.2.2/src/sql/
psql -U postgres -f insert_lock.sql template1
4.pgpool-II的配值及启动
4.1.pgpool-II的相关配置
建议将用到的 *.config.sample 复制并重命名,然后修改重命名后的文件。
具体参数配置参考pgpool官网: https://www.pgpool.net/docs/latest/en/html/example-cluster.html.
4.1.1 pcp.conf
cp /usr/package/etc/pcp.conf.sample /usr/package/etc/pcp.conf
pg_md5 your_password
可以生成MD5密码。然后复制到pcp.conf
pcp.conf格式如下:
username:[md5 encrypted password]
或者直接使用命令pg_md5 -p
生成
4.1.2 pgpool 登录账号密码
pg_md5 -p -m -u postgres pool_passwd
pg_md5 -p -m -u pgcheck pool_passwd
# 输入数据库密码
4.1.1 pgpool.conf
cd /usr/package/pgpool-II-4.2.2/etc
cp pgpool.conf.sample-stream pgpool.conf
首先创建socker_dir目录:
mkdir -p /var/run/postgresql
chown -R postgres:postgres /var/run/postgresql
# 通用设置
listen_addresses = '*'
port = 9999 # 端口
socket_dir = '/var/run/postgresql'
pcp_socket_dir = '/var/run/postgresql'
# 流复制模式
backend_clustering_mode ='streaming_replication'
# 流复制检查
sr_check_user = 'pgcheck'
sr_check_password = '123456'
# 设置postgres
backend_hostname0 = '172.16.106.61'
backend_port0 = 5433
backend_weight0 = 1
backend_data_directory0 = '/data/masterdata'
backend_hostname1 = '172.16.106.62'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/data/slavedata'
backend_hostname2 = '172.16.106.63'
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/data/slavedata'
#日志记录
# 需要先创建文件夹 mkdir -p /data/pgsql/pgpool/pgpool_log
log_statement = on
log_per_node_statement = on
logdir = '/usr/local/pgpool/log'
log_connections = on
log_hostname = on
pid_file_name = '/usr/local/pgpool/pgpool.pid'
debug_level = 1
sr_check_user = 'postgres'
delay_threshold = 10000
# 修改后 注意 netmask 需要根据服务器配置
if_up_cmd = '/usr/bin/sudo /usr/sbin/ifconfig eth0:0 inet $_IP_$ netmask 255.255.255.0'
if_down_cmd = '/usr/bin/sudo /usr/sbin/ifconfig eth0:0 down'
arping_cmd = '/usr/bin/sudo /usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I eth0'
# 配置wd_escalation_command 修改escalation.sh文件 cp /usr/package/pgpool-II-4.2.2/etc/escalation.sh.sample /usr/package/pgpool-II-4.2.2/etc/escalation.sh
wd_escalation_command = '/usr/package/pgpool-II-4.2.2/etc/escalation.sh'
# Executes this command at escalation on new active pgpool.
# (change requires restart)
# pgpool 集群设置
use_watchdog = on # 开启看门狗
delegate_IP = '172.16.106.60' # 虚拟ip 多台时才会创建
hostname0 = '172.16.106.61'
wd_port0 = 9000
pgpool_port0 = 9999
hostname1 = '172.16.106.62'
wd_port1 = 9000
pgpool_port1 = 9999
hostname2 = '172.16.106.63'
wd_port2 = 9000
pgpool_port2 = 9999
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30
# 故障转移配置执行命令
failover_command ='/usr/package/pgpool-II-4.2.2/etc/pgpool-II/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'
follow_primary_command ='/usr/package/pgpool-II-4.2.2/etc/follow_primary.sh %d %h %p %D %m %H %M %P %r %R'
# pgpool在线恢复配置
recovery_user = 'postgres' # Online recovery user 服务器用户
recovery_password = '123456' # Online recovery password
recovery_1st_stage_command = 'recovery_1st_stage'
# 健康检查
# 主节点创建pgpool 健康检测用户CREATE USER pgcheck WITH PASSWORD '123456';GRANT pg_monitor TO pgcheck;
health_check_period = 5
health_check_timeout = 30
health_check_user = 'pgcheck'
health_check_password = '123456'
health_check_max_retries = 3
4.2启动与关闭
pgpool
启动
-D 丢弃pgpool_status文件,并且不恢复以前的状态。
pgpool -n -d > /tmp/pgpool.log 2>&1 &
启动并记录日志
pgpool stop
// 等待所有连接关闭
pgpool -m fast stop
// 强制关闭
5.pgpool 测试
5.1 在线恢复测试
pcp_recovery_node -h 172.16.106.60 -p 9898 -U pgcheck -n 1
成功返回信息:
pcp_recovery_node – Command Successful
注意:
如果recovery_1st_stage 命令出错,通过postgres 主节点日志(/data/pgsql/qgzhdc/)查询可能存在的问题 PRIMARY_NODE_HOST=$(/usr/bin/sudo /usr/sbin/ifconfig eth0 | grep "inet " | awk ‘{print $2}’)未获取到,可能是postgres用户无sudo免密,需要在/etc/sudoers 中添加:
postgres ALL=(ALL) NOPASSWD: ALL
5.2 pgpool 主从切换
pcp_watchdog_info -h 172.16.106.60 -p 9898 -U pgcheck
返回信息:
3 YES 172.16.106.61:9999 Linux host-10-18-34-147 172.16.106.61
172.16.106.61:9999 Linux host-172.16.106.61 172.16.106.61 9999 9000 4 LEADER
172.16.106.62:9999 Linux host-172.16.106.62 172.16.106.62 9999 9000 7 STANDBY
172.16.106.63:9999 Linux host-172.16.106.63 172.16.106.63 9999 9000 7 STANDBY
停止pgpool leader主节点,验证是否自动切换。