pg_rewind的使用

环境:
PG:pg14
切换前:
主库:192.168.1.108
备库:192.168.1.109
切换后
主库:192.168.1.109
备库:192.168.1.108

 

在做主从切换后发现主从不一致的情况,可以尝试使用pg_rewind进行一致性同步.

下面是切换后的操作步骤

1.停掉之前的之前的主库
su - postgres
[postgres@pg2 data]$sudo systemctl stop postgresql-14

 

2.执行pg_rewind

su - postgres
[postgres@pg2 log]$ pg_rewind -D /opt/pg14/data --source-server='host=192.168.1.109 port=5432 user=postgres dbname=postgres password=postgres'
pg_rewind: servers diverged at WAL location 0/A000000 on timeline 1
pg_rewind: error: could not open file "/opt/pg14/data/pg_wal/000000010000000000000009": No such file or directory
pg_rewind: fatal: could not find previous WAL record at 0/90000D8
[postgres@pg2 log]$

 

这里提示wal日志不存在,需要从归档目录找到该文件然后copy到pg_wal目录,若本机的归档目录不存在的话
从新主库上查找,然后scp过来

[postgres@pg2 log]$cp /opt/pg14/archivelog/000000010000000000000009 /opt/pg14/data/pg_wal/

 

继续pg_rewind

[postgres@pg2 log]$ pg_rewind -D /opt/pg14/data --source-server='host=192.168.1.109 port=5432 user=postgres dbname=postgres password=postgres'
pg_rewind: servers diverged at WAL location 0/A000000 on timeline 1
pg_rewind: rewinding from last common checkpoint at 0/9000028 on timeline 1
pg_rewind: Done!

 

3.创建standby.signal文件
[postgres@pg2 data]$ cd /opt/pg14/data
[postgres@pg2 data]$ touch standby.signal

 

4.修改配置文件postgresql.auto.conf
做了pg_rewind后会自动将主库上的配置文件postgresql.auto.conf和postgresql.conf拷贝过来
我们这里修改postgresql.auto.conf文件,同步指向新的主库

修改前的文件内容

[postgres@pg2 data]$ more postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=repl password=postgres channel_binding=prefer host=192.168.1.108 port=5432 sslmode=prefer ss
lcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=a
ny'

 

修改后的

[postgres@pg2 data]$ more postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=repl password=postgres channel_binding=prefer host=192.168.1.109 port=5432 sslmode=prefer ss
lcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=a
ny'

 

5.启动数据库
sudo systemctl start postgresql-14