pg逻辑复制的一些特性

已于 2023-11-24 20:31:26 修改
阅读量1.1k 收藏
点赞数
分类专栏： PG 文章标签： 数据库 postgresql 逻辑复制 replica
于 2023-11-24 20:24:24 首次发布
无版权，随便玩儿
本文链接：https://blog.csdn.net/qq_40687433/article/details/134606336
版权

之前已经写过一篇比较详细的关于逻辑复制的文章了，这里不会再重复描述一些基础知识。不过难免有些知识点有遗漏，最近发一些有意思的逻辑复制特性。

replica identity与old/new值

replica identity是用来在逻辑复制期间标识一行数据的。
上面这句话当然没有问题，但是没有解释old和new数据的变化。

DEFAULT
Records the old values of the columns of the primary key, if any. This is the default for non-system tables.
USING INDEX index_name
Records the old values of the columns covered by the named index, that must be unique, not partial, not deferrable, and include only columns marked NOT NULL. If this index is dropped, the behavior is the same as NOTHING.
FULL
Records the old values of all columns in the row.
NOTHING
Records no information about the old row. This is the default for system tables.

pg官方文档对于replica identity甚至只解释了old值的情况，例如nothing不复制update/delete都没解释，足见old值的重要性。

创建一个复制链路：

select pg_create_logical_replication_slot('pubtestlzl2','test_decoding');
pg_recvlogical -d lzldb --slot=pubtestlzl2 --start -f recv.sql &

test_decoding的复制链路正常模拟

--replica identity默认是d：有主键时用主键；没有主键时为nothing，无法复制update和delete
M=>  create table lzltest(a bigint primary key,b varchar(100),c varchar(100));
CREATE TABLE
M=> insert into lzltest values(1,'bbbbbb','ccccccccc');
INSERT 0 1
M=> update lzltest set b='b';
UPDATE 1

recvlogical输出

table public.lzltest: INSERT: a[bigint]:1 b[character varying]:'bbbbbb' c[character varying]:'ccccccccc'
table public.lzltest: UPDATE: a[bigint]:1 b[character varying]:'b' c[character varying]:'ccccccccc'

replica identity为default，更新非主键字段，所有字段只有new值

M=> update lzltest set a='111';
UPDATE 1
table public.lzltest: UPDATE: old-key: a[bigint]:1 new-tuple: a[bigint]:111 b[character varying]:'bb' c[character varying]:'ccccccccc'

replica identity为default，更新主键时，解析出了主键标识的old和new值，其他字段只有new值

M=> alter table lzltest replica identity full;
ALTER TABLE

M=>  update lzltest set b='b';
UPDATE 1
table public.lzltest: UPDATE: old-key: a[bigint]:2 b[character varying]:'b' c[character varying]:'ccccccccc' new-tuple: a[bigint]:2 b[character varying]:'b' c[character varying]:'ccccccccc'

replica identity设置为full，保留整行数据的old和new值

无论是default（主键）还是full模式，都会记录所有字段的信息，区别在于是否有old数据。default时：

  • insert：本身是new数据，当然没有old值，会记录new值的所有字段
  • update：记录所有字段的new值，只有标识字段才有old值（如果有更新标识字段）
  • delete：本身是old数据，不一定记录所有字段。同样适用只有标识字段才有old值，只记录标识位。

总结：当replica identity为default时，无论什么操作（INSERT,UPDATE,DELTE)，只要是old数据，只记录标识字段；只要是new数据，记录所有字段。
把default修改为full时，解析出来的日志量差别不会特别大，因为new数据永远是所有字段，（不考虑全是delete的场景下）full解析出的日志量小于default时的两倍。

pgoutput不能peek

用pgoutput去创建一个复制槽

select pg_create_logical_replication_slot('pubtestlzl','pgoutput');

然后去peek或者接收，都失败

select * from pg_logical_slot_peek_changes('pubtestlzl',null,null);
pg_recvlogical -d lzldb --slot=pubtestlzl --start -f recv.sql &

pg_recvlogical: error: could not send replication command "START_REPLICATION SLOT "pubtestlzl" LOGICAL 0/0": ERROR:  client sent proto_version=0 but we only support protocol 1 or higher
CONTEXT:  slot "pubtestlzl", output plugin "pgoutput", in the startup callback
pg_recvlogical: disconnected; waiting 5 seconds to try again

不能peek或pg_recvlogical去接收pgoutput的复制槽。由于pgoutput是发布订阅的output plugin，这个plugin不能人工去peek或接收···

发布订阅可以不在pg-pg之间

create publication、create subscription都是pg内部的命令，也可以用他们来创建pg库之间的链路。
三方软件同样可以使用创建发布，并模拟订阅并创建复制槽。这样比直接创建复制槽要更好，因为可以通过发布管理复制表。

toast和逻辑解析

发送toast的字段不会被解析！也就是说整行数据可能只有一部分会传递出来（toast字段本身没有更新的情况下）
正常解析会解析所有字段：

--创建一个test-coding的复制槽
=>  select pg_create_logical_replication_slot('logical_dest','test_decoding');
 pg_create_logical_replication_slot 
------------------------------------
 (logical_dest,349/A80040E0)
(1 row)

--创建一个小字段的表
=> create table test1(a int primary key,b varchar(100),c varchar(100));
CREATE TABLE

=> select* from pg_replication_slots;
  slot_name   |    plugin     | slot_type | datoid | database | temporary | active | active_pid |  xmin  | catalog_xmin | restart_lsn  | confirmed_flush_lsn | wal_status | safe_wal_size 
--------------+---------------+-----------+--------+----------+-----------+--------+------------+--------+--------------+--------------+---------------------+------------+---------------
 logical_dest | test_decoding | logical   | 418679 | lzldb    | f         | f      |     [null] | [null] |    872483335 | 349/A80040A8 | 349/A80040E0        | reserved   |        [null]
(1 row)


=>  insert into test1 values (1,'qwer','qwer');
INSERT 0 1
Time: 0.915 ms
=> select * from pg_logical_slot_peek_changes('logical_dest',null,null);
     lsn      |    xid    |                                               data                                               
--------------+-----------+--------------------------------------------------------------------------------------------------
 349/A8004C78 | 872483335 | BEGIN 872483335
 349/A80103E8 | 872483335 | COMMIT 872483335
 349/A8018B30 | 872483369 | BEGIN 872483369
 349/A8018B30 | 872483369 | table public.test1: INSERT: a[integer]:1 b[character varying]:'qwer' c[character varying]:'qwer'
 349/A8018C50 | 872483369 | COMMIT 872483369
(5 rows)
--insert被解析，包含所有字段

=> update test1 set b='zxcv' where c='qwer';
UPDATE 1
Time: 4.005 ms
=>  select * from pg_logical_slot_peek_changes('logical_dest',null,null);
     lsn      |    xid    |                                               data                                               
--------------+-----------+--------------------------------------------------------------------------------------------------
 349/A8004C78 | 872483335 | BEGIN 872483335
 349/A80103E8 | 872483335 | COMMIT 872483335
 349/A8018B30 | 872483369 | BEGIN 872483369
 349/A8018B30 | 872483369 | table public.test1: INSERT: a[integer]:1 b[character varying]:'qwer' c[character varying]:'qwer'
 349/A8018C50 | 872483369 | COMMIT 872483369
 349/A801D018 | 872483378 | BEGIN 872483378
 349/A801D018 | 872483378 | table public.test1: UPDATE: a[integer]:1 b[character varying]:'zxcv' c[character varying]:'qwer'
 349/A801D098 | 872483378 | COMMIT 872483378
(8 rows)
--update被解析，包含所有字段

正常来说，没有toast时，解析出来的数据是包含该行所有字段的数据的。

toast解析测试：

--将字段改大
=>  alter table test1 alter column b type varchar(3000);
ALTER TABLE
Time: 8.091 ms
=>   alter table test1 alter column c type varchar(3000);
ALTER TABLE
Time: 0.937 ms

--一个批量random的function
=> create or replace function f_random_str(length INTEGER) returns character varying
-> LANGUAGE plpgsql
-> AS $$
-> DECLARE
-> result varchar(3000);
-> BEGIN
-> SELECT array_to_string(ARRAY(SELECT chr((65 + round(random() * 25)) :: integer)
-> FROM generate_series(1,length)), '') INTO result;
-> return result;
-> END
-> $$;
CREATE FUNCTION

--插入数据
=>  insert into test1 values (2,f_random_str(2000),f_random_str(2000));
INSERT 0 1

--查看是否有toast
=> SELECT                                              
->   n.nspname as schema,                              
-> s.oid::regclass as relname,                     
-> s.reltoastrelid::regclass as toast_name,        
-> pg_relation_size(s.reltoastrelid) AS toast_size 
->   FROM                                              
-> pg_class s join pg_namespace n                  
-> on s.relnamespace = n.oid                       
->   WHERE                                             
-> relkind = 'r'                                   
-> AND reltoastrelid <> 0                          
-> AND n.nspname = 'public'                        
->   ORDER BY                                          
-> 3 DESC;
 schema | relname |        toast_name        | toast_size 
--------+---------+--------------------------+------------
 public | test1   | pg_toast.pg_toast_418714 |       8192
(1 row)

--通过主键进行更新，更新toast字段
=> update test1 set b='zxcv' where a=2;
UPDATE 1

=>  select * from pg_logical_slot_peek_changes('logical_dest',null,null);
     lsn      |    xid    |                                                                                                                                                                                      
--------------+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
...
 349/A851FD90 | 872483420 | BEGIN 872483420
 349/A85216E0 | 872483420 | table public.test1: INSERT: a[integer]:2 b[character varying]:'GIORCXQQWDBGTUNDZXAWMPYOUEGTECWTVQGDQGSPMEPJNPUQIFMESLRASBZWGONETRENDCHLDWVTDWJLTGRYUMFDOWHLEYLUTECPOVCYXFIATLKVEQTHSC'
 349/A85218A0 | 872483420 | COMMIT 872483420
 349/A8525CA8 | 872483429 | BEGIN 872483429
 349/A8525D50 | 872483429 | table public.test1: UPDATE: a[integer]:2 b[character varying]:'zxcv' c[character varying]:unchanged-toast-datum
 349/A8525DE0 | 872483429 | COMMIT 872483429

有toast的字段c，不涉及更新，没有解析的数据，直接抛出toast数据未变unchanged-toast-datum

用wal2json测试：

=>  select pg_create_logical_replication_slot('logical_json','wal2json');
pg_create_logical_replication_slot 
------------------------------------
(logical_json,349/A87CAB58)
(1 row)

=>  update test1 set b='zxcv' where a=2;
UPDATE 1
=>  \pset format wrapped
Output format is wrapped.
=>  \pset columns 200
Target width is 200.
=>  select * from pg_logical_slot_peek_changes('logical_json',null,null);
-[ RECORD 1 ]------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
lsn  | 349/A87CACF8
xid  | 872483495
data | {"change":[{"kind":"update","schema":"public","table":"test1","columnnames":["a","b"],"columntypes":["integer","character varying(3000)"],"columnvalues":[2,"zxcv"],"oldkeys":{"keynames":["a"],.
    |."keytypes":["integer"],"keyvalues":[2]}}]}

=> update test1 set b='zxcv' where a=1;
UPDATE 1
Time: 1.391 ms
=>  select * from pg_logical_slot_peek_changes('logical_json',null,null);
-[ RECORD 1 ]------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
lsn  | 349/A87CACF8
xid  | 872483495
data | {"change":[{"kind":"update","schema":"public","table":"test1","columnnames":["a","b"],"columntypes":["integer","character varying(3000)"],"columnvalues":[2,"zxcv"],"oldkeys":{"keynames":["a"],.
    |."keytypes":["integer"],"keyvalues":[2]}}]}
-[ RECORD 2 ]------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
lsn  | 349/A8CCA0D8
xid  | 872483509
data | {"change":[{"kind":"update","schema":"public","table":"test1","columnnames":["a","b","c"],"columntypes":["integer","character varying(3000)","character varying(3000)"],"columnvalues":[1,"zxcv".
    |.,"qwer"],"oldkeys":{"keynames":["a"],"keytypes":["integer"],"keyvalues":[1]}}]}
--更新时，解析出来没有c列的数据

wal2json也是一样的效果。

mysql的参数binlog_row_image可以调整binlog是否记录大字段：

  • full (Log all columns)
  • minimal (Log only changed columns, and columns needed to identify rows)
  • noblob (Log all columns, except for unneeded BLOB and TEXT columns)

pg库完全没有这种控制，默认就是toast字段不解析，无其他选项可以设置~

【PostgreSQL】PostgreSQL表replica identity属性查询
tttzzzqqq2018的博客
10-15 377
这种形式更改被写入到预写式日志来标识被更新或删除行的信息。除非使用逻辑复制， 这个选项将不会产生效果。设置表的REPLICA IDENTITY属性并查询。表的replica identity属性存储在。= 默认 (主键，如果存在),
PostgreSQL数据库从入门到精通系列之十五：创建索引，创建REPLICA IDENTITY
zhengzaifeidelushang的博客
08-03 241
请注意，创建唯一索引会对数据库的性能产生一定的影响，特别是在大型表上。因此，需要在创建索引之前仔细评估其对性能的影响，并根据具体情况进行调整。的表上创建一个唯一索引，用于保证。是要在该列上创建唯一索引的列名。是你给索引起的名称，是要创建索引的表名，
PostgreSQL 的逻辑复制 与 部分疑问
sql server的专栏
07-03 492
PostgreSQL 本身是支持流式复制的，而大部分数据库都支持逻辑复制的方式，流式复制稳定高效，但缺点是不灵活，而逻辑复制的优点就在于此。逻辑的复制的优点1 可以进行数据的过滤2 可以...
PostgreSQL逻辑复制槽功能
sql server的专栏
03-18 1065
下面是简单的实际操作案例 （版本PG16） 注意事项： 1.在数据复制中，需要保持复制中的所有表均有主键，或唯一索引，如表中无这两项，也可以进行逻辑复制但会严重影响性能，或导致逻辑复制报错。下面的命令并不准确，尤其在主库并未有真实的数据变动或插入则这个语句就会产生问题，但如果能保证主库持续有数据，则这个语句可以观察逻辑复制的从库与主库的数据之间的延迟。检查逻辑复制槽的状态,明显active=F 的逻辑复制出现问题，可能是网络，也可能是WAL丢失，或者是订阅端出现问题等等，都需要后续的分析和排除故障。
PG逻辑复制的REPLICA IDENTITY设置
热门推荐
bisal的专栏
07-27 1万+
前两天同事问了一个PG的错误，创建一张普通表，insert插入正常，但是执行update和delete时，提示这个错误，SQL 错误 [55000]: ERROR: cannot dele...
PG逻辑复制的REPLICA IDENTITY几种设置
snowyar的专栏
11-08 1364
默认情况下，复制所有操作类型。首先了解下，逻辑复制的概念。因此，可以问创建test_publication的人有什么意图，或者将逻辑复制logical更改成物理复制，如果逻辑复制是必须的，可能有效的方式，就是设置主键，其实从数据库应用设计开发的规范角度，无特殊情况，还是要为表创建一个主键，而且推荐是无意义的类型，例如序列。，使用发布者/订阅者模型，使用订阅复制槽技术，可并行的传输WAL日志，通过在订阅端回放WAL日志中的逻辑条目，保持复制表的数据同步，注意这里不是“SQL”复制，而是复制SQL操作的结果。
openGauss 6.0.0-RC1逻辑复制特性测试
openGauss的博客
10-10 1028
一、环境部署 ...
PG12逻辑复制的限制
03-08
首先，我需要回忆一下PG12的逻辑复制功能有哪些已知的限制，或者官方文档中提到的内容。 首先，逻辑复制在PG12中的基本概念是什么？逻辑复制允许基于订阅的方式复制数据更改，与物理复制（流复制）不同，它可以在...
PostgreSQL：逻辑复制与物理复制
最新发布
在技术的广袤天地里，本博客如精准罗盘。剖析前沿科技，深掘代码奥秘，以精炼笔触，带您穿越复杂技术迷宫，速达知识彼岸。
04-12 1928
PostgreSQL的复制体系就像生物进化的活化石，记录着数据同步技术从简单镜像到智能分发的演化轨迹。在物理复制的确定性世界与逻辑复制的概率宇宙之间，我们看到了数据库技术应对不同场景的智慧平衡。随着PostgreSQL 16引入的逻辑复制并行应用（max_logical_replication_workers）和双向逻辑复制实验特性，这场复制革命正开启新的篇章。掌握这些技术，就如同获得在数据洪流中冲浪的智慧桨板，助您在数字时代的浪潮中破浪前行。
PG12逻辑复制支持视图吗
03-08
我需要先回想一下PG的逻辑复制机制。逻辑复制主要是通过复制槽和发布订阅的方式，传输WAL中的逻辑变化。默认情况下，逻辑复制是基于表的，也就是说只有基表的数据变更会被复制。视图在PostgreSQL中并不存储实际数据...
PostgreSQL 10 新特性解析：分区表与逻辑复制
"这份文档详细介绍了PostgreSQL 10的新特性，包括原生分区表、逻辑复制和并行查询的增强等。文档旨在为PostgreSQL的用户和管理员提供关于新版本改进的深入理解，帮助他们更好地利用这些新功能来优化数据库性能和可靠...
pg内功修炼：逻辑复制
liuzhilong的博客
03-01 3332
postgresql逻辑复制知识点详解，逻辑解析、复制槽、output plugin、replica identity、发布订阅、事务snap落盘、解析溢出spill···
Postgresql 逻辑复制REPLICA IDENTITY：调整逻辑复制输出的记录行信息量
高矮
05-05 7652
当原数据表发生更新时，默认的逻辑复制流只包含历史记录的key，如果需要输出更新记录的历史记录的所有字段，需要在表级别修改参数： REPLICA IDENTITY 例： ALTER TABLE test2 REPLICA IDENTITY FULL; REPLICA IDENTITY有四个值可选择： DEFAULT - 更新和删除将包含primary key列的现前值 NOTHING -...
批量修改逻辑复制表标识方式
m0_37535673的博客
08-14 614
PG逻辑复制时，对表进行update 或者delete之后，解析出来的SQL需要在where条件上加以限制，从而防止出现在订阅端重放时出现较大的误差，这个时候就需要通过之前删除的旧值来做限定。 限制的方式主要分四种 1、default：默认通过主键进行限定(非系统表的默认值)，仅保留主键旧值 2、nothing：不保留任何旧值（系统表默认） 3、full：保留所有旧值 4、using i...
postgresql复制标识问题
dylan的博客
11-17 1729
postgresql复制逻辑-复制标识问题，ERROR: cannot delete from table xxxxx because it does not have a replica identity and publishes deletes
PostgreSQL 变化数据捕捉（CDC）
sunny05296的博客
02-09 5446
函数：pg_logical_slot_get_changes(slot_name name, upto_lsn pg_lsn, upto_nchanges int, VARIADIC options text[])
数据变更捕获 (CDC)：PostgreSQL 与 ClickHouse - 第二部分
ClickHouseDB的博客
04-25 1352
我们在之前的博客中提出的端到端架构如下所示。该架构假设用户已经拥有一个运行Kafka Connect框架的Kafka实例。在我们的示例中，我们假设用户正在使用Confluent Cloud来托管Kafka，该服务会自动为事件创建相应的主题。不过，用户也可以选择自行托管Kafka。我们提出的架构将适用于任何能够写入由Debezium生成的事件的摄取管道。
PostgreSQL数据库从入门到精通系列之六：深入理解逻辑复制槽，创建逻辑复制槽，删除逻辑复制槽
zhengzaifeidelushang的博客
06-29 2727
PostgreSQL数据库从入门到精通系列之六：深入理解逻辑复制槽，创建逻辑复制槽，删除逻辑复制槽
openGauss SQL语法 —函数和操作符：逻辑复制函数
weixin_53596073的博客
08-23 657
例：select * from pg_logical_slot_peek_binary_changes('slot1', NULL, 4096, 'white-table-list', 'public.t1,public.t2');options：此项为可选参数，由一系列options_name和options_value一一对应组成，可以缺省，详见pg_logical_slot_peek_changes。只能在使用pg_replication_origin_session_setup选择复制源时调用。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

liuzhilongDBA

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值