Query to get the size of a PostgreSQL database (public schema)

pg_db_size.sql

select
    schemaname,
    tablename,
    pg_size_pretty(size) as size_pretty,
    pg_size_pretty(total_size) as total_size_pretty

from (
    select
        *,
        pg_relation_size(schemaname ||'.'|| tablename) as size,
        pg_total_relation_size(schemaname ||'.'|| tablename) as total_size
    from pg_tables
) as tables

where schemaname='public'
order by total_size desc;