GaussDB查看数据库、表空间、表、schema大小以及常用的数据库元命令

查看表空间的大小

openGauss=# select pg_size_pretty(pg_tablespace_size('pg_default')); pg_size_pretty ---------------- 102 MB(1 row)openGauss=# select pg_size_pretty(pg_tablespace_size('hr_data'));pg_size_pretty ---------------- 1286 MB(1 row)

查看所有数据库的大小

select pg_size_pretty(sum(pg_database_size(oid))) from pg_database;

查看每个数据库的大小

openGauss=# select datname,pg_size_pretty(pg_database_size(oid)) from pg_database;datname| pg_size_pretty -----------+---------------- template1 | 14 MB test_gbk| 14 MB template0 | 14 MB test1 | 14 MB postgres| 31 MB mm| 14 MB db_hr | 1286 MB(7 rows)

元命令\l+可以快速查看

openGauss=# \l+ List of databases Name|Owner | Encoding| Collate | Ctype | Access privileges |Size | Tablespace |Description -----------+----------+-----------+---------+-------+-----------------------+---------+------------+-------------------------------------------- db_hr | lily | GBK | C | C | | 1286 MB | hr_data|mm| lily | GBK | C | C | | 14 MB | pg_default |postgres| rdsAdmin | SQL_ASCII | C | C | | 31 MB | pg_default | default administrative connection database template0 | rdsAdmin | SQL_ASCII | C | C | =c/rdsAdmin+| 14 MB | pg_default | default template for new databases || | | | rdsAdmin=CTc/rdsAdmin | ||template1 | rdsAdmin | SQL_ASCII | C | C | =c/rdsAdmin+| 14 MB | pg_default | unmodifiable empty database || | | | rdsAdmin=CTc/rdsAdmin | ||test1 | rdsAdmin | GBK | C | C | =Tc/rdsAdmin +| 14 MB | pg_default ||| | | | rdsAdmin=CTc/rdsAdmin+| |||| | | | lily=c/rdsAdmin | ||test_gbk| test_gbk | GBK | C | C | | 14 MB | pg_default | (7 rows)

查看指定数据库的大小

openGauss=# select pg_size_pretty(pg_database_size('db_hr'));pg_size_pretty ---------------- 1286 MB(1 row)

查看每个schema的大小

select schemaname,pg_size_pretty(cast(sum(pg_relation_size(schemaname||'.'||tablename))as bigint)) from pg_tables t inner join pg_namespace d on t.schemaname=d.nspname group by schemaname;

SELECT n.nspname as "Schema",pg_size_pretty(sum(pg_relation_size(n.nspname||'.'||c.relname))) as "TotalSize"FROM pg_catalog.pg_class cinner JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespacewhere c.relkind in('r','i')and n.nspname not in ('pg_catalog','pg_toast','cstore','public','dbms_job','sys','dbms_output','dbms_lob','dbms_random','utl_raw','information_schema')group by n.nspname; 

查看所有表的大小，并以表大小排序

select tableowner,schemaname,tablename,pg_table_size(schemaname||'.'||tablename) as table_size from pg_tables order by table_size desc;

查看指定schema下所有表的大小

select schemaname || '.' || tablename tname, pg_size_pretty(pg_total_relation_size('"' || schemaname || '"."' || tablename || '"')) from pg_tables where schemaname = '模式名' order by pg_total_relation_size('"' || schemaname || '"."' || tablename || '"')desc ;

查看单张表的大小

select pg_size_pretty(pg_table_size('表名'));select tableowner,schemaname,tablename,pg_size_pretty(pg_table_size(schemaname||'.'||tablename)) as table_size from pg_tables where tablename='表名';

查看表的分区的大小

select pg_size_pretty(pg_partition_size('表名',' 分区名'));

基本操作命令

\l列出所有数据库\d tablename 列出指定表的所有字段\d+ tablename 查看指定表的基本情况\d 列出当前数据库下的表\c database_name 切换数据库\dn 展示当前数据库下所有schema信息\du 列出角色\dv 列表视图\di 列表索引\q 退出登录gsql -d 数据库名字 -p 端口 -U 用户名字 -W '密码'cm_ctl query -Cvipd 查询集群的状态SHOW search_path; 显示当前使用的schemaSET search_path TO myschema; 切换当前schema