Postgres Database Disk Usage

To figure out how much space is being used by databases in PostgreSQL, you can take a look at the postgres data directory in data/base. This directory contains the data, but it’s by “table filenode” numbers[1]. The PostgreSQL source code contains the utility oid2name, which translates this number into the database name. The following command[2] can be used to display the sizes and names of the databases:

(du -sh * | while read size oid; do echo "$size $(oid2name | grep -w $oid | awk '{print $2}')"; done;)

My PostgreSQL installation was built from source and did not contain oid2name. I had to go back to the source code and build it from the contrib/oid2name directory.

[1] http://developer.postgresql.org/pgdocs/postgres/oid2name.html
[2] Page 76 in: http://www.scribd.com/doc/7800899/Mastering-PostgreSQL-Administration

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s