PostgreSQL : How to check size of the database

As a PostgreSQL database administrator, you may come across situations where you want to check the size of the full database, here is a quick solution for the same

Here is my PostgreSQL cluster details, there is one database called technodb, and I need to get the size of this database, here is how we can check

There is a command called pg_database_size('<database name>'), here is how we can execute it

postgres=# select pg_database_size('technodb');

 pg_database_size

------------------

          7773319

(1 row)

by default, it will result in bytes and if you want to bring a result in megabytes (MB), here is how you can do it 

postgres=# select pg_database_size('technodb')/1024/1024;

 ?column?

----------

        7

(1 row)

There is another way to check the size of the database, just execute the command \l+ <database name>

postgres=# \l+ technodb

                                                         List of databases

   Name   |  Owner   | Encoding |      Collate       |       Ctype        | Access privileges |  Size   | Tablespace | Description

----------+----------+----------+--------------------+--------------------+-------------------+---------+------------+-------------

 technodb | postgres | UTF8     | English_India.1252 | English_India.1252 |                   | 7591 kB | pg_default |

(1 row)

and if you want to get the size of all databases together, here is how you can fetch details using pg_database view

 

postgres=# select datname as Database , (pg_database_size(datname))/1024/1024 as Database_size_MB from pg_database;

 database  | database_size_mb

-----------+------------------

 postgres  |                7

 mydb      |                7

 template1 |                7

 template0 |                7

 technodb  |                7

 

[Also read - Types of shutdown in PostgreSQL database]