PostgreSQL数据库的日常维护工作

《PostgreSQL数据库的日常维护工作》由会员分享，可在线阅读，更多相关《PostgreSQL数据库的日常维护工作（8页珍藏版）》请在金锄头文库上搜索。

1、PostgreSQL数据库的日常维护工作日常数据库维护工作作者：小p来自： LinuxSir.Org摘要：为了保持所安装的 PostgreSQL 服务器平稳运行, 我们必须做一些日常性的维护工作。我们在这里 讨论的这些工作都是经常重复的事情，可以很容易地使用标准的Unix工具，比如cron脚本来实现；目录1. 综述；2. 日常清理；2.1 VACUUM;2.1.1语法结构；2.1.2描述；2.1.3参数；2.1.4为什么要用VACUUM；2.2恢复磁盘空间；2.2.1概述；2.2.2 VACUUM FULL;2.3更新规划器统计；2.4避免事务ID重叠造成的问题；2.5 auto-vacuum

2、 守护进程；3. 经常重建索引；4. 日志文件维护；5. 关于本文；6. 更新日志；7. 参考文档；&相关文档；+正文+1. 综述； 为了保持所安装的 PostgreSQL 服务器平稳运行, 我们必须做一些日常性的维护工作。我们在这里讨论的 这些工作都是经常重复的事情， 可以很容易地使用标准的 Unix 工具，比如 cron 脚本来实现。 不过， 设置合适的脚本以及检查它们是否成功执行则是数据库管理员的责任，一件很明显的维护工作就是经常性 地创建数据的备份拷贝。 如果没有最近的备份，那么您就没有从灾难中恢复的机会（比如磁盘坏了，失火， 误删了表等等）。其它主要的维护范畴的工作包括周期性的 va

3、cuuming （清理）数据库。 其它需要周期性注意的东西是日志文件的管理。PostgreSQL 和其它数据库产品比较起来是低维护量的。 但是，适当在这些任务上放一些注意将更加能够 确保我们的愉快工作和获取对这个系统富有成效的经验。操作环境：PostgreSQL8.2+Ubuntu 7.042. 日常清理；2.1 VACUUM;2.1.1 语法结构；VACUUM FULL | FREEZE VERBOSE table VACUUM FULL | FREEZE VERBOSE ANALYZE table （column , . ） 2.1.2 描述；VACUUM 回收已删除元组占据的存储空间。

4、在一般的 PostgreSQL 操作里， 那些已经 DELETE 的元 组或者被 UPDATE 过后过时的元组是没有从它们所属的表中物理删除的； 在完成 VACUUM 之前它们 仍然存在。因此我们有必须周期地运行VACUUM,特别是在常更新的表上，如果没有参数，VACUUM 处理当前数据库里每个表，如果有参数，VACUUM只处理那个表，简单的VACUUM （没有FULL）只 是简单地回收空间并且令其可以再次使用；2.1.3 参数；FULL 选择完全清理，这样可以恢复更多的空间， 但是花的时间更多并且在表上施加了排它锁。FREEZE 选择激进的元组冻结。VERBOSE 为每个表打印一份详细的清理

5、工作报告。ANALYZE 更新用于优化器的统计信息，以决定执行查询的最有效方法。table 要清理的表的名称（可以有模式修饰）。缺省时是当前数据库中的所有表。column 要分析的具体的列/字段名称。缺省是所有列/字段。2.1.4 为什么要用 VACUUM；VACUUM命令的含义为：垃圾收集以及可选地分析一个数据库。VACUUM回收已删除元组占据的存储空 间。在一般的 PostgreSQL 操作里， 那些已经 DELETE 的元组或者被 UPDATE 过后过时的元组是没 有从它们所属的表中物理删除的； 在完成 VACUUM 之前它们仍然存在。 由于以下几个原因，我们必须 周期性运行Postgr

6、eSQL的VACUUM命令：1恢复那些由已更新的或已删除的行占据的磁盘空间。2. 更新 PostgreSQL 查询规划器使用的数据统计信息。3. 避免因为事务 ID 重叠造成的老旧数据的丢失。 对上面每个条件进行 VACUUM 操作的频率和范围因 不同的节点而不同。 因此，数据库管理员必须理解这些问题并且开发出合适的维护策略。建议在经常VACUUM （清理）（至少每晚一次）生产数据库，以保证不断地删除失效的行。尤其是在增 删了大量记录之后， 对受影响的表执行 VACUUM ANALYZE 命令是一个很好的习惯。例如： sir=# VACUUM VERBOSE ANALYZE access ;信

7、息： 正在清理(vacuum) public .access信息： index access_pkey now contains 0 row versions in 1 pagesDETAIL: 0 index row versions were removed0 index pages have been deleted, 0 are currently reusableCPU 000s/000u sec elapsed 000 sec.信息： access: found 0 removable, 0 nonremovable row versions in 0 pagesDETAIL: 0

8、 dead row versions cannot be removed yetThere were 0 unused item pointers0 pages contain useful free space0 pages are entirely empty.CPU 000s/000u sec elapsed 000 sec.信息：正在清理(vacuum) pg_toast pg_toast_16464信息： index pg_toast_16464_index now contains 0 row versions in 1 pagesDETAIL: 0 index row versi

9、ons were removed0 index pages have been deleted, 0 are currently reusableCPU 000s/000u sec elapsed 000 sec.信息：pg_toast_16464: found 0 removable, 0 nonremovable row versions in 0 pagesDETAIL: 0 dead row versions cannot be removed yetThere were 0 unused item pointers0 pages contain useful free space0

10、pages are entirely empty.CPU 000s/000u sec elapsed 000 sec.信息：正在分析public .access信息： access: scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rowsVACUUM这样做将更新系统目录为最近的更改，并且允许 PostgreSQL 查询优化器在规划用户查询时有更好的选 择。不建议日常使用 FULL 选项，但是可以在特殊情况下使用。 一个例子就是在您删除了一个表的大

11、部分行 之后，希望从物理上缩小该表以减少磁盘空间占用。VACUUM FULL通常要比单纯的VACUUM收缩更 多表的尺寸;2.2 恢复磁盘空间;2.2.1 概述;在正常的PostgreSQL操作里，对一行的UPDATE或DELETE并未立即删除旧版本的数据行。这个方 法对于获取多版本并行控制的好处是必要的： 如果一个行的版本仍有可能被其它事务看到，那么您就不能 删除它。 但到了最后，不会有任何事务对过期的或者已经删除的元组感兴趣。 而它占据的空间必须为那 些新的元组使用而回收， 以避免对磁盘空间增长的无休止的需求。这件事是通过运行 VACUUM 实现的。 很明显，那些经常更新或者删除元组的表需

12、要比那些较少更新的表清理的更频繁一些。 所以，设置一个周 期性的 cron 任务 VACUUM 那些选定的表，而忽略那些已经知道变化比较少的表 这个方法只是在您 拥有大量更新频繁的表和大量很少更新的表的时候有意义 清理一个小表的额外开销根本不值得担心； VACUUM 命令有两个变种: 第一种形式，叫做懒汉vacuum或者只是VACUUM,在表和索引中标记过期的数据为将来使用；它并 不试图立即恢复这些过期数据使用的空间。 因此，表文件不会缩小，并且任何文件中没有使用的空间都不 会返回给操作系统。 这个变种的 VACUUM 可以和通常的数据库操作并发执行;第二种形式是 VACUUM FULL 命令

13、。这个形式使用一种更加激进的算法来恢复过期的的行版本占据的空 间。 任何 VACUUM FULL 释放的空间都立即返回给操作系统。 但是，这个形式的 VACUUM 命令在进 行2.2.2 VACUUM FULL;VACUUM FULL 一个表的时候在其上要求一个排他锁。 因此，经常使用 VACUUM FULL 会对并发数据 库查询有着非常糟糕的影响;标准形式的 VACUUM 最适合用于维护相当程度的磁盘用量的稳定状态。 如果您需要把磁盘空间归还给 操作系统，那么您可以使用 VACUUM FULL 不过如果释放的磁盘空间又会很快再次被分配又怎样？ 如果维护更新频繁的表，那么中等频率的多次标准 V

14、ACUUM 运行方法比很低频率的 VACUUM FULL 更 好;对于大多数节点而言，我们推荐的习惯是在一天中的低使用的时段安排一次整个数据库的 VACUUM， 必 要时外加对更新频繁的表的更经常的清理。 （有些环境下，对那些更新非常频繁的表可能会每几分钟就 VACUUM 一次。）如果您的集群中有多个数据库，别忘记对每个库进行清理；vacuumdb脚本可能会 帮您的忙;如果您知道自己刚删除掉一个表中大部分的行，那么我们建议使用VACUUM FULL，这样该表的稳定态 尺寸可以因为VACUUM FULL更富侵略性的方法而显著减小。日常的磁盘空间清理，请使用VACUUM， 而不是 VACUUM F

15、ULL;如果您有一个表，它的内容经常被完全删除，那么可以考虑用TRUNCATE，而不是后面跟着VACUUM的 DELETE。TRUNCATE立即册U除整个表的内容，而不要求随后的VACUUM或者VACUUM FULL来 恢复现在没有用的磁盘空间;2.3 更新规划器统计;PostgreSQL 的查询规划器依赖一些有关表内容的统计信息用以为查询生成好的规划。 这些统计是通过ANALYZE 命令获得的，您可以直接调用这条命令， 也可以把它当做 VACUUM 里的一个可选步骤来调 用。 拥有合理准确的统计是非常重要的，否则，选择了恶劣的规划很可能会降低数据库的性能； 和为了 回收空间做清理一样，经常更新统计信息也是对更新频繁的表更有用。 不过，即使是更新非常频繁的表， 如果它的数据的统计分布并不经常改变，那么也不需要更新统计信息。 一条简单的拇指定律就是想想表中 字段的最大很最小值改变的幅度。 比如，一个包含行更新时间的 timestamp 字段将是随着行的追加和更