PostgreSQL | 设备上没有剩余空间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/50833992/
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 | No space left on device
提问by Yogi
I am getting space issue while running a batch process on PostgreSQL database.
在 PostgreSQL 数据库上运行批处理时出现空间问题。
However, df -hcommand shows that machine has enough space
但是，df -h命令显示机器有足够的空间
below is the exact error
下面是确切的错误
org.springframework.dao.DataAccessResourceFailureException: PreparedStatementCallback; SQL [INSERT into BATCH_JOB_INSTANCE(JOB_INSTANCE_ID, JOB_NAME, JOB_KEY, VERSION) values (?, ?, ?, ?)]; ERROR: could not extend file "base/16388/16452": No space left on device
Hint: Check free disk space.
What is causing this issue?
是什么导致了这个问题？
EDIT
编辑
postgres data directory is /var/opt/rh/rh-postgresql96/lib/pgsql/data
postgres 数据目录是 /var/opt/rh/rh-postgresql96/lib/pgsql/data
df -h /var/opt/rh/rh-postgresql96/lib/pgsql/data
Filesystem Size Used Avail Use% Mounted on
/dev/xvda2 100G 63G 38G 63% /
回答by Laurenz Albe
Most likely there are some queries that create large temporary files which fill up your hard disk temporarily. These files will be deleted as soon as the query is done (or has failed), so the file system has enough free space when you look.
很可能有一些查询会创建临时填满您的硬盘的大型临时文件。这些文件将在查询完成（或失败）后立即删除，因此当您查看时，文件系统有足够的可用空间。
Set log_temp_files = 10240in postgresql.conf(and reload) to log all temporary files exceeding 10 MB, then you can check the log file to see if this is indeed the reason.
设置log_temp_files = 10240在postgresql.conf（和重载）来记录所有的临时文件超过10 MB，那么您可以检查日志文件，看看是否这确实是原因。
Try to identify the bad queries and fix them.
尝试识别错误的查询并修复它们。
If temporary files are not the problem, maybe temporary tables are. They are dropped automatically when the database session ends. Does your application use temporary tables?
如果临时文件不是问题，则可能是临时表。当数据库会话结束时，它们会被自动删除。您的应用程序是否使用临时表？
Another possibility might be files created by something else than the database.
另一种可能性可能是由数据库以外的其他东西创建的文件。
回答by Akhilesh
Try using limit(for ex. 100) on huge records like this:
尝试在像这样的大记录上使用限制（例如 100）：
select .... from table .... limit (100);
