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 c
inner JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
where 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; 显示当前使用的schema
                SET search_path TO myschema; 切换当前schema