How to find biggest files in cluster

Question

Postgres 13 cluster in Debian Linux server contains 30 databases. Databases contain number of schemas. How to find biggest files which occupy most space in disk ? I tried

select
    relname::char(25),
    pg_size_pretty(pg_total_relation_size(c.oid))::char(10) as totalsize,
    n.nspname::char(12),
    case
        when c.relkind='i' then 'index'
        when c.relkind='t' then 'toast'
        when c.relkind='r' then 'table'
        when c.relkind='v' then 'view'
        when c.relkind='c' then 'composite type'
        when c.relkind='S' then 'sequence'
        else c.relkind::text
      end ::char(14) as "type"
from
    pg_class c
    left join pg_namespace n on n.oid = c.relnamespace
    left join pg_tablespace t on t.oid = c.reltablespace
where
    (pg_total_relation_size(c.oid)>>21)>0 order by
    pg_total_relation_size(c.oid) desc

But it returns sizes for current database only. How to run in over whole cluster ? Can some plpgsql script used for this. Output should include database name column.

Client application uses psqlODBC to get data so psql or shell scripts should preferably avoided.

Answer 1

You cannot do that, as you can only query the database to which you are connected. You need to connect to each database in turn.