1、查看数据库大小：
select pg_database_size(‘log_analysis‘);
***(Single step mode: verify command)******************************************* select pg_database_size(‘log_analysis‘); ***(press return to proceed or enter x and return to cancel)******************** pg_database_size ------------------ 23799992 (1 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; ***(Single step mode: verify command)******************************************* select pg_database.datname, pg_size_pretty (pg_database_size(pg_database.datname)) AS size from pg_database; ***(press return to proceed or enter x and return to cancel)******************** datname | size --------------+--------- template1 | 6594 kB template0 | 6457 kB postgres | 6586 kB b2c_product | 27 GB spider | 11 MB crm | 54 MB log_analysis | 23 MB (7 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; ***(Single step mode: verify command)******************************************* select indexrelname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_indexes where schemaname=‘public‘ order by pg_relation_size(relid) desc; ***(press return to proceed or enter x and return to cancel)******************** indexrelname | pg_size_pretty ------------------------------------------------------------+---------------- pk_dim_sight_area | 184 kB idx_area_dim_sight_area | 184 kB idx_city_dim_sight_area | 184 kB idx_country_dim_sight_area | 184 kB idx_region_dim_sight_area | 184 kB pk_dim_page_type | 8192 bytes cpc_supplier_sight_daily_pkey | 0 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; ***(Single step mode: verify command)******************************************* select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname=‘public‘ order by pg_relation_size(relid) desc; ***(press return to proceed or enter x and return to cancel)******************** relname | pg_size_pretty ------------------------------------------------+---------------- dim_sight_area | 184 kB dim_page_type | 8192 bytes wirelessapi_log_2014_08_08 | 0 bytes trace_log_2014_08_04 | 0 bytes dm_mobile | 0 bytes trace_log_2014_07_14 | 0 bytes