Date: Fri, 8 Nov 2019 14:24:19 +0100
From: Christoph Berg <myon@debian.org>
To: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Subject: Monitoring disk space from within the server
Message-ID: <20191108132419.GG8017@msg.df7cb.de>
Mail-Followup-To: Christoph Berg <myon@debian.org>,
	PostgreSQL Hackers <pgsql-hackers@postgresql.org>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="CUfgB8w4ZwR/yMy5"
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
User-Agent: Mutt/1.12.2 (2019-09-21)
Precedence: bulk


--CUfgB8w4ZwR/yMy5
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit

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

--CUfgB8w4ZwR/yMy5
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
	filename="0001-Add-pg_tablespace_statfs-functions.patch"

From bcd0c16e4ce12d406e41e1a77cbc2cf781accc93 Mon Sep 17 00:00:00 2001
From: Christoph Berg <christoph.berg@credativ.de>
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


--CUfgB8w4ZwR/yMy5--