PostgreSQL中的VACUUM命令用法说明

PostgreSQL中的VACUUM命令用法说明

每当PostgreSQL数据库中的表中的行被更新或删除时，死亡行会被遗留下来。VACUUM则会把它们除去来使空间能被重新利用。如果一个表没有被清空，它会变得臃肿，浪费磁盘空间而且会降低顺序表扫描的速度，而且在较小范围内也会降低索引扫描的速度。

VACUUM命令只可以移除这些不再被需要的行版本（也被称为元组）。如果被删除事务的事务ID（存储在xmax系统列中）比仍然活跃在PostgreSQL数据库（或者共享表的整个集群）中最老的事务（xmin界限）更老，那么这个元组将不再被需要。

注意以下三种情况就可以抑制PostgreSQL集群中的xmin界限 1、 查找长时间运行的事务

我们可以查找长时间运行的事务，然后使用pg_terminate_backend()函数去终止阻碍VACUUM命令的数据库会话。

2、 查找复制槽

复制槽是一种数据结构，它使PostgreSQL服务器免于丢弃备用服务器仍然需要的信息。如果复制被推迟或者备用服务器被关闭，复制槽就会阻止VACUUM命令删除旧的行。

复制槽提供了一种自动化的方式来确保主服务器不移除WAL块直到它们被所有的从服务器接收。而且主服务器即使当从服务器断开连接时也不移除可能导致恢复冲突的行。

复制槽只保留已知所需数量的WAL块而不是多于所需数量。

使用复制槽可以避免这个问题：在从服务器未连接的任意时间段内不提供保护。

我们可以使用pg_drop_replication_slot()函数去丢弃不需要的复制槽。

这种情况只会发生在当hot_standby_feedback参数设置为on时的物理复制中。如果是逻辑复制，那么会有一个相似的危险，但是只有系统目录会被影响。

3、查找准备好的事务

二阶段提交协议是一种原子性确认协议。它是一种分布式算法，用来协调参与分布式原子事务的所有进程，确定是否提交或者终止（回滚）这个事务。

在二阶段提交过程中，一个分布式事务首先使用PREPARE TRANSACTION，为二阶段提交准备当前事务。如果由于任何原因PREPARE TRANSACTION 命令失败，会变成ROLLBACK，而当前事务则会被取消。

然后我们使用COMMIT PREPARED，提交一个之前为两阶段提交预备的事务。

一旦一个事务被准备好，它会一直保持一种“游荡”状态直到被提交或者中止。通常情况下，事务不会在准备状态中保持很长时间，但有时会出现错误所以事务必须被管理员手动移除。

我们也可以使用ROLLBACK PREPARED，取消一个之前为两阶段提交准备好的事务。

补充：postgresql vacuum *** 作

PostgreSQL数据库管理工作中,定期vacuum是一个重要的工作.

vacuum的效果

1.1释放,再利用 更新/删除的行所占据的磁盘空间.

1.2更新POSTGRESQL查询计划中使用的统计数据

1.3防止因事务ID的重置而使非常老的数据丢失。

第一点的原因是PostgreSQL数据的插入,更新,删除 *** 作并不是真正放到数据库空间.如果不定期释放空间的话,由于数据太多,查询速度会巨降.

第二点的原因是PostgreSQL在做查询处理的时候,为了是查询速度提高,会根据统计数据来确定执行计划.如果不及时更新的话,查询的效果可能不如预期.

第三点的原因是PostgreSQL中每一个事务都会产生一个事务ID,但这个数字是有上限的. 当事务ID达到最大值后,会重新从最小值开始循环.这样如果不及时把以前的数据释放掉的话,原来的老数据会因为事务ID的丢失而丢失掉.

虽然在新版本的Postgresql中有自动的vacuum，但是如果是大批量的数据IO可能会导致自动执行很慢，需要配合手动执行以及自己的脚本来清理数据库。

1. vacuumdb 是 SQL 命令 VACUUM的封装

所以用vacuumdb和vacuum来清理数据库都可以，效果是一样的。

2.vacuumdb 中的几个重要参数

可以用vacuumdb --help查询。

-a/--all vacuum所有的数据库

-d dbname 只vacuum dbname这个数据库

-f/--full 执行full的vacuum

-t table 只vacuum table这个数据表

-z/--analyze Calculate statistics for use by the optimizer

3. 切换到postgres用户下

vacuumdb -d yourdbname -f -z -v 来清理你的数据库。

或者加到conrtab中15 1 * * * postgres vacuumdb -d mydb -f -z -v >> /tmp/vacuumdb.log

每天的一点一刻开始进行清理。

4. 如何查询我的XID是否接近临界值的命令：

select age(datfrozenxid) from pg_database;

或者：

select max(age(datfrozenxid)) from pg_database;

5. 然而我们关心的是哪一个大的表组要真正的vacuum

SELECT relname, age(relfrozenxid) as xid_age, pg_size_pretty(pg_table_size(oid)) as table_size FROM pg_class WHERE relkind = 'r' and pg_table_size(oid) > 1073741824
ORDER BY age(relfrozenxid) DESC LIMIT 20;

这个命令是查询按照最老的XID排序，查看大于1G而且是排名前20的表。

下面是一个例子：

relname | xid_age | table_size
------------------------+-----------+------------
postgres_log | 199785216 | 12 GB
statements | 4551790 | 1271 MB
normal_statement_times | 31 | 12 GB

然后你可以单独每个表进行vacuum：

vacuumdb --analyze --verbose --table 'postgres_log' mydb

以上为个人经验，希望能给大家一个参考，也希望大家多多支持脚本之家。如有错误或未考虑完全的地方，望不吝赐教。