pg逻辑复制

逻辑复制

1.主库配置的修改
wal_level = logical
max_wal_senders = 10
max_replication_slots = 10 
2.postgres=# \c demo_ds_1   //在master机器的某个库上执行
进一个库中执行
CREATE PUBLICATION myorder FOR TABLE t_order_0;

3.从库上执行订阅
	清空从库里面的要同步表的数据,防止主键冲突
4.从库上执行 SUBSCRIPTION 进入一个具体的库中执行,默认会在主库上创建一个p1_sub的槽
CREATE SUBSCRIPTION p1_sub CONNECTION 'host=192.168.232.190 port=5432 user=postgres dbname=demo_ds_1 password=postgres' PUBLICATION myorder;
#主库上执行创建槽 貌似没啥用
#SELECT pg_create_logical_replication_slot('logical_slot','test_decoding');

场景

场景一在主库上执行槽位后,在从库上关联这个槽位

1.在主库创建publication
2.在主库创建复制槽--逻辑槽
	SELECT * FROM pg_create_logical_replication_slot('p2_sub', 'pgoutput');
3. 在同一个cluster 的 另一个订阅库，创建不带复制槽的订阅
	CREATE SUBSCRIPTION p2_sub CONNECTION 'host=192.168.232.190 dbname=demo_ds_1 user=postgres password=postgres port=5432' PUBLICATION myorder with (create_slot = false,slot_name = p2_sub);

场景二备库上追加数据

copy_data=false表示的备库的基础上可以追加数据

copy_data=false表示追加数据
CREATE SUBSCRIPTION xxxp1_subxxx CONNECTION 'host=192.168.232.190 port=5432 user=postgres dbname=demo_ds_1 password=postgres' PUBLICATION myorder with(copy_data=false,create_slot=true,synchronous_commit=off,connect=true);

此时数据并不是按顺序的有可能追加的这样显示

场景三发布端与订阅端建立并且要求同步

发布端已经创建完了,此时在订阅端创建时带上application_name=pgsub

CREATE SUBSCRIPTION p1_sub CONNECTION 'application_name=pgsub host=192.168.232.190 port=5432 user=postgres dbname=demo_ds_1 password=postgres' PUBLICATION myorder with(copy_data=true,create_slot=true,synchronous_commit=on,connect=true);

然后在主的postgresql.conf文件里搜 synchronous_standby_names

synchronous_standby_names = 'pgsub'

此时再去查询主备关系

demo_ds_1=# selecct * f^C
demo_ds_1=# select * from pg_stat_replication;
-[ RECORD 1 ]----+-----------------------------
pid              | 12637
usesysid         | 10
usename          | postgres
application_name | pgsub
client_addr      | 192.168.232.191
client_hostname  | 
client_port      | 60466
backend_start    | 2020-08-12 15:31:04.32948+08
backend_xmin     | 
state            | streaming
sent_lsn         | 0/175F3F8
write_lsn        | 0/175F3F8
flush_lsn        | 0/175F3F8
replay_lsn       | 0/175F3F8
write_lag        | 
flush_lag        | 
replay_lag       | 
sync_priority    | 1
sync_state       | sync

如果此时从挂了主会hung住

发布主题相关命令

发布创建

postgres=# \c demo_ds_1   //在master机器的某个库上执行
CREATE PUBLICATION myorder FOR TABLE t_order_0;

发布查询

postgres=# \c demo_ds_1   //在master机器的某个库上执行
SELECT * FROM pg_publication;

发布端添加新表

在添加新表到发布端之前 先确认已经在订阅端添加成功之后才能往PUBLICATION里追加

 demo_ds_1=# ALTER PUBLICATION xxxpub1xxx ADD TABLE xxxtb2xxx;

发布端tb列表查询

demo_ds_1=# SELECT * FROM pg_publication_tables;
     pubname     | schemaname |   tablename    
-----------------+------------+----------------
 myorder         | public     | t_order_0
 dbz_publication | public     | t_order_0
 dbz_publication | public     | t_order_1
 dbz_publication | public     | t_order_item_0
 dbz_publication | public     | t_order_item_1

订阅主题相关命令

订阅创建

订阅创建,不创建槽,此时不会同步数据

copy_data 同步历史数据
create_slot 在发布端创建槽
synchronous_commit off为异步同步
connect=true 是否连接到发布服务器true为默认值,如果为false则为表没有订阅没有任何内容

CREATE SUBSCRIPTION xxxp2_subxxx CONNECTION 'host=192.168.232.190 port=5432 user=postgres dbname=demo_ds_1 password=postgres' PUBLICATION xxxmyorderxxx with(copy_data=true,create_slot=true,synchronous_commit=off,connect=true);

create_slot=false 此时查询槽的信息如下,没有同步信息

select * from pg_stat_subscription;
  subid | subname | pid | relid | received_lsn | last_msg_send_time | last_msg_receipt_time | latest_end_lsn | latest_end_time 
-------+---------+-----+-------+--------------+--------------------+-----------------------+----------------+-----------------
 18044 | p2_sub  |     |       |              |                    |                       |                | 
(1 行记录)

指定一个老的槽

CREATE SUBSCRIPTION xxxsubxxx CONNECTION 'host=192.168.198.200 dbname=dvdrental user=publication password=XXXXX port=5432' PUBLICATION dvdrental with (create_slot = false,slot_name = xxxpublication_dvdxxx);

订阅修改或失败

ALTER SUBSCRIPTION p2_sub sub SET(slot_name = xxxp1_subxxx); #指定槽的名字
#有时删除主题失败需要先置成失效,再清空槽,再drop
ALTER SUBSCRIPTION xxxsubxxx disable;
ALTER SUBSCRIPTION xxxsubxxx  SET (slot_name =NONE);
drop subscription xxxsubxxx;

订阅删除

drop subscription xxxp2_subxxx;

订阅查询

(在发布端执行)

demo_ds_1=# select * from pg_replication_slots;
 slot_name |  plugin  | slot_type | datoid | database  | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn 
-----------+----------+-----------+--------+-----------+-----------+--------+------------+------+--------------+-------------+---------------------
 p1_sub    | pgoutput | logical   |  16390 | demo_ds_1 | f         | f      |            |      |          584 | 0/17333B8   | 0/1733690
 debezium  | pgoutput | logical   |  16390 | demo_ds_1 | f         | f      |            |      |          582 | 0/1732520   | 0/1732638
 p2_sub    | pgoutput | logical   |  16390 | demo_ds_1 | f         | t      |       8424 |      |          584 | 0/17336C8   | 0/1733700
(3 rows)

在订阅端查询

demo_ds_0=#  select * from pg_subscription;
-[ RECORD 1 ]---+--------------------------------------------------------------------------------
subdbid         | 16390
subname         | p1_sub
subowner        | 10
subenabled      | t
subconninfo     | host=192.168.232.190 port=5432 user=postgres dbname=demo_ds_1 password=postgres
subslotname     | p1_sub
subsynccommit   | off
subpublications | {myorder}

demo_ds_0=#  select * from pg_replication_origin_status; 
-[ RECORD 1 ]----------
local_id    | 1
external_id | pg_16455
remote_lsn  | 0/1732A70
local_lsn   | 0/0

订阅刷新

demo_ds_1=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;

常用于有时主库的publication里增加了一张表,从库也有这张表,但是从库里的表确没有记录可以通过此命令刷新一下

应用接入问题:

看logfile文件

调试错误要看logfile文件 否则完法定位

清空备库文件

如果新建一个默认的备库的订阅端的话,从库表要清了,否则报错主键冲突

此时清了备库表中的数据 或者 使用追加方式 copy_data=false 添加订阅端