host replication stream_replication 0.0.0.0/0 md5
slave主机操作
初始化系统
[root@pgpool-tdb02 ~]# /usr/pgsql-10/bin/postgresql-10-setup initdb
Initializing database ... OK
修改postgresql-10.service
[root@pgpool-tdb02 ~]# vi /usr/lib/systemd/system/postgresql-10.service
内容如下:
# Include the default config:
#.include /usr/lib/systemd/system/postgresql-10.service
[Service]
Environment=PGDATA=/data1/pg_data
重启PG服务
systemctl daemon-reload
基础备份复制到备库服务器
rm -rf /data1/pg_data # 如果没有重要数据可操作,主要为同步主库路径
[root@pgpool-tdb02 ~]# su - postgres -c 'pg_basebackup -D $PGDATA --
format=p -h master -p 54321 -U stream_replication -W'
![PostgreSQL+Pgpool实现HA读写分离](/upload/otherpic54/18313.png)
我们提供的服务有:成都网站设计、成都做网站、微信公众号开发、网站优化、网站认证、象州ssl等。为千余家企事业单位解决了网站和推广的问题。提供周到的售前咨询和贴心的售后服务,是有科学管理、有技术的象州网站制作公司
关闭所有主机防火墙:(也可以在防火墙上设置端口限制)
如果你的系统上没有安装,使用命令安装
安装firewalld 防火墙yum install firewalld
开启服务
关闭防火墙systemctl stop firewalld.service
开机自动启动systemctl enable firewalld.service
关闭开机制动启动systemctl disable firewalld.service
赋权:
[root@pgpool-tdb01 pg_data]# chown postgres.postgres pg_hba.conf.bak
[root@pgpool-tdb01 pg_data]# chown postgres.postgres postgresql.conf.bak
error:
pg_basebackup: could not connect to server: could not connect to server: No route
to host
Is the server running on host "master" (10.0.40.191) and accepting
TCP/IP connections on port 54321?
pg_basebackup: could not connect to server: FATAL: number of requested standby
connections exceeds max_wal_senders (currently 1)
pg_basebackup: could not get write-ahead log end position from server: ERROR:
could not open file "./pg_hba.conf.bak": Permission denied
以上报错
需要关闭防火墙或在防火墙上设置端口限制。
[root@pgpool-tdb02 ~]# su - postgres -c 'pg_basebackup -D $PGDATA --
format=p -h master -p 54321 -U stream_replication -W'
Password:
[root@pgpool-tdb02 ~]# ll /data1/
total 4
drwxr-xr-x. 2 postgres postgres 6 Feb 17 19:03 pg_bin
drwx------. 21 postgres postgres 4096 Feb 17 21:18 pg_data
drwxr-xr-x. 2 postgres postgres 6 Feb 17 19:03 pg_logs
修改备库配置信息:
cp $PGHOME/share/recovery.conf.sample $PGDATA/recovery.conf
vi $PGDATA/recovery.conf
[postgres@pgpool-tdb02 ~]$ vi $PGDATA/recovery.conf
增加以下内容
standby_mode='on'
primary_conninfo = 'host=master port=54321 user=stream_replication
password=1qaz.com'
restore_command = ''
recovery_target_timeline = 'latest'
重启PG服务:
systemctl restart postgresql-10
systemctl enable postgresql-10.service
验证
主节点执行
create table test (id int4, create_time timestamp(0) without time zone);
insert into test values (3, now());
select * from test;
备节点执行
select * from test;
其他查询
进入测试数据库test,主库上执行如下命令返回f,备库上返回t。
select pg_is_in_recovery();
执行如下命令查看快照,它返回主库记录点、备库记录点;主库每增加一条写入,记录点的
值就会加1。
select txid_current_snapshot();
执行如下命令可以查看主备同步状态。
select * from pg_stat_replication;
字段state显示的同步状态有:startup(连接中)、catchup(同步中)、streaming(同
步);字段sync_state显示的模式有:async(异步)、sync(同步)、potential(虽然现
在是异步模式,但是有可能升级到同步模式)。
主:
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
f
(1 row)
备:
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)
主备切换
假设主库崩溃了,备库如何从只读状态切换为读写状态呢?只要把备库的
postgresql.conf中hot_standby修改为off,并且删除recovery.conf,然后重启
库就可以提供服务了。
PGPool2(pool主机操作)
安装PGPool2
yum install -y http://www.pgpool.net/yum/rpms/3.6/redhat/rhel-7-
x86_64/pgpool-II-release-3.6-1.noarch.rpm
yum -y install pgpool-II-pg96 pgpool-II-pg96-debuginfo pgpool-II-pg96-devel
pgpool-II-pg96-extensions
systemctl enable pgpool.service #开启自动启动
下面的可以都安装:
yum install -y http://www.pgpool.net/yum/rpms/3.6/redhat/rhel-7-x86_64/pgpool-
II-pg10-3.6.14-1pgdg.rhel7.x86_64.rpm
yum install -y http://www.pgpool.net/yum/rpms/3.6/redhat/rhel-7-x86_64/pgpool-
II-pg10-debuginfo-3.6.14-1pgdg.rhel7.x86_64.rpm
yum install -y http://www.pgpool.net/yum/rpms/3.6/redhat/rhel-7-x86_64/pgpool-
II-pg10-devel-3.6.14-1pgdg.rhel7.x86_64.rpm
yum install -y http://www.pgpool.net/yum/rpms/3.6/redhat/rhel-7-x86_64/pgpool-
II-pg10-extensions-3.6.14-1pgdg.rhel7.x86_64.rpm
yum install -y http://www.pgpool.net/yum/rpms/3.6/redhat/rhel-7-x86_64/pgpool-
II-release-3.6-1.noarch.rpm
yum -y install pgpool-II-pg10 pgpool-II-pg10-debuginfo pgpool-II-pg10-devel
pgpool-II-pg10-extensions
systemctl enable pgpool.service #开启自动启动
添加Pgpool-II运行用户
useradd postgres # 环境准备时已操作
chown -R postgres.postgres /etc/pgpool-II
chown -R postgres.postgres /var/run/pgpool/
配置pool_hba.conf
cp /etc/pgpool-II/pool_hba.conf{,.bak}
vi /etc/pgpool-II/pool_hba.conf
增加内容
host all all 0.0.0.0/0 md5
配置pcp.conf
主节点登陆后执行:
postgres=# select rolname,rolpassword from pg_authid;
rolname | rolpassword
--------------------+-------------------------------------
pg_signal_backend |
srcheck | md5662c10f61b27a9ab38ce69157186b25f
postgres | md5d3612d57ee8d4c147cf27b11e3a0974d
stream_replication | md59279ef6b904bc483e4f85e6d44cfc0ed
(4 rows)
[root@pgpool-tdb01 ~]# su - postgres
Last login: Mon Feb 18 00:05:49 CST 2019 on pts/0
[postgres@pgpool-tdb01 ~]$ psql
psql (10.7)
Type "help" for help.
主节点上执行:
要postgres用户有MD5值,给postgres用户加密码。
alter user postgres with password '123';
postgres=# create user devuser with password 'XXXX';
CREATE ROLE
postgres=# create database wmsexpressdb;
CREATE DATABASE
postgres=# GRANT ALL PRIVILEGES ON DATABASE wmsexpressdb to devuser;
GRANT
postgres=# select rolname,rolpassword from pg_authid;
rolname | rolpassword
----------------------+-------------------------------------
pg_monitor |
pg_read_all_settings |
pg_read_all_stats |
pg_stat_scan_tables |
pg_signal_backend |
stream_replication | md5b90626724a074c98507b58e9937298cb
srcheck | md544f2f6847b7c760bfb8331b345d2591f
postgres | md5289451de0ccee765f70bb6146e4d6c20
devuser | md5e75b559c51552b755247ecae024a6f24
(9 rows)
加入MD5认证:
[root@wmsexpress-pgpool01 pgpool-II]# vi pool_passwd
stream_replication:md5b90626724a074c98507b58e9937298cb
srcheck:md544f2f6847b7c760bfb8331b345d2591f
postgres:md5289451de0ccee765f70bb6146e4d6c20
devuser:md5e75b559c51552b755247ecae024a6f24
重启pgpool 让设置生效:
[root@wmsexpress-pgpool01 pgpool-II]# psql -p 54321 -h 10.0.4.37 -U devuser -d
postgres
psql: FATAL: md5 authentication failed
DETAIL: pool_passwd file does not contain an entry for "devuser"
[root@wmsexpress-pgpool01 pgpool-II]# pgpool stop
2019-03-04 11:56:08: pid 16620: LOG: stop request sent to pgpool. waiting for
termination...
.done.
[root@wmsexpress-pgpool01 pgpool-II]# pgpool -C -D
[root@wmsexpress-pgpool01 pgpool-II]# psql -p 54321 -h 10.0.4.37 -U devuser -d
postgres
Password for user devuser:
psql (10.7)
Type "help" for help.
postgres=>
postgres=# select rolname,rolpassword from pg_authid;
rolname | rolpassword
----------------------+-------------------------------------
pg_monitor |
pg_read_all_settings |
pg_read_all_stats |
pg_stat_scan_tables |
pg_signal_backend |
stream_replication | md5cbdf308c01aeea690cefb040a85dd4ee
srcheck | md5ed70f2fcca0c9060ac658734adbae8bb
postgres | md510220e27448e3d1bf2531c9d99ad7b9e
(8 rows)
[root@pgpool-t01 pgpool-II]# vi /etc/pgpool-II/pool_passwd
增加SQL执行结果的内容,形式为$rolname:$rolpassword例如:
srcheck:md5662c10f61b27a9ab38ce69157186b25f #建议用这个方式
1
或者:
pg_md5 -u postgres your_password
vi /etc/pgpool-II/pcp.conf ## 加入 postgres:上一命令的输出
增加devuser,并设置superuser权限,加入MD5认证。
配置pgpool.conf
cp /etc/pgpool-II/pgpool.conf{,.bak}
vi /etc/pgpool-II/pgpool.conf
# CONNECTIONS
listen_addresses = '*'
port = 54321
socket_dir = '/var/run/pgpool'
pcp_listen_addresses = '*'
pcp_port = 9898
pcp_socket_dir = '/var/run/pgpool'
# - Backend Connection Settings -
backend_hostname0 = 'master'
backend_port0 = 54321
backend_weight0 = 1
backend_data_directory0 = '/data1/pg_data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_hostname1 = 'slave'
backend_port1 = 54321
backend_weight1 = 1
backend_data_directory1 = '/data1/pg_data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
# - Authentication -
enable_pool_hba = on
pool_passwd = 'pool_passwd'
# FILE LOCATIONS
pid_file_name = '/var/run/pgpool/pgpool.pid'
logdir = '/data1/pg_logs'
replication_mode = off
load_balance_mode = on
master_slave_mode = on
master_slave_sub_mode = 'stream'
sr_check_period = 5
sr_check_user = 'srcheck'
sr_check_password = 'XXXX'
sr_check_database = 'postgres'
# HEALTH CHECK 健康检查
health_check_period = 10
health_check_timeout = 20
health_check_user = 'srcheck'
health_check_password = 'XXXX'
health_check_database = 'postgres'
# FAILOVER AND FAILBACK
failover_command = '/data1/pg_bin/failover_stream.sh %H'
failover_stream.sh脚本
vim /data1/pg_bin/failover_stream.sh
[root@pgpool-t01 pg_bin]# chown postgres.postgres failover_stream.sh
chmod 777 /data1/pg_bin/failover_stream.sh
chmod u+s /sbin/ifconfig
chmod u+s /usr/sbin
pgpool -n -d -D > /data1/pg_logs/pgpool.log 2>&1 & ## 启动
pgpool -m fast stop ## 关闭
[root@pgpool-t01 ~]# pgpool -n -d -D > /data1/pg_logs/pgpool.log 2>&1 &
[1] 3404
[root@pgpool-t01 ~]# pgpool -m fast stop
2019-02-18 00:49:29: pid 3439: LOG: stop request sent to pgpool. waiting for
termination...
.done.
[1]+ Done pgpool -n -d -D > /data1/pg_logs/pgpool.log 2>&1
failover_stream.sh内容:
#! /bin/sh
# Failover command for streaming replication.
# Arguments: $1: new master hostname.
new_master=$1
trigger_command="$PGHOME/bin/pg_ctl promote -D $PGDATA"
# Prompte standby database.
/usr/bin/ssh -T $new_master $trigger_command
exit 0;
登陆设置
当执行 pgpool -n -d -D > /data1/pg_logs/pgpool.log 2>&1 & 后可查看集群状态:
[root@pgpool-t01 ~]# psql -p 54321 -h 10.0.40.193 -U srcheck -d
postgres
Password for user srcheck:
psql (10.7)
Type "help" for help.
postgres=> show pool_nodes;
node_id | hostname | port | status | lb_weight | role | select_cnt |
load_balance_node | replication_delay
---------+----------+-------+--------+-----------+---------+------------+---
----------------+-------------------
0 | master | 54321 | up | 0.500000 | primary | 0 | true
| 0
1 | slave | 54321 | up | 0.500000 | standby | 0 | false
| 0
(2 rows)
如果未发现集群状态,请在master和slave主机分别执行以下操作:
[postgres@pool ~]$ pcp_attach_node -d -U postgres -h pool -p 9898 -n 0
[postgres@pool ~]$ pcp_attach_node -d -U postgres -h pool -p 9898 -n 1
#详情查询命令pcp_attach_node
查看pgpool的服务状态:
[root@pgpool-t01 pgpool-II]# systemctl status pgpool.service
[root@pgpool-t01 pgpool-II]# systemctl status pgpool
[root@pgpool-t01 pgpool-II]# systemctl start pgpool.service
[root@pgpool-t01 pgpool-II]# systemctl start pgpool
[root@pgpool-t01 pgpool-II]# systemctl stop pgpool.service
测试SQL:
insert into test values (20,now());
select * from test;
psql -p 54321 -h 10.0.40.193 -U srcheck -d postgres
systemctl daemon-reload
su - postgres -c '/usr/pgsql-10/bin/initdb -D /data1/pg_data'
systemctl restart postgresql-10
systemctl enable postgresql-10.service
systemctl status postgresql-10
所有应用连接到PGPOOL节点,写入会话会自动分配到PG主节点,只读会话会以会话级平
均分配到PG主从两个节点上。
文章名称:PostgreSQL+Pgpool实现HA读写分离
文章位置:http://bjjierui.cn/article/gihhes.html