1.什么是数据字典
GaussDB数据字典
GaussDB的数据字典主要包括系统表和系统视图。
系统表是数据库存放结构元数据的地方，它是系统运行控制信息的来源，是数据库系统的核心组成部分。
系统视图提供了查询系统表和访问数据库内部状态的方法。
系统表和系统视图要么只对管理员可见，要么对所有用户可见。
正常情况下用户应通过由DDL、DCL等SQL语句管理维护系统表信息2.GaussDB有哪些有用的数据字典表
常见GaussDB的系统表：
系统表名 简述
PG_DATABASE 存储关于可用数据库的信息
PG_NAMESPACE 存储名字空间，即存储schema相关的信息
PG_ATTRIBUTE 存储关于表字段的信息
PG_INDEXES 存储索引的一部分信息，其他的信息大多数在PG_CLASS中
PG_CLASS 存储数据库对象信息及其之间的关系
PG_TABLESPACE 存储表空间的相关信息
PG_TYPE 存储数据类型的相关信息
PGXC_NODE 存储集群节点信息
PGXC_GROUP 存储节点组信息,无法直接查看。
PGXC_CLASS 存储每张表的复制或分布信息
PGXC_Slice 针对range范围分布和list分布创建的系统表，用来记录分布具体信息，
当前不支持range interval自动扩展分片，不过在系统表中预留3.数据字典表的查询演示
主要包括:
(1)数据库查询
(2)查看所有的schema
(3)查看表分析时间
(4)查询表OID,列名,列顺序,精度注释。
(5)查询表的索引
(6)查看表空间的基本信息
(7)集群节点查看,IP,节点名称,端口
(8)每张表的存储或分布信息
(9)LIST分布和RANGE分布的表查询
--数据库查询
sjzt=> select * from PG_DATABASE;
datname | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | dattablespace | datcompatibility | datacl | datfrozenxid64 | datminmxid | dattimezone
-----------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--------------+---------------+------------------+-------------------------------------------------------------------------------------------------------------------+----------------+------------+-------------
template1 | 10 | 0 | C | C | t | t | -1 | 12732 | 0 | 1663 | MYSQL | {=c/rdsAdmin,rdsAdmin=CTc/rdsAdmin} | 3 | 1 | PRC
template0 | 10 | 0 | C | C | t | f | -1 | 12732 | 0 | 1663 | MYSQL | {=c/rdsAdmin,rdsAdmin=CTc/rdsAdmin} | 3 | 0 | PRC
postgres | 10 | 0 | C | C | f | t | -1 | 12732 | 0 | 1663 | MYSQL | {=Tc/rdsAdmin,rdsAdmin=CTc/rdsAdmin,rdsBackup=c/rdsAdmin,rdsRepl=c/rdsAdmin,root=c/rdsAdmin,rdsMetric=c/rdsAdmin} | 3 | 0 | PRC
mydb2 | 18191 | 0 | C | C | f | t | -1 | 12732 | 0 | 18203 | MYSQL | | 3 | 0 | PRC
mydb3 | 18204 | 7 | zh_CN.UTF-8 | zh_CN.UTF-8 | f | t | 1000 | 12732 | 0 | 18203 | ORA | | 3 | 0 | PRC
sjzt | 16900 | 0 | C | C | f | t | -1 | 12732 | 0 | 1663 | MYSQL | {=Tc/sjzt,sjzt=CTc/sjzt,sjzt=APm/sjzt} | 3 | 0 | PRC
(6 rows)
--查看所有的schema;
sjzt=> select * from PG_NAMESPACE;
nspname | nspowner | nsptimeline | nspacl | in_redistribution | nspblockchain | nspcollation
----------------------+----------+-------------+------------------------------------+-------------------+---------------+--------------
pg_catalog | 10 | 0 | {rdsAdmin=UC/rdsAdmin,=U/rdsAdmin} | n | f |
sqladvisor | 10 | 0 | {rdsAdmin=UC/rdsAdmin,=U/rdsAdmin} | n | f |
dbe_pldebugger | 10 | 0 | {rdsAdmin=UC/rdsAdmin,=U/rdsAdmin} | n | f |
dbe_pldeveloper | 10 | 0 | {rdsAdmin=UC/rdsAdmin,=U/rdsAdmin} | n | f |
dbe_sql_util | 10 | 0 | {rdsAdmin=UC/rdsAdmin,=U/rdsAdmin} | n | f |
information_schema | 10 | 0 | {rdsAdmin=UC/rdsAdmin,=U/rdsAdmin} | n | f |
pkg_util | 10 | 0 | {rdsAdmin=UC/rdsAdmin,=U/rdsAdmin} | n | f |
dbe_scheduler | 10 | 0 | {rdsAdmin=UC/rdsAdmin,=U/rdsAdmin} | n | f |
pkg_service | 10 | 0 | {rdsAdmin=UC/rdsAdmin,=U/rdsAdmin} | n | f |
dbe_raw | 10 | 0 | {rdsAdmin=UC/rdsAdmin,=U/rdsAdmin} | n | f |
dbe_utility | 10 | 0 | {rdsAdmin=UC/rdsAdmin,=U/rdsAdmin} | n | f |
dbe_output | 10 | 0 | {rdsAdmin=UC/rdsAdmin,=U/rdsAdmin} | n | f |
dbe_xml | 10 | 0 | {rdsAdmin=UC/rdsAdmin,=U/rdsAdmin} | n | f |
dbe_xmldom | 10 | 0 | {rdsAdmin=UC/rdsAdmin,=U/rdsAdmin} | n | f |
dbe_xmlparser | 10 | 0 | {rdsAdmin=UC/rdsAdmin,=U/rdsAdmin} | n | f |
dbe_file | 10 | 0 | {rdsAdmin=UC/rdsAdmin,=U/rdsAdmin} | n | f |
dbe_random | 10 | 0 | {rdsAdmin=UC/rdsAdmin,=U/rdsAdmin} | n | f |
dbe_application_info | 10 | 0 | {rdsAdmin=UC/rdsAdmin,=U/rdsAdmin} | n | f |
dbe_sql | 10 | 0 | {rdsAdmin=UC/rdsAdmin,=U/rdsAdmin} | n | f |
dbe_lob | 10 | 0 | {rdsAdmin=UC/rdsAdmin,=U/rdsAdmin} | n | f |
dbe_task | 10 | 0 | {rdsAdmin=UC/rdsAdmin,=U/rdsAdmin} | n | f |
dbe_match | 10 | 0 | {rdsAdmin=UC/rdsAdmin,=U/rdsAdmin} | n | f |
dbe_session | 10 | 0 | {rdsAdmin=UC/rdsAdmin,=U/rdsAdmin} | n | f |
pg_toast | 10 | 0 | | n | f |
cstore | 10 | 0 | | n | f |
dbe_perf | 10 | 0 | | n | f |
snapshot | 10 | 0 | | n | f |
blockchain | 10 | 0 | | n | f |
db4ai | 10 | 0 | | n | f |
sys | 10 | 0 | | n | f |
public | 10 | 0 | {rdsAdmin=UC/rdsAdmin,=U/rdsAdmin} | n | f |
mydb1 | 16900 | 0 | | n | f |
sjzt | 16900 | 0 | {sjzt=UC/sjzt} | n | f |
jack | 18204 | 0 | | n | f |
(34 rows)
--查看表分析时间。
sjzt=> analyze t1;
ANALYZE
sjzt=> select pg_stat_get_last_analyze_time(c.oid),c.relname
from pg_class c
left join pg_namespace n on c.relnamespace = n.oid
where c.relkind='r' and n.nspname='sjzt'
;sjzt-> sjzt-> sjzt-> sjzt->
pg_stat_get_last_analyze_time | relname
-------------------------------+--------------
| warehouse_t1
| warehouse_t2
| warehouse_t3
| emp
| emp2
| dept
| part_tbl1
| part_tbl2
| pt1
2023-09-05 11:43:10.297426+08 | t1
| t2
(11 rows)
--查询表OID,列名,列顺序,精度注释。
SELECT
c.oid table_oid,
nc.nspname schema_name,
c.relname table_name ,
a.attname column_name ,
format_type(a.atttypid, a.atttypmod),
a.attnum column_id ,
information_schema._pg_char_max_length(a.atttypid, a.atttypmod) data_length ,
information_schema._pg_numeric_precision(a.atttypid, a.atttypmod) numeric_precision ,
information_schema._pg_numeric_scale(a.atttypid, a.atttypmod) numeric_scale ,
col_description(c.oid, a.attnum) column_comment
from pg_attribute a
JOIN (pg_class c JOIN pg_namespace nc ON c.relnamespace = nc.oid) ON a.attrelid = c.oid
WHERE NOT pg_is_other_temp_schema(nc.oid)
AND a.attnum > 0
AND NOT a.attisdropped
AND (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char", 'f'::"char"]))
AND (pg_has_role(c.relowner, 'USAGE'::text) OR has_column_privilege(c.oid, a.attnum,'SELECT,INSERT,UPDATE,REFERENCES'::text))
and nc.nspname = 'sjzt' --user_name
and c.relname = 't1' --table
order by column_id;
table_oid | schema_name | table_name | column_name | format_type | column_id | data_length | numeric_precision | numeric_scale | column_comment
-----------+-------------+------------+------------------+------------------+-----------+-------------+-------------------+---------------+----------------
26469 | sjzt | t1 | oid | oid | 1 | | | |
26469 | sjzt | t1 | relname | name | 2 | | | |
26469 | sjzt | t1 | relnamespace | oid | 3 | | | |
26469 | sjzt | t1 | reltype | oid | 4 | | | |
26469 | sjzt | t1 | reloftype | oid | 5 | | | |
26469 | sjzt | t1 | relowner | oid | 6 | | | |
26469 | sjzt | t1 | relam | oid | 7 | | | |
26469 | sjzt | t1 | relfilenode | oid | 8 | | | |
26469 | sjzt | t1 | reltablespace | oid | 9 | | | |
26469 | sjzt | t1 | relpages | double precision | 10 | | 53 | |
26469 | sjzt | t1 | reltuples | double precision | 11 | | 53 | |
26469 | sjzt | t1 | relallvisible | integer | 12 | | 32 | 0 |
26469 | sjzt | t1 | reltoastrelid | oid | 13 | | | |
26469 | sjzt | t1 | reltoastidxid | oid | 14 | | | |
26469 | sjzt | t1 | reldeltarelid | oid | 15 | | | |
26469 | sjzt | t1 | reldeltaidx | oid | 16 | | | |
26469 | sjzt | t1 | relcudescrelid | oid | 17 | | | |
26469 | sjzt | t1 | relcudescidx | oid | 18 | | | |
26469 | sjzt | t1 | relhasindex | boolean | 19 | | | |
26469 | sjzt | t1 | relisshared | boolean | 20 | | | |
26469 | sjzt | t1 | relpersistence | "char" | 21 | | | |
26469 | sjzt | t1 | relkind | "char" | 22 | | | |
26469 | sjzt | t1 | relnatts | smallint | 23 | | 16 | 0 |
26469 | sjzt | t1 | relchecks | smallint | 24 | | 16 | 0 |
26469 | sjzt | t1 | relhasoids | boolean | 25 | | | |
26469 | sjzt | t1 | relhaspkey | boolean | 26 | | | |
26469 | sjzt | t1 | relhasrules | boolean | 27 | | | |
26469 | sjzt | t1 | relhastriggers | boolean | 28 | | | |
26469 | sjzt | t1 | relhassubclass | boolean | 29 | | | |
26469 | sjzt | t1 | relcmprs | tinyint | 30 | | 8 | 0 |
26469 | sjzt | t1 | relhasclusterkey | boolean | 31 | | | |
26469 | sjzt | t1 | relrowmovement | boolean | 32 | | | |
26469 | sjzt | t1 | parttype | "char" | 33 | | | |
26469 | sjzt | t1 | relfrozenxid | xid32 | 34 | | | |
26469 | sjzt | t1 | relacl | aclitem[] | 35 | | | |
26469 | sjzt | t1 | reloptions | text[] | 36 | | | |
26469 | sjzt | t1 | relreplident | "char" | 37 | | | |
26469 | sjzt | t1 | relfrozenxid64 | xid | 38 | | | |
26469 | sjzt | t1 | relbucket | oid | 39 | | | |
26469 | sjzt | t1 | relbucketkey | int2vector | 40 | | | |
26469 | sjzt | t1 | relminmxid | xid | 41 | | | |
(41 rows)
--查询表的索引。
sjzt=> SELECT * FROM PG_INDEXES WHERE TABLENAME='t1';
schemaname | tablename | indexname | tablespace | indexdef
------------+-----------+------------------+------------+---------------------------------------------------------------------------------------------------------------------
sjzt | t1 | t1_lttbs_idx | | CREATE INDEX t1_lttbs_idx ON t1 USING btree (reltablespace) TABLESPACE pg_default WHERE (reltablespace < (20)::oid)
sjzt | t1 | t1_upname_idx | | CREATE INDEX t1_upname_idx ON t1 USING btree (upper((relname)::text)) TABLESPACE pg_default
sjzt | t1 | t1_owner_tbs_idx | | CREATE INDEX t1_owner_tbs_idx ON t1 USING btree (relowner, reltablespace) TABLESPACE pg_default
sjzt | t1 | t1_fn_idx2 | tbs3 | CREATE UNIQUE INDEX t1_fn_idx2 ON t1 USING btree (relfilenode, relallvisible) TABLESPACE tbs3
(4 rows)
--查看表空间的基本信息
sjzt=> select * from PG_TABLESPACE;
spcname | spcowner | spcacl | spcoptions | spcmaxsize | relative
------------+----------+---------------------------+------------+------------+----------
pg_default | 10 | | | | f
pg_global | 10 | | | | f
tbs3 | 18204 | {jack=C/jack,sjzt=C/jack} | | | f
tbs2 | 16420 | {root=C/root,sjzt=C/root} | | | t
(4 rows)
--集群节点查看,IP,节点名称,端口
sjzt=> select * from PGXC_NODE;
node_name | node_type | node_port | node_host | node_port1 | node_host1 | hostis_primary | nodeis_primary | nodeis_preferred | node_id | sctp_port | control_port | sctp_port1 | control_port1 | nodeis_central | nodeis_active
-------------------+-----------+-----------+--------------+------------+--------------+----------------+----------------+------------------+-------------+-----------+--------------+------------+---------------+----------------+---------------
dn_6001_6002_6003 | D | 33100 | 192.168.2.52 | 33100 | 192.168.2.52 | t | f | f | -1072999043 | 33102 | 33103 | 0 | 0 | f | t
cn_5001 | C | 8000 | 192.168.2.52 | 8000 | 192.168.3.52 | t | f | f | 1120683504 | 8002 | 8003 | 0 | 0 | t | t
cn_5002 | C | 8000 | 192.168.2.53 | 8000 | 192.168.3.53 | t | f | f | -1736975100 | 8002 | 8003 | 0 | 0 | f | t
cn_5003 | C | 8000 | 192.168.2.54 | 8000 | 192.168.3.54 | t | f | f | -125853378 | 8002 | 8003 | 0 | 0 | f | t
dn_6001_6002_6003 | S | 33100 | 192.168.2.54 | 33100 | 192.168.2.54 | t | f | f | -1072999041 | 33102 | 33103 | 0 | 0 | f | t
dn_6001_6002_6003 | S | 33100 | 192.168.2.53 | 33100 | 192.168.2.53 | t | f | f | -1072999042 | 33102 | 33103 | 0 | 0 | f | t
dn_6004_6005_6006 | S | 33120 | 192.168.2.54 | 33120 | 192.168.2.54 | t | f | f | -564789566 | 33122 | 33123 | 0 | 0 | f | t
dn_6004_6005_6006 | S | 33120 | 192.168.2.53 | 33120 | 192.168.2.53 | t | f | f | -564789568 | 33122 | 33123 | 0 | 0 | f | t
dn_6004_6005_6006 | D | 33120 | 192.168.2.52 | 33120 | 192.168.2.52 | t | f | f | -564789567 | 33122 | 33123 | 0 | 0 | f | t
dn_6007_6008_6009 | S | 33140 | 192.168.2.54 | 33140 | 192.168.2.54 | t | f | f | 1532339558 | 33142 | 33143 | 0 | 0 | f | t
dn_6007_6008_6009 | S | 33140 | 192.168.2.53 | 33140 | 192.168.2.53 | t | f | f | 1532339559 | 33142 | 33143 | 0 | 0 | f | t
dn_6007_6008_6009 | D | 33140 | 192.168.2.52 | 33140 | 192.168.2.52 | t | f | f | 1532339560 | 33142 | 33143 | 0 | 0 | f | t
(12 rows)
--每张表的存储或分布信息
sjzt=> select * from PGXC_CLASS;
pcrelid | pclocatortype | pchashalgorithm | pchashbuckets | pgroup | redistributed | redis_order | pcattnum | nodeoids | options
---------+---------------+-----------------+---------------+----------------+---------------+-------------+----------+-------------------+---------
17214 | H | 1 | 4096 | group_version1 | n | 1024 | 1 | 16385 16389 16394 |
18239 | H | 1 | 4096 | group_version1 | n | 1024 | 3 | 16385 16389 16394 |
18246 | H | 1 | 4096 | group_version1 | n | 1024 | 1 | 16385 16389 16394 |
18252 | H | 1 | 4096 | group_version1 | n | 1024 | 1 | 16385 16389 16394 |
18254 | H | 1 | 4096 | group_version1 | n | 1024 | 1 | 16385 16389 16394 |
18288 | H | 1 | 4096 | group_version1 | n | 1024 | 4 | 16385 16389 16394 |
18294 | H | 1 | 4096 | group_version1 | n | 1024 | 1 | 16385 16389 16394 |
18300 | H | 1 | 4096 | group_version1 | n | 1024 | 1 | 16385 16389 16394 |
18312 | H | 1 | 4096 | group_version1 | n | 1024 | 1 | 16385 16389 16394 |
26391 | H | 1 | 4096 | group_version1 | n | 1024 | 1 | 16385 16389 16394 |
26398 | H | 1 | 4096 | group_version1 | n | 1024 | 1 | 16385 16389 16394 |
26419 | H | 1 | 4096 | group_version1 | n | 1024 | 1 | 16385 16389 16394 |
26469 | H | 1 | 4096 | group_version1 | n | 1024 | 12 | 16385 16389 16394 |
26521 | H | 1 | 4096 | group_version1 | n | 1024 | 1 | 16385 16389 16394 |
(14 rows)
--LIST分布和RANGE分布的表查询
sjzt=> select * from PGXC_Slice;
relname | type | strategy | relid | referenceoid | sindex | interval | transitboundary | transitno | nodeoid | boundaries | specified | sliceorder
---------+------+----------+-------+--------------+--------+----------+-----------------+-----------+---------+------------+-----------+------------
(0 rows)4.总结
由于暂时没不清楚GaussDB的RANGE,LIST分布的分布规则，这块的演示暂时没有。
系统字典表对于我们掌握整个数据库的信息有非常重要的作用。熟记常用的数据字典表，能够帮助提升运维效率。
803
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
