PSQL复制表数据无写入却占满磁盘,临时空间如何释放?
PostgreSQL 表间复制磁盘占用问题解答
临时空间存在性说明
PostgreSQL 确实存在专门的临时空间存储未提交、未完成操作的中间数据,你遇到的场景是典型的大写入事务失败后临时数据未正常释放的情况,临时数据主要分为两类:
- 操作级临时文件:执行大查询、批量写入、表复制、排序聚合等操作时,如果数据量超过
work_mem/maintenance_work_mem配置的内存上限,会自动在$PGDATA/base/pgsql_tmp目录下创建临时文件存储溢出数据,操作正常结束(成功/失败终止)后默认自动清理,进程异常崩溃、磁盘满等极端场景下可能出现残留。 - 事务级临时数据:所有写入操作的变更会先写入预写日志(WAL),未提交的表数据也会先写入表文件的预留空间,事务触发回滚后,这些空间会被PostgreSQL内部标记为可复用,但不会直接归还给操作系统,因此从系统层面看磁盘仍处于已占用状态。
磁盘空间释放操作步骤
- 第一步:终止残留异常事务
先查询数据库中运行时间过长的僵尸/异常事务,长事务会阻止空间回收:
确认是异常事务后执行终止命令:SELECT pid, now() - xact_start as duration, query FROM pg_stat_activity WHERE state IN ('idle in transaction', 'active') AND xact_start < now() - INTERVAL '10 minutes';SELECT pg_terminate_backend(目标PID); - 第二步:清理残留临时文件
首先查询数据库数据目录路径:
必须先停止PostgreSQL服务后,进入返回的目录下的SHOW data_directory;base/pgsql_tmp子目录,删除目录下所有残留的临时文件即可,数据库运行状态下禁止手动删除该目录文件,否则会导致运行中进程崩溃。 - 第三步:执行VACUUM回收可复用空间
重启数据库后,先执行普通VACUUM标记可复用空间,不需要额外磁盘空间,不会锁表:
若需要将空间直接归还给操作系统,且可接受对应表锁表、剩余磁盘至少有等于目标表大小的空余空间,可执行:-- 全库回收 VACUUM VERBOSE; -- 若明确知道是哪张表的复制操作,可单独回收该表 VACUUM VERBOSE 目标表名;VACUUM FULL VERBOSE 目标表名; - 第四步:清理堆积WAL日志
若磁盘占用来源于WAL目录(默认$PGDATA/pg_wal),可先检查WAL归档配置是否正常,归档故障会导致WAL无法正常清理。确认无归档问题后,可使用官方工具清理过期WAL:pg_archivecleanup $PGDATA/pg_wal 最新需要保留的WAL文件名
后续规避建议
- 大表复制操作建议分批提交,避免单次事务过大产生过多临时数据占用磁盘
- 数据库所在磁盘建议至少预留20%~30%的空余空间,供临时操作、WAL写入使用
- 定期监控长事务、临时文件大小、WAL磁盘占用情况,提前规避空间占满问题
内容的提问来源于stack exchange,提问作者Masoud
相关产品推荐
相关产品推荐

