Postgresql pglogical手动failover

172.31.43.79 （原主节点）
172.31.30.125 （原备节点）
172.31.37.213 （逻辑复制目标节点）
failover后，原来的主切换为备节点，由于用的pglogical插件，逻辑复制槽需要手动切换

1.查看node信息，并添加interface,这里源节点,目标节点的node名称一样。
 172.31.30.125上操作如下
postgres=# \c  melotlog postgres
You are now connected to database "melotlog" as user "postgres".
melotlog=# select * from pglogical.node;
  node_id   |    node_name     
------------+------------------
 3898668408 | providermelotlog
(1 row)

melotlog=# select * from pglogical.node_interface;
   if_id   |      if_name      | if_nodeid  |                             if_dsn                             
-----------+-------------------+------------+----------------------------------------------------------------
 390523749 | providermelotlog  | 3898668408 | host=pg27 port=1923 dbname=melotlog
 307620643 | providermelotlog1 | 3898668408 | host=172.31.43.79 port=1923 password=postgres dbname=melotlog
select * from pglogical.alter_node_add_interface('providermelotlog','providermelotlog2','host=172.31.30.125 port=1923 password=postgres dbname=melotlog');


2.172.31.37.213上操作,查看复制槽名称，也就是sub_slot_name字段，然后添加interface到node
select * from pglogical.subscription ;
  543279045 | subscription3 | 3898668408 |  648161748 |     314351209 |    1013769360 | t           | pgl_melotall_providermelotlog_subscription3 | {default,d
efault_insert_only,ddl_sql} | {all}               | 00:00:00

select * from pglogical.alter_node_add_interface('providermelotlog','providermelotlog2','host=172.31.30.125 port=1923 password=postgres dbname=melotlog');


3.回到172.31.30.125上操作如下：
SELECT * FROM pg_create_logical_replication_slot('pgl_melotall_providermelotlog_subscription3','pglogical_output');


4.再回到172.31.37.213,启用该interface
select * from pglogical.alter_subscription_interface('subscription3','providermelotlog2');
select * from pglogical.alter_subscription_enable('subscription3');


5.172.31.30.125查看复制槽状态，已恢复正常
melotlog=# select * from pg_replication_slots ;
                  slot_name                  |      plugin      | slot_type | datoid | database | active | active_pid | xmin | catalog_xmin | restart_lsn  | c
onfirmed_flush_lsn 
---------------------------------------------+------------------+-----------+--------+----------+--------+------------+------+--------------+--------------+--
-------------------
 pgl_melotall_providermelotlog_subscription3 | pglogical_output | logical   |  16446 | melotlog | t      |      31738 |      |   1970416336 | D58/5CDC2C58 | D
58/5CF0D658
(1 row)