PostgreSQL配置主从同步

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:$PATH

source  ~/.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 -confirm

chmod 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.conf

node_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 show
 ID | 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=postgres
 2  | 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 show
 ID | 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=postgres
 2  | test2 | standby |   running | test1    | default  | 100      | 1        | host=192.168.10.101 user=repmgr port=5432 dbname=postgres