PostgreSQL

目录 目录 1 基本概念 1.1 业务场景 1.2 逻辑复制 1.3 实现思路 1.4 基本概念 2 使用方法 2.1 发布端：配置参数 2.2 发布端：创建发布 2.3 订阅端：创建订阅 3 核心设计 3.1 整体架构 3.2 工作流程 3.3 数据格式 4 实现源码 4.1 订阅端：apply-launch 4.2 订阅端：apply-worker 4.3 发布端：postmaster 4.4 发布端：wal-sender 5 性能优化 5.1 现状 5.2 优化方案 优化点一：发布端-流复制协议（逻辑数据） 优化点二：订阅段-并行回放机制（逻辑数据） 优化点三：发布端-并行解码机制 5.3 未来方案 一、计划优化点一：发布端-事务提交等待 5.4 方案分析 一、事务正确性 6 性能测试 参考资料 1 基本概念 1.1 业务场景 场景一：数据共享（异构数据库） 1个公司，有2个部门：商品库存部门、商品成本部门。2个部门有不同技术栈，使用不同数据库产品:postgresql、mysql。2个部门需共享相同的表：商品信息表。 场景二：数据容灾 应用存储数据时，同时将数据存储至postgresql于mysql中，避免某款数据库出现无法恢复的数据损坏问题。 场景三：版本升级 从postgresql 13版本，升级到postgresql 16版本。 场景四：数据迁移（异构数据库） 从postgresql，将数据库迁移到mysql中。 ...

2 使用 2.1 发布端 配置 # vb echo "wal_level=logical" >> $GAUSSHOME/data/postgresql.conf echo "max_replication_slots=4" >> $GAUSSHOME/data/postgresql.conf echo "max_wal_senders=4" >> $GAUSSHOME/data/postgresql.conf # echo "max_worker_processes=8" >> $GAUSSHOME/data/postgresql.conf echo "max_logical_replication_workers=32" >> $GAUSSHOME/data/postgresql.conf echo "listen_addresses='*'" >> $GAUSSHOME/data/postgresql.conf sed -i '1i host all all 0.0.0.0/0 md5\n' $GAUSSHOME/data/pg_hba.conf echo "host replication all 0.0.0.0/0 md5" >> $GAUSSHOME/data/pg_hba.conf # host all all 0.0.0.0/0 md5 psql -d postgres -c "SELECT name,setting FROM pg_settings WHERE name in ('wal_level', 'max_replication_slots', 'max_wal_senders', 'max_worker_processes', 'max_logical_replication_workers')" 创建基表 CREATE DATABASE pubdb; \c pubdb CREATE TABLE pt1(c1 INT,c2 TEXT); CREATE TABLE pt2(c1 INT, c2 TEXT); INSERT INTO pt1 VALUES (1,'data1-1'), (2,'data1-2'); INSERT INTO pt1 VALUES (1,'data2-1'), (2,'data2-2'); 创建发布用户 ...

2025-11-21：持续更新中… 1 需求概述 1.1 原始需求 主备部署模式下，滚动升级时，可能存在主节点未升级，备节点已升级的情况。在该条件下，客户要求主节点持续执行事务，并且主节点及时将数据同步至备节点。目前，存在一下问题： 物理复制无法使用：由于主节点、备节点版本不一致，主节点生成的wal，备节点可能无法使用。因此，只能使用逻辑复制方案。 逻辑复制性能较低：主节点作为发布端，解码wal。备节点作为订阅端，接收loggic-wal，并且回放logic-wal，可解决主备版本不一致的问题。但是，目前Vastbase的逻辑复制功能性能较低，无法满足及时同步数据的要求。 本需求将针对上述场景，端到端优化逻辑复制功能，让逻辑复制性能整体提升5倍以上，达到100m/s的要求。 需求来源：https://doc.weixin.qq.com/doc/w3_AYsAjQYkADwCNecwqsBM8ShS9YejB?scode=AHUAfwdSAA8UDeKla8AdwAfAa7AFk&roomid=Person%3A1688855660690652%3A1688855164259723&version=5.0.2.6008&platform=win 1.2 问题分析 逻辑复制分为多个阶段，openGauss和PostgreSQL在部分阶段中做了优化，但未端到端优化。因此，本文将以早期逻辑复制为基础，指出优化设计。 版本一：串行复制 在postgresql 13以及之前版本，逻辑复制的关键设计如下： 发布端： 解码日志：wal-sender进程，持续读取wal，串行对wal进行decode，生成logic-wal，并将不同事务的logic-wal，分别存放。 发送日志：对wal进行decode时，如果是事务提交产生的wal，则将该事务的所有logic-wal打包，并发送到订阅端。 订阅端： 回放日志：apply-worker进程，一次接收同一事务的一批logic-wal，串行回放本批logic-wal后，重新等待下一个事务的logic-wal。 整体方案如下： 以上设计，存在多项性能瓶颈，以常见的tpcc测试为例，可能有400+postgres进程在执行事务，实时产生wal日志。但是，只有1个wal-sender进程在串行解码日志，只有1个apply-workder在串行回放日志。 1.3 方案设计 版本二：发布端并行解码 在openGauss中，针对发布端的解码日志阶段，进行了优化，引入多个decoder线程，并行解码wal生成logic-wal。方案设计如下： 但是，在订阅端，每次对一个事务的一批logica-wal全部回放后，才会接收下一个事务的logic-wal。仅引入并发解码机制，对性能提升较小。发布端和订阅端，均存在负载不均衡的问题，例如，订阅端回放完一个事务的logic-wal之后，需等待下一个事务的logic-wal。 版本三：流氏复制协议 在postgrsql 14版本，针对发布端发送日志阶段，进行了优化，引入流式复制协议，即无需一次发送完一个事务完整的一批logic-wal，而是随时发送logic-wal，即使事务未提交。可解决发布端、订阅端负载不均衡的问题。 但是，在订阅端，tpcc场景，发布端400+postgres进程执行事务，订阅端1个apply-worker进程回放日志，仍是瓶颈。 版本四：订阅端并行回放 在postgrsql 16版本，针对订阅端回放日志阶段，进行了优化，引入多个apply进程，并行回放logic-wal。方案设计如下： 版本五：并行复制 结论：本需求参考性能测试结果，认为需同时结合：并行解码、流式复制、并行回放机制，才能提升逻辑复制端到端性能。 优先级：其中，流式复制、并行回放机制，对性能影响较大，优先级较高。可独立地、优先地实现。 其他工作：同时采用上述3个机制，需合理控制发布端解码速率、订阅端回放速率，在实现负载均衡的同事，避免logic-wal堆积。 2 设计分析 2.1 必要性 根据postgrsql-16的性能测试结果，流式复制和并行回放机制，可达到70Mb/s的复制速度。 一、测试场景 测试机器： IP：172.16.103.90 硬件：CPU：鲲鹏920-ARM-128核。缓存L1-L2-L3：8M/64M/256M。内存：760G。磁盘：NVME 系统：openEuler tpcc测试场景（为了快速测试，不太标准） 数据量：100 warhouse 并发：400 执行时间：1 min 逻辑复制配置： 部署：发布端、订阅端在同一台机器（找不到2台性能机） 订阅：创建1个publiction，涵盖所有table 二、测试数据 由于机器、时间等限制，测试未严格控制变量，但是，和准确结果应该有80%以上符合度。 版本 配置 tpmc 发布端-wal写入速度 逻辑复制速度（峰值） 逻辑复制速度（平均值） postgrsql-14 串行解码、非流式复制、串行回放 约65w 207 Mb/s - 16 Mb/s postgresql-16 串行解码、流式复制、串行回放 约65w 212 Mb/s 117 Mb/s 55 Mb/s postgresql-16 串行解码、流式复制、并行回放(4) 约65w 197 Mb/s 113 Mb/s 71 Mb/s 测试：贾旭辉 详细数据和结论见wiki：https://www.tapd.cn/60475194/markdown_wikis/show/#1160475194001007526 尝试修改源码，让订阅端只接受日志，不apply，确定发布端解码和发送的瓶颈，但是，测试结果有点奇怪，有空再继续。 2.2 可行性 并行解码：openGauss已引入 流式复制：postgreql-16版本已成熟 并行回放：postgreql-16版本已成熟 2.3 潜在风险 一、数据有一致性 写写冲突 ...

1 逻辑复制背景 1.1 逻辑复制场景 在使用数据库时，为提高整个系统的可靠性，或实现不同部门数据同步等场景，很多客户的部署模型存在：一主多备、异构数据库等特点，在保证性能的前提下，常见部署模型如下： +-------------+ sql +---------------------+ wal +--------------------+ | application | --------> | postgresql (master) | -------> | postgresql (slave) | +-------------+ +---------------------+ +--------------------+ | | | decoded-wal +--------------------+ +---------------------> | mysql, oracle, .. | +--------------------+ 1.2 逻辑复制功能 1.3 逻辑复制基础 首先，需自行了解一些存储的基本知识，包括事务特性、mvcc机制，表的物理存储格式：filenode、page、tuple等。 此处，以一个例子，介绍什么wal日志的特点。需了解一些 应用执行事务 假设同一时间，有2个事务，事务id分别为10和11。 时间 应用1 应用2 0 CREATE TABLE t1 (c1 INT,c2 INT) - 1 CREATE TABLE t2 (c1 INT,c2 INT) - 2 BEGIN (xid=10) - 3 INSERT INTO t1 VALUES(10, 1) - 4 - BEGIN (xid=11) 5 - INSERT INTO t1 VALUES (11, 1) 6 INSERT INTO t2 VALUES (10, 2) - 7 COMMIT 8 - DELETE FROM t1 WHERE c1 = 10 9 - INSERT INTO t2 VALUES (11, 2) 10 - COMMIT 内核产生wal日志 所有表的wal日志，按生成wal的顺序，组织在一起。上述示例中，产生的wal如下：（此处仅列举关键信息） ...

1 物理复制基本原理 1.1 使用场景 介绍为什么需要物理复制 1.2 相关原理 介绍什么是wal，wal是用来干嘛的 1.3 实现思路 介绍如何基于wal，实现物理复制，并解决1.1中的问题 2 物理复制使用方式 2.1 使用物理复制 3 物理复制工作流程 3.1 整体架构 包括Postgres生成wal，walwriter存储wal，walsender发送wal，walreceive接收wal，startup重放wal，checkpoint/bgwriter等。 3.2 工作流程 参考2.1，梳理完整的工作流程，从主机执行SQL语句开始，到最终备机达到同样效果 4 物流复制实现源码 参考2.1，介绍每一阶段的关键原理，另外，包括wal日志格式等 你好，我要基本当前目录下的代码，生成一份流复制的skill，所以，接下来的对话，都和这件事有关。

1 使用pgaudt 1.1 常见SQL -- 1 ddl CREATE USER u1 PASSWORD 'u1.password'; CREATE TABLE t1(c1 INT, c2 TEXT); CREATE TABLE t2 (c1 INT, c2 TEXT); CREATE INDEX i1 ON t1(c2); -- 2 dml INSERT INTO t1 VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc'); INSERT INTO t2 VALUES (1, 'aaa'), (5, 'bbb'); UPDATE t1 SET c2 = 'ddd' WHERE c1 < 3; DELETE FROM t1 WHERE c1 = 2; -- 3 select SELECT * FROM t1; -- 4 function SELECT c2 || '_oper' FROM t1; SELECT concat(c2, '_func') FROM t1; INSERT INTO t1 VALUES (5, concat('aaa', '_func')), (6, concat('aaa', '_func')); -- 5 multi INSERT INTO t1 SELECT * FROM t2; SELECT * FROM t1 JOIN t2 ON t1.c1 = t2.c1; CREATE TABLE t3 AS SELECT * FROM t2; DROP TABLE t1,t2,t3; 1 安装pgaudit 1.1 安装postgresql # 1 配置环境变量 echo 'export BUILD_ROOT=`pwd`' > pgenv echo 'export PG_HOME=$BUILD_ROOT/install' >> pgenv echo 'export PATH="$PG_HOME/bin:$PATH"' >> pgenv source pgenv # 2 下载源码 wget https://ftp.postgresql.org/pub/source/v14.18/postgresql-14.18.tar.gz tar -zxvf postgresql-14.18.tar.gz cd postgresql-14.18.tar.gz # 3 编译源码 (不开启debug模式，提高性能) #./configure --prefix=$PG_HOME --enable-debug=yes ./configure --prefix=$PG_HOME --enable-debug=no --enable-cassert=no make -sj make install -sj # ========================================== cd $BUILD_ROOT/pg14/contrib/pgaudit make install USE_PGXS=1 PG_CONFIG=$PG_HOME/bin/pg_config cd - # ========================================== # 4 初始化集群 initdb -D $PG_HOME/data # 4 配置集群 # echo "port = 54000" >> $PG_HOME/data/postgresql.conf # echo "max_connections = 1000" >> $PG_HOME/data/postgresql.conf # ========================================== echo "logging_collector = on" >> $PG_HOME/data/postgresql.conf echo "shared_preload_libraries = 'pgaudit'" >> $PG_HOME/data/postgresql.conf echo "pgaudit.log = 'ALL'" >> $PG_HOME/data/postgresql.conf echo "pgaudit.log_catalog = true" >> $PG_HOME/data/postgresql.conf echo "pgaudit.log_client = true" >> $PG_HOME/data/postgresql.conf echo "pgaudit.log_parameter = true" >> $PG_HOME/data/postgresql.conf echo "pgaudit.log_relation = true" >> $PG_HOME/data/postgresql.conf echo "pgaudit.log_rows = true" >> $PG_HOME/data/postgresql.conf echo "pgaudit.log_statement = true" >> $PG_HOME/data/postgresql.conf # ========================================== # 5 启动集群 pg_ctl start -D $PG_HOME/data # ========================================== DROP EXTENSION pgaudit; CREATE EXTENSION pgaudit; CREATE UNLOGGED TABLE adt(c1 TEXT); CREATE TABLE t1 (c1 INT); INSERT INTO t1 VALUES (1); \! ls $PG_HOME/data/log \! tail $PG_HOME/data/log/ # ========================================== 1.2 tpcc优化 echo " shared_buffers = 200GB work_mem = 1GB maintenance_work_mem = 4GB effective_cache_size = 500GB wal_buffers = 1GB checkpoint_timeout = 55min " >> $PG_HOME/data/postgresql.conf show shared_buffers; show work_mem; show maintenance_work_mem; show effective_cache_size; show wal_buffers; show checkpoint_timeout; 1.3 安装pgaudit # 1 下载源码 wget https://codeload.github.com/pgaudit/pgaudit/tar.gz/refs/tags/1.6.2 -O pgaudit-1.6.2.tar.gz tar -zxvf pgaudit-1.6.2.tar.gz # 2 编译 cd pgaudit-1.6.2 make install USE_PGXS=1 PG_CONFIG=$PG_HOME/bin/pg_config # 3 验证编译成功 ll $PG_HOME/share/extension cat $PG_HOME/data/postgresql.conf | grep shared_preload_libraries # 4 开启日志功能 echo "logging_collector = on" >> $PG_HOME/data/postgresql.conf echo "log_statement = all" >> $PG_HOME/data/postgresql.conf # 5 开启 pgaudit echo "shared_preload_libraries = 'pgaudit'" >> $PG_HOME/data/postgresql.conf echo " pgaudit.log = 'ALL'" >> $PG_HOME/data/postgresql.conf -- 1 安装pgaudit CREATE EXTENSION pgaudit; -- 2 查看配置参数 SHOW pgaudit.log; -- ALL -- READ: SELECT -- WRITE: INSERT, UPDATE, DELETE -- ROLE -- DDL -- FUNCTION -- MISC -- MISC_SET SHOW pgaudit.role; SHOW pgaudit.log_level; -- 查看审计文件 SHOW log_directory; SHOW log_filename; 审计策略：客户 优先3.0 ...

简介 postgresql 简单架构 application driver(jdbc/libpq/..) server +-----------------------------------------------+-----------------------------------------+--------- | PQconnect(host, port, user, name) | do something .. | PQexec("create table t1(c1 int, c2 text)") | | PQexecStart() | PQsendQuery() | pqPutMsgStart('Q') | pqPutnchar("$sql") | pqPutMsgEnd() | pqFlush() | | SocketBackend() | pq_parse_input() | 'Q': exec_simple_query() | PQexec("insert into t1 values(1,'a'),(2,'b')")| | PQexec("select * from t1") | .. | .. | exec_simple_query() | libpq的功能 连接管理 协议编码 数据收发 连接管理 application driver(jdbc/libpq/..) server +-------------------------------------------+-----------------------------------------+--------- | connect(host, port, user, name) 协议编码 消息通用格式 ...

假设集群里已经存在表级透明加密模块，在此基础上设计一个高性能、高易用性的 WAL 加密模块。 WAL 加密的难点几乎不在"加密"本身，而在两件事：同一个 8KB 页会被反复重写，以及恢复路径要在拿不到 catalog 的时候就能解密。下面按设计决策的顺序展开。 1 威胁模型 先划清边界，因为它直接决定后面几个取舍。 要防的：磁盘 / 备份 / 归档介质被拖走；DBA 之外的运维人员直接读 pg_wal/；WAL 归档到对象存储。 不防的：有 root 权限的在线攻击者（内存里必然有明文）、旁路攻击。 1.1 为什么不用 dm-crypt 了事 如果只是防"介质丢失"，块设备加密（LUKS/dm-crypt）性价比高得多。在数据库内核里做 WAL 加密，真正的理由只有三个： 密钥要由数据库自己管，不能落在 OS 层（密评 / 国测要求）； 归档件要天然带密； 和已有的表级 TDE 共用一套密钥体系。 设计要始终对齐这三个理由，否则很容易做成一个又慢、又没多大意义的模块。 1.2 FPI 是必须堵的洞 既然表级 TDE 已经存在，那么 FPI（full page image）就是个必堵的泄漏点。 如果 TDE 是在 smgr 层做的（缓冲区里是明文、写盘时加密），那么 WAL 里的 FPI 记录的是明文页 —— 加密表的数据会原封不动地漏进 WAL。 只要集群里存在任何一张加密表，WAL 加密就必须强制开启，不能是两个独立开关。 实现上做成一个集群级的 data_encryption = on，或者至少让 CREATE ENCRYPTION POLICY 在 WAL 加密未开启时直接报错。这是易用性设计里最重要的一条：不要把一个"配错了就静默泄密"的组合留给用户。 ...

# 单文件 pg_xlogdump 000000010000000000000001 # 多文件，指定lsn开始 pg_xlogdump -s 0/1000020 000000010000000000000001 # 多文件，整个目录 pg_xlogdump -p /opt/openGauss/data/pg_xlog SELECT pg_current_wal_lsn(); \! pg_xlogdump -p $GAUSSHOME/data/pg_xlog -s num=1640180680 printf "%X/%X\n" $((num >> 32)) $((num & 0xFFFFFFFF)) # 示例： 0/61c32bc8 pg_xlogdump -s 0/61c32bc8 -x 131128 printf "%x\n" 1640180680 # 示例： 61c32bc8 pg_xlogdump -s 000000010000000100000061 -x 131128

1 背景 1.1 友商分析 oracle 范围：行级、列级 实现：动态谓词过滤 金仓 范围：列级 mysql 无 sql server 范围：行级 实现：谓词过滤 oracle 基于标签的访问控制OLS：绝密HS、机密S、秘密C、公开P https://docs.oracle.com/en/database/oracle/oracle-database/26/olsag/part1.html#GUID-C20C62AE-2A30-45F9-AEEA-52A0D3286FBA -- 1 定义策略 SA_SYSDBA.CREATE_POLICY(policy_name => 'HR_POLICY', column_name => 'HR_LABEL') -- 2 定义标签 SA_COMPONENTS.CREATE_LEVEL('HR_POLICY', 1000, 'PUBLIC', 'P'); -- 3 设置标签属性 SA_USER_ADMIN.SET_LEVELS('HR_POLICY', 'SCOTT', 'C', 'S'); SA_USER_ADMIN.SET_COMPARTMENTS('HR_POLICY', 'SCOTT', 'FIN'); -- 4 插入数据（主动定义标签） INSERT INTO employees (emp_id, emp_name, salary, HR_LABEL) VALUES (1001, 'John Doe', 50000, CHAR_TO_LABEL('HR_POLICY', 'C:FIN')); sql server：无 kngbase 行级 链接：https://help.kingbase.com.cn/v8/safety/safety-guide/label-and-mac.html#id5 范围：table, view, index, procedure, function, package, sequence, trigger, synonym ...