How to check postgres database size and data occupied size?

itflow

The size of database and table in postgres can be found using following command.

To get the databse size:
SELECT pg_size_pretty( pg_database_size('dbname') );
To get the table Size:
SELECT pg_size_pretty( pg_total_relation_size('tablename') );

The below command used to get the list of all databases with their respective size.

SELECT d.datname as Name,  pg_catalog.pg_get_userbyid(d.datdba) as Owner,
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
        ELSE 'No Access'
    END as Size
FROM pg_catalog.pg_database d
    order by
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_database_size(d.datname)
        ELSE NULL
    END desc
    LIMIT 20;