postgreSQL 真空临时文件？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1378713/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
postgreSQL vacuum temp files?
提问by jorgen
I've got a "little" problem. A week ago my database was reaching full disk capasity. I deleted many rows in different tables trying to free up disk space. After wich i tried running a full vacuum wich did not complete.
我有一个“小”问题。一周前，我的数据库已达到磁盘容量。为了释放磁盘空间，我删除了不同表中的许多行。之后我尝试运行一个完整的真空但没有完成。
What i want to know is. When i stopped the vacuum from fully compliting does it leave any temp files on the disk that i have to delete manualy? I now have a database wich is at a 100% disk capasity, wich needlessly to say is a big problem.
我想知道的是。当我停止完全编译真空时，它是否会在磁盘上留下任何我必须手动删除的临时文件？我现在有一个 100% 磁盘容量的数据库，不用说这是一个大问题。
Any tips to free disk space?
释放磁盘空间的任何提示？
I'm running SUSE with a postgres 8.1.4 database.
我正在使用 postgres 8.1.4 数据库运行 SUSE。
回答by
First of all:
首先：
UPGRADE
升级
Even if you can't to 8.2, 8.3 or 8.4 - at least upgrade to newest 8.1 (which is 8.1.17 at the moment, but will be 8.1.18 in 1-2 days).
即使你不能升级到 8.2、8.3 或 8.4 - 至少升级到最新的 8.1（目前是 8.1.17，但将在 1-2 天内升级到 8.1.18）。
Second: diagnose what is the problem.
第二：诊断是什么问题。
Use dutool to diagnose where exactly did the space go. What directory is occupying too much space?
使用du工具来诊断空间究竟去了哪里。哪个目录占用太多空间？
Check with dfwhat is total used space, and then check how much of it is PostgreSQL directory.
使用df检查总已用空间是多少，然后检查其中有多少是 PostgreSQL 目录。
The best option is to:
最好的选择是：
cd YOUR_PGDATA_DIR
du -sk *
cd base
du -sk *
cd LARGEST DIR FROM PREVIOUS COMMAND
du -sk * | sort -nr | head
Now, that you know which directory in PGDATA is using space you can do something about it.
现在，您知道 PGDATA 中的哪个目录正在使用空间，您可以对此做一些事情。
if it's logs or pg_temp - restart pg or remove logs (pg_clog and pg_xlog are not logs in common meaning of the word, never delete anything from there!).
如果是日志或 pg_temp - 重新启动 pg 或删除日志（pg_clog 和 pg_xlog 不是这个词的常见含义的日志，永远不要从那里删除任何内容！）。
If it's something in your base directory, then:
如果它在您的基本目录中，则：
numerical directories in base directory relate to databases. You can check it with:
基本目录中的数字目录与数据库相关。您可以通过以下方式检查：
select oid, datname from pg_database;
When you know the database that is using most of the space, connect to it, and check which files are using most of the space.
当您知道使用大部分空间的数据库时，连接到它，并检查哪些文件使用了大部分空间。
File names will be numerical with optional ".digits" suffix - this suffix is (for now) irrelevant, and you can check what exactly the file represents by issuing:
文件名将是带有可选的“.digits”后缀的数字 - 这个后缀（现在）无关紧要，您可以通过发出以下命令来检查文件的确切含义：
select relname from pg_class where relfilenode = <NUMBER_FROM_FILE_NAME>;
Once you know which tables/indexes use most of the space - you can VACUUM FULL it, or (much better) issue CLUSTERcommand on them.
一旦你知道哪些表/索引使用了大部分空间 - 你可以 VACUUM FULL 它，或者（更好）对它们发出CLUSTER命令。
回答by Greg Smith
On the new tangent to your problem, you can find out what in the database is using lots of space using a query. That can help you locate candidates to TRUNCATE to reclaim enough working space to clean up the ones with deleted info.
在您的问题的新切线上，您可以使用query找出数据库中使用大量空间的内容。这可以帮助您找到 TRUNCATE 的候选人，以回收足够的工作空间来清理信息已删除的候选人。
Note that deleting lots of rows but not VACUUMing frequently enough to keep disk space in check will often lead to a condition called index bloat, which VACUUM FULL doesn't help with at all. You'll know you're there when the query I suggested shows most of your space is taken up by indexes rather than regular tables. You'll need CLUSTER, which needs as much free disk space as the table itself to rebuild everything, to recover from that problem.
请注意，删除大量行但没有足够频繁地 VACUUMing 以保持磁盘空间检查通常会导致称为索引膨胀的情况，而 VACUUM FULL 根本没有帮助。当我建议的查询显示您的大部分空间被索引而不是常规表占用时，您就会知道您在那里。您将需要 CLUSTER，它需要与表本身一样多的可用磁盘空间来重建所有内容，以从该问题中恢复。
