Postgresql pglogical安装以及使用实践

postgresql10以后自身具有逻辑复制功能，即发布和订阅，但是之前的版本如果想要表级别的同步，需要pglogic插件实现。原理不再详述，该篇文章只介绍安装以及使用。数据库安装略。

下载安装：

#切换到postgres系统用户解压安装，需要在需要逻辑复制的节点均安装该插件
wget https://github.com/2ndQuadrant/pglogical/archive/REL2_x_STABLE.zip
unzip REL2_x_STABLE.zip
cd pglogical-REL2_x_STABLE/
USE_PGXS=1 make clean
USE_PGXS=1 make
USE_PGXS=1 make install

配置pg参数

wal_level = 'logical'
max_worker_processes = 10   # one per database needed on provider node
                            # one per node needed on subscriber node
max_replication_slots = 10  # one per node needed on provider node
max_wal_senders = 10        # one per node needed on provider node
shared_preload_libraries = 'pglogical'

#9.5以上打开以下参数
track_commit_timestamp = on 

使用案例：

源节点IP:10.4.9.166
目标节点IP:10.4.9.250

#配置pg_hba.conf文件，测试服务器均为10网段，为了方便,我加入以下条目
host  all  all  10.0.0.0/8 trust

#以下操作在源节点操作，创建role,database,table并写入数据
postgres=# create role logic login ;
CREATE ROLE
postgres=# create database logic with owner postgres;
CREATE DATABASE
postgres=# grant all on DATABASE logic to logic with grant option;
GRANT
postgres=# \c  logic logic
You are now connected to database "logic" as user "logic".
logic=> create schema logic;
CREATE SCHEMA
logic=> grant USAGE on SCHEMA logic to logic ;
GRANT
logic=> create table tbl_logic(a int,b text);
CREATE TABLE
logic=> insert into tbl_logic values (1,'replica set');
INSERT 0 1
logic=> select * from tbl_logic ;
 a |      b      
---+-------------
 1 | replica set
(1 row)
logic=# alter table logic.tbl_logic add primary key(a);
ALTER TABLE

#使用超级用户安装插件
logic=> \c logic postgres 
You are now connected to database "logic" as user "postgres".
logic=# create extension pglogical;
CREATE EXTENSION
logic=# \dx
                   List of installed extensions
   Name    | Version |   Schema   |          Description           
-----------+---------+------------+--------------------------------
 pglogical | 2.3.3   | pglogical  | PostgreSQL Logical Replication
 plpgsql   | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

#创建provider node
SELECT pglogical.create_node(
    node_name := 'logic_pub',
    dsn := 'host=10.4.9.166 port=1921 user=postgres dbname=logic'
);

#可以复制某个schema下所有表
SELECT pglogical.replication_set_add_all_tables('default', ARRAY['hank']);
#也可以指定表
select pglogical.replication_set_add_table('default',relation:='logic.tbl_logic'::regclass,synchronize_data:=true);
 replication_set_add_table 
---------------------------
 t
(1 row)


#以下操作均在目标节点操作

postgres=# create role logic login ;
CREATE ROLE
postgres=# create database logic with owner postgres;
CREATE DATABASE
postgres=# grant all on DATABASE logic to logic with grant option;
GRANT
postgres=# \c  logic logic 
You are now connected to database "logic" as user "logic".
logic=> create schema logic;
CREATE SCHEMA
logic=> grant USAGE on SCHEMA logic to logic ;
GRANT
logic=> \c  logic postgres
logic=# create extension pglogical;
CREATE EXTENSION


#创建subscriber node，注意IP为目标复制节点IP
SELECT pglogical.create_node(
    node_name := 'logic_sub',
    dsn := 'host=10.4.9.250 port=1921 user=postgres dbname=logic'
);

#创建subscription，ip以及其他信息写源地址相关信息
SELECT pglogical.create_subscription(
    subscription_name := 'logic_subscription',
    provider_dsn := 'host=10.4.9.166 port=1921 user=postgres dbname=logic'
);

#可见
logic=# select * from logic.tbl_logic ;
 a |      b      
---+-------------
 1 | replica set
(1 row)

启用和禁用subscription

#如果不需要，可以删除掉node和subscription,目标端要先删除subscription,再删除node.
SELECT pglogical.drop_subscription('logic_subscription');
SELECT pglogical.drop_node('logic_sub');

#先查看一下subscription状态，可以看到为replicating
logic=# select pglogical.show_subscription_status('logic_subscription');
                                                                              show_subscription_status                                                
                               
------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------
 (logic_subscription,replicating,logic_pub,"host=10.4.9.166 port=1921 user=postgres dbname=logic",pgl_logic_logic_pub_logic_su0d01101,"{default,defaul
t_insert_only,ddl_sql}",{all})

#禁用subscription
logic=# select pglogical.alter_subscription_disable('logic_subscription', true);
 alter_subscription_disable 
----------------------------
 t
(1 row)
#源节点插入一行
logic=# insert INTO logic.tbl_logic values (3,'apple');
logic=# select * from logic.tbl_logic ;                
 a |      b      
---+-------------
 1 | replica set
 2 | dba
 3 | apple
 #由于禁用，备节点没有同步该行
logic=# select * from logic.tbl_logic ;
 a |      b      
---+-------------
 1 | replica set
 2 | dba
(2 rows)

#查看subscription基本状态信息，看到为disable
logic=# select pglogical.show_subscription_status('logic_subscription');        
                                                                             show_subscription_status                                                 
                            
------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------
 (logic_subscription,disabled,logic_pub,"host=10.4.9.166 port=1921 user=postgres dbname=logic",pgl_logic_logic_pub_logic_su0d01101,"{default,default_i
nsert_only,ddl_sql}",{all})
(1 row)

#启用后，自动复制数据
logic=# select pglogical.alter_subscription_enable('logic_subscription', true);        
 alter_subscription_enable 
---------------------------
 t
(1 row)

logic=# select pglogical.show_subscription_status('logic_subscription');       
                                                                              show_subscription_status                                                
                               
------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------
 (logic_subscription,replicating,logic_pub,"host=10.4.9.166 port=1921 user=postgres dbname=logic",pgl_logic_logic_pub_logic_su0d01101,"{default,defaul
t_insert_only,ddl_sql}",{all})
(1 row)

logic=# select * from logic.tbl_logic ;                                        
 a |      b      
---+-------------
 1 | replica set
 2 | dba
 3 | apple
 
 #重新同步已存在的表，注意这个函数首先会truncate表，再同步
 select
 pglogical.alter_subscription_resynchronize_table('logic_subscription', 'logic.tbl_logic'::regclass); 

可以使用函数支持DDL复制

#主节点执行添加列
logic=# select pglogical.replicate_ddl_command('alter table logic.tbl_logic add column c text');
 replicate_ddl_command 
-----------------------
 t
(1 row)
#复制节点可以看到添加列
logic=> select * from tbl_logic ;
 a | b | c 
---+---+---
(0 rows)

可以查看相关视图和表获取相关信息

#如下，都在pglogical schema下
logic=# \dtv pglogical.*
                  List of relations
  Schema   |         Name          | Type  |  Owner   
-----------+-----------------------+-------+----------
 pglogical | depend                | table | postgres
 pglogical | local_node            | table | postgres
 pglogical | local_sync_status     | table | postgres
 pglogical | node                  | table | postgres
 pglogical | node_interface        | table | postgres
 pglogical | queue                 | table | postgres
 pglogical | replication_set       | table | postgres
 pglogical | replication_set_seq   | table | postgres
 pglogical | replication_set_table | table | postgres
 pglogical | sequence_state        | table | postgres
 pglogical | subscription          | table | postgres
 pglogical | tables                | view  | postgres

参考：
https://www.2ndquadrant.com/en/resources-old/pglogical/pglogical-docs/