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?

Estuarine

Answer 1

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/

Upholster

To clarify, this actually shows the size of the DB, not how much space is left in the DB, which was the original question. – Latif

Answer 2

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

Overgrowth

Answer 3

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.

Lebensraum

Answer 4

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

Sarita

Another +1 for mountain climbing from a montaineer! – Estuarine

Answer 5

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

Deni

Answer 6

With an RDS Postgresql database you can use the FreeLocalStorage Cloudwatch metric subtracted from the value provided by the query @Robert Harvey provided to obtain the storage utilized.

Afterlife