pglogical

最新推荐文章于 2025-02-08 09:51:40 发布

原创最新推荐文章于 2025-02-08 09:51:40 发布 · 1.5k 阅读

2 ·

CC 4.0 BY-SA版权

Postgresql 专栏收录该内容

20 篇文章

订阅专栏

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

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

https://www.2ndquadrant.com

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

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 | bash

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

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

3、

引用

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

修改 pg_hba.conf 文件

主库

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

如果报类似错是因为主库 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库。

从库

4、重启pg服务

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

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

5.1

进入pg创建扩展模块

wenzhong=# create extension pglogical;

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

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'

);

将所有架构为 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 该命令只会对现有的表加到发布中，后续新的表不会自动加入发布。

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'

);

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

SELECT pglogical.create_subscription(

subscription_name := 'subscription1',

provider_dsn := 'host=172.31.107.1 port=5432 dbname=wenzhong user=repluser'

);

查看有哪些接口

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

创建复制集

wenzhong=# select pglogical.create_replication_set('repl_set01',true,true,true);

修改复制集

wenzhong=# select pglogical.alter_replication_set('repl_set01',true,true,true,true);

创建复制集

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

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');

wenzhong=# select pglogical.replication_set_add_table('repl_set01','t_a',true,NULL,'address=''aj''');

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

wenzhong=# select * from pglogical.replication_set;

查看订阅状态

wenzhong=# select subscription_name, status FROM pglogical.show_subscription_status();

创建一个新的订阅

select pglogical.create_subscription(

subscription_name := 'subscription021',

provider_dsn := 'host=172.31.107.1 port=5432 dbname=wenzhong user=repluser',

replication_sets := '{repl_set001}'

);

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');

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；