Check the size of specific or all database in PostgreSQL

Check the size of specific database:

With help of pg_size_pretty it will give the result in human readable format:

Syntax:
select pg_database_size('databaseName')/1024/1024;

Example:

testdb=# SELECT pg_size_pretty( pg_database_size('testdb') );
 pg_size_pretty
----------------
 7525 kB
(1 row)

Simple check in bytes:
select pg_database_size('databaseName');
Example:
testdb=# select pg_database_size('testdb');
 pg_database_size
------------------
          7705391
(1 row)
testdb=# select pg_database_size('testdb')/1024/1024;
 ?column?
----------
        7
(1 row)

Check the size of all databases in PostgreSQL:

select t1.datname AS db_name,  
       pg_size_pretty(pg_database_size(t1.datname)) as db_size
from pg_database t1
order by pg_database_size(t1.datname) desc;


  db_name  | db_size
-----------+---------
 testdb    | 7525 kB
 template1 | 7525 kB
 postgres  | 7453 kB
 template0 | 7297 kB

Check all database size and specific with \l+ command:

# \l+ databasename

Example:
testdb=# \l+ testdb
                                                                       List of databases
  Name  |  Owner   | Encoding |      Collate       |       Ctype        | ICU Locale | Locale Provider | Access privileges |  Size   | Tablespace | Description
--------+----------+----------+--------------------+--------------------+------------+-----------------+-------------------+---------+------------+-------------
 testdb | postgres | UTF8     | English_India.1252 | English_India.1252 |            | libc            |                   | 7525 kB | pg_default |
(1 row)


Check all database size:
# \l+

Example:
testdb=# \l+
                                                                                          List of databases
   Name    |  Owner   | Encoding |      Collate       |       Ctype        | ICU Locale | Locale Provider |   Access privileges   |  Size   | Tablespace |                Description
-----------+----------+----------+--------------------+--------------------+------------+-----------------+-----------------------+---------+------------+--------------------------------------------
 postgres  | postgres | UTF8     | English_India.1252 | English_India.1252 |            | libc            |                       | 7453 kB | pg_default | default administrative connection database
 template0 | postgres | UTF8     | English_India.1252 | English_India.1252 |            | libc            | =c/postgres          +| 7297 kB | pg_default | unmodifiable empty database
           |          |          |                    |                    |            |                 | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | English_India.1252 | English_India.1252 |            | libc            | =c/postgres          +| 7525 kB | pg_default | default template for new databases
           |          |          |                    |                    |            |                 | postgres=CTc/postgres |         |            |
 testdb    | postgres | UTF8     | English_India.1252 | English_India.1252 |            | libc            |                       | 7525 kB | pg_default |
(4 rows)