pglogical 之二 复制数据

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

# su - postgres
$ psql

postgres=# \l
                                      List of databases
        Name        |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
--------------------+----------+----------+-------------+-------------+-----------------------
 pgbench_plprofiler | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 pgbenchdb          | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres           | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0          | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                    |          |          |             |             | postgres=CTc/postgres
 template1          | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                    |          |          |             |             | postgres=CTc/postgres
 yewudb             | yewudata | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(6 rows)

postgres=# \c yewudb

yewudb=# select * from pg_available_extensions where name like '%pglogical%';
       name       | default_version | installed_version |                              comment                             
  
------------------+-----------------+-------------------+--------------------------------------------------------------------
 pglogical        | 2.3.1           |                   | PostgreSQL Logical Replication
 pglogical_origin | 1.0.0           |                   | Dummy extension for compatibility when upgrading from Postgres 9.4
(2 rows)

yewudb=# create extension pglogical;

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
(2 rows)

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 节点上查询数据

$ 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)

参考:

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