PostgreSQL Technologies

PostgreSQL: Show/find size of all databases

SELECT
datname AS DatabaseName
,pg_catalog.pg_get_userbyid(datdba) AS OwnerName
,CASE
WHEN pg_catalog.has_database_privilege(datname, 'CONNECT')
THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(datname))
ELSE 'No Access For You'
END AS DatabaseSize
FROM pg_catalog.pg_database
ORDER BY
CASE
WHEN pg_catalog.has_database_privilege(datname, 'CONNECT')
THEN pg_catalog.pg_database_size(datname)
ELSE NULL
END DESC;


Output:

+--------------+--------------+--------------+
| databasename | ownername | databasesize |
+--------------+--------------+--------------+
| postgres | postgres | 2396 MB |
| edb | enterprisedb | 11 MB |
+--------------+--------------+--------------+


To determine the size of a database, use the following command. Replace dbname with the name of the database that you want to check:

SELECT pg_size_pretty(pg_database_size('dbname'));

Output:

+----------------+
| pg_size_pretty |
+----------------+
| 11 MB |
+----------------+


Want to check size of top 10 tables, size of index(s), size of a table including index(s), click here

Please refer pg_database_size, pg_size_pretty functions documentation here.

Comments Rating 0 (0 reviews)

About the author

BestTechReads

Add Comment

Click here to post a comment

Sending

Categories

Categories