postgresql热备并使用pgpool实现高可用
前言：本文主要是介绍postgresql的流复制，并使用pgpool实现高可用，主机IP（192.168.163.129）、备机（192.168.163.130）
1.安装依赖（主机，备机） root用户
在一台有网的电脑上先下载好所需的依赖 yum install --downloadonly --downloaddir=/tmp/ zlib-devel readline-devel gcc-c++ -y 然后将下载的rpm全部拷贝到装postgresql电脑上，我这里打包好了所有的依赖和9.6.10的安装包，一起上传到服务器，有需要的自己下（链接：https://pan.baidu.com/s/1nDUcIdiMSXLrByH3S9YTNg 提取码：gxrj）
解压文件并安装依赖
tar -zxvf postgresql.tar.gz
进入到dependencies目录下执行
rpm -Uvh --force --nodeps *.rpm
2.关闭firewalld（主机，备机） root用户
关闭firewalld
systemctl stop firewalld
systemctl disable firewalld
3.编译安装（主机，备机） root用户
解压postgresqlan安装文件
tar -zxvf postgresql-9.6.10.tar.gz
创建安装目录
mkdir -p /usr/local/pgsql
检查安装环境
cd /opt/postgresql/postgresql-9.6.10
./configure --prefix=/usr/local/pgsql
编译
make world
安装
make world install
4.创建postgres用户来安装postgresql（主机，备机） root用户
创建用户组和用户
groupadd postgres useradd -g postgres postgres passwd postgres
在/usr/local/pgsql下创建数据目录data,log,archive
mkdir -p /usr/local/pgsql/data
mkdir -p /usr/local/pgsql/log
mkdir -p /usr/local/pgsql/archive
chmod 700 /usr/local/pgsql/archive
chown -R postgres:postgres /usr/local/pgsql
5.配置环境变量（主机，备机） root用户
配置环境变量
vim /etc/profile
#postgresql
export PGDATA=/usr/local/pgsql/data
export PGHOME=/usr/local/pgsql
export PATH=$PGHOME/bin:$PATH
使环境变量生效 source /etc/profile
6.初始化主机数据库（主机） postgres用户
切换到postgres用户
su - postgres
到安装包目录下执行
cd /opt/postgresql/postgresql-9.6.10
initdb
7.启动pgsql服务并配置主从关系（主机） postgres用户
启动服务 pg_ctl -D /usr/local/pgsql/data -l /usr/local/pgsql/log/postgresql.log start
查看服务进程
ps -ef|grep postgres
用自带的工具连接数据库并修改postgres用户密码
psql
\password
创建热备用户
create role replica login replication encrypted password 'replica';
使用\q退出数据库
修改配置文件
vim /usr/local/pgsql/data/postgresql.conf
按照下面进行配置修改
listen_addresses = '*'
port=5432
wal_level = hot_standby #wal归档复制级别为hot_standby异步流复制
max_wal_senders = 2 #多少server就填多少
wal_keep_segments = 10 #指定pg_xlog目录保存的wal log的最小数量。每个文件一般是16M
hot_standby = on
archive_mode = on
archive_command = 'cp %p /usr/local/pgsql/archive/%f'
wal_log_hints = on
vim /usr/local/pgsql/data/pg_hba.conf
按照如下进行配置修改
创建recovery.done文件
这个文件在安装postgresql时，会生成到/usr/local/pgsql/share/postgresql目录下，名字是recovery.conf.sample。
我们复制并修改名称为recovery.conf并放置在/usr/local/pgsql/data目录下
cp /usr/local/pgsql/share/recovery.conf.sample /usr/local/pgsql/data/recovery.conf
vim /usr/local/pgsql/data/recovery.conf
按照如下进修配置修改(host是主库的ip，后面高可用的时候需要改动）
#开启备库
standby_mode = 'on'
#连接主库
primary_conninfo = 'host=192.168.163.129 port=5432 user=replica password=replica'
restore_command = 'cp /usr/local/pgsql/archive/%f %p'
重启数据库
pg_ctl -D /usr/local/pgsql/data -l /usr/local/pgsql/log/postgresql.log restart
可以使用数据库连接工具远程连接了
8. 启动备机数据库（备机） postgres用户
切换到postgres用户
su - postgres
从主机备份数据（-h是主机的IP）
pg_basebackup -h 192.168.163.129 -p 5432 -U replica -F p -P -D /usr/local/pgsql/data
这里要求输入数据库备份用户的密码，我这里是replica
将备份过来的recovery.done文件改名为recovery.conf
mv recovery.done recovery.conf
切换到root用户执行(用postgres备份过来的data下的文件权限不是700）
chmod 700 -R /usr/local/pgsql/data
然后切回到postgres用户启动从数据库
su - postgres
pg_ctl -D /usr/local/pgsql/data/ -l /usr/local/pgsql/log/postgresql.log start
查看主机postgres进程
查看备机postgres进程
9.配置SSH互信 （主机，备机）
使用Pgpool-II的自动故障转移和在线恢复；需要 pgpool 服务免密码在各个机器上执行；以及后续在在线恢复功能。 生成密钥对（root用户，postgres用户都操作下） ssh-keygen -t rsa (连续三次回车，即在本地生成了公钥和私钥，不设置密码，默认存储在 ~/.ssh目录下）
将所有公钥id_rsa.pub 追加到服务端 ~/.ssh/authorized_keys 文件中
为了保证名称一致冲突，将所有备机的id_rsa.pub重新命名(建议是：id_rsa_主机名_用户名.pub)
cp id_rsa.pub id_rsa_node2_postgres.pub
我这里使用scp传输（这里是需要输入主机root用户的密码）
scp id_rsa_node2_postgres.pub root@192.168.163.129:~/.ssh
在主机操作
cat id_rsa.pub >> ~/.ssh/authorized_keys
cat id_rsa_node2_postgres.pub >> ~/.ssh/authorized_keys
chmod 700 ~/.ssh
chmod 600 ~/.ssh/authorized_keys
把主机的authorized_keys传回到各个备机
scp authorized_keys root@192.168.163.130:~/.ssh
在各个备机整理权限
chmod 700 ~/.ssh
chmod 600 ~/.ssh/authorized_keys
测试：ssh 用户名@ip （这里不需要输入密码就能连接过去）
10，pgpool配置主从切换 root用户
先自行下载pgpool安装文件,上传到服务器（我这里上传到主机服务器，也可以是其他服务器），插件下载地址（链接：https://pan.baidu.com/s/1_iJgNEN9O7Zmnu9UIH20yw 提取码：gxrj）
解压pgpool-II-4.3.3.tar.gz
tar -zxvf pgpool-II-4.3.3.tar.gz
创建安装目录
mkdir -p /usr/local/pgpool
cd pgpool-II-4.3.3
./configure --prefix=/usr/local/pgpool
编译安装文件
make
make install
配置环境变量
vim /etc/profile
#pgpool
export PGPOOLHOME=/usr/local/pgpool
export PATH=$PGPOOLHOME/bin:$PATH
根据pgpool自带的配置文件模板设置对应的配置文件
设置 pcp 的管理用户/密码文件 pcp.conf(我这里就直接用配置文件中的postgres用户和密码(服务器的用户），因为我postgres用户的密码就是postgres,这里的密码是通过/usr/local/pgpool/bin/pg_md5生成的，这个工具是pgpool自带的）
设置pool_hba.conf
生成 pool_passwd（需要自行新建该文件）
vim pool_passwd
添加以下内容（这个是主数据库中postgres用户的用户名和密码（root），可以在数据库查询到：select rolname,rolpassword from pg_authid; 也可以通过pg_md5生成）
postgres:md52a29a4f7eb0a98abca0992ca3fb555b6
设置pgpool.conf(这个配置文件也有模板，我这里就直接新建一个文件）
vim pgpool.conf
添加以下内容：
listen_addresses = '*'
port = 9999
socket_dir = '/usr/local/pgpool'
pcp_listen_addresses = '*'
pcp_port = 9898
pcp_socket_dir = '/usr/local/pgpool'
backend_hostname0 = '192.168.163.129' #主库
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/usr/local/pgsql/data' #数据库data位置
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_hostname1 = '192.168.163.130' #从库
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/usr/local/pgsql/data' #数据库data位置
backend_flag1 = 'ALLOW_TO_FAILOVER'
enable_pool_hba = on
pool_passwd = 'pool_passwd'
pid_file_name = '/usr/local/pgpool/pgpool.pid' ##pid文件位置
logdir = '/usr/local/pgpool/logs' ##日志位置
replication_mode = off
# 开启读写分离
load_balance_mode = on
master_slave_mode = on
master_slave_sub_mode = 'stream'
# Streaming
sr_check_period = 0
sr_check_user = 'replica'
sr_check_password = 'replica'
# HEALTH CHECK 健康检查
health_check_period = 10
health_check_timeout = 20
health_check_user = 'replica' ##主库创建的用户
health_check_password = 'replica' ##密码
failover_command = '/usr/local/pgpool/failover.sh %d %H %R'
设置failover.sh（这个文件的路径与上面pgpool.conf中failover_command的一致）
vim /usr/local/pgpool/failover.sh
添加以下内容
#!/bin/sh
FAILED_NODE_ID=$1
NEW_MASTER_HOST=$2
PGDATA=$3
if [ $FAILED_NODE_ID -eq 0 ]; then
echo "promote standby node"
ssh postgres@$NEW_MASTER_HOST "/usr/local/pgsql/bin/pg_ctl -D $PGDATA promote"
fi
修改failover.sh权限为可执行
chmod +x /usr/local/pgpool/failover.sh
启动pgpool
pgpool -n -D >> /usr/local/pgpool/pgpool.log 2>&1 &
11，连接pgpool测试
使用navicate连接pgpool
后面自行在这个连接中做测试，新增一个是数据库，看主库和从库中是否能查询到新增的数据库；
12，模拟主库宕机操作
先查看主库备库的状态
在主库中直接关闭数据库 su - postgres pg_ctl stop
再查看主库从库的状态
13，宕机的主库以从库的角色回到集群中
先把主库中的data目录备份
mv data data_bak
通过pg_basebackup全量从新主库备份数据（也可以不备份掉原来的data，用pg_rewind -D /usr/local/pgsql/data --source-server='host=192.168.163.129 port=5432 user=postgres dbname=postgres password=root' -P来统一两个库的时间线）
pg_basebackup -h 192.168.163.130 -p 5432 -U replica -F p -P -D /usr/local/pgsql/data
把recovery.done改名为recovery.conf 然后修改里面的主库ip
mv /usr/local/pgsqp/data/recovery.done /usr/local/pgsqp/data/recovery.conf
vim /usr/local/pgsqp/data/recovery.conf
重启宕机数据库
pg_ctl -D /usr/local/pgsql/data/ -l /usr/local/pgsql/log/postgresql.log start
修改pgpool.conf(主库从库ip互换，其他配置不动）
vim /usr/local/pgpool/etc/pgpool.conf
重启pgpool
pgpool -m fast stop
pgpool -n -D >> /usr/local/pgpool/pgpool.log 2>&1 &
作者：zengwb 转载请注明原文链接：https://www.cnblogs.com/zengwb/p/17517934.html
