《童虎学习笔记》10分钟学会PostgreSQL逻辑复制pglogical

本文章配套视频	https://www.ixigua.com/7077056019024904717?id=7075728284322890253
本专栏全部文章	https://blog.csdn.net/tonghu_note/category_11713514.html
总目录	https://blog.csdn.net/tonghu_note/article/details/124333034

我的dou音 aa10246666， 看配套视频

---

一、场景

provider	pg 9.6	10.211.55.9
subscriber	pg 14	10.211.55.4

                        要搭建从provider到subscriber的逻辑复制

---

二、安装pglogical

1、provider上操作

curl https://techsupport.enterprisedb.com/api/repository/dl/default/release/deb | bash
apt-get install postgresql-9.6-pglogical

2、subscriber上操作

curl https://techsupport.enterprisedb.com/api/repository/dl/default/release/deb | bash
apt-get install postgresql-14-pglogical

3、provider、subscriber上都操作

postgresql.conf文件中需要有如下配置

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

pg_hba.conf 文件中需要有如下配置

host    replication     postgres            10.211.55.9/32      trust
host    d1                   postgres            10.211.55.9/32      trust
host    replication     postgres            10.211.55.4/32      trust
host    d1                   postgres            10.211.55.4/32      trust

建库及创建插件

create database d1;

\c d1
CREATE EXTENSION pglogical;

---

三、配置pglogical

1、provider上操作

\c d1

create table t1(id int primary key);
create table t2(id int primary key);
insert into t1 select 1;

创建provider节点

SELECT pglogical.create_node(
    node_name := 'provider1',
    dsn := 'host=10.211.55.9 port=5432 dbname=d1'
);

将public架构中的所有表添加到default复制集中, 复制集default的表都必需要primary key

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

2、subscriber上操作

创建subscriber节点

\c d1
SELECT pglogical.create_node(
    node_name := 'subscriber1',
    dsn := 'host=10.211.55.4 port=5432 dbname=d1'
);

创建subscription

\c d1
SELECT pglogical.create_subscription(
    subscription_name := 'subscription1',
    provider_dsn := 'host=10.211.55.9 port=5432 dbname=d1',
    synchronize_structure := true,
    synchronize_data := true
);

---

四、管理

1、provider上操作

查看有哪些节点

select * from pglogical.node_interface;

查看有哪些复制集

select * from pglogical.replication_set;

查看有哪些表参与复制

select * from pglogical.replication_set_table;

删除提供节点

select pglogical.drop_node(node_name := 'provider1', ifexists := true);

将新建t3表添加进复制集

select pglogical.replication_set_add_table(set_name := 'default', relation := 't3', synchronize_data := true);

将t2表移出复制集

select * from pglogical.replication_set_remove_table(set_name := 'default', relation := 't2')

将所有表加进复制集

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

如何实现ddl的复制

select pglogical.replicate_ddl_command('alter table public.t2 add column name varchar');

2、subscriber上操作

查看订阅状态

select pglogical.show_subscription_status('subscription1');

正常状态

(subscription1,replicating,provider1,"host=10.211.55.9 port=5432 dbname=d1",pgl_d1_provider1_subscription1,"{default,default_insert_only,ddl_sql}",{all})

故障状态, 可能是由表有没有先建好

(subscription1,down,provider1,"host=10.211.55.9 port=5432 dbname=d1",pgl_d1_provider1_subscription1,"{default,default_insert_only,ddl_sql}",{all})

禁用状态

(subscription1,disabled,provider1,"host=10.211.55.9 port=5432 dbname=d1",pgl_d1_provider1_subscription1,"{default,default_insert_only,ddl_sql}",{all})

查看有哪些节点

select * from pglogical.node_interface;

查看有哪些复制集

select * from pglogical.replication_set;

禁用订阅

select pglogical.alter_subscription_disable('subscription1', true);

启用订阅

select pglogical.alter_subscription_enable('subscription1', true);

删除订阅节点(需要先删除订阅）

select pglogical.drop_node(node_name := 'subscriber1', ifexists := true);

删除某个订阅

select pglogical.drop_subscription(subscription_name := 'subscription1', ifexists := true);

重新同步指定表，会先truncate掉表，然后重新同步

select pglogical.alter_subscription_resynchronize_table('subscription1', 't2'::regclass);

重新同步所有表

select pglogical.alter_subscription_synchronize(subscription_name := 'subscription1', truncate := true);

查看指定表的订阅状态, 正常状态是replicating, 但是有可能出现unknown状态而数据确是同步的

select pglogical.show_subscription_table('subscription1', 't1');

---

五、大版本PG升级的场景， 同步完升级后，可以把逻辑复制关系删除掉

1、subscriber上操作

\c d1

select pglogical.drop_subscription(subscription_name := 'subscription1', ifexists := true);
select pglogical.drop_node(node_name := 'subscriber1', ifexists := true);

2、provider上操作

\c d1

select pglogical.drop_node(node_name := 'provider1', ifexists := true);
 

六、作业

实现一个类似于MySQL多源复制的架构