User:Breinbaas/scratch/dn.sh
From PostgreSQL wiki
Jump to navigationJump to search#!/bin/sh echo echo " select catalog_name , 'catalog_name, current_catalog, current_database()' as \"a.k.a.\" from information_schema.information_schema_catalog_name ; select schema , (select pg_catalog.pg_get_userbyid(n.nspowner) from pg_catalog.pg_namespace n where n.nspname = schema) as owner , to_char( size, '99' || repeat('G999',3)) || '' || lpad(pg_size_pretty( size::bigint ), 10) ||' ' as size , to_char(total_size, '99' || repeat('G999',3)) || '' || lpad(pg_size_pretty( total_size::bigint ), 10) ||' ' as total_size , substring(current_setting('data_directory') from E'(pgsql\..*?)/') as instance , current_setting('port') as port , table_number as \"#\" , substring(tables::text, 1, 76) as tables from ( select table_schema as schema , sum( pg_relation_size (table_catalog || '.' || table_schema || '.' || table_name) ) as size , sum( pg_total_relation_size(table_catalog || '.' || table_schema || '.' || table_name) ) as total_size , count( table_catalog || '.' || table_schema || '.' || table_name) as table_number , array_agg(table_name::text order by table_name::text) as tables from information_schema.tables where table_type = 'BASE TABLE' and table_schema !~ '^pg_' and table_schema <> 'information_schema' group by 1 order by 1 ) as f ; " | psql -X | less -SF