如何从Google Cloud SQL管理的Postgres中删除未使用的临时文件？ [英] How to remove unused temp files from Google Cloud SQL managed Postgres?
问题描述
我们已移至Google Cloud SQL，创建了两个数据库并导入了大量数据。除此之外，还有大量查询在这里和那里被打断，以临时文件的形式留下了一些垃圾。而且存储使用量远远超过了1TB。
We've moved to Google Cloud SQL, created couple of databases and imported lots of data. Alongside it was a pretty large amount of queries which were interrupted here and there which have left some garbage in form of temp files. And the storage usage went far above 1TB.
postgres=> SELECT datname, temp_files AS "Temporary files", pg_size_pretty(temp_bytes) AS "Size of temporary files" FROM pg_stat_database;
datname | Temporary files | Size of temporary files
---------------+-----------------+-------------------------
cloudsqladmin | 0 | 0 bytes
template0 | 0 | 0 bytes
postgres | 0 | 0 bytes
template1 | 0 | 0 bytes
first | 33621 | 722 GB
second | 9 | 3399 MB
third | 293313 | 153 GB
(7 rows)
根据上面的查询结果，我们有〜 1TB可能无用的文件。有几个问题：
According to the results of the query above we have ~1TB of potentially useless files. There are couple of questions:
- 如何识别任何正在运行的查询未使用的临时文件？
- 如何删除具有Postgres由Google Cloud SQL管理的文件？
推荐答案
PostgreSQL 文档，该字段 temp_bytes 定义为：
As per the PostgreSQL documentation, the field temp_bytes is defined as:
通过查询写入临时文件的数据总量在此
数据库中。无论创建
临时文件的原因如何，以及log_temp_files
的设置如何，所有临时文件都被计数。
Total amount of data written to temporary files by queries in this database. All temporary files are counted, regardless of why the temporary file was created, and regardless of the log_temp_files setting.
意思是，该数字是自数据库创建（或自上一次pg_stat_reset（）以来）以来的临时文件大小的总和，而不是当前临时文件的大小文件使用情况。
Meaning, that the number is the sum of the temporary file sizes since the creation of the database (or since last pg_stat_reset()), and not the current temp file usage.
当前使用情况可以使用非云数据库实例中的文件功能确定，但是在Cloud SQL中，普通用户无法执行选择pg_ls_dir（'base / pgsql_temp'），因为这仅保留给超级用户。
The current usage could be determined using the 'file functions' in non-cloud database instance, but in Cloud SQL a normal user can not execute select pg_ls_dir('base/pgsql_temp') as this is reserved only to superusers.
正如您所说，Cloud SQL是一个托管服务，因此，目前无法查看当前临时文件的使用情况。
As you said, Cloud SQL is a managed service, therefore at the moment, there is no way to see the current temp file usage.
可以肯定会清除您看到的数字的一件事是pg_stat_reset（），虽然如前所述，这与当前临时文件的使用无关，而是历史的总数；
One thing that will definitely clear the number you see is pg_stat_reset(), though as said before, it is not about current temp file usage, but a historical total;
保证一件事临时文件是数据库实例的重新启动，而启动过程的一部分是擦除 base / pgsql_temp 目录。
One thing guaranteed to clean out temp files is restarting of the database instance, as part of the start process is wiping the base/pgsql_temp directory.
这篇关于如何从Google Cloud SQL管理的Postgres中删除未使用的临时文件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
