正文

POSTGRESQL 查看数据库 数据表大小

IP云
IP云V管理员/6 阅读
06/26
提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

1、查看数据库大小：

select pg_database_size('log_analysis');

select pg_database_size('log_analysis'); pg_database_size
------------------( row)

2、select pg_database.datname, pg_size_pretty (pg_database_size(pg_database.datname)) AS size from pg_database;

log_analysis=# select pg_database.datname, pg_size_pretty (pg_database_size(pg_database.datname)) AS size from pg_database; datname | size
--------------+---------
template1 | kB
template0 | kB
postgres | kB
b2c_product | GB
spider | MB
crm | MB
log_analysis | MB
( rows)log_analysis=#

3、按顺序查看索引

select indexrelname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_indexes where schemaname='public' order by pg_relation_size(relid) desc;

log_analysis=# select indexrelname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_indexes where schemaname='public' order by pg_relation_size(relid) desc; indexrelname | pg_size_pretty
------------------------------------------------------------+----------------
pk_dim_sight_area | kB
idx_area_dim_sight_area | kB
idx_city_dim_sight_area | kB
idx_country_dim_sight_area | kB
idx_region_dim_sight_area | kB
pk_dim_page_type | bytes
cpc_supplier_sight_daily_pkey | bytes

4、查看所有表的大小

select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname='public' order by pg_relation_size(relid) desc;

log_analysis=# select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname='public' order by pg_relation_size(relid) desc; relname | pg_size_pretty
------------------------------------------------+----------------
dim_sight_area | kB
dim_page_type | bytes
wirelessapi_log_2014_08_08 | bytes
trace_log_2014_08_04 | bytes
dm_mobile | bytes
trace_log_2014_07_14 | bytes