Check the database size in PostgreSQL

It is daily activity to monitor the database size on any database server. We have to check the database size either for planning or space monitoring.

1. Query to check the database size in PostgreSQL:

select datname database_name,pg_database_size(datname) from pg_database where datname='name of the database';

Example:

select datname database_name,pg_database_size(datname) from pg_database where datname='lonprod1';

Output:

database_name | pg_database_size 
---------------+------------------
 lonprod1      |          8233475

If we want to get the size of database in KB’s,

select datname database_name,pg_size_pretty(pg_database_size(datname)) from pg_database where datname='lonprod1';
 database_name | pg_size_pretty 
---------------+----------------
 lonprod1      | 8041 kB

To check the current database size in PostgreSQL:

select pg_size_pretty(pg_database_size(current_database())) database_size;
 database_size 
---------------
 7947 kB

To check the total size of all databases in PostgreSQL

select sum(pg_database_size(datname)) from pg_database;
   sum    
----------
 32363016