postgresql 如何在 Postgres 中获取当前可用磁盘空间？

How to get the current free disk space in Postgres?

提问by Christian

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:

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

select pg_get_free_disk_space();

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

是否可以？（我在文档中没有发现任何相关信息）。

PG: 9.3 & OS: Linux/Windows

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

采纳答案by Craig Ringer

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:

一方面，哪个磁盘？生产 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 UNLOGGEDtables 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.

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

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/bytesisn't the only constraint, as many file systems also have limits on number of files (inodes).

还有一个事实是，“空闲”空间并不一定意味着 PostgreSQL 可以使用它（想想：磁盘配额、系统保留的磁盘空间），而且空闲块/字节并不是唯一的限制，因为许多文件系统也有文件数量（inode）的限制。

How does aSELECT pg_get_free_disk_space()report this?

如何做一个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.

了解可用磁盘空间可能是一个安全问题。如果支持，它至少只对超级用户公开。

What you cando is use an untrusted procedural language like plpythonuto make operating system calls to interrogate the host OS for disk space information, using queries against pg_catalog.pg_tablespaceand using the data_directorysetting from pg_settingsto 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.

你可以做的是使用一种不受信任的过程语言，比如plpythonu调用操作系统来询问主机操作系统的磁盘空间信息，使用查询pg_catalog.pg_tablespace和使用data_directory设置pg_settings来发现 PostgreSQL 在主机操作系统上保存东西的位置。您还必须检查挂载点 (unix/Mac)/连接点 (Windows) 以发现pg_xlog等是否在单独的存储中。但是，这仍然不会真正帮助您获得日志空间。

I'd quite like to have a SELECT * FROM pg_get_free_diskspacethat reported the main datadir space, and any mount points or junction points within it like for pg_xlogor 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 platformsthough, and right now, nobody wants it enough to do the work.

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

---

---

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

同时，如果您愿意将您的需求简化为：

• One file system
• Target OS is UNIX/POSIX-compatible like Linux
• There's no quota system enabled
• There's no root-reserved block percentage
• inode exhaustion is not a concern

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

then you can CREATE LANGUAGE plpython3u;and CREATE FUNCTIONa LANGUAGE plpython3ufunction that does something like:

那么你可以CREATE LANGUAGE plpython3u;和CREATE FUNCTION一个LANGUAGE plpython3u，做类似的功能：

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

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

在一个函数中，returns bigint并且要么将其datadir_path作为参数，要么通过SELECT setting FROM pg_settings WHERE name = 'data_directory'在 PL/Python 中执行 SPI 查询来发现它。

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.

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

Or you could use this function someone wrote in PL/Perluto do it using dfand mountcommand output parsing, which will probably only work on Linux, but hey, it's prewritten.

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

回答by eshizhan

Here has a simple way to get free disk space without any extended language, just define a function using pgsql.

这里有一种无需任何扩展语言即可获得可用磁盘空间的简单方法，只需使用 pgsql 定义一个函数即可。

CREATE OR REPLACE FUNCTION sys_df() RETURNS SETOF text[]
LANGUAGE plpgsql $$
BEGIN
    CREATE TEMP TABLE IF NOT EXISTS tmp_sys_df (content text) ON COMMIT DROP;
    COPY tmp_sys_df FROM PROGRAM 'df | tail -n +2';
    RETURN QUERY SELECT regexp_split_to_array(content, '\s+') FROM tmp_sys_df;
END;
$$;

Usage:

用法：

select * from sys_df();
                          sys_df                               
-------------------------------------------------------------------
 {overlay,15148428,6660248,7695656,46%,/}
 {overlay,15148428,6660248,7695656,46%,/}
 {tmpfs,65536,0,65536,0%,/dev}
 {tmpfs,768284,0,768284,0%,/sys/fs/cgroup}
 {/dev/sda2,15148428,6660248,7695656,46%,/etc/resolv.conf}
 {/dev/sda2,15148428,6660248,7695656,46%,/etc/hostname}
 {/dev/sda2,15148428,6660248,7695656,46%,/etc/hosts}
 {shm,65536,8,65528,0%,/dev/shm}
 {/dev/sda2,15148428,6660248,7695656,46%,/var/lib/postgresql/data}
 {tmpfs,65536,0,65536,0%,/proc/kcore}
 {tmpfs,65536,0,65536,0%,/proc/timer_list}
 {tmpfs,65536,0,65536,0%,/proc/sched_debug}
 {tmpfs,768284,0,768284,0%,/sys/firmware}
(13 rows)

Using df $PGDATA | tail -n +2instead of df | tail -n +2while you saving all data in same path on disk. In this case, the function only return one row disk usage for $PGDATA path.

在将所有数据保存在磁盘上的同一路径时使用df $PGDATA | tail -n +2而不是df | tail -n +2。在这种情况下，该函数仅返回 $PGDATA 路径的一行磁盘使用情况。

NOTE FOR SECURITY

安全注意事项

PROGRAMcan run any command by shell, it like two-edged sword. it is best to use a fixed command string, or at least avoid passing any user input in it. See detail on document.

PROGRAM可以通过shell 运行任何命令，就像一把两刃的剑。最好使用固定的命令字符串，或者至少避免在其中传递任何用户输入。请参阅文档的详细信息。

回答by craigds

Here's a plpython2u implementation we've been using for a while.

这是我们使用了一段时间的 plpython2u 实现。

-- NOTE this function is a security definer, so it carries the superuser permissions
-- even when called by the plebs.
-- (required so we can access the data_directory setting.)
CREATE OR REPLACE FUNCTION get_tablespace_disk_usage()
    RETURNS TABLE (
        path VARCHAR,
        bytes_free BIGINT,
        total_bytes BIGINT
    )
AS $$
import os

data_directory = plpy.execute("select setting from pg_settings where name='data_directory';")[0]['setting']
records = []

for t in plpy.execute("select spcname, spcacl, pg_tablespace_location(oid) as path from pg_tablespace"):
    if t['spcacl']:
        # TODO handle ACLs. For now only show public tablespaces.
        continue

    name = t['spcname']
    if name == 'pg_default':
        path = os.path.join(data_directory, 'default')
    elif name == 'pg_global':
        path = os.path.join(data_directory, 'global')
    else:
        path = t['path']

    # not all tablespaces actually seem to exist(?) in particular, pg_default.
    if os.path.exists(path):
        s = os.statvfs(path)
        total_bytes = s.f_blocks * s.f_frsize
        bytes_free = s.f_bavail * s.f_frsize

        records.append((path, bytes_free, total_bytes))

return records

$$ LANGUAGE plpython2u STABLE SECURITY DEFINER;

Usage is something like:

用法类似于：

SELECT path, bytes_free, total_bytes FROM get_tablespace_disk_usage();

回答by Vasily Redkin

Cversion for those who still want a tool to check free space on postgresql server. Only for Linux and FreeBSD currently, need to add proper headers and defines for other OSes.

C那些仍然想要一个工具来检查 postgresql 服务器上可用空间的人的版本。目前仅适用于 Linux 和 FreeBSD，需要为其他操作系统添加适当的头文件和定义。

#if defined __FreeBSD__
# include <sys/param.h>
# include <sys/mount.h>
#elif defined __linux__
# define _XOPEN_SOURCE
# define _BSD_SOURCE
# include <sys/vfs.h>
#else
# error Unsupported OS
#endif
#include <postgres.h>
#include <catalog/pg_type.h>
#include <funcapi.h>
#include <utils/builtins.h>

/* Registration:
CREATE FUNCTION disk_free(path TEXT) RETURNS TABLE (
  size BIGINT, free BIGINT, available BIGINT, inodes INTEGER, ifree INTEGER, blksize INTEGER
) AS '$pglib/pg_df.so', 'df' LANGUAGE c STRICT;
*/

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

PG_FUNCTION_INFO_V1(df);

Datum df(PG_FUNCTION_ARGS)
{
  TupleDesc tupdesc;
  AttInMetadata *attinmeta;
  HeapTuple tuple;
  Datum result;
  char **values;
  struct statfs sfs;
  const char* path = text_to_cstring(PG_GETARG_TEXT_P(0));

  if(get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
    ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("function returning record called in context that cannot accept type record")));
  attinmeta = TupleDescGetAttInMetadata(tupdesc);

  if(0 != statfs(path, &sfs))
    ereport(ERROR, (errcode(ERRCODE_INTERNAL_ERROR), errmsg("statfs() system call failed: %m")));

  values = (char **) palloc(6 * sizeof(char *));
  values[0] = (char *) palloc(20 * sizeof(char));
  values[1] = (char *) palloc(20 * sizeof(char));
  values[2] = (char *) palloc(20 * sizeof(char));
  values[3] = (char *) palloc(10 * sizeof(char));
  values[4] = (char *) palloc(10 * sizeof(char));
  values[5] = (char *) palloc(10 * sizeof(char));

  int64 df_total_bytes = sfs.f_blocks * sfs.f_bsize;
  int64 df_free_bytes  = sfs.f_bfree  * sfs.f_bsize;
  int64 df_avail_bytes = sfs.f_bavail * sfs.f_bsize;
  snprintf(values[0], 20, "%lld", df_total_bytes);
  snprintf(values[1], 20, "%lld", df_free_bytes);
  snprintf(values[2], 20, "%lld", df_avail_bytes);
  snprintf(values[3], 10, "%d", sfs.f_files);
  snprintf(values[4], 10, "%d", sfs.f_ffree);
  snprintf(values[5], 10, "%d", sfs.f_bsize);

  tuple = BuildTupleFromCStrings(attinmeta, values);
  return HeapTupleGetDatum(tuple);
}