PostgreSQL流复制之主备切换

环境背景

	数据库版本	IP	端口
主库	13.2	192.168.60.190	5432
备库	13.2	192.168.60.191	5433

switchover

检查参数

主库

postgres=# select name,setting from pg_settings where name in('synchronous_commit','synchronous_standby_names');
           name            | setting 
---------------------------+---------
 synchronous_commit        | on
 synchronous_standby_names | pgs1
(2 rows)

postgres=# 

备库

postgres=# select name,setting from pg_settings where name in('synchronous_commit','synchronous_standby_names','primary_conninfo');
           name            |                                                                                                                   setting                                                                                   
                                
---------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------
 primary_conninfo          | application_name=pgs1 user=replixs password=replixs channel_binding=disable host=192.168.60.190 port=5432 sslmode=disable sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any
 synchronous_commit        | on
 synchronous_standby_names | 
(3 rows)

postgres=# 

主库操作

停止数据库

[postgres@lyp ~]$ pg_ctl stop
waiting for server to shut down.... done
server stopped
[postgres@lyp ~]$ 

添加standby.signal文件

[postgres@lyp ~]$ touch  /pgsql/data/standby.signal
[postgres@lyp ~]$ ll /pgsql/data/standby.signal
-rw-rw-r--. 1 postgres postgres 0 Jan 10 18:55 /pgsql/data/standby.signal
[postgres@lyp ~]$ chmod 600 /pgsql/data/standby.signal
[postgres@lyp ~]$ ll /pgsql/data/standby.signal
-rw-------. 1 postgres postgres 0 Jan 10 18:55 /pgsql/data/standby.signal
[postgres@lyp ~]$ 

修改参数

echo 
"primary_conninfo = 'user=replixs password=replixs channel_binding=disable host=192.168.60.191 port=5433 sslmode=disable sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any'"
>> /pgsql/data/postgresql.conf

备库操作

停止数据库

[postgres@pgs1 ~]$ pg_ctl stop
waiting for server to shut down........ done
server stopped
[postgres@pgs1 ~]$ 

删除standby.signal文件

[postgres@pgs1 data]$ pwd
/pgsql/data
[postgres@pgs1 data]$ rm standby.signal 
[postgres@pgs1 data]$ ll standbt.signal
ls: cannot access standbt.signal: No such file or directory
[postgres@pgs1 data]$ 

修改参数

由于备库的primary_conninfo参数是写到postgresql.auto.conf文件中，所以只需要对postgresql.auto.conf文件进行修改即可。

[postgres@pgs1 data]$ sed -i 's/primary_conninfo/#primary_conninfo/g' /pgsql/data/postgresql.auto.conf 
[postgres@pgs1 data]$ more /pgsql/data/postgresql.auto.conf 
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
#primary_conninfo = 'application_name=pgs1 user=replixs password=replixs channel_binding=disable host=192.168.60.190 port=5432 sslmode=disable sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any'
[postgres@pgs1 data]$ 

启动数据库

新主库

[postgres@pgs1 ~]$ pg_ctl start
waiting for server to start....2022-01-10 18:36:19.022 CST [21549] LOG:  redirecting log output to logging collector process
2022-01-10 18:36:19.022 CST [21549] HINT:  Future log output will appear in directory "pg_log".
 done
server started
[postgres@pgs1 ~]$