如何获得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 / ：快速可靠的存储在白平衡模式BBU RAID控制器上的RAID6，为目录和最重要的数据


• / PG / pg94 / pg_xlog里面的：一个快速可靠的RAID1，对事务日志


• / PG /表空间lowredundancy ：快速廉价存储的东西像索引和未登录表的RAID10的你不在乎失去这样你就可以用较低的冗余存储


• / PG /表空间bulkdata ：一个RAID6或类似的慢近线磁存储的用于旧审计日志，历史数据，写多为数据和其他东西可以慢访问。


• PostgreSQL的日志通常是别的地方了，但如果这被占满时，系统仍然可以停下来。凡取决于多项配置设置，其中一些你不能从PostgreSQL的看到所有的，像日志选项。

• /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的可以使用它（想想：磁盘配额，系统保留的磁盘空间），而事实上，自由的块 / 字节的不是唯一的约束，因为许多文件系统也对文件数量的限制（i节点）。

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 设置C>，发现那里的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 所报告的主要datadir的空间，任何挂载点或交接点中它像 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:

• 一个文件系统


• 目标操作系统是UNIX / POSIX兼容的Linux一样


• 有没有启用配额制度


• 有没有根，保留块的百分比


• inode的疲惫是不是一个问题

然后就可以 CREATE LANGUAGE plpython3u; 和 CREATE FUNCTION A 语言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发现它查询像从pg_settings选择设置，其中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 使用做东风和安装命令的输出解析，这将很可能只在Linux上工作，但嘿，这是prewritten。

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屋！