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