搭建PostgreSQL+pgpool高可用集群_pgpool pguser_super=postgres

1、PostgreSQL数据库安装

注：需要在两台机器上安装POstgreSQL数据库，方便后续操作

1.1、集群机器分配情况

主机名 IP 角色 端口
192.168.70.135 192.168.70.135 PG主节点 5432
192.168.70.135 192.168.70.135 Pgpool主节点 9999
192.168.70.136 192.168.70.136 PG备节点 5432
192.168.70.136 192.168.70.136 pgpool备节点 9999
192.168.70.150 192.168.70.150 VIP 9999

1.2、集成主机环境依赖

yum install -y gcc-c++
yum install -y flex
yum install -y readline-devel
yum install -y zlib-devel
yum install -y bzip2

1.3、POstgreSQL数据库安装

1.3.1、上传PostgreSQL源码包到机器 /tmp 目录

本次PostgreSQL集群搭建使用的数据库版本是10.3，源码包为：postgresql-10.3.tar.bz2，这里可以使用ftp文件传输、也可以使用CRT自带的SFTP方式上传等 。

1.3.2、解压源码包

tar -jxvf postgresql-10.3.tar.bz2

1.3.3、安装、配置数据库

进入解压后的PostgreSQL源码包（/opt/postgres为数据库的安装目录，可以自己定义）
安装数据库：./configure --prefix=/opt/postgres
编译且安装源码：gmake world && gmake install-world

1.3.4、添加用户并设置密码

useradd postgres 添加安装启动管理数据库的普通用户
passwd postgres 设置密码

1.3.5、授权

把安装数据库的目录授权给postgres：chown -R postgres:postgres /opt/postgres
在/opt/postgres目录下新增data文件夹：mkdir -p /opt/postgres/data
修改目录权限为755：chmod 755 /opt/postgres chmod 755 /opt/postgres/data

1.3.6、配置postgres的环境变量

vi /home/postgres/.bash_profile

export PGPORT=5432 # 数据库端口号
export PGDATA=/opt/postgres/data # 数据库数据存放的目录
export LANG=en_US.utf8
export PGHOME=/opt/postgres
exportLD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export DATE=date +"%Y%m%d%H%M"
export PATH=$PGHOME/bin:$PATH:.
export MANPATH=$PGHOME/share/man:$MANPATH
export PGUSER=postgres
export PGHOST=$PGDATA
alias rm=‘rm -i’
alias ll=‘ls -lh’
export PGDATABASE=postgres

1.3.7、检测数据库安装情况

su - postgres (切换postgres用户，此处需要携带-，环境变量复制才生效)
which psql 检查psql数据库管理系统是否安装正常
psql -V 检查psql数据库管理系统是否安装正常
echo $PGDATA 检查psql数据库管理系统是否安装正常
echo $PGHOME 检查psql数据库管理系统是否安装正常

1.3.8、初始化数据库

initdb -D $PGDATA -E UTF8 --locale=C -Upostgres –W 初始化数据库
密码：123456 （可自定义）

1.3.9、修改PostgreSQL配置文件

进入data目录：cd $PGDATA
1）、修改 pg_hba.conf 配置文件，新增内容：
host all all 0.0.0.0/0 md5
2）、修改 postgresql.conf 配置文件，新增内容：
listen_addresses = ‘0.0.0.0’
port = 5432
superuser_reserved_connections = 20
unix_socket_directories = ‘.’
unix_socket_permissions = 0700
tcp_keepalives_idle = 60
tcp_keepalives_interval = 10
tcp_keepalives_count = 10
vacuum_cost_delay = 10
bgwriter_delay = 10ms
synchronous_commit = off
wal_writer_delay = 10ms
logging_collector = on
log_directory = ‘pg_log’
log_min_duration_statement = 60
log_statement = ‘ddl’
log_file_mode = 0600
log_truncate_on_rotation = on
log_filename = ‘postgresql-%d.log’ # #最多保存一个月的日志,每天一个文件
log_rotation_age = 1d
log_rotation_size = 10MB

1.3.10、测试数据库是否安装成功

pg_ctl start 启动数据库
pg_ctl -m fast stop 停止数据库
pg_ctl restart 重启数据库
psql -h 127.0.0.1 -p 5432 -U postgres postgres (psql) 登录数据库

1.4、小结

至此，PostgreSQL数据库安装完毕，并且测试可以连接使用，需要注意以上步骤请在两个Linux系统中安装两套数据库系统，方便后续用来配置双机热备节点

2、节点配置

2.1、主节点配置

进入主节点data目录：cd $PGDATA

2.1.1、修改配置文件

1）、修改 pg_hba.conf 配置文件,添加如下内容:
host replication replica 127.0.0.1/32 md5
host replication replica 0.0.0.0/0 md5
2）、修改 postgresql.conf 配置文件，添加如下内容：
wal_log_hints = on
wal_level = hot_standby
archive_mode = on
archive_command = ‘/bin/date’
max_wal_senders = 10
wal_keep_segments = 256
synchronous_standby_names = ‘*’
hot_standby = on
max_standby_archive_delay = 300s
max_standby_streaming_delay = 300s
wal_receiver_status_interval = 1s
hot_standby_feedback = on
wal_receiver_timeout = 60s

2.1.2、新增数据库角色

重启主节点数据库：pg_ctl restart
连接数据库：psql -h 127.0.0.1 -p 5432 -U postgres postgres
创建用户：create role replica login replication encrypted password ‘replica’;

2.1.3、配置密码文件

1）、vi /home/postgres/.pgpass 添加如些内容：
192.168.70.136:5432:replication:replica:replica
2）、设置400权限
chmod 400 .pgpass

2.1.4、配置流复制配置文件

1）、进入data目录：cd $PGDATA
cp /opt/postgres/share/recovery.conf.sample ./recovery.done
2）、修改 recovery.done 添加如下内容
standby_mode = ‘on’
recovery_target_timeline = ‘latest’
primary_conninfo = ‘host=192.168.70.136 port=5432 user=replica password=replica’
trigger_file = ‘/tmp/trigger_file’

2.1.5、重启主节点数据库

pg_ctl restart

2.2、备节点配置

2.2.1、关闭备节点数据库

pg_ctl stop -m fast

2.2.2、配置密码文件

1）、vi /home/postgres/.pgpass 添加如些内容：
192.168.70.135:5432:replication:replica:replica
2）、设置400权限
chmod 400 .pgpass

2.2.3、主备节点同步

1）、删除备节点打他目录
rm -rf /opt/postgres/data
2）、复制主节点数据库信息
pg_basebackup -F p -D $PGDATA -h 192.168.70.135 -p 5432 -U replica

2.2.4、配置流复制配置文件

1）、进入data目录：cd $PGDATA
cp /opt/postgres/share/recovery.conf.sample ./recovery.conf
2）、修改 recovery.conf 添加如下内容
standby_mode = ‘on’
recovery_target_timeline = ‘latest’
primary_conninfo = ‘host=192.168.70.135 port=5432 user=replica password=replica’
trigger_file = ‘/tmp/trigger_file’

2.2.5、重新启动数据库

pg_ctl start

2.3、检验双机热备配置情况

1）、连接主节点数据库：psql -h 192.168.70.135 -p 5432 -U postgres postgres
2）、询主从节点状态：select * from pg_stat_replication;
出现如下截图信息，既数据库流复制配置成功

3、Pgpool 中间件安装

以下操作需要在主备节点同时执行

3.1、上传Pgpool源码包到机器 /tmp 目录

本次PostgreSQL集群搭建使用的数据库版本是3.7.7，源码包为：pgpool-II-3.7.7.tar.gz，这里可以使用ftp文件传输、也可以使用CRT自带的SFTP方式上传等 。

3.2、解压、编译、安装

1）、在opt目录下新增pgpool目录：mkdir -p /opt/pgpool
2）、解压pgpool源码包
tar -zxvf pgpool-II-3.7.7.tar.gz
3）、进入源码包
cd pgpool-II-3.7.7
4）、进行安装配置
./configure -prefix=/opt/pgpool -with-pgsql=/opt/postgres
5）、安装、编译
make && make install

3.3、配置主备节点 免密登录

主备节点执行以下语句，生成 主机密钥
ssh-keygen -t rsa

3.3.1、主备节点相互配置密钥（以下顺序不能乱）

2）、主节点执行以下语句：
scp ~/.ssh/id_rsa.pub postgres@192.168.70.136:~/.ssh/authorized_keys
3）、备节点执行以下语句：
scp ~/.ssh/id_rsa.pub postgres@192.168.70.135:~/.ssh/authorized_keys
4）、主节点执行以下语句：
ssh-copy-id postgres@192.168.70.135
5）、备节点执行以下语句：
ssh-copy-id postgres@192.168.70.136

3.3.2、自行测试免密登录

ssh 192.168.70.136 登陆成功

3.4、主节点创建角色

1）、连接数据库：psql -h 192.168.70.135 -p 5432 -U postgres postgres
2）、创建角色：create role srcheck nosuperuser login encrypted password ‘SRCHECK’;

3.5、修改配置文件

3.5.1、复制文件

1）、进入 /opt/pgpool/etc 目录：cd /opt/pgpool/etc/
2）、复制文件：
cp pcp.conf.sample pcp.conf
cp pgpool.conf.sample-stream pgpool.conf
cp pool_hba.conf.sample pool_hba.conf

3.5.2、修改 pool_hba.conf 配置文件,在文件末尾修改如下内容（注意是修改）

local all all md5
host all all 0.0.0.0/0 md5
host all all 0/0 md5

3.5.3、修改 pcp.conf 配置文件

1）、打开/opt/pgpool/bin 目录：cd /opt/pgpool/bin
2）、pg_md5 123456 （记录生成的md5加密密码）
e10adc3949ba59abbe56e057f20f883e
3）、打开/opt/pgpool/etc 目录：cd /opt/pgpool/etc
vi pcp.conf
然后添加如下内容：
postgres: e10adc3949ba59abbe56e057f20f883e
保存退出

3.5.6、在pgpool中添加pg数据库的用户名和密码

1）、打开/opt/pgpool/bin 目录：cd /opt/pgpool/bin
2）、执行语句：pg_md5 -p -m -u postgres pool_passwd
输入数据库密码：123456
3）、输入密码后，在/opt/pgpool/etc目录下会生成一个pool_passwd文件

3.6、使用root权限配置系统命令权限

chmod u+s /sbin/ifconfig
chmod u+s /usr/sbin

3.7、修改 pgpool.conf 配置文件

3.7.1、需要将现有的pgpool.conf文件内容清空，再重新配置

cat /dev/null > pgpool.conf

3.7.2、主节点配置pgpool.conf文件配置

#CONNECTIONS
listen_addresses = ‘’
port = 9999
pcp_listen_addresses = '’
pcp_port = 9898

#- Backend Connection Settings -

backend_hostname0 = ‘192.168.70.135’
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = ‘/opt/postgres/data’
backend_flag0 = ‘ALLOW_TO_FAILOVER’

backend_hostname1 = ‘192.168.70.136’
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = ‘/opt/postgres/data’
backend_flag1 = ‘ALLOW_TO_FAILOVER’

#- Authentication -
enable_pool_hba = on
pool_passwd = ‘pool_passwd’

#FILE LOCATIONS
pid_file_name = ‘/opt/pgpool/pgpool.pid’

replication_mode = off
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’

#------------------------------------------------------------------------------
#HEALTH CHECK 健康检查
#------------------------------------------------------------------------------

health_check_period = 10 # Health check period
# Disabled (0) by default
health_check_timeout = 20
# Health check timeout
# 0 means no timeout
health_check_user = ‘postgres’
# Health check user
health_check_password = ‘123456’ #数据库密码
# Password for health check user
health_check_database = ‘postgres’
#必须设置，否则primary数据库down了，pgpool不知道，不能及时切换。从库流复制还在连接数据，报连接失败。
#只有下次使用pgpool登录时，发现连接不上，然后报错，这时候，才知道挂了，pgpool进行切换。

#主备切换的命令行配置
#------------------------------------------------------------------------------
#FAILOVER AND FAILBACK
#------------------------------------------------------------------------------

failover_command = '/opt/pgpool/failover_stream.sh %H ’

#------------------------------------------------------------------------------
#WATCHDOG
#------------------------------------------------------------------------------

#- Enabling -
use_watchdog = on
#- Watchdog communication Settings -

wd_hostname = ‘192.168.70.135’
# Host name or IP address of this watchdog
# (change requires restart)
wd_port = 9000
# port number for watchdog service
# (change requires restart)
#- Virtual IP control Setting -

if_cmd_path = ‘/sbin’
delegate_IP = ‘192.168.70.150’

if_up_cmd = ‘ip addr add KaTeX parse error: Expected group after '_' at position 4: _IP_̲/24 dev ens33 label ens33:0’
if_down_cmd = ‘ip addr del KaTeX parse error: Expected group after '_' at position 4: _IP_̲/24 dev ens33’
arping_path = ‘/usr/sbin’
arping_cmd = ‘arping -U KaTeX parse error: Expected group after '_' at position 4: _IP_̲ -w 1’
# delegate IP address
# If this is empty, virtual IP never bring up.
# (change requires restart)
# path to the directory where if_up/down_cmd exists
# (change requires restart)
#if_up_cmd = ‘ifconfig ens33:0 inet KaTeX parse error: Expected group after '_' at position 4: _IP_̲ netmask 255.255.255.0’
# startup delegate IP command
# (change requires restart)
# ens33根据现场机器改掉
#if_down_cmd = ‘ifconfig ens33:0 down’
# rtm
# shutdown delegate IP command
# (change requires restart)
# ens33根据现场机器改掉
#-- heartbeat mode –

wd_heartbeat_port = 9694
# Port number for receiving heartbeat signal
# (change requires restart)
wd_heartbeat_keepalive = 2
# Interval time of sending heartbeat signal (sec)
# (change requires restart)
wd_heartbeat_deadtime = 30
# Deadtime interval for heartbeat signal (sec)
# (change requires restart)
heartbeat_destination0 = ‘192.168.70.136’
# Host name or IP address of destination 0
# for sending heartbeat signal.
# (change requires restart)
heartbeat_destination_port0 = 9694
# Port number of destination 0 for sending
# heartbeat signal. Usually this is the
# same as wd_heartbeat_port.
# (change requires restart)
heartbeat_device0 = ‘ens33’
# Name of NIC device (such like ‘eth0’)
# used for sending/receiving heartbeat
# signal to/from destination 0.
# This works only when this is not empty
# and pgpool has root privilege.
# (change requires restart)
# ens33根据现场机器改掉
#- Other pgpool Connection Settings -

other_pgpool_hostname0 = ‘192.168.70.136’ #对端
# Host name or IP address to connect to for other pgpool 0
# (change requires restart)
other_pgpool_port0 = 9999
# Port number for othet pgpool 0
# (change requires restart)
other_wd_port0 = 9000
# Port number for othet watchdog 0
# (change requires restart)

3.7.3、备节点配置pgpool.conf文件配置

#CONNECTIONS
listen_addresses = ‘’
port = 9999
pcp_listen_addresses = '’
pcp_port = 9898

#- Backend Connection Settings -

backend_hostname0 = ‘192.168.70.135’
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = ‘/opt/postgres/data’
backend_flag0 = ‘ALLOW_TO_FAILOVER’

backend_hostname1 = ‘192.168.70.136’
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = ‘/opt/postgres/data’
backend_flag1 = ‘ALLOW_TO_FAILOVER’

#- Authentication -
enable_pool_hba = on
pool_passwd = ‘pool_passwd’

#FILE LOCATIONS
pid_file_name = ‘/opt/pgpool/pgpool.pid’

replication_mode = off
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’

#------------------------------------------------------------------------------
#HEALTH CHECK 健康检查
#------------------------------------------------------------------------------

health_check_period = 10 # Health check period
# Disabled (0) by default
health_check_timeout = 20
# Health check timeout
# 0 means no timeout
health_check_user = ‘postgres’
# Health check user
health_check_password = ‘123456’ #数据库密码
# Password for health check user
health_check_database = ‘postgres’
#必须设置，否则primary数据库down了，pgpool不知道，不能及时切换。从库流复制还在连接数据，报连接失败。
#只有下次使用pgpool登录时，发现连接不上，然后报错，这时候，才知道挂了，pgpool进行切换。

#主备切换的命令行配置
#------------------------------------------------------------------------------
#FAILOVER AND FAILBACK
#------------------------------------------------------------------------------

failover_command = '/opt/pgpool/failover_stream.sh %H ’

#------------------------------------------------------------------------------
#WATCHDOG
#------------------------------------------------------------------------------

#- Enabling -
use_watchdog = on
#- Watchdog communication Settings -

wd_hostname = ‘192.168.70.136’ #本端
# Host name or IP address of this watchdog
# (change requires restart)
wd_port = 9000
# port number for watchdog service
# (change requires restart)
#- Virtual IP control Setting -
if_cmd_path = ‘/sbin’
delegate_IP = ‘192.168.70.150’

if_up_cmd = ‘ip addr add KaTeX parse error: Expected group after '_' at position 4: _IP_̲/24 dev ens33 label ens33:0’
if_down_cmd = ‘ip addr del KaTeX parse error: Expected group after '_' at position 4: _IP_̲/24 dev ens33’
arping_path = ‘/usr/sbin’
arping_cmd = ‘arping -U KaTeX parse error: Expected group after '_' at position 4: _IP_̲ -w 1’
# delegate IP address
# If this is empty, virtual IP never bring up.
# (change requires restart)
# path to the directory where if_up/down_cmd exists
# (change requires restart)
#if_up_cmd = ‘ifconfig ens33:0 inet KaTeX parse error: Expected group after '_' at position 4: _IP_̲ netmask 255.255.255.0’
# startup delegate IP command
# (change requires restart)
# ens33根据现场机器改掉
#if_down_cmd = ‘ifconfig ens33:0 down’
# shutdown delegate IP command
# (change requires restart)
# ens33根据现场机器改掉
#-- heartbeat mode –

wd_heartbeat_port = 9694
# Port number for receiving heartbeat signal
# (change requires restart)
wd_heartbeat_keepalive = 2
# Interval time of sending heartbeat signal (sec)
# (change requires restart)
wd_heartbeat_deadtime = 30
# Deadtime interval for heartbeat signal (sec)
# (change requires restart)
heartbeat_destination0 = ‘192.168.70.135’ #对端
# Host name or IP address of destination 0
# for sending heartbeat signal.
# (change requires restart)
heartbeat_destination_port0 = 9694
# Port number of destination 0 for sending
# heartbeat signal. Usually this is the
# same as wd_heartbeat_port.
# (change requires restart)
heartbeat_device0 = ‘ens33’
# Name of NIC device (such like ‘eth0’)
# used for sending/receiving heartbeat
# signal to/from destination 0.
# This works only when this is not empty
# and pgpool has root privilege.
# (change requires restart)
# ens33根据现场机器改掉
#- Other pgpool Connection Settings -

other_pgpool_hostname0 = ‘192.168.70.135’ #对端
# Host name or IP address to connect to for other pgpool 0
# (change requires restart)
other_pgpool_port0 = 9999
# Port number for othet pgpool 0
# (change requires restart)
other_wd_port0 = 9000
# Port number for othet watchdog 0
# (change requires restart)

3.8、配置脚本failover_stream.sh（master,slave节点都执行）

1）、打开/opt/pgpool目录： cd /opt/pgpool
2）、编辑 failover_stream.sh 文件：vi failover_stream.sh
#! /bin/sh
#Failover command for streaming replication.
#Arguments: $1: new master hostname.
new_master=$1trigge{r}_{c}ommand="$PGHOME/bin/pg_ctl promote -D $PGDATA"
#Prompte standby database.
/usr/bin/ssh -T $new_master $trigger_command
exit 0;

3.9、PGPool集群日志管理

mkdir -p /opt/pgpool/log/

3.10、集群测试

连接pgpool集群，可直接连接PostgreSQL数据库，连接数据库后执行show pool_nodes; 可以查看集群中节点状态均为up。

4、关键命令总结：

启动数据库：pg_ctl start
重启数据库：pg_ctl restart
关闭数据库：pg_ctl -m fast stop
启动pgpool：pgpool -n -D > /pgpool/log/pgpool.log 2>&1 &
关闭pgpool：/pgpool/bin/pgpool -m fast stop
连接数据库：psql -h 192.168.70.150 -p 9999 -U postgres postgres

5、PostgreSQL+Pgpool集群HA测试

5.1、Pgpool HA测试

1）、在master节点上停止pgpool服务
pgpool -m fast stop
2）、稍等片刻后，访问集群正常，在master节点上的pgpool宕机后，由slave节点的pgpool接管vip和集群服务，并未中断应用访问。模拟slave端pgpool宕机，同样正常。

5.2、POstgreSQL数据库HA测试

1）、手动关闭PostgreSQL主节点数据库
2）、连接数据库查询此时原主节点的数据库状态为dnow，原备节点接管成为主节点

3）、在原主节点机器执行同步时间线函数
pg_rewind --target-pgdata=/postgres/data --source-server=‘host=192.168.70.136 port=5432 user=postgres dbname=postgres password=123456’

4）、将原主节点数据库配置文件recovery.done修改为recovery.conf
cd $PGDATA
mv recovery.done recovery.conf
5）、重新启动原主节点dnow掉的数据库，并将其重新加入集群(-n 后面的数字表示nodeid)
pg_ctl start
pcp_attach_node -d -U postgres -h 192.168.70.150 -p 9898 -n 0

6）、连接数据库查询此时原主节点的数据库成为备节点，原备节点成为主节点，且此时主备机节点数据库数据保持一致