查询库占用大小： 查询单个库的大小 select pg_database_size('库名'); 单位：字节 select pg_size_pretty(pg_database_size('库名')); 单位：KB，MB，GB 查所有库 select pg_database.datname, pg_database_size(pg_database.datname) AS size from pg_database; 单位：字节 SELECT d.datname as "Name", pg_catalog.pg_get_userbyid(d.datdba) as "Owner", pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", d.datcollate as "Collate", d.datctype as "Ctype", pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges", CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT') THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname)) ELSE 'No Access' END as "Size", t.spcname as "Tablespace", pg_catalog.shobj_description(d.oid, 'pg_database') as "Description" FROM pg_catalog.pg_database d JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid ORDER BY 1; 单位：KB，MB，GB
查询表占用大小： 查询单表占用大小： select pg_relation_size('表名'); 单位：字节 select pg_size_pretty(pg_relation_size('表名')); 单位：KB，MB，GB
查询库下所有表： SELECT t.table_catalog as db, n.nspname AS schemaname, c.relname, c.reltuples::numeric as rowcount, pg_size_pretty(pg_table_size ( '"' || nspname || '"."' || relname || '"' )) AS table_size, pg_size_pretty(pg_indexes_size ( '"' || nspname || '"."' || relname || '"' )) AS indexes_size, pg_size_pretty (pg_total_relation_size ( '"' || nspname || '"."' || relname || '"' )) AS total_size --,pg_relation_filepath(table_name) filepath FROM pg_class C LEFT JOIN pg_namespace N ON ( N.oid = C.relnamespace ) left join information_schema.tables t on (n.nspname= t.table_schema and c.relname=t."table_name" ) WHERE nspname NOT IN ( 'pg_catalog', 'information_schema' ) AND relkind in ('r','p') ORDER BY reltuples DESC LIMIT 20;
推荐阅读
您阅读本篇文章共花了：
发表评论