PG逻辑复制的REPLICA IDENTITY设置

前两天同事问了一个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，其实讲的很清楚，根据一些资料，

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

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操作的结果。

逻辑复制的主要组成

(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确实勾选了，

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

CREATE PUBLICATION test_publication FOR ALL TABLES;

pg_publication的字段定义，

因此，可以问创建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/

近期更新的文章：

《最近碰到的几个问题》

《Linux的dd指令》

《Oracle、SQL Server和MySQL的隐式转换异同》

《JDK的版本号解惑》

《新增字段在数据块中的体现》

文章分类和索引：

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