1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
|
# 集群部署
# @主节点操作
# 创建同步用户
sudo -u postgres psql
create role actorcloud login replication encrypted password 'public';
# 退出sql cli
# 配置节点可访问
cat >> /var/lib/pgsql/10/data/pg_hba.conf <<-EOF
host replication actorcloud 172.15.110.251/24 trust
host replication actorcloud 172.15.110.252/24 trust
EOF
# 修改配置
cat >> /var/lib/pgsql/10/data/postgresql.conf <<-EOF
wal_level = replica
max_wal_senders= 10
wal_keep_segments = 10240
EOF
sed -i 's/max_connections = 100/max_connections = 512/' /var/lib/pgsql/10/data/postgresql.conf
# 重启
systemctl restart postgresql-10
# 集群部署
# @从节点操作
# 同步初始数据
su postgres
rm -rf /var/lib/pgsql/10/data/*
pg_basebackup -h 172.15.110.251 -U actorcloud -D /var/lib/pgsql/10/data -X stream -P
# 创建recovery配置文件
cp /usr/pgsql-10/share/recovery.conf.sample /var/lib/pgsql/10/data/recovery.conf
# 写入配置文件，配置主节点地址
cat >> /var/lib/pgsql/10/data/recovery.conf <<-EOF
standby_mode = on
primary_conninfo = 'host=172.15.110.251 port=5432 user=actorcloud password=public'
recovery_target_timeline = 'latest'
trigger_file = '/tmp/trigger_file0'
EOF
# 热备保护开启
echo "hot_standby = on" >> /var/lib/pgsql/10/data/postgresql.conf
# 退出postgres用户
# 重启数据库
systemctl restart postgresql-10
# 集群部署
# @主节点操作
# 查询从节点是否连接
sudo -u postgres psql
select client_addr,sync_state from pg_stat_replication;
# 集群部署
# @ all mathines
# 修改postgres用户密码
passwd postgres
mkdir -p /var/lib/pgsql/.ssh && chown postgres:postgres /var/lib/pgsql/.ssh
su postgres
ssh-keygen
ssh-copy-id <其他机器ip>
# 集群部署
# @all mathines
# root user
# 外网提前下载pgpool II
# 解压
tar xvf pgpoolII-rhel7-rpm.tgz
# 切换目录
cd pgpoolII
# 安装
yum localinstall *.rpm -y
# 修改pool_hba.conf配置文件
cat >> /etc/pgpool-II/pool_hba.conf <<-EOF
host replication actorcloud 172.15.110.251/24 trust
host replication actorcloud 172.15.110.252/24 trust
host all all 0.0.0.0/0 md5
EOF
# 此处省略了生成密码md5的过程（pg_md5 GeoStar@123）
# 设置连接密码
echo "postgres:6d0f26e2540c65101f0c8187e352fcaf" >> /etc/pgpool-II/pcp.conf
# 开启监听
cat >> /etc/pgpool-II/pgpool.conf <<-EOF
#
# listen all address
listen_addresses = '*'
EOF
# 添加第一台主机的backend
cat >> /etc/pgpool-II/pgpool.conf <<-EOF
# cluster_node1
backend_hostname0 = '172.15.110.251'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/pgsql/10/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
EOF
# 添加第二台主机的backend
cat >> /etc/pgpool-II/pgpool.conf <<-EOF
# cluster_node2
backend_hostname1 = '172.15.110.252'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/10/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
EOF
# pg_hba.conf生效
cat >> /etc/pgpool-II/pgpool.conf <<-EOF
# Use pool_hba.conf for client authentication
enable_pool_hba = on
EOF
# 开启负载均衡
cat >> /etc/pgpool-II/pgpool.conf <<-EOF
# load_balance_mode
load_balance_mode = on
EOF
# 主从流复制
cat >> /etc/pgpool-II/pgpool.conf <<-EOF
# Streaming replication check
master_slave_mode = on
sr_check_period = 6
sr_check_user = 'actorcloud'
sr_check_password = 'public'
sr_check_database = 'postgres'
delay_threshold = 10000000
EOF
# 配置主机故障触发执行的脚本
cat >> /etc/pgpool-II/pgpool.conf <<-EOF
# Executes this command at failover
failover_command = '/var/lib/pgsql/10/failover_stream.sh %d %H'
EOF
# 开启看门狗，IP为本机IP
cat >> /etc/pgpool-II/pgpool.conf <<-EOF
# Watchdog
use_watchdog = on
wd_hostname = '172.15.110.251'
EOF
# 开启虚拟IP，此处主从节点一致
cat >> /etc/pgpool-II/pgpool.conf <<-EOF
# delegate IP
delegate_IP = '172.15.110.253'
if_up_cmd = 'ip addr add $_IP_$/24 dev eth0 label eth0:0'
if_down_cmd = 'ip addr del $_IP_$/24 dev eth0'
arping_cmd = 'arping -U $_IP_$ -w 1 -I eth0'
EOF
# 心跳检查的配置与看门狗配置，IP为其他节点的IP。
cat >> /etc/pgpool-II/pgpool.conf <<-EOF
# delegate IP
heartbeat_destination0 = '172.15.110.252'
heartbeat_device0 = 'eth0'
other_pgpool_hostname0 = '172.15.110.252'
other_pgpool_port0 = 9999
other_wd_port0 = 9000
EOF
# 编写故障切换脚本
touch /var/lib/pgsql/10/failover_stream.sh
cat >> /var/lib/pgsql/10/failover_stream.sh <<-EOF
#! /bin/sh
# Failover command for streaming replication.
# Arguments: $1: new master hostname.
failed_node=$1
new_master=$2
trigger_file=$3
# Do nothing if standby goes down.
if [ $failed_node = 1 ]; then
exit 0;
fi
# Create the trigger file.
# use commond
/usr/bin/ssh -T $new_master /usr/pgsql-10/bin/pg_ctl promote -D /var/lib/pgsql/10/data/
# use file
# /usr/bin/ssh -T $new_master /bin/touch /tmp/trigger_file0
exit 0
EOF
# 各种权限处理
chmod u+s /sbin/ifconfig && chmod u+s /usr/sbin
chown postgres:postgres /var/lib/pgsql/10/failover_stream.sh && chmod 777 /var/lib/pgsql/10/failover_stream.sh
chown -R postgres.postgres /etc/pgpool-II
# pgpool 日志
mkdir /var/log/pgpool
chown -R postgres.postgres /var/log/pgpool
# pgpool pid目录
mkdir /var/run/pgpool
chown -R postgres.postgres /var/run/pgpool
# 启动
systemctl start pgpool
# 开机自启
systemctl enable pgpool
|
