PostgreSQL Physical Size

size.sql

--- Get total WAL sizes / file count
SELECT
    pg_size_pretty(SUM(size)) AS total_wal_size,
    COUNT(*) AS wal_file_count
FROM pg_ls_dir('pg_wal') f(name)
         JOIN LATERAL pg_stat_file('pg_wal/' || name) s ON true
WHERE name ~ '^[0-9A-F]{24}$';

--- Get sizes per db
SELECT
    d.datname AS database_name,
    pg_size_pretty(pg_database_size(d.datname)) AS size_pretty,
    pg_database_size(d.datname) AS size_bytes
FROM
    pg_database d
ORDER BY
    pg_database_size(d.datname) DESC;