英文:
Open temp files in PostgreSQL
问题
我们在PostgreSQL的temp文件夹中有大约200GB的文件。现在是清理它们的时候，但我不确定是否安全。这就是为什么我想先查看这些文件。文件名看起来像这样：pgsql_tmp644088.0。我如何打开并阅读它们？使用记事本，我只能看到一些符号，例如： ЎҐЖ№T ipadinstagram%instagram_stories j‹74 。你有什么建议？
英文:
We have about 200 GB files inside temp folder in PostgreSQL. And it's time to clear it but I'm not sure is it safe or not. Thats why I want to look at these files first. File names look like: pgsql_tmp644088.0. How can I open and read them? With Notepad I can only see some symbols, for example: ЎҐЖ№T ipadinstagram%instagram_stories j‹74 . What is your reccomendations?
答案1
得分: 1
只需重新启动PostgreSQL，它就会删除所有留下的临时文件。
通常，临时文件不会存在超过查询持续时间的时间。检查是否存在长时间运行的查询。
英文:
Simply restart PostgreSQL, and it should delete all the temporary files that were left behind.
Normally, temporary files don't exist longer than the duration of a query. Check for long running queries.
答案2
得分: 0
不要手动删除临时文件。作为第一步，我会检查哪些操作正在使用临时文件。您有一些选项来启用记录写入临时文件的日志：
- 启用 log_tmp_files
或者
-
使用 pgbadger
-
查询表 Pg_stat_statements，如下所示：
SELECT interval '1 millisecond' * total_time AS total_exec_time,
total_time / calls AS avg_exec_time_ms,
temp_blks_written,
query AS query
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC
LIMIT 20;
- 查询 pg_stat_database 以获取数据库级别的统计信息，如下所示：
select datname, temp_files, pg_size_pretty(temp_bytes) as temp_file_size FROM pg_stat_database order by temp_bytes desc;
- 运行 EXPLAIN ANALYZE 并将 work_mem 大小与 explain analyze 打印的值进行比较。
作为一般建议，请将 temp_file_limit 设置为与 -1（默认值）不同的值。确保使用正确的索引来调整查询。小心地调整 work_mem。将 statement_timeout 设置为最佳值。
请注意：
当内存不足时会创建临时文件。
英文:
Do not manually delete temp file. As a first step i would check what operations are using temp files. You have some options to enable logs on queries writing to temp files:
- Enable log_tmp_files
or
-
Use pgbadger
-
Query table Pg_stat_statements, as follows:
SELECT interval '1 millisecond' * total_time AS total_exec_time,
total_time / calls AS avg_exec_time_ms,
temp_blks_written,
query AS query
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC
LIMIT 20; -
Query pg_stat_database to give db level stats, as follows:
select datname, temp_files , pg_size_pretty(temp_bytes) as temp_file_size FROM pg_stat_database order by temp_bytes desc;
-
Run EXPLAIN ANALYZE and compare work_mem size with values printed by explain analyze.
As a general recommendation set temp_file_limit to a value different than -1 (default).
Be sure to use correct indices to tune queries.
Tune work_mem (carefully).
Set statement_timeout to an optimal value.
Please note:
Temp files are created when memory is not sufficient.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论