一）环境描述
主节点：172.31.0.2
从节点：172.31.0.3
数据库目录：/data/apps_data/postgres_data
postgres用户密码：a123456!
数据同步账号：repl
数据同步密码：a123456!
二）安装步骤与主从复制搭建
安装postgresql数据库
yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm -y
yum list | grep postgresql
yum install postgresql10-contrib.x86_64 postgresql10-server.x86_64 -y
创建数据目录，默认是/var/lib/pgsql，这里更改为/data/apps_data/postgres_data
mkdir /data/apps_data/postgres_data
chown postgres:postgres -R /data/apps_data/postgres_data/
chmod 750 /data/apps_data/postgres_data/
修改配置文件，更改第31行数据目录
vim /usr/lib/systemd/system/postgresql-10.service
Environment=PGDATA=/data/apps_data/postgres_data
添加环境变量
vim /etc/profile
exportPATH=/usr/pgsql-10/bin:$PATH
exportLD_LIBRARY_PATH=/usr/pgsql-10/lib
exportPGDATA=/data/apps_data/postgres_data
source /etc/profile
初始化postgresql并配置重启自动启动
/usr/pgsql-10/bin/postgresql-10-setup initdb
systemctl start postgresql-10
systemctl enable postgresql-10.service
设置登录密码
su - postgres
-bash-4.2$ psql
psql (10.9)
Type "help" for help.
postgres=# ALTER USER postgres WITH PASSWORD 'a123456!';
ALTER ROLE
postgres=# \q
设置远程访问及主从配置
创建复制账号：
su - postgres
createuser --replication -P repl
根据提示输入密码
vim /data/apps_data/postgres_data/pg_hba.conf #拉到最下面，更改如下
local all all md5
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5
# Allow replication connections from localhost, bya user with the
# replication privilege.
#local replication all md5
#host replication all 127.0.0.1/32 md5
#host replication all ::1/128 md5
host all all 0.0.0.0/0 md5
host replication repl 172.31.0.2/32 md5
host replication repl 172.31.0.3/32 md5
更改postgresql.conf配置文件
vim /data/apps_data/postgres_data/postgresql.conf
listen_addresses = '*'
port = 5432
max_connections = 1000
wal_level = hot_standby
synchronous_commit = local
archive_mode = on
archive_command = 'cp %p /data/apps_data/postgres_data/archive/%f'
max_wal_senders = 2
wal_keep_segments = 10
synchronous_standby_names = 'pgslave01'
创建archive目录，并修改权限和用户
mkdir -pv /data/apps_data/postgres_data/archive/
chmod 700 -R /data/apps_data/postgres_data/archive/
chown -R postgres:postgres /data/apps_data/postgres_data/archive/
启动主节点的postgres
systemctl start postgresql-10
从节点加载最新配置，并停止
systemctl start postgresql-10
systemctl stop postgresql-10
备份从节点数据目录，并创建新的数据目录
mv /data/apps_data/postgres_data /data/apps_data/postgres_data-back_`date+%Y-%m-%d-%M`
mkdir /data/apps_data/postgres_data && chmod-R 700 /data/apps_data/postgres_data && chown -R postgres:postgres /data/apps_data/postgres_data
进入postgres用户登陆，并将所有的数据目录从主服务器同步回来
su - postgres
pg_basebackup -h 172.31.0.2 -U repl -D /data/apps_data/postgres_data/ -P
输入密码：
传输完毕后，在/data/apps_data/postgres_data/目录下创建recovery.conf文件，并写入相关配置
touch /data/apps_data/postgres_data/recovery.conf
chown postgres:postgres /data/apps_data/postgres_data/recovery.conf
vim /data/apps_data/postgres_data/recovery.conf
standby_mode = 'on'
primary_conninfo = 'host=172.31.0.3 port=5432 user=repl password=a123456! application_name=pgslave1'
启动从节点的postgres，并且登陆主节点执行
select * frompg_stat_replication; 查看是否有从节点信息，若有则说明主从复制搭建成功
三）keepalived高可用搭建
安装keepalived
yum install -y net-tools psmisc
yum install -y net-snmp
yum install -y ipvsadm keepalived
cp /usr/share/doc/keepalived/samples/keepalived.conf.sample /etc/keepalived/keepalived.conf
主节点配置
cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
router_id LVS_DEVEL
script_user root
# enable_script_security
}
vrrp_script check_postgres {
script "/data0/scripts/keepalived_scripts/check-postgres.sh"
interval 10
weight +10
fall 5
rise 1
}
vrrp_instance VI_4 {
state BACKUP
notify_master "/data0/scripts/keepalived_scripts/start-postgres.sh"
unicast_src_ip 172.31.0.2
unicast_peer {
172.31.0.3
}
nopreempt #非抢占模式，用于在故障恢复的时候不切换
interface ens5
virtual_router_id 54
priority 140
authentication {
auth_type PASS
auth_pass 555555
}
virtual_ipaddress {
172.31.0.66
}
track_script {
check_postgres
}
}
cat /data0/scripts/keepalived_scripts/check-postgres.sh
#!/bin/bash
fix_pid=`ps -aux|grep postgres|grep -v check-postgres.sh|grep -v grep`
if [ -z "$fix_pid" ];
then
exit 1
else
exit 0
fi
cat /data0/scripts/keepalived_scripts/start-postgres.sh
#!/bin/bash
su - postgres -c "pg_ctl promote -D /data/apps_data/postgres_data/"
从节点配置
cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
router_id LVS_DEVEL
script_user root
# enable_script_security
}
vrrp_script check_postgres {
script "/data0/scripts/keepalived_scripts/check-postgres.sh"
interval 10
weight 1
fall 3
rise 1
}
vrrp_instance VI_4 {
state BACKUP
notify_master "/data0/scripts/keepalived_scripts/start-postgres.sh"
notify_backup "/data0/scripts/keepalived_scripts/kill-postgres.sh"
unicast_src_ip 172.31.0.3
unicast_peer {
172.31.0.2
}
interface ens5
virtual_router_id 54
# nopreempt
priority 142
authentication {
auth_type PASS
auth_pass 555555
}
virtual_ipaddress {
172.31.0.66
}
track_script {
check_postgres
}
}
cat /data0/scripts/keepalived_scripts/start-postgres.sh
#!/bin/bash
su - postgres -c "pg_ctl promote -D /data/apps_data/postgres_data/"
touch /data/scripts/keepalived_scripts/postgres.text
cat /data0/scripts/keepalived_scripts/kill-postgres.sh
#!/bin/bash
rm -rf /data/scripts/keepalived_scripts/postgres.text #作为标识位，用于识别此时的节点信息是否为主
cat /data0/scripts/keepalived_scripts/check-postgres.sh
#!/bin/bash
fix_pid=`ps -aux|grep postgres|grep -v check-postgres.sh|grep -v grep`
if [ -z "$fix_pid" ];
then
if [ -e "/data/scripts/keepalived_scripts/postgres.text" ]; #判断是否为主，若是从节点postgres宕机则无操作，为主节点时宕机则需要重启keepalived
then
systemctl restart keepalived
fi
exit 0
else
exit 0
fi
先启动主节点的keepalived然后再启动从节点的keepalived
高可用描述：当主从正常时，主节点的优先级为150，从节点的优先级为143，主节点获得vip：172.31.0.66，当主节点的postgres宕机时，其优先级下降为140，从节点升级为主机点获得vip提供服务，当旧的主节点恢复的时候由于设置了非抢占模式，所以哪怕优先级回到了150也不会抢占vip，将其配置为新主节点的从即可继续实现高可用，在此时如果新的主节点的postgres再发生宕机，那么根据keepalived的check脚本会重启keepalived，让非抢占式的从节点成为主节点，vip又切换回去了
写的有些匆忙可能还会有一些不足，欢迎指正
联系邮箱：958905365@qq.com
参考链接：
