postgresql库查看库表大小

查询库占用大小：
查询单个库的大小
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;