0

How to identify the total size of the db (Used) and the total size of the db.

for the total size of the db (Used) : pg_database_size('dbName') cmmnd works.

But I am not sure how to calculate the free space size of the db. (total capacity I mean)

I saw pg_spacefree('table'). but it would need GRANT access. Is there any other way?

please guide me here?

Vedika
  • 1
  • 1
    As far as I know, PostgreSQL can use the entire disk, PostgreSQL doesn't reserve a space for the database, which means you have to check the free space from the OS, not from the database. or write your own function using an untrusted language such as: `plpython` or `plperl` – Anthony Sotolongo May 06 '22 at 16:52
  • Is there any way to check if no space left on device. I need to show some kind of warning on a UI in my application in that case. how could I achieve this? – Vedika May 06 '22 at 17:07

1 Answers1

0

As I mentioned in the comment you can create your own function in an untrusted language, for example, plpython

CREATE EXTENSION plpython3u;
CREATE TYPE disk_use AS (Total_GB numeric , Used_GB numeric, free_GB numeric); 
CREATE OR REPLACE  FUNCTION fn_disk_use(part text default '/') RETURNS disk_use AS
$$
  import shutil
  hdd = shutil.disk_usage(part)
  return (hdd.total / (2**30), hdd.used / (2**30),hdd.free / (2**30))
$$ LANGUAGE plpython3u;

 SELECT * from fn_disk_use('/');
     total_gb      |      used_gb      |      free_gb       
-------------------+-------------------+--------------------
 455.2938232421875 | 402.6443672180176 | 29.455318450927734
(1 row)
Anthony Sotolongo
  • 1,395
  • 2
  • 9
  • 17