PostgreSQL配置主从同步
1 主、备库安装postgresql软件
su - pg12
cd /home/pg12/resource
tar -zxvf postgresql-12.9.tar.gz
cd postgresql-12.9/
./configure --prefix=/home/pg12/soft/
make -j 16 && make install
2 主、备库配置环境变量
vi ~/.bash_profile export PGHOME=/home/pg12/soft
export PGDATA=/home/pg12/repmgr
export PGPORT=5432
export PGUSER=postgres
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$PGHOME/lib
export PATH=$PGHOME/bin:$PATHsource ~/.bash_profile
3 主、备库安装repmgr软件
cd /home/pg12/resource/
tar -zxvf repmgr-5.1.0.tar.gz
cd repmgr-5.1.0/
./configure
make -j 8 && make install
4 配置ssh免密
– 主节点，root用户执行
cd /home/pg12/resource/
chmod +x sshUserSetup.sh
./sshUserSetup.sh -user pg12 -hosts "192.168.10.100 192.168.10.101" -advanced exverify -confirmchmod 600 /home/pg12/.ssh/config
5 主库初始化
su - pg12
initdb -D /home/pg12/repmgr -U postgres -k
6 主库配置 pg_hba.conf
vi /home/pg12/repmgr/pg_hba.conf
host replication all all trust
host all all all trust
7 主库配置 postgresql.conf
vi /home/pg12/repmgr/postgresql.conf
listen_addresses = '*'
port=5432
shared_preload_libraries ='repmgr'
wal_log_hints=on
8 重启主库
pg_ctl start -D /home/pg12/repmgr
9 创建复制用户repmgr
createuser -p 5432 -s repmgr
10 创建repmgr的配置目录
--两个节点
mkdir /home/pg12/conf/
11 主库配置 repmgr.conf
vi /home/pg12/conf/repmgr.confnode_id=1
node_name=test1
conninfo='host=192.168.10.100 user=repmgr port=5432 dbname=postgres'
data_directory='/home/pg12/repmgr'
log_file='/home/pg12/conf/repmgr.log'
pg_bindir='/home/pg12/soft/bin'monitoring_history=yes
monitor_interval_secs=1
failover='automatic'
reconnect_attempts=6
reconnect_interval=5
promote_command='repmgr standby promote -f /home/pg12/conf/repmgr.conf --log-to-file'
follow_command='repmgr standby follow -f /home/pg12/conf/repmgr.conf --log-to-file --upstream-node-id=%n'
12 备库配置 repmgr.conf
vi /home/pg12/conf/repmgr.conf node_id=2
node_name=test2
conninfo='host=192.168.10.101 user=repmgr port=5432 dbname=postgres'
data_directory='/home/pg12/repmgr'
log_file='/home/pg12/conf/repmgr.log'
pg_bindir='/home/pg12/soft/bin'monitoring_history=yes
monitor_interval_secs=1
failover='automatic'
reconnect_attempts=6
reconnect_interval=5
promote_command='repmgr standby promote -f /home/pg12/conf/repmgr.conf --log-to-file'
follow_command='repmgr standby follow -f /home/pg12/conf/repmgr.conf --log-to-file --upstream-node-id=%n'
13 创建软链接
--两个节点
su - root
ln -s /home/pg12/conf/repmgr.conf /etc/repmgr.conf
chown pg12.pg12 /etc/repmgr.conf
14 主库注册
su - pg12
repmgr -f /home/pg12/conf/repmgr.conf primary register
repmgr -f /home/pg12/conf/repmgr.conf cluster show
repmgrd -d -f /home/pg12/conf/repmgr.conf
15 备库注册
su - pg12
repmgr -h 192.168.10.100 -U repmgr -d postgres -f /home/pg12/conf/repmgr.conf standby clone --force
pg_ctl -D /home/pg12/repmgr start
repmgr -f /home/pg12/conf/repmgr.conf standby register
repmgr -f /home/pg12/conf/repmgr.conf cluster show
repmgrd -d -f /home/pg12/conf/repmgr.conf
16 查看主从状态
–主库执行
[pg12@test1 repmgr]$ repmgr -f /home/pg12/conf/repmgr.conf cluster showID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+-------+---------+-----------+----------+----------+----------+----------+-----------------------------------------------------------1 | test1 | primary | * running | | default | 100 | 1 | host=192.168.10.100 user=repmgr port=5432 dbname=postgres2 | test2 | standby | running | test1 | default | 100 | 1 | host=192.168.10.101 user=repmgr port=5432 dbname=postgres
–备库执行
[pg12@test2 ~]$ repmgr -f /home/pg12/conf/repmgr.conf cluster showID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+-------+---------+-----------+----------+----------+----------+----------+-----------------------------------------------------------1 | test1 | primary | * running | | default | 100 | 1 | host=192.168.10.100 user=repmgr port=5432 dbname=postgres2 | test2 | standby | running | test1 | default | 100 | 1 | host=192.168.10.101 user=repmgr port=5432 dbname=postgres
