pglogical

柔光的暖阳◎ 2022-02-19 01:11 180阅读 0赞

前段时间由于架构的不合理性，高并发下 整套集群性能下降厉害（50个库），最后定位是由于一个500+G的库 在并且的情况下，做了类似mongodb的 upsert操作，导致其他库受到牵连，而研发端又暂时没有好的解决方法。后决定将这个库从该实例上迁出。

于是采用了 pglogical 工具。先将 pglogical是使用共享

[https://www.2ndquadrant.com][https_www.2ndquadrant.com]

[https://www.2ndquadrant.com/en/resources/pglogical/pglogical-installation-instructions/][https_www.2ndquadrant.com_en_resources_pglogical_pglogical-installation-instructions]

[https://www.2ndquadrant.com/en/resources/pglogical/pglogical-docs/][https_www.2ndquadrant.com_en_resources_pglogical_pglogical-docs]

pglogical限制

1、目前只支持超级用户权限；

2、unlogged 和 temporary 不能被复制

3、一次只能发布和订阅一个库，即一一对应关系；

4、表必须 有 主键或唯一标识（唯一约束），否则 update和 delete 不能被复制；

如果表开启了 replica identity full 或 replica identity nothing，也会导致表不能被复制。

这时需将 表的的 full 改为 replica identity default.

**案例：**

**如果表a  当前为 ****replica identity full，并且有100万数据。如果要将表a也加入pglogical中（比如复制集repl\_set001中），则将表a 改为 ****replica identity default **

**再执行  ****select pglogical.replication\_set\_add\_table('repl\_set001','a',true);**

**这样 从修改为 defult后的数据会全部同步到新pg中，之前的full则会被pglogical选择丢弃， 如果这时 又把 表a 改为了 ****replica identity full  那么同样新数据还会同步到新pg上，最早full前的数据还是不会同步到新pg上（即pglogical选择丢弃）。**

**注：因为我们业务中使用到了 wal2json ，把一些表 开了 ****replica identity full，所以遇到了上面的情况。也就是说 wal2json和 pglogical同时使用时会有问题。**

5、如果是级联复制，则下游的表只应该存在一个unique索引，即便行满足primary key，但违反下游的的unique约束也会报错error，停止复制；

6、不能主动复制 DDL操作，必须用  pglogical.replicate\_ddl\_command  函数来 发布和订阅上运行以保持一致性；

7、不支持TRUNCATE;

8、不支持队列刷新（表结构的变更，即便用了replicate\_ddl\_command函数也可能失败），最好在做表结构变更时，停止表的写）；

9、不会校验强制外键约束；

10、队列sequences 不是实时同步，而是定时同步的；

11、create table 将不被复制；

12、低版本发布可以同步到 高版本；建议使用统一的版本；

13、数据库要统一编码（建议使用 utf-8);

14、表名和列名 必须一致及其上的约束也必须一致，约束不能设置为 NOT NULL

15、不建议使用 unique 来替代 primary key；

环境 CENTOS 7

主：172.31.107.1

从：172.31.107.2（物理复制）

从：172.31.107.3（逻辑复制）

1、安装

\[root@vlnx107001 ~\]\# curl [https://access.2ndquadrant.com/api/repository/dl/default/release/9.6/rpm][https_access.2ndquadrant.com_api_repository_dl_default_release_9.6_rpm] | bash![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW93ZW56aG9uZw_size_16_color_FFFFFF_t_70][]

\[root@vlnx107001 ~\]\# yum install postgresql96-pglogical -y

![20190415115542976.png][]

2、安装完毕后会在 ~/lib 和 ~/share 下产生 pglogical相关文件

![20190415115542971.png][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW93ZW56aG9uZw_size_16_color_FFFFFF_t_70 1][]

3、

引用

[https://www.2ndquadrant.com/en/resources/pglogical/pglogical-docs/][https_www.2ndquadrant.com_en_resources_pglogical_pglogical-docs]

修改 postgresql.conf文件

\[root@vlnx107001 pgsql-9.6\]\# vim /var/lib/pgsql/9.6/data/postgresql.conf

![2019041511554339.png][]

修改 pg\_hba.conf 文件

主库

![2019041511554345.png][]

下列是在 订阅PG服务器上执行时报的错误

![2019041511554389.png][]

**如****果报类似错是因为 主库 pg\_hba.conf 缺少**

**host    replication    postgres    172.31.107.1/32         trust**

**host    replication    postgres    172.31.107.3/32         trust**

**注意： 如果将 database 列 配置 为 all 也是不对的，因为 all 不包括  replication库。**

从库

![2019041511554393.png][]

4、重启pg服务

\[root@vlnx107001 pgsql-9.6\]\# systemctl restart postgresql-9.6.service

5、主库服务器上执行下列：

5.1

进入pg创建扩展模块

wenzhong=\# create extension pglogical;

![20190415115543110.png][]

主从创建复制专用账号（目前只支持超级用户权限）

postgres=\# create user repluser login  replication superuser; \#不起作用

postgres=\# alter user postgres with replication;

创建发布者节点

wenzhong=\# SELECT pglogical.create\_node(

node\_name := 'provider1',

dsn := 'host=172.31.107.1 port=5432 dbname=wenzhong'

);

![20190415115543118.png][]

![20190415115543140.png][]

将所有架构为 public下的 表设置为发布对象

wenzhong=\# SELECT pglogical.replication\_set\_add\_all\_tables('default', ARRAY\['public'\]);

或

wenzhong=\# SELECT pglogical.replication\_set\_add\_all\_tables('default', ARRAY\['public'\],true); \#如果数据量大会影响主库性能

**pglogical.****replication\_set\_add\_all\_tables****(set\_name name, schema\_names text\[\], synchronize\_data boolean)**

**synchronize\_data 默认值 false。 这样订阅服务器上并不会同步 原存储的数据，只会同步后续增量的数据。**

**如果为true 则会在 订阅服务器上创建完订阅后会将原存储的数据同步到所有订阅服务器上（在一个事务中完成），这样会导致主库性能下降；**

**replication\_set\_add\_all\_tables 该命令只会对现有的表加到发布中，后续新的表不会自动加入发布。**

![20190415115543145.png][]

5.2、从库服务器(在订阅服务器上)

创建复制专用账号（目前只支持超级用户权限）

postgres=\# create user repluser login  replication superuser;

postgres=\# alter user postgres with replication;

wenzhong=\# create extension pglogical;

创建订阅者节点

SELECT pglogical.create\_node(

node\_name := 'subscriber1',

dsn := 'host=172.31.107.3 port=5432 dbname=wenzhong'

);

![20190415115543149.png][]

![20190415115543163.png][]

最后创建订阅，开始以后台运行的方式同步数据

SELECT pglogical.create\_subscription(

subscription\_name := 'subscription1',

provider\_dsn := 'host=172.31.107.1 port=5432 dbname=wenzhong user=repluser'

);

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW93ZW56aG9uZw_size_16_color_FFFFFF_t_70 2][]

--------------------

查看有哪些 接口

wenzhong=\# select \* from pglogical.node\_interface;（主从都可以）

![20190415115543217.png][]

创建复制集

wenzhong=\# select pglogical.create\_replication\_set('repl\_set01',true,true,true);

![20190415115543214.png][]

修改复制集

wenzhong=\# select pglogical.alter\_replication\_set('repl\_set01',true,true,true,true);

![20190415115543232.png][]

创建复制集

fsdb002=\# SELECT pglogical.replication\_set\_add\_all\_tables('default', ARRAY\['public'\],true);

![20190415115543247.png][]

fsdb002=\# SELECT pglogical.replication\_set\_add\_all\_tables('default', ARRAY\['public'\]); \#默认模式 false；

**区别 见上面说明**

创建复制集并使用行过滤

wenzhong=\# select pglogical.replication\_set\_add\_table('repl\_set01','t\_b',true,NULL,row\_filter :='qty>300');

![20190415115543258.png][]

wenzhong=\# select pglogical.replication\_set\_add\_table('repl\_set01','t\_a',true,NULL,'address=''aj''');

![20190415115543252.png][]

查看有哪些复制集（主从都可以）

wenzhong=\# select \* from pglogical.replication\_set;

![20190415115543281.png][]

查看订阅状态

wenzhong=\# select subscription\_name, status FROM pglogical.show\_subscription\_status();

![20190415115543276.png][]

创建一个新的订阅

select pglogical.create\_subscription(

subscription\_name := 'subscription021',

provider\_dsn := 'host=172.31.107.1 port=5432 dbname=wenzhong user=repluser',

replication\_sets := '\{repl\_set001\}'

);

![20190415115543292.png][]

![20190415115543300.png][]

[https://www.2ndquadrant.com/en/resources/pglogical/pglogical-docs/][https_www.2ndquadrant.com_en_resources_pglogical_pglogical-docs]

**Node管理**

1、创建node

select pglogical.create\_node(node\_name name, dsn text)

2、删除node

select pglogical.drop\_node(node\_name name, ifexists bool)

3、给node添加其他接口

select pglogical.alter\_node\_add\_interface(node\_name name, interface\_name name, dsn text)

4、删除指定接口

select pglogical.alter\_node\_drop\_interface(node\_name name, interface\_name name)

5、查看有哪些接口

select \* from  pglogical.node\_interface;

6、查看有哪些复制集

select \* from pglogical.replication\_set;

Subscription管理

1、创建订阅

pglogical.create\_subscription(subscription\_name name, provider\_dsn text, replication\_sets text\[\], synchronize\_structure boolean, synchronize\_data boolean, forward\_origins text\[\], apply\_delay interval)

2、删除订阅

查看有哪些订阅

select \* from pglogical.subscription;

pglogical.drop\_subscription(subscription\_name name, ifexists bool)

select \* from pglogical.drop\_subscription('subscription11');

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW93ZW56aG9uZw_size_16_color_FFFFFF_t_70 3][]

3、禁用指定订阅并关闭现有连接

pglogical.alter\_subscription\_disable(subscription\_name name, immediate bool)

4、启用指定订阅

pglogical.alter\_subscription\_enable(subscription\_name name, immediate bool)

5、修改订阅接口名

pglogical.alter\_subscription\_interface(subscription\_name name, interface\_name name)

6、在复制集中所有未同步的表将在单个操作同步， 表将被 copy 并同步一个接一个，这个命令不会block，这是一个启动动作。

pglogical.alter\_subscription\_synchronize(subscription\_name name, truncate bool)

7、重新同步已存在的表，将会truncate表。慎用

pglogical.alter\_subscription\_resynchronize\_table(subscription\_name name, relation regclass)

8、查看订阅状态

pglogical.show\_subscription\_status(subscription\_name name)

9、查看指定表同步状态

pglogical.show\_subscription\_table(subscription\_name name, relation regclass)

10、向现有的订阅中添加一个复制集，不会同步数据。

pglogical.alter\_subscription\_add\_replication\_set(subscription\_name name, replication\_set name)

11、从订阅中移除指定复制集

pglogical.alter\_subscription\_remove\_replication\_set(subscription\_name name, replication\_set name)

Replication Sets管理

复制集 为订阅提供了 需要同步哪些表哪些列及表上的哪些(insert,update,delte,truncate)操作机制。

每个表可以在多个复制集中，每个订阅者也可以订阅多个复制集。

存在3个默认的复制集  default,default\_insert\_only,ddl\_sql;

default: 所有表；

default\_insert\_onley:仅复制 insert操作，适用于没有主键的表；(alter table table\_name replica identity full;

ddl\_sql:复制由 pglogical.replicate\_ddl\_command指定的架构更改；

1、创建复制集

pglogical.create\_replication\_set(set\_name name, replicate\_insert bool, replicate\_update bool, replicate\_delete bool, replicate\_truncate bool)

2、修改指定复制集的属性

pglogical.alter\_replication\_set(set\_name name, replicate\_inserts bool, replicate\_updates bool, replicate\_deletes bool, replicate\_truncate bool)

3、删除指定复制集

pglogical.drop\_replication\_set(set\_name text)

4、将表添加到复制集中（可以增 行过滤和列过滤）

pglogical.replication\_set\_add\_table(set\_name name, relation regclass, synchronize\_data boolean, columns text\[\], row\_filter text)

synchronize\_data：默认 false 不立即同步数据；true 立即同步数据；

columns：默认 NULL即所有列；

row\_filter：默认NULL 即不加行过滤；

synchronize\_data=true 与 row\_filter一起使用是一次性操作，如果修改了row\_filter后，并不会按最新的row\_filter过滤重新同步数据，

订阅者需要调用pglogical.alter\_subscription\_resynchronize\_table() 来同步数据；

行过滤 可以在 发布者服务器上配置也可以在 订阅者服务器上配置。

订阅者服务器上使用 before trigger机制过滤，需要将任何此类触发器标记为 enable replica 或 enable always,否则将不会被执行。

5、将制定架构下的所有表添加的复制集中

pglogical.replication\_set\_add\_all\_tables(set\_name name, schema\_names text\[\], synchronize\_data boolean)

synchronize\_data 默认false 即不立即同步数据，true立即同步数据（在一个大事务中完成），只对现有存在的表起作用，

后续新加的表不会自动添加到复制集中，如果要自动添加到复制集中，可通过事件触发器完成，请参考下面方法：

例如

RETURNS event\_trigger AS $$

DECLARE obj record;

BEGIN

FOR obj IN SELECT \* FROM pg\_event\_trigger\_ddl\_commands()

LOOP

IF obj.object\_type = 'table' THEN

IF obj.schema\_name = 'config' THEN

PERFORM pglogical.replication\_set\_add\_table('configuration', obj.objid);

ELSIF NOT obj.in\_extension THEN

PERFORM pglogical.replication\_set\_add\_table('default', obj.objid);

END IF;

END LOOP;

END;

$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER pglogical\_assign\_repset\_trg

ON ddl\_command\_end

WHEN TAG IN ('CREATE TABLE', 'CREATE TABLE AS')

EXECUTE PROCEDURE pglogical\_assign\_repset();

6、将指定表从复制集中移除

pglogical.replication\_set\_remove\_table(set\_name name, relation regclass) ；

7、将序列添加到复制集中

pglogical.replication\_set\_add\_sequence(set\_name name, relation regclass, synchronize\_data boolean)

relation ：序列名或序列对应的OID

8、将指定架构下的所有序列添加到复制集，同样只对现有的序列有效，后续新增加的序列不会自动添加到复制集中

pglogical.replication\_set\_add\_all\_sequences(set\_name name, schema\_names text\[\], synchronize\_data boolean)

9、将指定的序列移除复制集

pglogical.replication\_set\_remove\_sequence(set\_name name, relation regclass)

删除 发布和订阅时 建议先删除 订阅再删发布

查看订阅者

select \* from pglogical.subscription;

查看订阅节点

select \* from  pglogical.node\_interface;

1、select pglogical.drop\_subscription('subscription001'); 订阅者

2、select pglogical.drop\_node('subscriber001');订阅（类似分发）

3、drop extension pglogical；

发布

select \* from  pglogical.node\_interface;

1、select pglogical.drop\_node('provider001');

2、drop extension pglogical；

[https_www.2ndquadrant.com]: https://www.2ndquadrant.com/
[https_www.2ndquadrant.com_en_resources_pglogical_pglogical-installation-instructions]: https://www.2ndquadrant.com/en/resources/pglogical/pglogical-installation-instructions/
[https_www.2ndquadrant.com_en_resources_pglogical_pglogical-docs]: https://www.2ndquadrant.com/en/resources/pglogical/pglogical-docs/
[https_access.2ndquadrant.com_api_repository_dl_default_release_9.6_rpm]: https://access.2ndquadrant.com/api/repository/dl/default/release/9.6/rpm
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW93ZW56aG9uZw_size_16_color_FFFFFF_t_70]: /images/20220219/1343a3d272ef4d47829921f12c5f7d5b.png
[20190415115542976.png]: /images/20220219/6425e04fcca14e9aa69d5f4b84c582c9.png
[20190415115542971.png]: /images/20220219/561d6c01e39244f08b080545f71bf9af.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW93ZW56aG9uZw_size_16_color_FFFFFF_t_70 1]: /images/20220219/0351b0abc4bb41069536cc6835880727.png
[2019041511554339.png]: /images/20220219/556b2d5b707b4ac4a227e736448c782a.png
[2019041511554345.png]: /images/20220219/5f1bb2639d2943f19a9868f2c536edca.png
[2019041511554389.png]: /images/20220219/959926989f1c49719f0271c176cbaa81.png
[2019041511554393.png]: /images/20220219/da8370f9e8d84f968ee7838bbe674540.png
[20190415115543110.png]: /images/20220219/7e992ce4bcb44fe3b669f18d273c8789.png
[20190415115543118.png]: /images/20220219/602ac950eaed4e688a232d4e55cda9aa.png
[20190415115543140.png]: /images/20220219/d97aaa17aee3489ba4034178a9ebaa63.png
[20190415115543145.png]: /images/20220219/b1aff5dbd3ee49d397ff4cd2040648da.png
[20190415115543149.png]: /images/20220219/d84742e9b9bd4892938fe17eccbf3f04.png
[20190415115543163.png]: /images/20220219/ad6ad3e23bc54ff1b243750906d1d18e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW93ZW56aG9uZw_size_16_color_FFFFFF_t_70 2]: /images/20220219/d8cfbd1355ec438eb22162c6c4c925cf.png
[20190415115543217.png]: /images/20220219/6d328e18afa04fc083eb3457391187aa.png
[20190415115543214.png]: /images/20220219/09b5d10d1e34445fa1cbec2dfba03aa8.png
[20190415115543232.png]: /images/20220219/f0dd4417931f460489492e70b66100a9.png
[20190415115543247.png]: /images/20220219/271323c0371e4ab7846e7b95517d2cfd.png
[20190415115543258.png]: /images/20220219/4c0f8912d3404e18b011fd8a60e18d71.png
[20190415115543252.png]: /images/20220219/916c1140794b46ed8ad3c069b465ade6.png
[20190415115543281.png]: /images/20220219/a966058014304d408cfba3e05f67b207.png
[20190415115543276.png]: /images/20220219/656b1e40bcb54ca99e7ad050b2225350.png
[20190415115543292.png]: /images/20220219/5afe269af65543aaae560047e1243803.png
[20190415115543300.png]: /images/20220219/c8b2ca1899f8464e90e501ca4d492f15.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW93ZW56aG9uZw_size_16_color_FFFFFF_t_70 3]: /images/20220219/93f283092f7c4ccea1e7831b008e0160.png

pglogical

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 pglogical 之二复制数据

相关 pglogical