查看表空间的大小
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;
文章来源:https://www.toymoban.com/news/detail-508767.html
查看指定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 ;
文章来源地址https://www.toymoban.com/news/detail-508767.html
查看单张表的大小
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
到了这里,关于GaussDB查看数据库、表空间、表、schema大小以及常用的数据库元命令的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!