Size of Postgresql database on Ubuntu

Firstly, you need to login as a postgres user and run console with psql command.

sudo su - postgres
psql

After that run the following SQL in the psql console.

SELECT pg_database.datname,
       pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database;

And it gives you the list with all the databases and their sizes.

  datname  |  size
-----------+---------
 template0 | 6409 kB
 postgres  | 6531 kB
 mydb      | 15 MB
 template1 | 6531 kB
(4 rows)

Comments

Steve Purcell: You can also see the size of the tables inside a specific database like this:

SELECT schemaname, tablename,
pg_size_pretty(size) AS size_pretty,
pg_size_pretty(total_size) AS total_size_pretty
FROM (SELECT *,
pg_relation_size(schemaname||'.'||tablename) AS size,
pg_total_relation_size(schemaname||'.'||tablename) AS total_size
FROM pg_tables) AS TABLES
ORDER BY total_size DESC;

This doesn’t include indexes, which will often account for additional space.