List all the databases and their sizes in PostgreSQL

In the article you will learn how to list down/get all the databases and their size in MBs. To get database list from pg_Admin you can simply run the following on your current database and it will get all the databases.

select * from pg_database;      
 

The above query will get you a list of all the databases.

You can get just the database names if you change the * wildcard to just the database_name.

select datname from pg_database;      
 

If you will execute above query you will see that Postgres servers have three databases defined by default named template0, template1 and postgres. template0 and template1 are skeleton databases that are or can be used by the CREATE DATABASE command. postgres is the default database you will connect to before you have created any other databases.

How to get size of each database in PostgreSQL

Sometime we need to show database size in our application, to achieve this we will execute below query to get database size in MBs.

select pg_size_pretty(pg_database_size(pg_database.datname)) from pg_database;       
 

Below is the complete query to get database name and their sizes in MBs.

SELECT pg_database.datname, pg_database_size(pg_database.datname), pg_size_pretty(pg_database_size(pg_database.datname)) 
FROM pg_database 
ORDER BY pg_database_size DESC;