Monitoring the available disk space is the topmost thing on the
priority for PostgreSQL operation, yet this metric is not available
from the SQL level.
The attached patch implements a function pg_tablespace_statfs(tblspc)
to report disk space numbers per tablespace:
# select * from pg_tablespace_statfs('pg_default');
blocks │ bfree │ bavail │ files │ ffree
───────────┼──────────┼──────────┼──────────┼──────────
103179564 │ 20829222 │ 20815126 │ 26214400 │ 24426295
Open points:
* should these numbers be converted to bytes?
* the column names currently mirror the statfs() names and should
certainly be improved
* which of these columns add to \db+ output?
* possibly extend this (and \db) to pg_wal
Christoph
>From bcd0c16e4ce12d406e41e1a77cbc2cf781accc93 Mon Sep 17 00:00:00 2001
From: Christoph Berg <[email protected]>
Date: Fri, 8 Nov 2019 14:12:35 +0100
Subject: [PATCH] Add pg_tablespace_statfs() functions
This exposes statfs() on tablespace directories on the SQL level,
allowing monitoring of free disk space from within the server.
---
src/backend/utils/adt/dbsize.c | 86 +++++++++++++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 14 ++++++
2 files changed, 100 insertions(+)
diff --git a/src/backend/utils/adt/dbsize.c b/src/backend/utils/adt/dbsize.c
index a87e7214e9..7283fd19d9 100644
--- a/src/backend/utils/adt/dbsize.c
+++ b/src/backend/utils/adt/dbsize.c
@@ -12,19 +12,23 @@
#include "postgres.h"
#include <sys/stat.h>
+#include <sys/vfs.h>
#include "access/htup_details.h"
#include "access/relation.h"
#include "catalog/catalog.h"
#include "catalog/namespace.h"
#include "catalog/pg_authid.h"
+#include "catalog/pg_type.h"
#include "catalog/pg_tablespace.h"
#include "commands/dbcommands.h"
#include "commands/tablespace.h"
+#include "funcapi.h"
#include "miscadmin.h"
#include "storage/fd.h"
#include "utils/acl.h"
#include "utils/builtins.h"
+#include "utils/memutils.h"
#include "utils/numeric.h"
#include "utils/rel.h"
#include "utils/relfilenodemap.h"
@@ -263,6 +267,88 @@ pg_tablespace_size_name(PG_FUNCTION_ARGS)
}
+/*
+ * Return disk stats of tablespace. Returns -1 if the tablespace directory
+ * cannot be found.
+ */
+static Datum
+get_tablespace_statfs(Oid tblspcOid)
+{
+ char tblspcPath[MAXPGPATH];
+ AclResult aclresult;
+ struct statfs fst;
+ TupleDesc tupdesc;
+ Datum values[6];
+ bool isnull[6];
+ HeapTuple tuple;
+
+ /*
+ * User must be a member of pg_read_all_stats or have CREATE privilege for
+ * target tablespace, either explicitly granted or implicitly because it
+ * is default for current database.
+ */
+ if (tblspcOid != MyDatabaseTableSpace &&
+ !is_member_of_role(GetUserId(), DEFAULT_ROLE_READ_ALL_STATS))
+ {
+ aclresult = pg_tablespace_aclcheck(tblspcOid, GetUserId(), ACL_CREATE);
+ if (aclresult != ACLCHECK_OK)
+ aclcheck_error(aclresult, OBJECT_TABLESPACE,
+ get_tablespace_name(tblspcOid));
+ }
+
+ if (tblspcOid == DEFAULTTABLESPACE_OID)
+ snprintf(tblspcPath, MAXPGPATH, "base");
+ else if (tblspcOid == GLOBALTABLESPACE_OID)
+ snprintf(tblspcPath, MAXPGPATH, "global");
+ else
+ snprintf(tblspcPath, MAXPGPATH, "pg_tblspc/%u/%s", tblspcOid,
+ TABLESPACE_VERSION_DIRECTORY);
+
+ if (statfs(tblspcPath, &fst) < 0)
+ {
+ ereport(ERROR,
+ (errcode_for_file_access(),
+ errmsg("could not stat tablespace directory \"%s\": %m", tblspcPath)));
+ }
+
+ tupdesc = CreateTemplateTupleDesc(5);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 1, "blocks", INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 2, "bfree", INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 3, "bavail", INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 4, "files", INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 5, "ffree", INT8OID, -1, 0);
+ BlessTupleDesc(tupdesc);
+
+ memset(isnull, false, sizeof(isnull));
+ values[0] = Int64GetDatum((int64) fst.f_blocks);
+ values[1] = Int64GetDatum((int64) fst.f_bfree);
+ values[2] = Int64GetDatum((int64) fst.f_bavail);
+ values[3] = Int64GetDatum((int64) fst.f_files);
+ values[4] = Int64GetDatum((int64) fst.f_ffree);
+
+ tuple = heap_form_tuple(tupdesc, values, isnull);
+
+ PG_RETURN_DATUM(HeapTupleGetDatum(tuple));
+}
+
+Datum
+pg_tablespace_statfs_oid(PG_FUNCTION_ARGS)
+{
+ Oid tblspcOid = PG_GETARG_OID(0);
+
+ PG_RETURN_DATUM(get_tablespace_statfs(tblspcOid));
+}
+
+Datum
+pg_tablespace_statfs_name(PG_FUNCTION_ARGS)
+{
+ Name tblspcName = PG_GETARG_NAME(0);
+ Oid tblspcOid = get_tablespace_oid(NameStr(*tblspcName), false);
+
+ PG_RETURN_DATUM(get_tablespace_statfs(tblspcOid));
+}
+
+
/*
* calculate size of (one fork of) a relation
*
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 58ea5b982b..1f1398b46b 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6914,6 +6914,20 @@
descr => 'total disk space usage for the specified tablespace',
proname => 'pg_tablespace_size', provolatile => 'v', prorettype => 'int8',
proargtypes => 'name', prosrc => 'pg_tablespace_size_name' },
+{ oid => '4191',
+ descr => 'disk stats for the specified tablespace',
+ proname => 'pg_tablespace_statfs', provolatile => 'v',
+ prorettype => 'record', proargtypes => 'oid',
+ proallargtypes => '{oid,int8,int8,int8,int8,int8}', proargmodes => '{i,o,o,o,o,o}',
+ proargnames => '{oid,blocks,bfree,bavail,files,ffree}',
+ prosrc => 'pg_tablespace_statfs_oid' },
+{ oid => '4192',
+ descr => 'disk stats for the specified tablespace',
+ proname => 'pg_tablespace_statfs', provolatile => 'v',
+ prorettype => 'record', proargtypes => 'name',
+ proallargtypes => '{name,int8,int8,int8,int8,int8}', proargmodes => '{i,o,o,o,o,o}',
+ proargnames => '{name,blocks,bfree,bavail,files,ffree}',
+ prosrc => 'pg_tablespace_statfs_name' },
{ oid => '2324', descr => 'total disk space usage for the specified database',
proname => 'pg_database_size', provolatile => 'v', prorettype => 'int8',
proargtypes => 'oid', prosrc => 'pg_database_size_oid' },
--
2.24.0.rc1
