Size of Postgresql database on Ubuntu
Firstly, you need to login as a
postgres user and run console with
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)
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.
Thank you for reading! Did you like the article? Send me a comment.