Postgresql find total disk space used by a database

You could use postgresql Meta-Commands:

  • \l would list databases
  • \l+ extends list with Size, Tablespace, Description.

Use \? to get full list of meta-commands. Also see: https://www.postgresql.org/docs/9.5/static/app-psql.html


SELECT pg_database_size('geekdb')

or

SELECT pg_size_pretty(pg_database_size('geekdb'))

http://www.thegeekstuff.com/2009/05/15-advanced-postgresql-commands-with-examples/


This is an old question, but I created a way to see the results of linux command df -h (Filesystem, Size, Used, Avail, Use%, Mounted on) via a sql query, thus your free disk space and total available disk space for a given file system. Not exactly what the question is about, but helpful for some of use/me. I wish that answer was here hours ago, so I am putting it here (linux only):

create a cron job like this:

@hourly df -h | awk '{print $1","$2","$3","$4","$5","$6}' > /pathhere/diskspaceinfo.csv`

create a foreign table to query:

create extension file_fdw;

create server logserver FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE diskspaceinfo 
(file_sys text, size text, used text, avail text, used_pct text, mount text) 
SERVER fileserver 
OPTIONS (filename '/pathhere/diskspaceinfo.csv', format 'csv');

Then query your table like this:

select * from diskspaceinfo

If you just want something specific, of course just filter the table for what you want. It has limitations, but is very useful for me.

If you have plperlu, you could use this function: https://wiki.postgresql.org/wiki/Free_disk_space

A useful link: https://wiki.postgresql.org/wiki/Disk_Usage


Love mountainclimber's answer. I tweaked the cron to return bytes and remove the header row as follows.

* * * * * df -B1 | tail -n+2 | awk '{print $1","$2","$3","$4","$5","$6}' > /var/www/diskspaceinfo.csv