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 & OS: Linux/Windows
PostgreSQL does not currently have features to directly expose disk space.
For one thing, which disk? A production PostgreSQL instance often looks like this:
/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.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).
How does aSELECT pg_get_free_disk_space()
report this?
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 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.
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:
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
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.
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.
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.