安装部署 pglogical

规划图

版本	复制角色	主机名	IP	数据库	模式	用户名
11.2	提供者	test01	10.175.2.186	lxdb	lxdb01	lxdb01
11.2	订阅者	test02	10.175.2.187	lxdsdb	lxdb01	lxdb01

准备工作

提供者节点创建表空间，数据库，模式及其用户

postgres=# create tablespace lxdb_tbs location '/postgres/11/tbs/lxdb';
CREATE TABLESPACE
postgres=# create database lxdb tablespace lxdb_tbs ;
CREATE DATABASE
postgres=# create user lxdb01 with password 'lxdb01';
CREATE ROLE
postgres=# revoke CONNECT ON DATABASE lxdb from PUBLIC ;
REVOKE
postgres=# grant CONNECT on DATABASE lxdb to lxdb01 ;
GRANT
postgres=# \c lxdb
lxdb=# create schema lxdb01;
CREATE SCHEMA
lxdb=# grant ALL on SCHEMA lxdb01 to lxdb01 ;
GRANT
lxdb=# alter role lxdb01 set search_path to lxdb01 ;
ALTER ROLE

订阅者节点创建表空间，数据库，模式及其用户

postgres=# create tablespace lxdsdb_tbs location '/postgres/11/tbs/lxdsdb';
CREATE TABLESPACE
postgres=# create database lxdsdb tablespace lxdsdb_tbs;
CREATE DATABASE
postgres=# create user lxdb01 with password 'lxdb01';
CREATE ROLE
postgres=# revoke CONNECT on DATABASE lxdsdb from PUBLIC;
REVOKE
postgres=# grant CONNECT on DATABASE lxdsdb to lxdb01;
GRANT
postgres=# \c lxdsdb 
You are now connected to database "lxdsdb" as user "postgres".
lxdsdb=# create schema lxdb01;
CREATE SCHEMA
lxdsdb=# grant ALL on SCHEMA lxdb01 to lxdb01;
GRANT
lxdsdb=# alter role lxdb01 set search_path to lxdb01;
ALTER ROLE

安装配置pglogical

提供者节点&&订阅者节点

安装pglogical插件

yum install postgresql11-pglogical

修改配置文件postgresql.conf

wal_level = logical
max_worker_processes = 10
max_replication_slots = 10
max_wal_senders = 10
shared_preload_libraries = 'pglogical'

修改认证文件pg_hba.conf

host replication all 192.168.0.0/16 trust

重启postgres服务

postgres@test01:[~]: pg_ctl restart

设置复制订阅

提供者节点

在lxdb数据库中创建扩展

postgres=# \c lxdb 
You are now connected to database "lxdb" as user "postgres".
lxdb=# create extension pglogical;
CREATE EXTENSION
lxdb=# \dx
                   List of installed extensions
   Name    | Version |   Schema   |          Description           
-----------+---------+------------+--------------------------------
 pglogical | 3.6.1   | pglogical  | PostgreSQL Logical Replication
 plpgsql   | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

在lxdb数据库中创建节点

lxdb=# SELECT pglogical.create_node(node_name := 'test01-lxdb',dsn := 'host=test01 port=5432 dbname=lxdb');
 create_node 
-------------
  1467475886
(1 row)

lxdb=# select * from pglogical.node;
  node_id   |  node_name  
------------+-------------
 1467475886 | test01-lxdb
(1 row)

lxdb=# select * from pglogical.node_interface;
   if_id   |   if_name   | if_nodeid  |              if_dsn               
-----------+-------------+------------+-----------------------------------
 582727187 | test01-lxdb | 1467475886 | host=test01 port=5432 dbname=lxdb
(1 row)

在lxdb数据库中创建复制集

lxdb=# SELECT pglogical.create_replication_set (
       set_name := 'lxdb01_repset',
       replicate_insert := true,
       replicate_update := true,
       replicate_delete := true,
       replicate_truncate := false,
       autoadd_tables := false,
       autoadd_sequences := false,
       autoadd_existing := false);
 create_replication_set 
------------------------
             1714531502
(1 row)
lxdb=# select * from pglogical.replication_set;
   set_id   | set_nodeid |      set_name       | replicate_insert | replicate_update | replicate_delete | replicate_truncate | set_isinternal | set_autoadd_tables | set_autoadd_seqs 
------------+------------+---------------------+------------------+------------------+------------------+--------------------+----------------+--------------------+------------------
  822708183 | 1467475886 | default             | t                | t                | t                | t                  | f              | f                  | f
 3984194936 | 1467475886 | default_insert_only | t                | f                | f                | t                  | f              | f                  | f
  597667261 | 1467475886 | ddl_sql             | t                | f                | f                | f                  | f              | f                  | f
 1714531502 | 1467475886 | lxdb01_repset       | t                | t                | t                | f                  | f              | f                  | f
(4 rows)

在lxdb01_repset复制集中添加表

lxdb=# SELECT pglogical.replication_set_add_all_tables (
       set_name := 'lxdb01_repset',
       schema_names := ARRAY['lxdb01'],
       synchronize_data := true);
 replication_set_add_all_tables 
--------------------------------
 t
(1 row)

lxdb=# select * from pglogical.replication_set_table ;
   set_id   |   set_reloid   | set_att_list | set_row_filter 
------------+----------------+--------------+----------------
 1714531502 | lxdb01.student |              | 
(1 row)

对当前模式做一个备份，仅仅备份表结构，并拷贝到订阅者节点

postgres@test01:[~]: pg_dump lxdb -f /tmp/lxdb01.dmp -n lxdb01 -s
postgres@test01:[~]: scp /tmp/lxdb01.dmp test02:/tmp
lxdb01.dmp          100% 1155     1.8MB/s   00:00    

订阅者节点

在lxdsdb数据库中创建扩展

postgres=# \c lxdsdb
You are now connected to database "lxdsdb" as user "postgres".
lxdsdb=# create extension pglogical;
CREATE EXTENSION
lxdsdb=# \dx
                   List of installed extensions
   Name    | Version |   Schema   |          Description           
-----------+---------+------------+--------------------------------
 pglogical | 3.6.1   | pglogical  | PostgreSQL Logical Replication
 plpgsql   | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

在lxdsdb数据库中创建节点

lxdsdb=# SELECT pglogical.create_node(node_name := 'test02-lxdsdb',dsn := 'host=test02 port=5432 dbname=lxdsdb');
 create_node 
-------------
  1480287664
(1 row)

lxdsdb=# select * from pglogical.node;
  node_id   |   node_name   
------------+---------------
 1480287664 | test02-lxdsdb
(1 row)

lxdsdb=# select * from pglogical.node_interface;
   if_id    |    if_name    | if_nodeid  |               if_dsn                
------------+---------------+------------+-------------------------------------
 3433455137 | test02-lxdsdb | 1480287664 | host=test02 port=5432 dbname=lxdsdb
(1 row)

从提供者节点的备份文件中恢复模式

postgres@test02:[~]: psql lxdsdb -f /tmp/lxdb01.dmp

在lxdsdb数据库中创建订阅

lxdsdb=# SELECT pglogical.create_subscription (
         subscription_name := 'lxdb01_sub',
         provider_dsn := 'host=test01 port=5432 dbname=lxdb',
         replication_sets := ARRAY['lxdb01_repset']
         );
NOTICE:  created replication slot "pgl_lxdsdb_test01_lxdb_lxdb01_sub" on provider
 create_subscription 
---------------------
          1127779175
(1 row)
lxdsdb=# select subscription_name,status,provider_node,provider_dsn from pglogical.subscriptions;
 subscription_name |   status    | provider_node |           provider_dsn            
-------------------+-------------+---------------+-----------------------------------
 lxdb01_sub        | replicating | test01-lxdb   | host=test01 port=5432 dbname=lxdb
(1 row)

至此，pglogical在两个节点之间已经部署完成，提供者节点的dml语句都会同步到订阅者节点。