CODEWITHSUNDEEP
databasepostgresqldiskspace
pylover
pylover

Reputation: 8075

Postgresql find total disk space used by a database

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?

Upvotes: 86

Views: 108399

Answers (6)

saranicole
saranicole

Reputation: 2483

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.

Upvotes: 0

Ralph Ritoch
Ralph Ritoch

Reputation: 3440

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.

Upvotes: 12

Steve Lloyd
Steve Lloyd

Reputation: 949

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

Upvotes: 2

mountainclimber11
mountainclimber11

Reputation: 1400

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

Upvotes: 8

sharez
sharez

Reputation: 1497

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

Upvotes: 64

Robert Harvey
Robert Harvey

Reputation: 180908

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/

Upvotes: 137

Related Questions