PostgreSQL Storage Monitoring

If wanting to monitor the size of the data storage in PostgreSQL, use the following SQL script to generate a function to get used space, free space, free percentage of space, and to mark which disk/mount is the data disk/mount:

DROP FUNCTION IF EXISTS get_system_storage_space();
CREATE OR REPLACE FUNCTION get_system_storage_space()
RETURNS TABLE (
    storage_identifier TEXT,
    free_space_gb NUMERIC,
    used_space_gb NUMERIC,
    free_percent NUMERIC,
    is_data_storage BOOLEAN
) 
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
    is_windows BOOLEAN;
BEGIN
    -- Streamlined OS Detection: Covers "x86_64-windows", "msvc", "mingw", and legacy "w64" strings
    SELECT (version() ILIKE '%windows%' OR version() ILIKE '%msvc%' OR version() ILIKE '%mingw%' OR version() ILIKE '%w64%') INTO is_windows;

    -- Setup a shared temporary table
    CREATE TEMP TABLE IF NOT EXISTS system_storage_space_tmp (output text);
    TRUNCATE TABLE system_storage_space_tmp;

    -- Branch execution based on the detected OS
    IF is_windows THEN
        -- WINDOWS PATHWAY
        EXECUTE 'COPY system_storage_space_tmp FROM PROGRAM ' || 
        quote_literal('powershell -Command "Get-CimInstance Win32_LogicalDisk -Filter \"DriveType=3\" | ForEach-Object { $drive = $_.DeviceID; $free = [Math]::Round($_.FreeSpace / 1GB, 2); $used = [Math]::Round(($_.Size - $_.FreeSpace) / 1GB, 2); $pct = [Math]::Round(($_.FreeSpace / $_.Size) * 100, 2); ($drive, $free, $used, $pct) -join '','' }"');

        RETURN QUERY
        WITH current_db_drive AS (
            SELECT UPPER(SUBSTRING(setting FROM 1 FOR 1)) || ':' AS active_letter
            FROM pg_settings WHERE name = 'data_directory'
        ),
        parsed_windows_drives AS (
            SELECT 
                UPPER(split_part(output, ',', 1)) AS win_drive,
                split_part(output, ',', 2)::NUMERIC AS f_space,
                split_part(output, ',', 3)::NUMERIC AS u_space,
                split_part(output, ',', 4)::NUMERIC AS f_pct
            FROM system_storage_space_tmp WHERE output IS NOT NULL AND TRIM(output) != ''
        )
        SELECT 
            p.win_drive::TEXT AS storage_identifier,
            p.f_space AS free_space_gb,
            p.u_space AS used_space_gb,
            p.f_pct AS free_percent,
            (p.win_drive = d.active_letter) AS is_data_storage
        FROM parsed_windows_drives p CROSS JOIN current_db_drive d;

    ELSE
        -- LINUX PATHWAY
        EXECUTE 'COPY system_storage_space_tmp FROM PROGRAM ' || 
        quote_literal('df -k --local | tail -n +2');

        RETURN QUERY
        WITH current_db_dir AS (
            SELECT setting AS active_path FROM pg_settings WHERE name = 'data_directory'
        ),
        parsed_linux_drives AS (
            SELECT 
                -- Grabs the very last item in the row array, which is always the mount point
                (regexp_split_to_array(TRIM(output), '\s+'))[6] AS l_mount,
                -- Field 4 is Available blocks
                ROUND((regexp_split_to_array(TRIM(output), '\s+'))[4]::NUMERIC / 1024 / 1024, 2) AS f_space,
                -- Field 3 is Used blocks
                ROUND((regexp_split_to_array(TRIM(output), '\s+'))[3]::NUMERIC / 1024 / 1024, 2) AS u_space,
                -- Calculates exact free percentage based on raw partition blocks (Field 2 is Total blocks)
                ROUND(((regexp_split_to_array(TRIM(output), '\s+'))[4]::NUMERIC / (regexp_split_to_array(TRIM(output), '\s+'))[2]::NUMERIC) * 100, 2) AS f_pct
            FROM system_storage_space_tmp WHERE output IS NOT NULL AND TRIM(output) != ''
        )
        SELECT 
            p.l_mount::TEXT AS storage_identifier,
            p.f_space AS free_space_gb,
            p.u_space AS used_space_gb,
            p.f_pct AS free_percent,
            (d.active_path LIKE p.l_mount || '%') AS is_data_storage
        FROM parsed_linux_drives p CROSS JOIN current_db_dir d
        ORDER BY (d.active_path LIKE p.l_mount || '%') DESC, LENGTH(p.l_mount) DESC;
        
    END IF;

    -- Clean up temporary session table explicitly
    DROP TABLE IF EXISTS system_storage_space_tmp;
END;
$$;

Then to use this function in a query from anywhere (including Ignition), run the following query:

SELECT *
FROM get_system_storage_space();

If you only want to see the storage disk/mount information, the following query returns only that row:

SELECT *
FROM get_system_storage_space()
WHERE is_data_storage;