如何在 Postgres 中获取当前可用磁盘空间? [英] How to get the current free disk space in Postgres?

问题描述

在开始在我的数据库中做一些工作之前，我需要确保我至少有 1Gb 的可用磁盘空间.我正在寻找这样的东西:

I need to be sure that I have at least 1Gb of free disk space before start doing some work in my database. I'm looking for something like this:

select pg_get_free_disk_space();

有可能吗?(我在文档中一无所获).

Is it possible? (I found nothing about it in docs).

PG:9.3 &操作系统:Linux/Windows

PG: 9.3 & OS: Linux/Windows

推荐答案

PostgreSQL 目前没有直接公开磁盘空间的功能.

PostgreSQL does not currently have features to directly expose disk space.

一方面，哪个磁盘?生产 PostgreSQL 实例通常如下所示:

For one thing, which disk? A production PostgreSQL instance often looks like this:

• /pg/pg94/:在 WB 模式下，BBU RAID 控制器上快速可靠存储的 RAID6，用于目录和最重要的数据
• /pg/pg94/pg_xlog:快速可靠的 RAID1，用于事务日志
• /pg/tablespace-lowredundancy:一个 RAID10 的快速廉价存储，用于存储您不关心丢失的索引和 UNLOGGED 表，因此您可以使用较低的-冗余存储
• /pg/tablespace-bulkdata:RAID6 或类似的慢速近线磁存储，用于旧审计日志、历史数据、主要写入数据和其他可能较慢的东西访问.
• postgreSQL 日志通常又在别处，但如果这已满，系统可能仍会停止.哪里取决于许多配置设置，其中一些在 PostgreSQL 中根本看不到，例如 syslog 选项.

• /pg/pg94/: a RAID6 of fast reliable storage on a BBU RAID controller in WB mode, for the catalogs and most important data
• /pg/pg94/pg_xlog: a fast reliable RAID1, for the transaction logs
• /pg/tablespace-lowredundancy: A RAID10 of fast cheap storage for things like indexes and UNLOGGED tables that you don't care about losing so you can use lower-redundancy storage
• /pg/tablespace-bulkdata: A RAID6 or similar of slow near-line magnetic storage used for old audit logs, historical data, write-mostly data, and other things that can be slower to access.
• The postgreSQL logs are usually somewhere else again, but if this fills up, the system may still stop. Where depends on a number of configuration settings, some of which you can't see from PostgreSQL at all, like syslog options.

还有一个事实，即空闲"空间并不一定意味着 PostgreSQL 可以使用它(想想:磁盘配额、系统保留的磁盘空间)，以及空闲块/bytes 不是唯一的限制，因为许多文件系统对文件(inode)数量也有限制.

Then there's the fact that "free" space doesn't necessarily mean PostgreSQL can use it (think: disk quotas, system-reserved disk space), and the fact that free blocks/bytes isn't the only constraint, as many file systems also have limits on number of files (inodes).

SELECT pg_get_free_disk_space() 如何报告这个?

了解可用磁盘空间可能是一个安全问题.如果支持，它至少会暴露给超级用户.

Knowing the free disk space could be a security concern. If supported, it's something that'd only be exposed to the superuser, at least.

您可以做的是使用诸如plpythonu之类的不受信任的过程语言，使用针对的查询进行操作系统调用，以询问主机操作系统的磁盘空间信息pg_catalog.pg_tablespace 并使用 pg_settings 中的 data_directory 设置来发现 PostgreSQL 在主机操作系统上保存东西的位置.您还必须检查挂载点 (unix/Mac)/连接点 (Windows) 以发现 pg_xlog 等是否在单独的存储中.不过，这仍然无法真正帮助您留出日志空间.

What you can do is use an untrusted procedural language like plpythonu to make operating system calls to interrogate the host OS for disk space information, using queries against pg_catalog.pg_tablespace and using the data_directory setting from pg_settings to discover where PostgreSQL is keeping stuff on the host OS. You also have to check for mount points (unix/Mac) / junction points (Windows) to discover if pg_xlog, etc, are on separate storage. This still won't really help you with space for logs, though.

我非常想要一个 SELECT * FROM pg_get_free_diskspace 来报告主数据目录空间，以及其中的任何挂载点或连接点，例如 pg_xlog 或 pg_clog，并报告每个表空间和其中的任何挂载点.这将是一个集合返回函数.一个足够关心的人将不得不为所有目标平台实施它，但现在，没有人想要它来完成这项工作.

I'd quite like to have a SELECT * FROM pg_get_free_diskspace that reported the main datadir space, and any mount points or junction points within it like for pg_xlog or pg_clog, and also reported each tablespace and any mount points within it. It'd be a set-returning function. Someone who cares enough would have to bother to implement it for all target platforms though, and right now, nobody wants it enough to do the work.

与此同时，如果您愿意将您的需求简化为:

In the mean time, if you're willing to simplify your needs to:

• 一个文件系统
• 目标操作系统与 Linux 一样与 UNIX/POSIX 兼容
• 没有启用配额系统
• 没有根保留块百分比
• inode 耗尽不是问题

然后你可以 CREATE LANGUAGE plpython3u; 和 CREATE FUNCTION 一个 LANGUAGE plpython3u 函数，它执行如下操作:

then you can CREATE LANGUAGE plpython3u; and CREATE FUNCTION a LANGUAGE plpython3u function that does something like:

import os
st = os.statvfs(datadir_path)
return st.f_bavail * st.f_frsize

在一个返回 bigint 并且将 datadir_path 作为参数的函数中，或者通过执行 SPI 查询(如 SELECT setting FROM pg_settings WHERE name =)来发现它'data_directory' 来自 PL/Python.

in a function that returns bigint and either takes datadir_path as an argument, or discovers it by doing an SPI query like SELECT setting FROM pg_settings WHERE name = 'data_directory' from within PL/Python.

如果您也想支持 Windows，请参阅使用 python 的卷上剩余的跨平台空间.不过，我会使用 Windows 管理接口 (WMI) 查询，而不是使用 ctypes 来调用 Windows API.

If you want to support Windows too, see Cross-platform space remaining on volume using python . I'd use Windows Management Interface (WMI) queries rather than using ctypes to call the Windows API though.

或者你可以使用某人在 PL/Perlu 中编写的这个函数来使用 df 和 mount 命令输出解析，这可能只适用于 Linux，但是嘿，它是预先编写的.

Or you could use this function someone wrote in PL/Perlu to do it using df and mount command output parsing, which will probably only work on Linux, but hey, it's prewritten.

这篇关于如何在 Postgres 中获取当前可用磁盘空间?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！