postgresql - 如何从 Google Cloud SQL 托管的 Postgres 中删除未使用的临时文件？

Question

我们已经迁移到 Google Cloud SQL，创建了几个数据库并导入了大量数据。除此之外，还有大量的查询被打断，留下了一些临时文件形式的垃圾。存储使用量远远超过 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 的潜在无用文件。有几个问题:

1. 如何识别未被任何正在运行的查询使用的临时文件？
2. 如何删除他们让 postgres 由 Google Cloud SQL 管理？

Answer 1

根据 PostgreSQL documentation ，字段 temp_bytes 定义为:

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() 以来)临时文件大小的总和，不是当前临时文件文件使用。

可以使用非云数据库实例中的“文件函数”来确定当前使用情况，但在 Cloud SQL 中，普通用户无法像这样执行 select pg_ls_dir('base/pgsql_temp')仅保留给 super 用户。

正如您所说，Cloud SQL 是一项托管服务，因此目前无法查看当前临时文件的使用情况。

肯定会清除您看到的数字的一件事是 pg_stat_reset()，尽管如前所述，它与当前临时文件使用情况无关，而是历史总数；

保证清除临时文件的一件事是重新启动数据库实例，作为启动过程的一部分是删除base/pgsql_temp目录。