Postgresql find total disk space used by a database

Question

I have more than 50 databases hosted in my postgresql server. I need to move some of them on another host, to free-up some disk space,but how can I measure the disk-space used by each database on my volume?

Is there any function exists to get the information that I want?

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

Execute the following query to show the size of each of the databases in the server.

SELECT datname as db_name, pg_size_pretty(pg_database_size(datname)) as db_usage FROM pg_database;

note: This is based on the accepted answer but the saves time of not having to run one query per database.