PG逻辑复制的REPLICA IDENTITY设置

拼搏现实的明天。 2022-09-01 10:57 301阅读 0赞

前两天同事问了一个PG的错误，创建一张普通表，insert插入正常，但是执行update和delete时，提示这个错误，

SQL 错误 [55000]: ERROR: cannot delete from table "temp_tb" because it does not have a replica identity and publishes updates
    Hint: To enable updating from the table, set REPLICA IDENTITY using ALTER TABLE.
    
    
    SQL 错误 [55000]: ERROR: cannot delete from table "temp_tb" because it does not have a replica identity and publishes deletes
    Hint: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.

PG确实接触少，顺着错误，捋一捋，首先是PG版本，是12，

SELECT current_setting('server_version_num');

从报错来看，为了能执行更新和删除操作，需要用ALTER TABLE设置REPLICA IDENTITY，其实讲的很清楚，根据一些资料，

![e5b7b00edb85a7ca2c5406b86df66d50.png][]

如果一张表缺少主键，并且涉及到复制，就需要执行这条语句，

ALTER TABLE temp_tb REPLICA IDENTITY FULL;

执行完成，确实能正常更新和删除了。但这是为什么？

首先了解下，逻辑复制的概念。逻辑复制是PostgreSQL V10重量级新特性，支持内置的逻辑复制。在10版本之前，虽然没有内置的逻辑复制，也可以通过其它方式实现，例如触发器、自定义脚本实现表级别同步，另外也可以通过外部工具Londiste3实现。从9.4版本开始，PostgreSQL就支持逻辑复制了，只是一直没有将其引入内核。可以针对同一个数据库实例，同时使用逻辑复制和物理复制，因为他们都是基于REDO的。

**逻辑复制的应用场景**，可基于表级别复制，是一种粒度可细的复制，主要用在以下场景，

(1) 满足业务上需求，实现某些指定表数据同步。

(2) 报表系统，采集报表数据。

(3) PostgreSQL 跨版本数据同步。

(4) PostgreSQL 大版本升级。

(5) 可从多个上游服务器,做数据的聚集和合并。

**逻辑复制原理**，使用发布者/订阅者模型，使用订阅复制槽技术，可并行的传输WAL日志，通过在订阅端回放WAL日志中的逻辑条目，保持复制表的数据同步，注意这里不是“SQL”复制，而是复制SQL操作的结果。

![d995eb8386de42ffe246e6e8816ffc27.png][]

**逻辑复制的主要组成**

(1) publication - 发布

可以在任何物理复制主机上定义发布；定义发布的节点称为发布者。发布是从一个表或一组表中生成的一组更改，也可能被描述为更改集或复制集。每个发布只存在于一个数据库中。发布与模式不同，不影响表格的访问方式；如果需要，每张表可以添加到多个发布。发布目前可能只包含表；对象必须显式添加， 除非为ALL TABLES创建了一个发布。发布可以选择将它们所产生的改变限制在INSERT，UPDATE和DELETE的任意组合上，类似于触发器。默认情况下，复制所有操作类型。

(2) subscription - 订阅

就是订阅变更的内容。

(3) Replication Slots - 复制槽(发布端)

每个(活动)订阅都从远程(发布)端的复制槽接收更改。通常，使用CREATE SUBSCRIPTION创建订阅时会自动创建远程复制槽，使用DROP SUBSCRIPTION删除订阅时会自动删除该槽。复制槽提供了一种自动化的方法来确保主控机在所有的后备机收到WAL段之前不会移除它们，主库随时知道从库应用WAL的情况，哪怕从库掉线，主库依然保留WAL日志。这种机制的缺点是，如果从库掉线很久, 那么主库的WAL日志会一直保留以至于撑暴硬盘，这时监控需要做到位。

**关于发布端和订阅端**，

(1) 发布端

逻辑复制的前提是将数据库wal\_level参数设置成logical。

源库上逻辑复制的用户必须具有replicatoin或superuser角色。

逻辑复制目前仅支持数据库表逻辑复制，其它对象例如函数、视图不支持。

逻辑复制支持DML(UPDATE、INSERT、DELETE)操作，TRUNCATE 和 DDL 操作不支持。

需要发布逻辑复制的表，须配置表的REPLICA IDENTITY特性。

一个数据库中可以有多个publication，通过pg\_publication查看。

允许一次发布所有表，语法，

CREATE PUBLICATION alltables FOR ALL TABLES;

(2) 订阅端

订阅节点需要指定发布者的连接信息。

一个数据库中可以有多个订阅者。

可以使用enable/disable启用/暂停该订阅。

发布节点和订阅节点表的模式名、表名必须一致，订阅节点允许表有额外字段。

发布节点增加表名，订阅节点需要执行：

ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION

被复制的表上最好有主键约束；如果没有，必须执行，

ALTER TABLE reptest REPLICA IDENTITY FULL;

REPLICA IDENTITY，复制标识，共有4种配置模式，分别为，

(1) 默认模式(default)：

非系统表采用的默认模式，如果有主键，则用主键列作为身份标识，否则用完整模式。

(2) 索引模式(index)：将某一个符合条件的索引中的列，用作身份标识。

(3) 完整模式(full)：将整行记录中的所有列作为复制标识(类似于整个表上每一列共同组成主键)。

(4) 无身份模式(nothing)：不记录任何复制标识，这意味着UPDATE|DELETE操作无法复制到订阅者上。

表改复制标识可以通过ALTER TABLE进行修改。

ALTER TABLE tbl REPLICA 
    IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING };

具体有四种形式，

(1) 使用主键，如果没有主键则为FULL，

ALTER TABLE t_normal REPLICA IDENTITY DEFAULT;

(2) 使用整行作为标识，

ALTER TABLE t_normal REPLICA IDENTITY FULL;

(3) 使用唯一索引，

ALTER TABLE t_normal REPLICA IDENTITY USING INDEX t_normal_v_key;

(4) 不设置复制标识，

ALTER TABLE t_normal REPLICA IDENTITY NOTHING;

如果没有任何复制标识，可以将复制标识设置为FULL，也就是把整个行当作复制标识。但是，使用FULL模式的复制标识效率很低，所以这种配置只能是保底方案，或者用于很小的表。因为每一行修改都需要在订阅者上执行全表扫描，很容易将订阅者拖垮。

模拟创建过程，并证明下这个库像上面所说设置了相关的参数配置。

1. 创建表，插入正常，更新和删除，就会提示55000错误，

CREATE TABLE temp_tb (
      code varchar(32) COLLATE pg_catalog.default primary key,
      name varchar(32) COLLATE pg_catalog.default,
      aliases jsonb
    )
    ;

执行如下语句，更新和删除正常，

ALTER TABLE reptest REPLICA IDENTITY FULL;

2. 查询当前表的复制属性，返回值是logical，说明设置了逻辑复制，

select setting from pg_settings where name='wal_level';

3. 查询表当前复制标识，返回值是f，说明这张表确实设置了复制，而且是基于所有列的，

select relreplident from pg_class where relname='temp_tb';

返回值说明，

d = 默认(主键，如果存在)

n = 无

f = 所有列

i = 索引的indisreplident被设置或者为默认

4. 查询视图，temp\_tb隶属于test\_publication这个发布，

select * from pg_publication_tables where tablename='temp_tb';
    pubname             schemaname  tablename
    test_publication    public      temp_tb

5. 检索这个发布，

select * from pg_publication;

puballtables确实勾选了，

![ae5bfe0db306d0a9acdbfed9a07f958e.png][]

说明这个发布，发布所有表中的所有更改，

CREATE PUBLICATION test_publication FOR ALL TABLES;

pg\_publication的字段定义，

![a7d872be756595826ade05f5eb4d5091.png][]

因此，可以问创建test\_publication的人有什么意图，或者将逻辑复制logical更改成物理复制，如果逻辑复制是必须的，可能有效的方式，就是设置主键，其实从数据库应用设计开发的规范角度，无特殊情况，还是要为表创建一个主键，而且推荐是无意义的类型，例如序列。

参考链接，

http://postgres.cn/docs/12/catalog-pg-publication.html

https://stackoverflow.com/questions/56000702/cannot-update-table-temp-table-because-it-does-not-have-a-replica-identity-and-p

https://www.pgfans.cn/a?id=194

http://postgres.cn/docs/12/logical-replication-publication.html

http://postgres.cn/docs/12/sql-createpublication.html

https://www.bbsmax.com/A/GBJrvBkZJ0/

https://pigsty.cc/zh/blog/2021/03/03/pg%E5%A4%8D%E5%88%B6%E6%A0%87%E8%AF%86%E8%AF%A6%E8%A7%A3replica-identity/

**近期更新的文章：**

《[最近碰到的几个问题][Link 1]》

《[Linux的dd指令][Linux_dd]》

《[Oracle、SQL Server和MySQL的隐式转换异同][Oracle_SQL Server_MySQL]》

《[JDK的版本号解惑][JDK]》

《[新增字段在数据块中的体现][Link 2]》

**文章分类和索引：**

《[公众号700篇文章分类和索引][700]》

[e5b7b00edb85a7ca2c5406b86df66d50.png]: /images/20220829/78b4fecd46964aecbc000b2c3d2e356d.png
[d995eb8386de42ffe246e6e8816ffc27.png]: /images/20220829/6deada54d50b44c8b68529715295bb4b.png
[ae5bfe0db306d0a9acdbfed9a07f958e.png]: /images/20220829/99406f4b6e5546529082733549361bc7.png
[a7d872be756595826ade05f5eb4d5091.png]: /images/20220829/1a0f48e09d704290b284417fcab4acd5.png
[Link 1]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a0c0ddf529d60af3003a5dcf0542db53d411e4ced7dc04ce3dcfa272b7e2fd00dab0c4e4&idx=1&mid=2247492915&scene=21&sn=02cc8b5f8d2d6b41947fc4351a14b3d9#wechat_redirect
[Linux_dd]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a0e3ddf529f507604737a7d047ca6b8726bb02003507b7101a748048caacb39d976ee5af&idx=1&mid=2247492880&scene=21&sn=bc96fe6d7764dda91f652c0f70dc6f97#wechat_redirect
[Oracle_SQL Server_MySQL]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a0f4ddf529e2a052e6e34e71931074e0a662e1472d9a8725c5584363bb054761715d061e&idx=1&mid=2247492871&scene=21&sn=db396b9b0d0b9483c34f3a28fc897349#wechat_redirect
[JDK]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a159ddf5284f2f7b440676c128aa9d342e3331ecaf5bd3eb4f700e80080b0798c3ac4009&idx=1&mid=2247492778&scene=21&sn=211b4ccd0d6b56da233114235b787b73#wechat_redirect
[Link 2]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a190ddf52886b9393435abb5f4941b2e7b4ab072d4bd93f690cedcac6b814c61a5a728e5&idx=1&mid=2247492707&scene=21&sn=59107188dea8535a6f7d131bfffe3787#wechat_redirect
[700]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a5dfddf52cc949a5b8b316ee905927ff399713b34aec7d806c656e8ada09bfb562c039ca&idx=1&mid=2247491628&scene=21&sn=83bd320bd28da84501fada2e8d515e77#wechat_redirect