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.

score 0 · Accepted Answer · answered Jul 23 '21 at 12:26

0

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.

answered Jul 23 '21 at 12:26

Laurenz Albe

209,280
17
206
263

How to create PL/pgSQL script which runs query in every database and consolidates results to single table. Maybe there is some generic script for this which can used to run same quuery is all databases. – Andrus Jul 24 '21 at 13:58
As I said, you cannot do that, since a PL/pgSQL function always runs in a single database session. Use a shell script or something like that. – Laurenz Albe Jul 25 '21 at 16:40
Can PL/pgSQL call postgres_fdw to retrieve data from other databases in cluster. – Andrus Jul 26 '21 at 19:21
Sure, why not?. – Laurenz Albe Jul 28 '21 at 08:28
Where to find PL/pgSQL sample code which enumerates all user databases in cluster and runs some command on them ? – Andrus Jul 29 '21 at 09:09
Can PL/pgSQL script use DbLink to connect to cluster, get list of databases and run query in each database ? It can use different connection string for every database – Andrus Jul 30 '21 at 12:09

How to find biggest files in cluster

1 Answers1