PG逻辑复制的REPLICA IDENTITY几种设置

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

代码语言：javascript

复制

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，

代码语言：javascript

复制

SELECT current_setting('server_version_num');

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

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

代码语言：javascript

复制

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查看。

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

代码语言：javascript

复制

CREATE PUBLICATION alltables FOR ALL TABLES;

(2) 订阅端

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

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

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

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

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

代码语言：javascript

复制

ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION

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

代码语言：javascript

复制

ALTER TABLE reptest REPLICA IDENTITY FULL;

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

(1) 默认模式(default)：

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

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

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

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

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

代码语言：javascript

复制

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

具体有四种形式，

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

代码语言：javascript

复制

ALTER TABLE t_normal REPLICA IDENTITY DEFAULT;

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

代码语言：javascript

复制

ALTER TABLE t_normal REPLICA IDENTITY FULL;

(3) 使用唯一索引，

代码语言：javascript

复制

ALTER TABLE t_normal REPLICA IDENTITY USING INDEX t_normal_v_key;

(4) 不设置复制标识，

代码语言：javascript

复制

ALTER TABLE t_normal REPLICA IDENTITY NOTHING;

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

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

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

代码语言：javascript

复制

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

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

代码语言：javascript

复制

ALTER TABLE reptest REPLICA IDENTITY FULL;

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

代码语言：javascript

复制

select setting from pg_settings where name='wal_level';

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

代码语言：javascript

复制

select relreplident from pg_class where relname='temp_tb';

返回值说明， 

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

n = 无

f = 所有列

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

4. 查询视图，temp_tb隶属于test_publication这个发布，

代码语言：javascript

复制

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

5. 检索这个发布，

代码语言：javascript

复制

select * from pg_publication;

puballtables确实勾选了，

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

代码语言：javascript

复制

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/

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/912335.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

【人工智能】利用大语言模型（LLM）实现机器学习模型选择与实验的自动化

【人工智能】利用大语言模型（LLM）实现机器学习模型选择与实验的自动化

文章目录 引言环境准备数据集说明 项目结构主要文件说明 导入必要的软件包软件包功能简述 辅助函数定义加载配置文件加载数据集预处理数据集函数功能详解 集成LLM进行模型选择调用LLM的函数定义函数功能详解 清理和验证LLM的输出清理超参数建议提取模型名称验证超参数修正超参数…
阅读更多...
机器学习在时间序列预测中的应用与实现——以电力负荷预测为例（附代码）

机器学习在时间序列预测中的应用与实现——以电力负荷预测为例（附代码）

📝个人主页🌹：一ge科研小菜鸡-CSDN博客 🌹🌹期待您的关注 🌹🌹 1. 引言 随着数据采集技术的发展，时间序列数据在各个领域中的应用越来越广泛。时间序列预测旨在基于过去的时间数据来…
阅读更多...
Python学习------第四天

Python学习------第四天

Python的判断语句 一、布尔类型和比较运算符 二、 if语句的基本格式 if语句注意空格缩进！！！ if else python判断语句的嵌套用法：
阅读更多...
Vue3版本的uniapp项目运行至鸿蒙系统

Vue3版本的uniapp项目运行至鸿蒙系统

新建Vue3版本的uniapp项目 注意，先将HbuilderX升级至最新版本，这样才支持鸿蒙系统的调试与运行； 按照如下图片点击，快速升级皆可。 通过HbuilderX创建 官方文档指导链接 点击HbuilderX中左上角文件->新建->项目 创建vue3…
阅读更多...
让Apache正确处理不同编码的文件避免中文乱码

让Apache正确处理不同编码的文件避免中文乱码

安装了apache2.4.39以后&#xff0c;默认编码是UTF-8&#xff0c;不管你文件是什么编码&#xff0c;统统按这个来解析&#xff0c;因此 GB2312编码文件内的中文将显示为乱码。 <!doctype html> <html> <head><meta http-equiv"Content-Type" c…
阅读更多...
LabVIEW实验室液压制动系统

LabVIEW实验室液压制动系统

压制动系统是许多实验设备的重要安全组件&#xff0c;尤其在高负荷、高速实验环境下&#xff0c;制动系统的性能对设备和操作人员的安全至关重要。传统的实验室液压制动系统监测方法存在数据采集实时性差、精度低、故障预警不及时等问题。为了提高实验安全性和设备运行的稳定性…
阅读更多...
Qt教程（007）：资源文件添加

Qt教程（007）：资源文件添加

文章目录 7.1 创建新的项目7.2 添加资源文件7.2 设置页面7.1 创建新的项目 选择创建项目类型 输入项目名称 勾选UI界面 7.2 添加资源文件 选中项目名称，右键，选择【Add New】 添加资源文件 选择Qt Resource File文件
阅读更多...
【Python爬虫实战】深入解锁 DrissionPage：ChromiumPage 自动化网页操作指南

【Python爬虫实战】深入解锁 DrissionPage：ChromiumPage 自动化网页操作指南

&#x1f308;个人主页&#xff1a;易辰君-CSDN博客 &#x1f525; 系列专栏&#xff1a;https://blog.csdn.net/2401_86688088/category_12797772.html ​ 目录 前言 一、ChromiumPage基础操作 &#xff08;一&#xff09;初始化Drission 和 ChromiumPage 对象 &#xff0…
阅读更多...
如何保证kafka生产者数据可靠性

如何保证kafka生产者数据可靠性

ack参数的设置&#xff1a; 0&#xff1a;生产者发送过来的数据&#xff0c;不需要等数据落盘应答 假如发送了Hello 和 World两个信息&#xff0c;Leader直接挂掉&#xff0c;数据就会丢失 生产者 ---> Kafka集群 一放进去就跑 数据可靠性分析&#xff1a;丢数 1&#…
阅读更多...
python爬虫指南——初学者避坑篇

python爬虫指南——初学者避坑篇

目录 Python爬虫初学者学习指南一、学习方向二、Python爬虫知识点总结三、具体知识点详解和实现步骤1. HTTP请求和HTML解析2. 正则表达式提取数据3. 动态内容爬取4. 数据存储5. 反爬虫应对措施 四、完整案例&#xff1a;爬取京东商品信息1. 导入库和设置基本信息2. 获取网页内容…
阅读更多...
C 语言学习-05【数组】

C 语言学习-05【数组】

1、一维数组元素的操作 输入一个数&#xff0c;按原来排序的规律将它插入到一个一排列好的数组中&#xff1a; #include <stdio.h>int main() {int i, data, a[10] {2, 3, 6, 9, 11, 12, 14, 17, 19};printf("Primitive series: \n");for (i 0; i < 9; i)…
阅读更多...
C 语言学习-1【基本语法】

C 语言学习-1【基本语法】

1、符号常量 #define 符号常量名 常量 使用符号常量计算圆柱体的体积&#xff1a; #include <stdio.h> #define PI 3.1415926int main() {float r, h, volum;printf("Please enter the radius: ");scanf("%f", &r);printf("Please enter th…
阅读更多...
【微服务】不同微服务之间用户信息的获取和传递方案

【微服务】不同微服务之间用户信息的获取和传递方案

如何才能在每个微服务中都拿到用户信息&#xff1f;如何在微服务之间传递用户信息&#xff1f; 文章目录 概述利用微服务网关做登录校验网关转微服务获取用户信息openFeign传递微服务之间的用户信息 概述 要在每个微服务中获取用户信息&#xff0c;可以采用以下几种方法&#…
阅读更多...
HarmonyOS入门 : 获取网络数据，并渲染到界面上

HarmonyOS入门 : 获取网络数据，并渲染到界面上

1. 环境搭建 开发HarmonyOS需要安装DevEco Studio&#xff0c;下载地址 : https://developer.huawei.com/consumer/cn/deveco-studio/ 2. 如何入门 入门HarmonyOS我们可以从一个实际的小例子入手&#xff0c;比如获取网络数据&#xff0c;并将其渲染到界面上。 本文就是基于…
阅读更多...
SpringMVC总结 我的学习笔记

SpringMVC总结 我的学习笔记

SpringMVC总结 我的学习笔记 一、SpringMVC简介1.MVC2.SpringMVC概述3. SpringMVC中的核心组件4.SpringMVC核心架构流程 二、SpringMVC框架实例具体实现使用注解实现 四、数据处理及跳转1.结果跳转方式2.处理器方法的参数与返回值处理提交数据数据显示到前端 五、RestFul风格1.…
阅读更多...
Qml 模型-视图-代理（贰）之 动态视图学习

Qml 模型-视图-代理（贰）之 动态视图学习

Repeater 元素适合有限的静态数据&#xff0c; QtQuick 提供了 ListView 和 GridView, 这两个都是基于 Flickable(可滑动) 区域的元素 &#xff0c; ListView 与 Repeater 相比&#xff0c; ListView 使用了一个 model&#xff0c; 使用delegate 来 实例化&#xff0c;并且在两…
阅读更多...
rce代码层面

rce代码层面

目录 RCE的分类Remote Code Execute 远程代码执⾏php Remote Command Execte 远程命令执⾏php shell的相关知识管道重定向fd反弹shell linux进程的创建c/php/python下的system()/popen()函数python的subprocess.call函数java的Runtime.getRuntime().exec和ProcessBuilder()命令…
阅读更多...
一篇文章入门docker！

一篇文章入门docker！

文章目录 DockerUbuntu 下 docker 安装安装docker运行docker Docker的常用命令帮助命令镜像命令容器命令其他常用命令小结 分层理解一、Docker镜像的分层结构二、UnionFS与镜像分层三、镜像层的具体内容四、镜像分层的好处五、容器层与镜像层的关系 如何提交一个自己的镜像 Doc…
阅读更多...
【大数据学习 | HBASE】habse的表结构

【大数据学习 | HBASE】habse的表结构

在使用的时候hbase就是一个普通的表&#xff0c;但是hbase是一个列式存储的表结构&#xff0c;与我们常用的mysql等关系型数据库的存储方式不同&#xff0c;mysql中的所有列的数据是按照行级别进行存储的&#xff0c;查询数据要整个一行查询出来&#xff0c;不想要的字段也需要…
阅读更多...
泛微E9 OA与金蝶云的差旅费报销接口集成

泛微E9 OA与金蝶云的差旅费报销接口集成

FD001-差旅费报销申请 泛微>金蝶--498 集成案例分享 在企业日常运营中&#xff0c;差旅费报销申请的处理效率直接影响到员工满意度和财务管理的精确性。为了实现泛微OA-Http系统与金蝶云星空平台之间的数据无缝对接&#xff0c;我们设计并实施了FD001-差旅费报销申请集成方…
阅读更多...
最新文章

Copyright @ 2022~2023