PostgreSQL
has a nice
built-in function
pg_database_size() to get the size of a database.
This is how to use it:
SELECT pg_database_size('datname');
Where datname is the name of the database you want to know the size of.
Examples of use
In order to know the database name, use \l to list database. Result:
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
analytics | webinbox | UTF8 | en_DK.UTF-8 | en_DK.UTF-8 |
appdev | postgres | UTF8 | en_DK.UTF-8 | en_DK.UTF-8 |
postgres | postgres | UTF8 | en_DK.UTF-8 | en_DK.UTF-8 |
sales | postgres | UTF8 | en_DK.UTF-8 | en_DK.UTF-8 |
template0 | postgres | UTF8 | en_DK.UTF-8 | en_DK.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_DK.UTF-8 | en_DK.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(6 rows)
postgres=#
Let’s look at sales:
postgres=# SELECT pg_database_size('sales');
pg_database_size
------------------
749683203
(1 row)
postgres=#
This is the size in kb.
Typically, I want to have human readable sizes.
Luckily, PostgreSQL has a nice built-in function for that as well called pg_size_pretty().
Here is how to use it:
postgres=# SELECT pg_size_pretty(pg_database_size('pastebin'));
pg_size_pretty
----------------
715 MB
(1 row)
postgres=#
If you want to see the sizes of all databases, this can be done with:
postgres=# SELECT datname, pg_database_size(datname) FROM pg_database;
datname | pg_database_size
---------------+------------------
sales | 8172191
developers | 8630943
payroll | 11145887
geolocation | 262247071
template0 | 7918239
weatherlogger | 8082079
template1 | 8032927
benchmark | 171807391
support | 8778399
webinbox | 8647327
inventory | 1293734559
postgres | 8032927
(12 rows)
postgres=#
Or with human readable sizes:
postgres=# select datname,pg_size_pretty(pg_database_size(datname)) from pg_database;
datname | pg_size_pretty
---------------+----------------
sales | 7981 kB
developers | 8429 kB
payroll | 11 MB
geolocation | 250 MB
template0 | 7733 kB
weatherlogger | 7893 kB
template1 | 7845 kB
benchmark | 164 MB
support | 8573 kB
webinbox | 8445 kB
inventory | 1234 MB
postgres | 7845 kB
(12 rows)