PostgreSQL

1 使用方式 vsql -d vastbase -r CREATE USER u1 PASSWORD 'u1.password'; CREATE USER u2 SYSADMIN PASSWORD 'u2.password'; CREATE USER u3 SUPERUSER PASSWORD 'u3.password'; SELECT rolname,rolsuper,rolsystemadmin FROM pg_authid ORDER BY rolname; vsql -d vastbase -c "SELECT rolname,rolsuper,rolsystemadmin FROM pg_authid ORDER BY rolname;" # 导出 vb_dumpall --roles-only -f ./u.sql # 清理 vsql -d vastbase -c " DROP USER IF EXISTS u1,u2,u3; " # 恢复 vsql -d vastbase -f ./u.sql vsql -d vastbase -c "SELECT rolname,rolsuper,rolsystemadmin FROM pg_authid ORDER BY rolname;"

1 libpq编程 1.1 配置数据库 安装数据库 # 假设安装目录为 $PG_HOME 启动数据库 # 假设数据目录为 $PG_HOME/data 配置数据库 echo 'host all u1 0.0.0.0/0 md5' >> $PG_HOME/data/pg_hba.conf echo "listen_addresses='0.0.0.0'" >> $PG_HOME/data/postgresql.conf pg_ctl reload -D $PG_HOME/data 创建用户 psql -d postgres -c "create user u1 password 'u1.password';" # 验证一下 psql -h 127.0.0.1 -p 5432 -d postgres -U u1 -W 'u1.password' -c '\d' 1.2 编写libpq demo 生成源文件 touch pq.c # 复制 1.3 章节的源码 到 pq.c中 # 配置环境变量 export LD_LIBRARY_PATH=$PG_HOME/lib:$LD_LIBRARH_PATH 编译 gcc pq.c -o pq -I$PG_HOME/include -L$PG_HOME/lib -lpq 执行 ...

1 E100 fdw +---- | User | 定义外表 -- \c atlasdb -- ALTER SYSTEM SET password_encryption = 'scram-sha-256'; -- SELECT pg_reload_conf(); -- SHOW password_encryption; -- 外部数据库：fdb1 CREATE DATABASE fdb1; CREATE USER fu1 PASSWORd 'QAZ2wsx@123'; \c fdb1 set role fu1; CREATE TABLE ft1 (c1 INT); INSERT INTO ft1 VALUES (11); reset role; -- 配置本地数据库 \c atlasdb CREATE EXTENSION postgres_fdw; CREATE USER u1 PASSWORD 'u1.password'; CREATE SERVER s1 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '172.16.100.134', port '5432', dbname 'fdb1'); GRANT ALL ON FOREIGN SERVER s1 TO u1; set role u1; CREATE FOREIGN TABLE t1 (c1 INT) SERVER s1 OPTIONS (schema_name 'public', table_name 'ft1'); CREATE USER MAPPING FOR u1 SERVER s1 OPTIONS (user 'fu1', password 'QAZ2wsx@123'); -- 配置远程登录 echo 'host all u1 0.0.0.0/0 md5' >> $PG_HOME/data/atlasdb_hba.conf echo 'host all fu1 0.0.0.0/0 md5' >> $PG_HOME/data/atlasdb_hba.conf echo "listen_addresses='0.0.0.0'" >> $PG_HOME/data/atlasdb.conf pstop pstart -- 访问外表 set role u1; SELECT * FROM t1; SELECT * FROM pg_user_mapping; -- clean DROP SERVER s1 CASCADE; DROP USER u1; DROP DATABASE fdb1; DROP USER fu1; 2 G100 fdw 安装外部集群 ...

基本概念 这里是融合版的一个需求，属于兼容性需求类别，主要实现的是一个黑名单，是要把一个列表中的多个插件不允许在MSSQL兼容安装包中出现，或只能在5432端口（PG模式）中使用，不能在1433端口（MSSQL模式）中使用。 初步分析 根据需求列表，可以基本将需要修改的插件分为三类： 禁止在双端口安装使用； 在5432端口允许使用，1433端口禁止安装使用； 双端口都允许使用； 其中，需求描述特别提到，允许在编译阶段进行修改，由此可以对应的提出三类插件的修改方案： 在编译阶段不安装，也就是在contrib/Makefile中的SUBDIR安装插件，删除这些不允许在双端口安装的插件； 在hooks.c中增加一个名单，名单中包含这些允许在5432不允许在1433安装的插件，首先检测当前dialect是否为tsql，如果为tsql，检查是否是T_CreExtensionStmt，如果是则检查插件名单是否在这个名单之内，如果没有则放行，有则报错； 不做任何处理； 理论上，以上方案实施后，能够达到双端口不可用则直接无法找到插件，用户自行安装则不保证可能出现的任何问题；对于5432可用1433不可用，会进行报错；其他类型不做处理；应该可以达到需求描述的目的实现； 边界考虑 由于删除了某些插件编译且对某些插件在一定场景进行了屏蔽，考虑数据库升级场景，如果以前使用过此插件，现在进行了屏蔽，可能导致升级失败； 灵活性 现在的屏蔽方式是使用直接修改编译文件与硬编码名单的方式实现，如果在一个版本内，由于某些特殊原因想启用已屏蔽的插件，则在当前版本是无法实现的。如果将名单作成guc参数，则可以提供灵活性，但是一旦被手动篡改，则无法预料可能产生的问题；

基本概念 逻辑复制是数据同步的一种方式，逻辑二字主要指的是传输数据的格式，与之相对的是物理复制。 逻辑数据格式 逻辑数据格式是指，能够由一定规则描述的，符合SQL直觉的一种数据格式，例如： [MsgType：Insert，Relation：test，New Tuple：[a, int, 100]] 从这种数据格式上，能够通过解析等方式，将数据还原为SQL语句，这也同时表现了逻辑复制的第一个特点：无视物理结构上的差异，可以在异构、跨版本的数据库之间进行数据同步。 接上例，通过一定的解析步骤，我们总能够得到： Insert into test(a) values 100::int; 对于不同内核来说，可能在语法上稍有差异，但只需要修改一些解析方式，也能够得到自身看的懂、可以运行的命令。 与物理复制的区别 逻辑复制的另外一个特点，是相对于另一种主流的数据同步方式：物理复制，来作为比较的。 几乎所有的数据库内核，在进行数据存储的时候，都是采用随机写的方式，最大化利用性能优势，将数据存储在磁盘上。那么从物理上看，一个relation的所有数据，可能并不是连续的：他们可能分布在不同的page上，由指针进行连接。那么从磁盘角度看，一个page的分布，可能是这样的： -------------- Page Header -------------- Tuple 1 for r1 -------------- Tuple 2 for r1 -------------- Tuple 1 for r2 -------------- Tuple 1 for r3 -------------- Tuple 2 for r2 -------------- 如此一来，想要在物理层面进行数据传输时，以page为单位，是无法将不同relation的tuple分开的，也就是细粒度不够。 逻辑复制的另一个特点，是可以以表为单位，进行数据同步，由一个Reader完成。 Reader逐行的去读取物理中的tuple，筛选那些符合要求的tuple，进行下一步工作。 逻辑复制提高的数据同步的细粒度，可以在库、表、甚至行上进行数据过滤，显然是一种更灵活的同步方式，在某些场景下，其效果更好，常见的有： 应用场景 某数据库使用厂商需要进行版本升级，但升级前后版本由于迭代问题，在物理存储上存在差异，导致无法进行直接替换； 某数据库使用厂商需要将原有的数据库数据迁移到一款新的数据库产品上，而这两个数据库的内核架构完全不同，物理存储存在本质差异； 某厂商部门之间使用的数据库产品不同，但部门之间存在协作，需要进行数据传递； … 架构 如图所示，逻辑复制功能，是两个实例（或生产者与消费者）之间完成的数据同步，通常我们把作为数据源端的实例，称为发布端（Publisher），而准备接收数据并进行回放的实例称为订阅端（Subscriber）。 发布端 发布端的组成部分，共分为以下几个部分：WALReader、Decoder、Sender，通常是由三个独立的线程，按照其角色完成不同的工作： WALReader：主要完成对Xlog日志的读取，即从磁盘上将当前已罗盘的Xlog record读取到内存，并发送给Decoder； Decoder：解码线程，由于WALReader发送来的数据，是物理层面的记录，Decoder会按照特定的规则，将物理信息转换为逻辑信息，从内存上看，使用一个结构体，保存Xlog record中的xid、LSN、DMLType等信息。同时，Decoder还需要将这些信息，按照约定好的协议（Protocol）将数据进行打包，其中应用最广泛的端到端协议是pgoutput（后面会介绍他的协议格式以及细节实现）。Decoder会将这些按照协议进行拼接的逻辑数据存储到私有内存中，当达到一定条件时，将数据发送给Sender线程执行发送。 Sender：将Decoder发送来的数据通过网络发送给订阅端。 发布端如何保证事务一致性 对于数据同步来说，最重要的是事务一致性，如果不能保证一致性，那么逻辑复制将是毫无意义的。而要保证事务一致性，通常主要责任在于发布端，发布端需要确保自己发送逻辑信息的顺序，是严格的，即使不能保证发送顺序一定与WAL日志写入顺序完全一致，但必须保证事务的最终一致性。为了保证事务一致性，在发布端，通常会有以下几个部分保证： 严格按照commit顺序进行事务发送：事务的提交顺序非常重要，Decoder解码到一条逻辑数据时，并不会立刻发送给Sender，而是将条变更存储起来，直到解码到该事务对应的commit，才将整个事务完发送； 错误重发：当订阅端接收到一个事务的commit时，即代表这个事务经传输完毕，订阅端会发送一个确认LSN给发布端，发布端需要确认LSN是在当前处理LSN的前面，如果此时LSN发生错乱，发布端会立即这条返回的LSN开始，重新发送这些变更； commit时间确认：在Decoder将Xlog信息解码为逻辑协议信息时对于某些类型的消息，会携带commit timestampz，这是该事务提时的瞬时时间戳； 发布端对于内存的管理 在业务系统中，有时不可避免的会出现大事务，通常包含几M甚至百M的事务变更数据，在逻辑复制模块中，上文提到，通常是将所有事务存储于内存，当遇到该事务的commit时，才进行发送。对于这种大事务来说，如果多几条，可能很容易造成内存压力，引起CPU抖动，有影响系统整体性能的可能性。 为了保证系统整体性能，当遇到这种大事务的时候，通常会设计一个内存阈值，当在内存中已经存储了多条事务变更时，一旦当前内存占用达到阈值，发布端会选择当前内存中一个最大的事务（不一定是完整的，只是transaction size最大）spill to disk；诚然，这会带来一定的IO开销，因为当我们读到被spill的事务的commit时，需要把它从磁盘上读出来，但相比于其带来的内存负荷，这是合理的取舍。 ...