pglogical 之二复制数据

一时失言乱红尘 2023-03-13 02:46 10阅读 0赞

os: centos 7.4  
db: postgresql 11.7  
pglogical: 2.3.1

pglogical is a logical replication system implemented entirely **as a PostgreSQL extension**.  
Fully integrated, it requires **no triggers** or **external programs**.  
This alternative to physical replication is a highly efficient method of replicating data using a **publish/subscribe** model for selective replication.

192.168.56.111  pg1
    192.168.56.112  pg2
    192.168.56.113  pg3

# 版本 #

# cat /etc/centos-release
    CentOS Linux release 7.4.1708 (Core) 
    # 
    # 
    # yum list installed |grep -i postgresql
    postgresql11.x86_64                11.7-1PGDG.rhel7                    @pgdg11  
    postgresql11-contrib.x86_64        11.7-1PGDG.rhel7                    @pgdg11  
    postgresql11-debuginfo.x86_64      11.5-1PGDG.rhel7                    @pgdg11  
    postgresql11-devel.x86_64          11.7-1PGDG.rhel7                    @pgdg11  
    postgresql11-docs.x86_64           11.7-1PGDG.rhel7                    @pgdg11  
    postgresql11-libs.x86_64           11.7-1PGDG.rhel7                    @pgdg11  
    postgresql11-llvmjit.x86_64        11.7-1PGDG.rhel7                    @pgdg11  
    postgresql11-odbc.x86_64           12.01.0000-1PGDG.rhel7              @pgdg11  
    postgresql11-plperl.x86_64         11.7-1PGDG.rhel7                    @pgdg11  
    postgresql11-plpython.x86_64       11.7-1PGDG.rhel7                    @pgdg11  
    postgresql11-plpython3.x86_64      11.7-1PGDG.rhel7                    @pgdg11  
    postgresql11-pltcl.x86_64          11.7-1PGDG.rhel7                    @pgdg11  
    postgresql11-server.x86_64         11.7-1PGDG.rhel7                    @pgdg11  
    postgresql11-tcl.x86_64            2.4.0-2.rhel7.1                     @pgdg11  
    postgresql11-test.x86_64           11.7-1PGDG.rhel7                    @pgdg11
    
    # su - postgres
    Last login: Wed Jan 15 18:34:12 CST 2020 on pts/0
    $
    $
    $ psql -c "select version();"
                                                     version                                                 
    ---------------------------------------------------------------------------------------------------------
     PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
    (1 row)

# create extension pglogical #

三个节点都安装 pglogical extension

# create extension uuid-ossp #

三个节点都安装 uuid-ossp extension，主要用来获取表的主键，尽量不使用 sequence

yewudb=# select * from pg_available_extensions where name like '%uuid%';
       name    | default_version | installed_version |                     comment                     
    -----------+-----------------+-------------------+-------------------------------------------------
     uuid-ossp | 1.1             |                   | generate universally unique identifiers (UUIDs)
    (1 row)
    
    yewudb=# create extension "uuid-ossp";
    
    yewudb=# select uuid_generate_v4();
               uuid_generate_v4           
    --------------------------------------
     026de20e-c1ac-4917-9fc6-468ab843de0c
    (1 row)
    
    yewudb=# \dx
                                List of installed extensions
       Name    | Version |   Schema   |                   Description                   
    -----------+---------+------------+-------------------------------------------------
     pglogical | 2.3.1   | pglogical  | PostgreSQL Logical Replication
     plpgsql   | 1.0     | pg_catalog | PL/pgSQL procedural language
     uuid-ossp | 1.1     | public     | generate universally unique identifiers (UUIDs)
    (3 rows)

# create table #

三个节点都执行 create table  
pglogical 默认是不支持 ddl 复制的(可以通过 pglogical.replicate\_ddl\_command 修改)

$ psql -U yewudata yewudb
    
    yewudb=> create table tmp_t0 (
     id   varchar(100) not null default uuid_generate_v4() primary key ,
     name varchar(100)
    )
    ;

# pg1 节点上执行 #

create the provider

yewudb=# SELECT pglogical.create_node(
        node_name := 'provider1',
        dsn := 'host=pg1 port=5432 dbname=yewudb'
    );

Add all tables in public schema to the default replication set.

yewudb=# SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);

# pg2 节点上执行 #

First the subscriber node must be created:

yewudb=# SELECT pglogical.create_node(
        node_name := 'subscriber1',
        dsn := 'host=pg2 port=5432 dbname=yewudb'
    );

yewudb=# SELECT pglogical.create_subscription(
        subscription_name := 'subscription1',
        provider_dsn := 'host=pg1 port=5432 dbname=yewudb'
    );

# pg3 节点上执行 #

the subscriber node must be created:

yewudb=# SELECT pglogical.create_node(
        node_name := 'subscriber2',
        dsn := 'host=pg3 port=5432 dbname=yewudb'
    );

yewudb=# SELECT pglogical.create_subscription(
        subscription_name := 'subscription2',
        provider_dsn := 'host=pg1 port=5432 dbname=yewudb'
    );

# 验证 #

pg1 节点上插入数据

$ psql -U yewudata yewudb
    
    yewudb=> insert into tmp_t0 (name)
    select md5(id::varchar) as name
      from generate_series(1,10) as id
    ;
    
    yewudb=# select * from tmp_t0;
                      id                  |               name               
    --------------------------------------+----------------------------------
     deb36cc4-224f-41bb-9096-9b058bd7d01f | c4ca4238a0b923820dcc509a6f75849b
     68ff028b-27cc-4fc4-aa20-86db21c71d59 | c81e728d9d4c2f636f067f89cc14862c
     7a27c2f5-2835-40ce-bacb-f51c2500aaa6 | eccbc87e4b5ce2fe28308fd9f2a7baf3
     e11ee5f9-4bd6-4e86-b6a5-83f1aee6b2b4 | a87ff679a2f3e71d9181a67b7542122c
     e250a715-cfda-4f50-9bc3-d02667f6ba96 | e4da3b7fbbce2345d7772b0674a318d5
     80f8bb7c-6928-4628-b0e6-f0e3531f0639 | 1679091c5a880faf6fb5e6087eb1b2dc
     bc73613e-9bc9-48c4-a980-fac071981792 | 8f14e45fceea167a5a36dedd4bea2543
     a813d998-f121-41dd-8815-fe18b3eaef8f | c9f0f895fb98ab9159f51fd0297e236d
     b9c9c5ca-8a04-4289-a7e0-ba9dd646116e | 45c48cce2e2d7fbdea1afc51c7c6ad26
     b2399453-2a40-4882-b030-b4fd884ffec0 | d3d9446802a44259755d38e6d163e820
    (10 rows)

pg2 节点上查询数据

$ psql -U yewudata yewudb
    
    yewudb=# select * from tmp_t0;
                      id                  |               name               
    --------------------------------------+----------------------------------
     deb36cc4-224f-41bb-9096-9b058bd7d01f | c4ca4238a0b923820dcc509a6f75849b
     68ff028b-27cc-4fc4-aa20-86db21c71d59 | c81e728d9d4c2f636f067f89cc14862c
     7a27c2f5-2835-40ce-bacb-f51c2500aaa6 | eccbc87e4b5ce2fe28308fd9f2a7baf3
     e11ee5f9-4bd6-4e86-b6a5-83f1aee6b2b4 | a87ff679a2f3e71d9181a67b7542122c
     e250a715-cfda-4f50-9bc3-d02667f6ba96 | e4da3b7fbbce2345d7772b0674a318d5
     80f8bb7c-6928-4628-b0e6-f0e3531f0639 | 1679091c5a880faf6fb5e6087eb1b2dc
     bc73613e-9bc9-48c4-a980-fac071981792 | 8f14e45fceea167a5a36dedd4bea2543
     a813d998-f121-41dd-8815-fe18b3eaef8f | c9f0f895fb98ab9159f51fd0297e236d
     b9c9c5ca-8a04-4289-a7e0-ba9dd646116e | 45c48cce2e2d7fbdea1afc51c7c6ad26
     b2399453-2a40-4882-b030-b4fd884ffec0 | d3d9446802a44259755d38e6d163e820
    (10 rows)

pg3 节点上查询数据

参考:

https://www.2ndquadrant.com/en/resources/pglogical/  
https://www.2ndquadrant.com/en/resources/pglogical/pglogical-docs/  
https://www.2ndquadrant.com/en/resources/pglogical/pglogical-installation-instructions/

https://github.com/2ndQuadrant/pglogical