Size of Postgresql database on Ubuntu

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

sudo su - postgres

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)


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
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.

Would you like to get the most interesting content about programming every Monday?
Sign up to Programming Digest and stay up to date!