postgresql查看数据库大小

1. 查所有数据库

\l

2. 查数据库大小

 select pg_database_size('database');

3. 以KB，MB，GB的方式来查看数据库大小

select pg_size_pretty('database');

4. 统计各数据库占用磁盘大小：

SELECT d.datname AS Name,  pg_catalog.pg_get_userbyid(d.datdba) AS Owner,  
   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  
FROM pg_catalog.pg_database d  
   ORDER BY  
   CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')  
       THEN pg_catalog.pg_database_size(d.datname)  
       ELSE NULL  
   END DESC -- nulls first  
   LIMIT 20

5. 查表磁盘大小

select pg_relation_size('tablename');

以KB，MB，GB的方式来查看表大小

select pg_size_pretty(pg_relation_size('tablename'));

统计数据库中各表大小

SELECT  
    table_schema || '.' || table_name AS table_full_name,  
    pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size  
FROM information_schema.tables  
ORDER BY  
    pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC  

6. 查看数据库中索引

\di

查看表的总大小，包括索引大小

select pg_size_pretty(pg_total_relation_size('table_name'));