How to get current free disk space in Postgres?

Question

I need to be sure that I have at least 1 GB of free disk space before starting to do some work in my database. I am looking for something like this:

select pg_get_free_disk_space();

Is it possible? (I did not find anything in the docs).

PG: 9.3 and OS: Linux / Windows

Answer 1

PostgreSQL does not currently have functions for directly providing disk space.

On the one hand, which drive? A working PostgreSQL instance often looks like this:

• /pg/pg94/: RAID6 fast reliable storage on the BBU RAID controller in WB mode, for directories and the most important data
• /pg/pg94/pg_xlog: RAID1
• /pg/tablespace-lowredundancy: RAID10 , UNLOGGED, ,
• /pg/tablespace-bulkdata: RAID6 , , , , , , .
• postgreSQL - , , . , PostgreSQL, , .

, , "" , PostgreSQL (: , ), , / , (inode).

SELECT pg_get_free_disk_space() ?

. , , , .

, , , plpythonu, - , pg_catalog.pg_tablespace data_directory pg_settings, , PostgreSQL -. (Unix/Mac)/ (Windows), , pg_xlog .. . .

SELECT * FROM pg_get_free_diskspace, , pg_xlog pg_clog, . , . -, , , .

---

, :

• UNIX/POSIX, Linux

CREATE LANGUAGE plpython3u; CREATE FUNCTION LANGUAGE plpython3u, - :

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

, returns bigint datadir_path , , SPI-, SELECT setting FROM pg_settings WHERE name = 'data_directory', PL/Python.

Windows, . , python. Windows (WMI), ctypes Windows API.

, - PL/Perlu, , df mount, , , Linux, , , .

Answer 2

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;

- - :

SELECT path, bytes_free, total_bytes FROM get_tablespace_disk_usage();

Answer 3

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);
}

Answer 4

- , 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;
$$;

:

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)

df $PGDATA | tail -n +2 df | tail -n +2 . $ PGDATA.

PROGRAM , . , , - . .