PostgreSQL数据库的日常维护工作

PostgreSQL数据库的日常维护工作日常数据库维护工作作者：小p来自：LinuxSir.Org摘要：为了保持所安装的PostgreSQL服务器平稳运行,我们必须做一些日常性的维护工作。我们在这里讨论的这些工作都是经常重复的事情，可以很容易地使用标准的Unix工具，比如cron脚本来实现；目录综述；日常清理；2.1VACUUM;2.1.1语法结构；2.1.2描述；2.1.3参数；2.1.4为什么要用VACUUM；2.2恢复磁盘空间；2.2.1概述；2.2.2VACUUMFULL;2.3更新规划器统计；2.4避免事务ID重叠造成的问题；2.5auto-vacuum守护进程；经常重建索引；日志文件维护；关于本文；更新日志；参考文档；&相关文档；+++++++++++++++++++++++++++++++++++++++++++正文+++++++++++++++++++++++++++++++++++++++++++1.综述；为了保持所安装的PostgreSQL服务器平稳运行,我们必须做一些日常性的维护工作。我们在这里讨论的这些工作都是经常重复的事情，可以很容易地使用标准的Unix工具，比如cron脚本来实现。不过，设置合适的脚本以及检查它们是否成功执行则是数据库管理员的责任，一件很明显的维护工作就是经常性地创建数据的备份拷贝。如果没有最近的备份，那么您就没有从灾难中恢复的机会（比如磁盘坏了，失火，误删了表等等）。其它主要的维护范畴的工作包括周期性的"vacuuming"（清理）数据库。其它需要周期性注意的东西是日志文件的管理。PostgreSQL和其它数据库产品比较起来是低维护量的。但是，适当在这些任务上放一些注意将更加能够确保我们的愉快工作和获取对这个系统富有成效的经验。操作环境：PostgreSQL8.2+Ubuntu7.042.日常清理；VACUUM;语法结构；VACUUM[FULL|FREEZE][VERBOSE][table]VACUUM[FULL|FREEZE][VERBOSE]ANALYZE[table[（column[,...]）]]2.1.2描述；VACUUM回收已删除元组占据的存储空间。在一般的PostgreSQL操作里，那些已经DELETE的元组或者被UPDATE过后过时的元组是没有从它们所属的表中物理删除的；在完成VACUUM之前它们仍然存在。因此我们有必须周期地运行VACUUM,特别是在常更新的表上，如果没有参数，VACUUM处理当前数据库里每个表，如果有参数，VACUUM只处理那个表，简单的VACUUM（没有FULL）只是简单地回收空间并且令其可以再次使用；参数；FULL 选择"完全"清理，这样可以恢复更多的空间，但是花的时间更多并且在表上施加了排它锁。FREEZE 选择激进的元组"冻结"。VERBOSE 为每个表打印一份详细的清理工作报告。ANALYZE 更新用于优化器的统计信息，以决定执行查询的最有效方法。table 要清理的表的名称（可以有模式修饰）。缺省时是当前数据库中的所有表。column 要分析的具体的列/字段名称。缺省是所有列/字段。为什么要用VACUUM；VACUUM命令的含义为：垃圾收集以及可选地分析一个数据库。VACUUM回收已删除元组占据的存储空间。在一般的PostgreSQL操作里，那些已经DELETE的元组或者被UPDATE过后过时的元组是没有从它们所属的表中物理删除的；在完成VACUUM之前它们仍然存在。由于以下几个原因，我们必须周期性运行PostgreSQL的VACUUM命令：1•恢复那些由已更新的或已删除的行占据的磁盘空间。更新PostgreSQL查询规划器使用的数据统计信息。避免因为事务ID重叠造成的老旧数据的丢失。对上面每个条件进行VACUUM操作的频率和范围因不同的节点而不同。因此，数据库管理员必须理解这些问题并且开发出合适的维护策略。建议在经常VACUUM（清理）（至少每晚一次）生产数据库，以保证不断地删除失效的行。尤其是在增删了大量记录之后，对受影响的表执行VACUUMANALYZE命令是一个很好的习惯。例如：sir=#VACUUMVERBOSEANALYZEaccess;信息：正在清理(vacuum)""public.access""信息：index""access_pkey"nowcontains0rowversionsin1pagesDETAIL:0indexrowversionswereremoved・0indexpageshavebeendeleted,0arecurrentlyreusable・CPU0・00s/0・00usecelapsed0・00sec.信息：""access":found0removable,0nonremovablerowversionsin0pagesDETAIL:0deadrowversionscannotberemovedyet・Therewere0unuseditempointers・0pagescontainusefulfreespace・0pagesareentirelyempty.CPU0・00s/0・00usecelapsed0・00sec.信息：正在清理(vacuum)""pg_toast・pg_toast_16464""信息：index""pg_toast_16464_index"nowcontains0rowversionsin1pagesDETAIL:0indexrowversionswereremoved・0indexpageshavebeendeleted,0arecurrentlyreusable・CPU0・00s/0・00usecelapsed0・00sec.信息： ""pg_toast_16464"":found0removable,0nonremovablerowversionsin0pagesDETAIL:0deadrowversionscannotberemovedyet・Therewere0unuseditempointers・0pagescontainusefulfreespace・0pagesareentirelyempty.CPU0・00s/0・00usecelapsed0・00sec.信息：正在分析""public.access""信息：""access":scanned0of0pages,containing0liverowsand0deadrows;0rowsinsample,0estimatedtotalrowsVACUUM这样做将更新系统目录为最近的更改，并且允许PostgreSQL查询优化器在规划用户查询时有更好的选择。不建议日常使用FULL选项，但是可以在特殊情况下使用。一个例子就是在您删除了一个表的大部分行之后，希望从物理上缩小该表以减少磁盘空间占用。VACUUMFULL通常要比单纯的VACUUM收缩更多表的尺寸;2.2恢复磁盘空间;概述;在正常的PostgreSQL操作里，对一行的UPDATE或DELETE并未立即删除旧版本的数据行。这个方法对于获取多版本并行控制的好处是必要的：如果一个行的版本仍有可能被其它事务看到，那么您就不能删除它。但到了最后，不会有任何事务对过期的或者已经删除的元组感兴趣。而它占据的空间必须为那些新的元组使用而回收，以避免对磁盘空间增长的无休止的需求。这件事是通过运行VACUUM实现的。很明显，那些经常更新或者删除元组的表需要比那些较少更新的表清理的更频繁一些。所以，设置一个周期性的cron任务VACUUM那些选定的表，而忽略那些已经知道变化比较少的表．这个方法只是在您拥有大量更新频繁的表和大量很少更新的表的时候有意义—清理一个小表的额外开销根本不值得担心；VACUUM命令有两个变种:第一种形式，叫做"懒汉vacuum"或者只是VACUUM,在表和索引中标记过期的数据为将来使用；它并不试图立即恢复这些过期数据使用的空间。因此，表文件不会缩小，并且任何文件中没有使用的空间都不会返回给操作系统。这个变种的VACUUM可以和通常的数据库操作并发执行;第二种形式是VACUUMFULL命令。这个形式使用一种更加激进的算法来恢复过期的的行版本占据的空间。任何VACUUMFULL释放的空间都立即返回给操作系统。但是，这个形式的VACUUM命令在进行VACUUMFULL;VACUUMFULL一个表的时候在其上要求一个排他锁。因此，经常使用VACUUMFULL会对并发数据库查询有着非常糟糕的影响;标准形式的VACUUM最适合用于维护相当程度的磁盘用量的稳定状态。如果您需要把磁盘空间归还给操作系统，那么您可以使用VACUUMFULL—不过如果释放的磁盘空间又会很快再次被分配又怎样？如果维护更新频繁的表，那么中等频率的多次标准VACUUM运行方法比很低频率的VACUUMFULL更好;对于大多数节点而言，我们推荐的习惯是在一天中的低使用的时段安排一次整个数据库的VACUUM，必要时外加对更新频繁的表的更经常的清理。（有些环境下，对那些更新非常频繁的表可能会每几分钟就VACUUM一次。）如果您的集群中有多个数据库，别忘记对每个库进行清理；vacuumdb脚本可能会帮您的忙;如果您知道自己刚删除掉一个表中大部分的行，那么我们建议使用VACUUMFULL，这样该表的稳定态尺寸可以因为VACUUMFULL更富侵略性的方法而显著减小。日常的磁盘空间清理，请使用VACUUM，而不是VACUUMFULL;如果您有一个表，它的内容经常被完全删除，那么可以考虑用TRUNCATE，而不是后面跟着VACUUM的DELETE。TRUNCATE立即册U除整个表的内容，而不要求随后的VACUUM或者VACUUMFULL来恢复现在没有用的磁盘空间;更新规划器统计;PostgreSQL的查询规划器依赖一些有关表内容的统计信息用以为查询生成好的规划。这些统计是通过ANALYZE命令获得的，您可以直接调用这条命令，也可以把它当做VACUUM里的一个可选步骤来调用。拥有合理准确的统计是非常重要的，否则，选择了恶劣的规划很可能会降低数据库的性能；和为了回收空间做清理一样，经常更新统计信息也是对更新频繁的表更有用。不过，即使是更新非常频繁的表，如果它的数据的统计分布并不经常改变，那么也不需要更新统计信息。一条简单的拇指定律就是想想表中字段的最大很最小值改变的幅度。比如，一个包含行更新时间的timestamp字段将是随着行的追加和更新稳定增长最大值的；这样的字段可能需要比那些包含访问网站的URL的字段更频繁一些更新统计信息。那些URL字段可能改变得一样频繁，但是其数值的统计分布的改变相对要缓慢得多；我们可以在特定的表，甚至是表中特定的字段上运行ANALYZE，所以如果您的应用有需求的话，我们是可以对某些信息更新得比其它信息更频繁的。不过，在实际中，这种做法的实用性是值得怀疑的。ANALYZE是一项相当快的操作，即时在大表上也很快，因为它使用了统计学上的随机采样的方法进行行采样，而不是把每一行都读取进来。因此，每隔一段时间对整个数据库运行一便这条命令可能更简单；注：尽管用ANALYZE按字段进行挖掘的方式可能不是很实用，但您可能还是会发现值得按字段对ANALYZE收集的统计信息的详细级别进行调整。那些经常在WHERE子句里使用的字段如果有非常不规则的数据分布，那么就可能需要比其它字段更细致的数据图表．参阅ALTERTABLESETSTATISTICS．我们对大多数节点都建议在每天的低使用时段安排一次数据库范围的ANALYZE：这个任务可以有效地和每天的VACUUM组合在一起。不过，这对那些表统计信息改变相对缓慢的节点可能会过于夸张，而且少一些的ANALYZE也足够了；避免事务ID重叠造成的问题;PostgreSQL的MVCC事务语意依赖于比较事务ID（XID）的数值：一条带有大于当前事务的XID的插入XID的行版本是"属于未来的"，并且不应为当前事务可见。但是因为事务ID的大小有限（在我们写这些的时候是32位），如果一次集群如果运行的时间很长（大于4十亿次事务），那么它就要受到事务ID重叠的折磨：XID计数器回到零位，然后突然间所有以前的事务就变成看上去是在将来的—这意味着它们的输出将变得可见。简而言之，可怕的数据丢失，（实际上数据仍然在那里，但是如果您无法获取数据，这么说也只是幸灾乐祸。）在PostgreSQL7.2之前，防御XID重叠的唯一办法就是至少每40亿事务就重新做一次initdb。这种做法对高流量的节点而言当然不是令人满意的做法，所以我们设计了更好的方法。新的方法允许某个服务器仍然保持运行状态，不需要initdb或者任何类型的重启。代价就是下面这样的维护要求：数据库中的每个表都必须在每十亿次事务中至少清理一次．从实际角度出发，这个要求不算一个很繁重的要求，但是因为如果我们没能满足这个要求的后果是全部数据的丢失（而不仅仅是磁盘空间的浪费或者性能的下降），我们制作了一些特殊的东西来帮助数据库管理员避免灾难的发生。对于集群中的每个数据库，PostgreSQL都跟踪自上次全数据库范围VACUUM以来的时间。如果任何数据库接近了十亿次事务的危险级别，系统就开始发出警告信息。如果什么都不干，那么系统最终会停止正常的操作，直到进行了合适的手工操作。本节剩下的部分给出这方面的细节。XID比较的新方法剥离出两个特殊的XID，数字1和2（BootstrapXID和FrozenXID）。这两个XID总是被认为表任何普通的XID旧。普通的XID（那些大于2的）使用模-231运算进行比较。这就意味着对于每个普通的XID，总是有二十亿个XID是"更旧"以及二十亿个XID"更新"；表达这个意思的另外一个方法是普通的XID空间是没有终点的环。因此，一旦一条元组带着特定的普通XID创建出来，那么该元组将在以后的二十亿次事务中表现得是"在过去"，而不管我们说的是哪个普通XID。如果该元组在超过二十亿次事务之后仍然存在，那么它就会突然变成在将来的元组。为了避免数据丢失，老的元组必须在到达二十亿次事务的年龄之前的某个时候赋予XIDFrozenXID。一旦它被赋予了这个特殊的XID，那么它们在所有普通事务面前表现为"在过去"，而不管事务ID是否重叠，因此这样的元组直到删除之前都会完好，不管要保存多长时间•这个XID的重新赋值是VACUUM控制的.VACUUM的正常策略是给任何其普通XID有超过十亿次已过去事务行版本重新赋值为FrozenXID。这个策略保留了原来的插入XID直到该数值不再令人感兴趣为止。（实际上，大多数行版本将可能在还没有"冻结"之前就完成生存和消亡了）。在这个策略下，任何表在两次VACUUM运行之间的最大的安全间隔是十亿次事务：如果您等的时间更长，那么最后就可能就会有一条不够老的行版本在重新赋值时变成比二十亿次事务更老，并因此重叠到了未来—也就是说，您失去它了。（当然，它在另外二十亿次事务之后会重新出现，不过那样也无济于事。）因为上面的原因，我们需要周期性地运行VACUUM，所以很难有哪个表会到十亿次事务还没有清理过。但是，为了帮助管理员确保满足了这个要求，VACUUM在系统表pg_database里存储了事务ID统计。尤其是一个数据库的pg_database行中的datfrozenxid字段在任何数据库范围的VACUUM操作（也就是没有声明任何表的VACUUM）之后将会被更新。这个字段里存储的数值是该VACUUM命令使用的冻结终止的XID。系统保证在该数据库中所有比这个终止XID老的普通XID都被FrozenXID代替。检查这个信息的一个便利的方法是执行下面的查询SELECTdatname,age（datfrozenxid）FROMpg_database;age字段用于测量从中止XID到当前事务的XID的数目。使用了这种标准的冻结策略，对一个刚清理过的数据库而言，age字段将从十亿处开始。当age到达二十亿次的时候，数据库必须再次清理以避免事务标识重叠造成的问题。我们建议的策略是至少每半个十亿次（5亿次）事务VACUUM一次数据库，这样就可以保证足够的安全边界范围.为了帮助满足这条规则，如果有任何pg_database记录显示出超过15亿次事务的age，那么每次数据库范围的VACUUM都会自动发出一条警告，比如：play=#VACUUM;WARNING:database"mydb"mustbevacuumedwithin177009986transactionsHINT:Toavoidadatabaseshutdown,executeafull-databaseVACUUMin"mydb".VACUUM如果忽略了上面这样的VACUUM信息，如果距离事务ID重叠小于1千万次，那么PostgreSQL就会在每次事务开始前发出类似上面的警告。如果这些警告还是被忽略了，那么系统将在距离重叠小于1百万次的时候关闭，并且拒绝执行任何新的事务：play=#select2+2;ERROR:databaseisshutdowntoavoidwraparounddatalossindatabase"mydb"HINT:StopthepostmasteranduseastandalonebackendtoVACUUMin"mydb".这个1百万的事务安全边界留下来用于让管理员在不丢失数据的情况下进行恢复，方法是手工执行所需要的VACUUM命令。不过，因为一旦进入了安全关闭模式，系统就不能再执行命令，做这件事情的唯一的方法是停止postmaster，使用一个单独运行的后端来执行VACUUM。关闭模式不会强制于独立运行的后端。带着FREEZE选项的VACUUM使用了更大胆的冻结策略：如果行版本已经老得被所有打开的事务看做是良好的，那么就都冻结.特别是如果在一个空闲的数据库上运行VACUUMFREEZE，那么就保证该数据库中所有的行版本都被冻结。因此，只要该数据库没有其它的变化，那么它就不需要后续的清理以避免事务ID重叠问题。这个技巧被initdb用于准备template0数据库。我们也应该用这个方法对所有在pg_database表里标记着datallowconn=false的数据库进行初始化，因为我们还没有任何便利的方法VACUUM一个您无法联接的数据库。2.5auto-vacuum守护进程;从PostgreSQL8.1开始，系统带有一个额外的可选服务进程，叫做autovacuum守护进程，它的目的是自动执行VACUUM和ANALYZE命令。在打开这个选项之后，autovacuum守护进程将周期性运行并且检查那些有大量插入，更新或者删除元组操作的表。这些检查使用行级别的统计收集设施；因此，除非把statsrowlevel和statsrowlevel设置为true，否则无法使用autovacuum守护。还有，在为superuserreservedconnections选择数值的时候，不要忘记给autovacuum进程保留一个槽位。如果打开了autovacuum守护，那么它会每隔autovacummnatime秒钟运行一次，并且检查应该处理哪个数据库。任何临近事务ID重叠的数据库都会被立即处理。这个时候，autovacuum发出一个数据库范围的VACUUM调用，如果是模板数据库，则发出VACUUMFREEZE，然后终止。如果没有数据库复合这个标准，则选择被上次autovacuum处理时间最远的那个数据库。这种情况下，该数据库里的表被检查，然后根据需要发出独立的VACUUM或者ANALYZE命令。对于每个表，用两个条件来判断应该使用哪个操作。如果上次VACUUM之后的过期元组的数量超过了"清理阈值(vacuumthreshold)"，那么就清理改表。清理阈值是定义为：清理阈值=清理基本阈值+清理缩放系数*元组数(vacuumthreshold=vacuumbasethreshold+vacuumscalefactor*numberoftuples)这里的清理基本阈值是autovacuum_vacuum_threshold，清理的缩放系数是autovacuum_vacuum_scale_factor，元组的数目是失效的元组数目是从统计收集器里面获取的；这事一个半精确的计数，由每次UPDATE和DELETE操作更新。(它只是半精确的是因为在重载下，有些信息可能会丢失。)为了分析，使用了一个类似的条件：分析阈值，定义为分析阈值=分析基本阈值+分析缩放系数*元组数目(analyzethreshold=analyzebasethreshold+analyzescalefactor*numberoftuples)它会和上次ANALYZE插入，更新，或者删除的元组总数进行比较。缺省的阈值和伸缩系数是从postgresql.conf里面取得的，不过，我们可以以每个表独立设置的方式覆盖它，方法就是在系统表pg_autovacuum里输入记录。如果pg_autovacuum里面存在对某个特定表的行，那么就使用它声明的设置；否则使用全局设置。除了基本阈值和缩放系数之外，在pg_autovacuum里还有三个参数可以为每个表进行设置。首先，pg_autovacuum.enabled可以设置为false,让autovacuum守护进程完全忽略某个表。这种情况下，autovacuum只有在为了避免事务ID重叠清理整个数据库的时候才会动那个表。另外两个参数，清理开销延迟（pg_autovacuum.vac_cost_delay）和清理开销限制（pg_autovacuum.vac_cost_limit），用于为基于开销的清理延迟特性设置表相关的数值。如果在pg_autovacuum里任何数值设置为负数，或者在pg_autovacuum里就根本没有出现特定表的数据行，那么使用postgresql.conf里面对应的数值。目前没有任何制作pg_autovacuum记录的支持，只能手工向该系统表中INSERT。这个特性将在以后的版本中改进，并且这个系统表的定义也很有可能会改变。3.经常重建索引;有时候我们值得用REINDEX命令周期的重建索引;在PostgreSQL版本7.4之前，我们经常有必要避免"索引膨胀"，因为缺乏在B-tree索引内部的空间恢复机制。一个情况是就是索引健字的范围随着时间而变化—比如，一个在某个表的时间戳上的索引，随着时间的推移，旧的记录会最终被删除—就会导致膨胀，因为那些用于不再使用的键字范围的索引页面不回得到重复使用。随着时间的推移，索引的尺寸可能会变得比里面的有用的数据大得多。从PostgreSQL7.4开始，那些已经完全清空的索引页会得到重复使用。不过这样仍然会有不充分使用空间的可能：如果一个页面中大多数索引键字被删除，只留下很少的部分呢，那么该页仍然将被分配。所以，如果使用模式是这样的：每个范围里除了少数键字之外，其他大部分键字最终都被删除；那么这样也会导致空间的低效使用。膨胀的可能性不是无穷的—最差的情况是每个页面至少还有一个键字—但是对这样的使用模式，我们可能仍然值得安排周期性的重新索引;对于非B-tree索引的膨胀可能还没有很好地定量分析。在使用非B-tree索引的时候保持对索引的物理尺寸的监控是个很好的主意;还有，对于B-tree索引，一个新建立的索引从某种意义上比更新了多次的访问起来要快，因为在新建立的索引上，逻辑上连接的页面通常物理上也连接在一起。（这样的考虑目前并不适用于非B-tree索引。）仅仅从提高访问速度角度出发，可能我们也值得周期性的重建索引。4.日志文件维护;把数据库服务器的日志输出保存在一个地方是个好主意。在碰到危险的问题的时候，日志输出是非常宝贵的。不过，日志输出可能很庞大（特别是在比较高的调试级别上），而且您不会无休止地保存它们．您需要"旋转"日志文件，这样生成新的日志文件并且经常抛弃老的；如果您简单地把postmaster的stderr定向到一个文件中，您会有日志输出，但是截断日志文件的唯一的方法是停止并重起postmaster。这样做对于开发环境中使用PostgreSQL可能是可以的，但是您肯定不想在生产环境上这么干；一个更好的办法是把postmaster的stderr（错误流■s