1、PostgreSQL索引损坏到数据安全的思考演讲人 阎书利公司&职位 云和恩墨&技术顾问Subject 1 查询执行流程Profile hereSubject 2 索引损坏的一些现象Profile hereSubject 3 索引损坏的案例分析Profile hereSubject 4 数据安全方面的思考Profile hereCONTENTPART 01 查询执行流程Content hereSQL执行流程解析器 解析器从纯文本的 SQL 语句生成解析树。后续子系统可以从纯文本的 SQL 语句中读取该解析树。分析器 解行解析树的语义分析并生成查询树。重写器 如果存在这样的规则，则重写器使用存储

2、在规则系统中的规则来转换查询树。计划器 计划器生成可以从查询树最有效地执行的计划树。执行器 执行器通过按计划树创建的顺序访问表和索引来执行查询。CBO决定最优路径优化器（Optimizer）是数据库里的一个核心子系统，它的目的是按照一定规则来得到它认为的目标SQL在当前情形下的最高效的执行路径,也就是说优化器的目的是为了得到目标SQL的执行计划。即CBO会选择那些消耗系统I/O和CPU资源最少的执行路径作为最优选择索引与表一样，单独存储，同样是被切分成很多页，索引扫描，先访问索引（访问索引所在页，在索引页中继续找，找到匹配的键，获取被查找的表的元组的TID，再根据该TID去访问表的某页，根据页

3、偏移找到指定元组返回）索引元组中是kv的结构，key代表的是查询条件的值，value即TID，TID中记录了两部分信息，block=2代表页面号，数据位于第几个块（页面），offset=2代表第二个元组，这样就通过索引直接定位了某一条记录，而不需要对页面进行扫描。PART 02 索引损坏的一些现象Content here索引损坏的一些现象1.ERROR:could not read block 1233 in file base/16392/2633 2.ERROR:invalid page header in block 118 of relation base/16384/305673.E

4、RROR:index t1_info_idx contains unexpected zero page at block 04.登录数据库失败postgresysla$psqlpsql:error:could not connect to server:FATAL:index pg_class_oid_index contains unexpected zero page at block 0HINT:Please REINDEX it.5.表上的Autovacuum失效，不能自动收缩age在PostgreSQL中，有多种情况可能导致索引或表的损坏多种情况可能导致索引或表的损坏。可能是Pos

5、tgreSQL的BUG。可能是文件系统的问题，也可能是掉电引起的，存储系统不能再写入磁盘上的文件。而索引损坏的表现形式表现形式比较多样，例如损坏当时没有问题，重启库之后，psql登陆不进数据库。或者能登陆到数据库里，在进行一些查询的时候报出损坏问题。对受影响的表执行DDL或DML操作时，数据库中的损坏通常以错误的形式表现出来。这些错误通常显示为由于缺少文件而导致的读取表的错误。然而，有时候损坏的索引并不像损坏的表那样明显。通常情况下，为了发现是否确实存在索引损坏，需要观察查询的执行时间和结果。需要关注：需要关注：1.使用索引的查询执行了更多的顺序扫描。2.pg_stat_user_tables

6、视图，与seq_scan相比，idx_scan列增加的速度要慢得多（或者根本就没有变化）。3.获取到的一些数据并不是预期的数据，也就是索引查询的数据和顺序扫描的数据不一致。cat postgresql_11.log|grep-E 0-9|grep-i-E ERROR|FATAL|PANIC|awk-F print$5,$6,$7,$8,$9,$10,$11,$12|sort|uniq c例如每天有一个固定的查询，根据应用需求查询数据，并根据策略删除数据，但是某一天查询到的结果和之前的结果查了非常多，就可能出现了问题根据日志和命令筛选出报错大致种类，并结合重点报错查看日志详细内容分析日志分析日志

7、，日志里可能出现报错例如：“ERROR:could not read block xxx in file”的错误，查询正在块/页中寻找信息，但由于某种原因无法读取它。在PG里，所有的表和索引都被存储为数据页/数据块的集合，这些页面默认为8KB，可以在服务器编译期间指定。a amcheckmcheck工具Postgresql-10版本开始提供了该插件验证索引或者是表的逻辑一致性。用于验证物理磁盘损坏，文件系统损坏,内存故障等导致的数据库中的索引损坏。如果结构有问题，则会报错，如果结构 没问题，则不会返回任何信息。bt_index_checkbt_index_check-加AccessShareL

8、ock，这个锁模式和简单SELECT语句在关系上加的锁模式相同。bt_index_check不验证跨越父子关系的结构条件。在heapallindexed为true时会验证堆表的所有元组都有对应的索引项存在。这个行为是在完整验证和减少对业务的影响间的权衡，函数可以作为快速的轻量验证使用。bt_index_parent_checkbt_index_parent_check-加ShareLock锁，这个所模式会阻塞INSERT、UPDATE以及DELETE的并发数据修改，同时防止所属堆表被VACUUM。bt_index_parent_check执行的检查是bt_index_check执行检查的超集。

9、只在其运行期间而不是整个事务期间持有锁。当heapallindexed参数为true时，函数验证堆表的所有元组都有对应的索引项存在。和bt_index_check不同，函数会验证索引结构中的父子关系，会检查父节点是否有缺失的down link。当可选参数rootdescend值为true时，函数会通过从根页面开始搜索来查找叶节点的元组。检查表的完整性检查表的完整性对索引所属的表执行pg_dump，因为pg_dump不使用任何索引，而是直接读取表内数据pg_dump-p 6000-v-t test1 postgres backup_test1.sql backup_test1.log 2&1表的

10、数据没问题，需要重建这个索引重建这个索引。如果日志里没有报错日志里没有报错，查询运行正常，但是返回的数据不正确。我们可以生成查询的“explain analyze”计划，它会显示它从哪里获得数据。explain analyze select*from test1 where aid1 and aid 1 and aid /proc/sys/vm/drop_caches清理系统缓存可以看到，这时候顺序扫描和走索引看到的数据是不一致的，走索引获取的数据是一条，顺序扫描的数据是一条，并且表中只有abcd和hello两种数据，但因为索引的原因，查询到了我们进行更改的结果。create index on

11、 t1(info)with(deduplicate_items=off);构造数据PG13+，对Btree索引进行了优化，表的列如果不是唯一的，可能会有很多相同的值，对应的B树索引也会有很多重复的索引记录。B树索引借鉴了GIN 索引的做法，将相同的Key 指向的对应行的ctid链接起来。这样既减小了索引的大小，又减少了很多不必要的分裂，提高了索引的检索速度（需要读取的索引页减少），也就是de-duplicate。通过pageinspect查看索引第二个数据块内容通过pageinspect查看索引第一个数据块内容 61 62 63 64和68 65 6c 6c 6f分别为数据abcd以及hell

12、o通过dd命令，把第一个索引块变成zero page!dd if=/dev/zero of=/home/pgysl/data/base/13580/16448 seek=0 bs=8192 count=1 conv=notrunc对于“zero page”，PostgreSQL是可以识别出来的。此时索引需要重建。1.使用REINDEX。这个方法允许读取，但会锁定该索引所在父表的所有写入。它还会在被处理的索引上放置一个独占锁；所以在这个操作期间，尝试对该索引进行读取也会失败。建议在停业务时或负载较少的时候使用。2.使用REINDEX CONCURRENTLY（PostgreSQL12+），优于第

13、一种。允许在重组索引操作发生时仍在父表上进行读/写操作，将创建一个新索引，然后在操作结束时替换现有索引。3.PG-12版本以上比较建议使用CREATE INDEX CONCURRENTLY 并删除旧的/损坏的索引，不会阻塞对表的现有操作。CREATE INDEX CONCURRENTLY在工作时，当表上有大量的更新/插入/删除时，会变慢。建议停机时或负载较少的时候使用。对于系统表索引损坏，无法进入数据库场景。echo ignore_system_indexes=true$PGDATA/postgresql.auto.conf并重启数据库，进入数据库重建索引。也可以单用户模式reindex。PA

14、RT 04 数据安全方面的思考Content here 损坏数据块、数据文件及索引的定位和解决 传统行业、政府部门对数据安全很敏感，数据文件的保护 数据加密 如何发现数据库的隐患 如何能保证数据完整性，可恢复性以及数据库服务可用性1.checksum（于检测任何Postgres存储在磁盘上的数据损坏，每次读取该块时，checksum值就会重新计算，并与所存储的checksum值进行比较。这可以检验数据是否损坏。）调initdb程序时，添加-data-checksums标志(如果您更喜欢短参数，则添加-k)Postgres的第12 版引了个新的内置程序pg_checksum，它可以在现有的校验和

15、数据库上启校验和。2.pg_index视图的indisvalid字段。如果为真，此索引当前可以用于查询。为假表示此索引可能不完整：它肯定还在被INSERT/UPDATE操作所修改，但它不能安全地被用于查询。如果索引是唯一索引，唯一性属性也不能被保证。数据块损坏和索引损坏如何定位2.透明加密-TDE（好处：TDE保护突破了文件系统访问控制的安全威胁。）透明加密TDE(Transparent Data Encryption)通过在数据库层执行静止数据加密，阻止可能的攻击者绕过数据库直接从存储读取敏感信息。经过数据库身份认证的应用和用户可以继续透明地访问数据，而尝试读取数据库文件中敏感数据的操作系统

16、用户或者未经认证的用户将不允许访问数据。社区从2016年开始就在讨论这个话题，2018年提出了表级别透明数据加密以及集成KMS(密钥管理系统)的方法。2019年的补丁使用两层密钥体系实现了表级别加密，并使用通用密钥管理API与外部密钥管理系统通信。目前PostgreSQL官方并未推出透明加密功能。cybertec开源了一个分支，支持透明加密，一些基于PG的数据库厂商也集成了TDE进去。所谓透明是指加密对用户来说是透明的，用户无需更改原有的操作习惯,用户和应用程序都不需要关心密钥管理或者加密/解密。可供使用的功能-透明加密-TDE如何发现隐患日常巡检应该关注哪些？我们需要什么样的监控痛点问题相关

17、监控配置可以结合脚本输出关键指标，如长时间未使用的索引，形成完善的监控告警体系，防患于未然。eg.开源方案：监控项脚本+node_exporter/postgres_exporter+Prometheus+Grafana（+Altermanager）1.调度程序发起job，或通过agent接受请求，按要求返回数据。采集数据包括很多项，包括系统的资源信息、磁盘、网络等，数据库的相关信息。2.将采集回来的数据按时间戳存入数据库为以后图形化展示做数据储备。3.对采集的数据进行阈值判断，需定义通用的阈值哦安段规则语言。4.阈值判断发出告警，告警方式有多种选择：面板展示、邮件、短信。5.启动预定义动作，例如部分收集异常诊断信息的命令或者脚本。数据的完整性和服务可用性1.合理的备份，能随时根据备份和WAL做PITR。=+定时物理全备+增量备份&连续的WAL日志。逻辑备份（表结构备份+全局对象备份-Roles）2.高可用、同步备，可以应对单节点磁盘损坏或者异常掉电等问题，提供数据库服务可用性。高可用架构核心准则：冗余+故障转移（容灾恢复）全备建议业务低峰期进行RTO是一个时间窗口，表示业务或服务中断的最大可接受时间RPO是指在发生灾难事件后，系统或服务需要恢复到的数据状态THANK YOU联系信息CONTACT INFORMATION微信/公众号二维码