pg13 交换主备库的角色（switchover）

数据库版本：

postgres=# select version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)

数据库基本信息：

主：127.0.0.1 5432  目录 /data/master

备：127.0.0.1 5433 目录/data/s001

01，停止主库和备库

-bash-4.2$ pg_ctl -D /data/master/ stop
waiting for server to shut down.... done
server stopped

-bash-4.2$ pg_ctl -D /data/s001/ stop
waiting for server to shut down.... done
server stopped

02，主库 建立standby.signal文件和修改postgresql.auto.conf文件

-bash-4.2$ touch standby.signal

-bash-4.2$ cat postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
synchronous_commit = 'on'
synchronous_standby_names = 'standby01'
primary_conninfo = 'application_name=standby02 user=postgres passfile=''/var/lib/pgsql/.pgpass'' channel_binding=disable host=127.0.0.1 port=5433 sslmode=disable sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any'

postgresql.auto.conf修改为原备机信息

03，备机删除standby.signal文件和修改postgresql.auto.conf文件

-bash-4.2$ rm standby.signal

-bash-4.2$ cat postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
synchronous_commit = 'on'
synchronous_standby_names = 'standby02'

05，启动原来备机

-bash-4.2$ pg_ctl -D /data/s001/ start
waiting for server to start....2021-10-24 16:45:58.956 CST [10171] LOG:  starting PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
2021-10-24 16:45:58.957 CST [10171] LOG:  listening on IPv6 address "::1", port 5433
2021-10-24 16:45:58.957 CST [10171] LOG:  listening on IPv4 address "127.0.0.1", port 5433
2021-10-24 16:45:58.972 CST [10171] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5433"
2021-10-24 16:45:58.989 CST [10172] LOG:  database system was shut down in recovery at 2021-10-24 16:37:31 CST
2021-10-24 16:45:58.990 CST [10172] LOG:  database system was not properly shut down; automatic recovery in progress
2021-10-24 16:45:58.998 CST [10172] LOG:  invalid record length at 0/30001C0: wanted 24, got 0
2021-10-24 16:45:58.998 CST [10172] LOG:  redo is not required
2021-10-24 16:45:59.052 CST [10171] LOG:  database system is ready to accept connections
 done
server started

06,启动原主数据库

-bash-4.2$ pg_ctl -D /data/master start
waiting for server to start....2021-10-24 16:46:56.103 CST [10606] LOG:  starting PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
2021-10-24 16:46:56.105 CST [10606] LOG:  listening on IPv6 address "::1", port 5432
2021-10-24 16:46:56.105 CST [10606] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2021-10-24 16:46:56.128 CST [10606] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2021-10-24 16:46:56.166 CST [10607] LOG:  database system was shut down at 2021-10-24 16:36:57 CST
2021-10-24 16:46:56.170 CST [10607] LOG:  entering standby mode
2021-10-24 16:46:56.182 CST [10607] LOG:  consistent recovery state reached at 0/30001C0
2021-10-24 16:46:56.182 CST [10607] LOG:  invalid record length at 0/30001C0: wanted 24, got 0
2021-10-24 16:46:56.184 CST [10606] LOG:  database system is ready to accept read only connections
2021-10-24 16:46:56.204 CST [10611] LOG:  started streaming WAL from primary at 0/3000000 on timeline 1
2021-10-24 16:46:56.204 CST [10612] LOG:  standby "standby02" is now a synchronous standby with priority 1
2021-10-24 16:46:56.204 CST [10612] STATEMENT:  START_REPLICATION 0/3000000 TIMELINE 1
2021-10-24 16:46:56.212 CST [10607] LOG:  redo starts at 0/30001C0
 done
server started

07，检查同步状态

-bash-4.2$ psql -p 5433
psql (13.4)
Type "help" for help.

postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 10612
usesysid         | 10
usename          | postgres
application_name | standby02
client_addr      | 127.0.0.1
client_hostname  |
client_port      | 58472
backend_start    | 2021-10-24 16:46:56.200167+08
backend_xmin     |
state            | streaming
sent_lsn         | 0/3000270
write_lsn        | 0/3000270
flush_lsn        | 0/3000270
replay_lsn       | 0/3000270
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 1
sync_state       | sync
reply_time       | 2021-10-24 16:47:46.437295+08

#主从切换完成。