postgresql-12数据库安装+流复制+主备切换
主库安装
yum install -y postgresql12-*
vim /usr/lib/systemd/system/postgresql-12.service
修改PGDATA目录为自定义目录，可复制一个作为备库程序，并修改成备库的PGDATA目录
导入环境变量
export PATH=/usr/pgsql-12/bin:$PATH
创建自定义的PGDATA目录并修改权限用于初始化数据库
mkdir /home/pg12/data
chmod 700 /home/pg12/data
/usr/pgsql-12/bin/postgresql-12-setup initdb [若启动自定义的pg程序时需要指定service name]
一般安装好后psql等命令应该可以执行，如果 psql：找不到命令 可以如下操作
vim ~/.bashrc
添加一行
export PATH=/usr/pgsql-12/bin:$PATH
再重新连接服务器后即可
vim PGDATA/postgresql.conf
listen_addresses = '*'
port = 自定义
wal_level = replica
max_wal_senders = 10
vim PGDATA/pg_hba.conf
添加
host all all 0.0.0.0/0 md5
host replication all 0.0.0.0/0 md5
systemctl enable postgresql-12
systemctl start postgresql-12
su postgres
psql -p 自定义端口
\l 查看是不是正确的数据库
修改数据库用户postgres密码
ALTER USER postgres WITH PASSWORD 'postgres';
--必须以分号结束，成功执行后会出现ALTER ROLE
\q 退出
复制备库
若是同机器上的备库只需要创建备库的PGDATA目录即可
若是不同机器的备库则重复上面的安装步骤到PGDATA目录创建并修改权限完成即可
su postgres
pg_basebackup -D /home/pg12/data（备库PGDATA目录） -X stream -R -Fp -v -P -h 主库ip（若是本机则localhost） -p 5432（主库端口）
需要输入密码，即上面修改的postgres密码
复制完成后进入备库PGDATA目录，查看是否有standby.signal（有这个文件则启动时会作为备库）
vim postgresql.auto.conf
查看这个文件里的host和port信息是否是主库的，否则需要修改
vim postgresql.conf
因为是复制的所以只需要修改
port = 自定义（同机需要改，不同机看有没有冲突端口）
hot_standby = on（备库需要打开，允许在备库执行查询sql）
完成后启动备库
回到root用户
systemctl start postgresql-12.service（同机的则启动上面复制的新程序）
查看是主库还是备库
pg_controldata /home/pg12/data（要看的库的PGDATA目录）
Database cluster state: in production （主库）
Database cluster state: in archive recovery （备库）
数据库簇状态: 在运行中 （主库）
数据库簇状态: 正在归档恢复 （备库）
进入主库
su postgres
psql -p 5432（主库端口）
select client_addr, sync_state from pg_stat_replication;
如下则表示正在异步流复制
client_addr | sync_state
--------------+------------
10.10.10.2 | async
::1 | async
主备切换
停止主库
systemctl stop postgresql-12
切换备库为主库
su postgres
psql -p 5434（备库端口）
select pg_promote();
如下则表示成功
pg_promote
------------
t
但此时在新的主库查询是没有其他备库在流复制的
select client_addr, sync_state from pg_stat_replication;
client_addr | sync_state
-------------+------------
(0 行记录)
将其他备库的流复制切换到新的主库
到其他备库上执行以下操作
vim postgresql.auto.conf 修改host和port信息为新主库
需要有standby.signal文件，没有可以touch standby.signal
重启备库
再回到新的主库
select client_addr, sync_state from pg_stat_replication;
client_addr | sync_state
--------------+------------
10.10.10.2 | async
(1 行记录)
其他备库切换期间对新主库的写入操作也会复制到备库
将原主库启动作为新的备库
su postgres
vim postgresql.auto.conf 修改host和port信息为新主库
touch standby.signal
重启原主库
再回到新的主库
select client_addr, sync_state, state from pg_stat_replication;
client_addr | sync_state | state
--------------+------------+-----------
10.10.10.2 | async | streaming
::1 | async | streaming
也可以清空原主库的PGDATA目录，重复pg_basebackup操作
异步流复制，原主库宕机很久后再恢复，可能和新主库的时间线不同，pg_rewind暂时没研究，所以还是用pg_basebackup重来
=========================================================================
PostgreSQL数据默认会创建一个linux用户postgres，可以修改linux系统的postgres用户的密码
1.删除PostgreSQL用户密码
# sudo passwd -d postgres
passwd: password expiry information changed.
//passwd -d 是清空指定用户密码的意思
2.设置PostgreSQL用户密码
#sudo -u postgres passwd
输入新的 UNIX 密码：
重新输入新的 UNIX 密码：
passwd：已成功更新密码
=========================================================================
PGPool-II-12+PG-12流复制实现HA主备切换
规划
pgpool 10.10.10.1 主
pg 10.10.10.1 主
pgpool 10.10.10.2 备
pg 10.10.10.2 备
vip 10.10.10.3
分别在两台机器上
vim /etc/hosts
10.10.10.1 master
10.10.10.2 slave
10.10.10.3 vip
在master,slave机器上都生成ssh如下：
su postgres
ssh-keygen -t rsa 一路回车
cat ~/.ssh/id_rsa.pub 查看生成的公钥
把生成的公钥追加到 ~/.ssh/authorized_keys
chmod 600 ~/.ssh/authorized_keys
分别将
master的公钥追加到slave的authorized_keys，
slave的公钥追加到master的authorized_keys。
两边互相ssh验证
ssh postgres@slave
ssh postgres@master
安装pgpool
yum install -y pgpool-II-12
pgpool配置目录: /etc/pgpool-II-12
su postgres
cp pool_hba.conf.sample pool_hba.conf
vim pool_hba.conf
将trust都改成md5，并添加一行
host all all 0.0.0.0/0 md5
cp pcp.conf.sample pcp.conf
pg_md5 postgres 会生成密文密码
vim pcp.conf
添加一行postgres:密文密码
pg_md5 -p -m -u postgres pool_passwd
输入密码postgres回车
会生成pool_passwd文件
回到root
chmod u+s /usr/sbin
vim /etc/sudoers
添加两行
postgres ALL=NOPASSWD: /sbin/ip
postgres ALL=NOPASSWD: /usr/sbin/arping
查看本机网卡，配置后面的delegate_IP需要
ifconfig找到本机ip的网卡，这个em1后面要用
em1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 10.10.10.1 netmask 255.255.255.0 broadcast 10.10.10.255
cp pgpool.conf.sample pgpool.conf
vim pgpool.conf
需要修改的配置如下（备机上的pgpool.conf配置类似，把其中的对端换一下）：
=========================================================================
listen_addresses = '*'
port = 9999
pcp_listen_addresses = '*'
pcp_port = 9898
backend_hostname0 = 'master'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/home/postgres/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_hostname1 = 'slave'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/home/postgres/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
enable_pool_hba = on
pool_passwd = 'pool_passwd'
replication_mode = off
load_balance_mode = on
master_slave_mode = on
master_slave_sub_mode = 'stream'
sr_check_period = 10
sr_check_user = 'postgres'
sr_check_password = 'postgres'
sr_check_database = 'postgres'
health_check_period = 10
health_check_timeout = 20
health_check_user = 'postgres'
health_check_password = 'postgres' #数据库密码
health_check_database = 'postgres'
#必须设置，否则primary数据库down了，pgpool不知道，不能及时切换。从库流复制还在连接数据，报连接失败。
#只有下次使用pgpool登录时，发现连接不上，然后报错，这时候，才知道挂了，pgpool进行切换。
failover_command = '/etc/pgpool-II-12/failover_stream.sh %H '
use_watchdog = on
wd_hostname = 'master'
wd_port = 9000
delegate_IP = '10.10.10.3' # 找一个没有用到的ip地址
if_up_cmd = '/usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev em1 label em1:0' # eth1根据现场机器改掉，下面一样
if_down_cmd = '/usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev em1'
arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I em1'
enable_consensus_with_half_votes = on # 一定要打开这个开关才能使单台pgpool获取vip，或者两台时down一台另一台自动切换vip
wd_heartbeat_port = 9694
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30
heartbeat_destination0 = 'slave'
heartbeat_destination_port0 = 9694
heartbeat_device0 = 'em1' # em1根据现场机器改掉
other_pgpool_hostname0 = 'slave' #对端
other_pgpool_port0 = 9999
other_wd_port0 = 9000
=========================================================================
编写脚本
vim failover_stream.sh
内容如下：
=========================================================================
#! /bin/sh
# Failover command for streaming replication.
# Arguments: $1: new master hostname.
new_master=$1
trigger_command="/usr/pgsql-12/bin/pg_ctl promote -D /home/pg12/data"
# Prompte standby database.
/usr/bin/ssh -T $new_master $trigger_command
exit 0;
=========================================================================
修改配置文件和脚本权限
chown -R postgres.postgres /etc/pgpool-II-12
chmod 777 /etc/pgpool-II-12/failover_stream.sh
创建日志目录
mkdir /var/log/pgpool
chown -R postgres.postgres /var/log/pgpool
在postgres用户下启动pgpool，否则会在执行failover.sh时提示输入密码而失败
需要debug日志时可以加参数 -d
su postgres
pgpool -n -D > /var/log/pgpool/pgpool.log 2>&1 &
停止命令
pgpool -m fast stop
主备机pgpool都启动后，查看状态
主机上 ip addr 可以看到em1网卡下有vip（ifconfig看不到）
su postgres
psql -h vip -p 9999 （此时可以将vip作为对外统一的数据库ip，端口9999）
输入密码后查询
show pool_nodes;
node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node
---------+--------------+------+--------+-----------+---------+------------+-------------------
0 | 10.10.10.1 | 5434 | up | 0.500000 | primary | 0 | true
1 | 10.10.10.2 | 5432 | up | 0.500000 | standby | 0 | false
(2 行记录)
pgpool的HA，stop任何一台pgpool，仍然可以用vip+9999端口访问数据库，重新启动后即可恢复
pg的HA
停止主库，此时备库会自动变成主库
node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node
---------+--------------+------+--------+-----------+---------+------------+-------------------
0 | 10.10.10.1 | 5434 | down | 0.500000 | standby | 0 | false
1 | 10.10.10.2 | 5432 | up | 0.500000 | primary | 0 | true
(2 行记录)
主库使用上面方法手动进行恢复成备库后启动，需要手动加入pgpool集群
pcp_attach_node -d -U postgres -h 10.10.10.3(vip) -p 9898 -n 0（node_id）
输入pcp管理密码postgres
加入后节点状态一直是waiting，需要停止pgpool主节点，再删除pgpool_status后重启pgpool
rm /var/log/pgpool-II-12/pgpool_status （这个目录是自动创建的，不是前面自定义的日志目录）
node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node
---------+--------------+------+--------+-----------+---------+------------+-------------------
0 | 10.10.10.1 | 5434 | up | 0.500000 | standby | 5 | true
1 | 10.10.10.2 | 5432 | up | 0.500000 | primary | 132 | false
(2 行记录)
扫一扫
143
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
