1.用户管理  --用户创建 --创建用户: --创建用户并设置密码 gaussdb=>  create user user1 identified by 'Gauss_123'; CREATE ROLE --创建用户并授予权限:sysadmin  gaussdb=> create user user2 with sysadmin password 'Gauss_123';  CREATE ROLE --创建用户时指定用的寿命 gaussdb=> create user user3 with VALID BEGIN '2023-09-04 23:59:59' VALID UNTIL '2023-09-10 00:00:00' identified by 'Gauss_123' CONNECTION LIMIT 100; CREATE ROLE --修改用户具有sysadmin权限。 gaussdb=> alter user user1 with sysadmin; ALTER ROLE --重命名用户 gaussdb=> alter user user2 rename to user4; ALTER ROLE gaussdb=> drop user user4; DROP ROLE   --删除有数据的用户; --无法删除,有四个对象在sjzt数据库里。 gaussdb=> drop user user1 cascade; ERROR:  role "user1" cannot be dropped because some objects depend on it DETAIL:  4 objects in database sjzt   --解决方法。 revoke all on database sjzt from user1; DROP user user1; --如果数据库是用户user1创建的. alter database sjzt owner to sjzt; DROP user user1;  2.表空间管理  --表空间管理 --创建表空间： --创建自定义表空间 gaussdb=> create tablespace tbs2 relative location 'tablespace/tbs2' maxsize '10M';   CREATE TABLESPACE gaussdb=> select * from pg_tablespace_location((select oid from pg_tablespace where spcname='tbs2'));  pg_tablespace_location  ------------------------  tablespace/tbs2 (1 row)   gaussdb=> create user jack with password 'Gauss_123'; CREATE ROLE   --只在第一个节点上手工创建目录。 cd /data/cluster/data mkdir my_tbs chown -R omm:ommm my_tbs chmod -R 775 my_tbs   gaussdb=> create tablespace tbs3 owner jack location '/data/cluster/data/my_tbs/tbs3'; CREATE TABLESPACE gaussdb=> select oid,* from pg_tablespace;   oid  |  spcname   | spcowner | spcacl | spcoptions | spcmaxsize | relative  -------+------------+----------+--------+------------+------------+----------   1663 | pg_default |       10 |        |            |            | f   1664 | pg_global  |       10 |        |            |            | f  18203 | tbs2       |    16420 |        |            | 10240 K    | t  18208 | tbs3       |    18204 |        |            |            | f (4 rows)   --可以看到,虽然只在第一个目录创建了表空间的目录,但是创建成功后, --集群所有自定义表空间的信息都复制到了所有节点。 --所以创建自定义表空间,只需要在一个节点上创建目录即可,不需要在所有的节点都创建。 [omm@gaussdb01 my_tbs]$ cd tbs3/ [omm@gaussdb01 tbs3]$ ll total 16 drwx------ 3 omm omm 4096 Sep  4 18:57 PG_9.2_201611171_cn_5001 drwx------ 3 omm omm 4096 Sep  4 18:57 PG_9.2_201611171_dn_6001_6002_6003 drwx------ 3 omm omm 4096 Sep  4 18:57 PG_9.2_201611171_dn_6004_6005_6006 drwx------ 3 omm omm 4096 Sep  4 18:57 PG_9.2_201611171_dn_6007_6008_6009 [omm@gaussdb02 my_tbs]$ cd tbs3/ [omm@gaussdb02 tbs3]$ ll total 16 drwx------ 3 omm omm 4096 Sep  4 18:56 PG_9.2_201611171_cn_5002 drwx------ 3 omm omm 4096 Sep  4 18:56 PG_9.2_201611171_dn_6001_6002_6003 drwx------ 3 omm omm 4096 Sep  4 18:56 PG_9.2_201611171_dn_6004_6005_6006 drwx------ 3 omm omm 4096 Sep  4 18:56 PG_9.2_201611171_dn_6007_6008_6009 [omm@gaussdb02 tbs3]$  [omm@gaussdb03 my_tbs]$ cd tbs3/ [omm@gaussdb03 tbs3]$ ll total 16 drwx------ 3 omm omm 4096 Sep  4 18:57 PG_9.2_201611171_cn_5003 drwx------ 3 omm omm 4096 Sep  4 18:57 PG_9.2_201611171_dn_6001_6002_6003 drwx------ 3 omm omm 4096 Sep  4 18:57 PG_9.2_201611171_dn_6004_6005_6006 drwx------ 3 omm omm 4096 Sep  4 18:57 PG_9.2_201611171_dn_6007_6008_6009 [omm@gaussdb03 tbs3]$      ---查看表空间 gaussdb=> select oid,* from pg_tablespace;    oid  |  spcname   | spcowner | spcacl | spcoptions | spcmaxsize | relative  -------+------------+----------+--------+------------+------------+----------   1663 | pg_default |       10 |        |            |            | f   1664 | pg_global  |       10 |        |            |            | f  18203 | tbs2       |    16420 |        |            | 10240 K    | t  18208 | tbs3       |    18204 |        |            |            | f (4 rows)   gaussdb=> alter tablespace tbs3 rename to tbs4;  --重命名  ALTER TABLESPACE gaussdb=> alter tablespace tbs4 owner to jack;   --修改属主 ALTER TABLESPACE gaussdb=> alter tablespace tbs4 resize maxsize unlimited; --修改大小 ALTER TABLESPACE gaussdb=>  alter tablespace tbs4 reset (random_page_cost); --修改访问页的成本值。 ALTER TABLESPACE gaussdb=> drop tablespace tbs4;  --删除表空间。 DROP TABLESPACE  3.数据库管理  --数据库管理 --创建数据库： gaussdb=> create database mydb2; CREATE DATABASE gaussdb=> create database mydb3 with owner=jack encoding='UTF-8' LC_COLLATE='zh_CN.UTF-8' LC_CTYPE='zh_CN.UTF-8' DBCOMPATIBILITY='ORA' TABLESPACE=tbs2 CONNECTION LIMIT=1000; CREATE DATABASE --指定了数据库mydb3的拥有者(owner)、编码(encoding)、字符集(LC_COLLATE)、字符分类(LC_CTYPE)、兼容模式(DBCOMPATIBILITY)、 --默认表空间(TABLESPACE)、并发连接限制(CONNECTION LIMIT) --修改数据库属性： gaussdb=> alter database mydb3 rename to mydb4;  --修改数据库的名字 ALTER DATABASE gaussdb=> alter database mydb2 owner to user1;  --修改数据库的属主 ALTER DATABASE gaussdb=> alter database mydb2 set tablespace tbs2;  --设置数据库的表空间。 ALTER DATABASE gaussdb=> drop database mydb4; --删除数据库。 DROP DATABASE --查看数据库。 gaussdb=> \l                              List of databases    Name    |  Owner   | Encoding  | Collate | Ctype |   Access privileges    -----------+----------+-----------+---------+-------+-----------------------  mydb2     | user1    | SQL_ASCII | C       | C     |   postgres  | rdsAdmin | SQL_ASCII | C       | C     | =Tc/rdsAdmin         +            |          |           |         |       | rdsAdmin=CTc/rdsAdmin+            |          |           |         |       | rdsBackup=c/rdsAdmin +            |          |           |         |       | rdsRepl=c/rdsAdmin   +            |          |           |         |       | root=c/rdsAdmin      +            |          |           |         |       | rdsMetric=c/rdsAdmin  sjzt      | sjzt     | SQL_ASCII | C       | C     | =Tc/sjzt             +            |          |           |         |       | sjzt=CTc/sjzt        +            |          |           |         |       | sjzt=APm/sjzt  template0 | rdsAdmin | SQL_ASCII | C       | C     | =c/rdsAdmin          +            |          |           |         |       | rdsAdmin=CTc/rdsAdmin  template1 | rdsAdmin | SQL_ASCII | C       | C     | =c/rdsAdmin          +            |          |           |         |       | rdsAdmin=CTc/rdsAdmin (5 rows)   --系统表查看数据库。 gaussdb=> 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  sjzt      |  16900 |        0 | C          | C        | f             | t            |           -1 |         12732 | 0            |          1663 | MYSQL            | {=Tc/sjzt,sjzt=CTc/sjzt,sjzt=APm/sjzt}|              3 |          0 | PRC (5 rows) gaussdb=>   4.总结  GaussDB中可以设置用户的寿命，这是它的一个特色。表空间可以自定义，并且只需要在一个节点创建目录授权即可。数据库的创建，支持：ORA,MYSQL,TD,PG四种模式。 作者：薛双奇

 1.创建行存表  sjzt=> create table emp(id int,ename varchar(20),sal number,empno int,deptno int) distribute by hash(empno); CREATE TABLE insert into emp values(1,'xsq1',2000,6379,10); sjzt=> create table dept(deptno int,name varchar(20)); CREATE TABLE sjzt=> create table emp1 as select * from emp where sal<2000; INSERT 0 0 sjzt=> create table emp2 as table emp; INSERT 0 0 2.创建表并使用特定表空间  -- 表不存在时才创建，使得当该表存在时该建表语句不会报错  -- 事务结束时检查字段是否有重复  -- 缺省值为'GA'  --需要给用户授予表空间的CREATE权限,才可以在表空间中创建对象。 gaussdb=> grant create on tablespace tbs3 to sjzt; GRANT   sjzt=> CREATE TABLE IF NOT EXISTS warehouse_t1  ( W_WAREHOUSE_SK INTEGER NOT NULL, W_WAREHOUSE_ID CHAR(16) NOT NULL, W_WAREHOUSE_NAME VARCHAR(20) UNIQUE DEFERRABLE, W_SUITE_NUMBER CHAR(10), W_STATE CHAR(2) DEFAULT 'GA',  W_GMT_OFFSET DECIMAL(5,2) ) TABLESPACE tbs3; NOTICE:  CREATE TABLE / UNIQUE will create implicit index "warehouse_t1_w_warehouse_name_key" for table "warehouse_t1" CREATE TABLE -- 创建非日志表，即不写入预写日志 -- 主键约束,-- 唯一键约束,-- 检查列约束 sjzt=>  CREATE UNLOGGED TABLE warehouse_t2   ( W_WAREHOUSE_SK INTEGER PRIMARY KEY,  W_WAREHOUSE_ID CHAR(16) NOT NULL, W_WAREHOUSE_NAME VARCHAR(20) UNIQUE,  W_GMT_OFFSET DECIMAL(5,2) , CONSTRAINT W_CONSTR_KEY2 CHECK(W_WAREHOUSE_SK > 0 AND W_WAREHOUSE_NAME IS NOT NULL)  ) WITH(fillfactor=70);   NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "warehouse_t2_pkey" for table "warehouse_t2" NOTICE:  CREATE TABLE / UNIQUE will create implicit global secondary index "warehouse_t2_w_warehouse_name_key_g" for table "warehouse_t2" CREATE TABLE  3.列存表创建  --列存表创建：  -- 局部聚簇存储  -- 带有压缩特性的列存储表(列存储表不支持约束) sjzt=> CREATE TABLE warehouse_t3 ( W_WAREHOUSE_SK INTEGER NOT NULL, W_WAREHOUSE_ID CHAR(16) NOT NULL, W_WAREHOUSE_NAME VARCHAR(20), W_GMT_OFFSET DECIMAL(5,2), PARTIAL CLUSTER KEY(W_WAREHOUSE_SK, W_WAREHOUSE_ID) ) WITH (ORIENTATION = COLUMN, COMPRESSION=HIGH);   NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'w_warehouse_sk' as the distribution column by default. HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column. CREATE TABLE 4.修改表的属性  --修改表属性: --WHERE条件创建表。 sjzt=> create table emp1 as select * from emp where sal<2000; NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'id' as the distribution column by default. HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column. INSERT 0 0 --AS 创建表。 sjzt=> create table emp2 as table emp; NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'id' as the distribution column by default. HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column. INSERT 0 1 --修改列属性。 sjzt=> alter table emp1 modify sal number(10,2); ALTER TABLE --重命名列名称。 sjzt=> alter table emp1 rename column ename to name; ALTER TABLE  --无法直接创建主键,必须保护分布列。emp1的分布列是ID; sjzt=> alter table emp1 add primary key (empno); ERROR:  Cannot create index whose evaluation cannot be enforced to remote nodes sjzt=> \d+ emp1                                  Table "sjzt.emp1"  Column |         Type          | Modifiers | Storage  | Stats target | Description  --------+-----------------------+-----------+----------+--------------+-------------  id     | integer               |           | plain    |              |   name   | character varying(20) |           | extended |              |   sal    | numeric(10,2)         |           | main     |              |   empno  | integer               |           | plain    |              |   deptno | integer               |           | plain    |              |  Has OIDs: no Distribute By: HASH(id) Location Nodes: ALL DATANODES Options: orientation=row, compression=no   --添加主键,需要增加分布列。 sjzt=> alter table emp1 add primary key (empno,id); NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "emp1_pkey" for table "emp1" ALTER TABLE --添加检查约束。 sjzt=>  alter table emp1 add constraint chk_dept check (deptno is not null); ALTER TABLE   --GaussDB目前不支持外键约束。 sjzt=>  alter table emp1 add constraint fk_dept foreign key (deptno) references dept(deptno); ERROR:  FOREIGN KEY ... REFERENCES constraint is not yet supported. --设置sal列为非空约束。 sjzt=> alter table emp1 modify sal constraint chk_sal not null;  ALTER TABLE --重命名约束名称 sjzt=> alter table emp1 rename constraint chk_dept to chk_deptno; ALTER TABLE --重置schema; sjzt=> alter table emp1 set schema mydb1; ALTER TABLE --表重命名 sjzt=> alter table mydb1.emp1 rename to emp2; ALTER TABLE sjzt=> \d mydb1.emp2              Table "mydb1.emp2"  Column |         Type          | Modifiers  --------+-----------------------+-----------  id     | integer               | not null  name   | character varying(20) |   sal    | numeric(10,2)         | not null  empno  | integer               | not null  deptno | integer               |  Indexes:     "emp1_pkey" PRIMARY KEY, btree (empno, id) TABLESPACE pg_default Check constraints:     "chk_deptno" CHECK (deptno IS NOT NULL)  5.总结  （1）使用表空间需要有对表空间的CREATE权限。  （2）GaussDB MYSQL兼容模式不支持外键。  （3）GaussDB 支持列存和行存表。 作者：薛双奇

 1.范围分区表相关技术包括如下  分区表: (1)按照数字范围分区 (2)包含START,END关键字的按照数字范围分区 (3)分区表的分区指定特定的表空间。 (4)删除包含某个数字值的分区。 (5)添加分区 (6)重命名分区 (7)移动分区到某个指定表空间 (8)在某个值处对某个分区进行拆分 (9)将两个分区合并成一个分区。 (10)分区交换技术 2.范围分区表的详细演练  --范围分区表。 CREATE TABLE part_tbl1 (a int, b int) PARTITION BY RANGE(a) ( PARTITION part1 VALUES LESS THAN (10), PARTITION part2 VALUES LESS THAN (100), PARTITION part3 VALUES LESS THAN (MAXVALUE) ); NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'a' as the distribution column by default. HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column. CREATE TABLE sjzt=>    --包含START 和END 关键字的分区表。 sjzt=> CREATE TABLE part_tbl2 (a int, b int) PARTITION BY RANGE(a) ( partition part1 START(1) END(100) EVERY(50), partition part2 END(200), partition part3 START(200) END(300), partition part4 start(300) ); NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'a' as the distribution column by default. HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column. CREATE TABLE   --创建范围分区表： -- 按照属性范围划分分区,指定分区。 CREATE TABLE pt1 ( id INTEGER, name Varchar(20), score DECIMAL(5,2)  ) PARTITION BY RANGE(score) ( PARTITION P1 VALUES LESS THAN(60) tablespace tbs3,  PARTITION P2 VALUES LESS THAN(85) tablespace tbs3, PARTITION P3 VALUES LESS THAN(MAXVALUE) tablespace tbs3 ); NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'id' as the distribution column by default. HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column. CREATE TABLE sjzt=>    --插入数据： sjzt=> insert into pt1 values(1,'Jerry',99); INSERT 0 1 sjzt=> insert into pt1 values(2,'Tom',80); INSERT 0 1 sjzt=> insert into pt1 values(3,'Jack',55); INSERT 0 1     --查询分区表： sjzt=> select * from pt1 partition (p3);  id | name  | score  ----+-------+-------   1 | Jerry | 99.00 (1 row)   --查询分区表的分区  sjzt=> select p.relname,c.relname from pg_partition p,pg_class c where p.parentid=c.oid;   relname  |  relname   -----------+-----------  part3     | part_tbl1  part2     | part_tbl1  part1     | part_tbl1  part_tbl1 | part_tbl1  part4     | part_tbl2  part3     | part_tbl2  part2     | part_tbl2  part1_2   | part_tbl2  part1_1   | part_tbl2  part1_0   | part_tbl2  part_tbl2 | part_tbl2  p3        | pt1  p2        | pt1  p1        | pt1  pt1       | pt1 (15 rows)   -- 删除分区： --删除90所在的分区,看到分区P3已经没有了。 sjzt=> alter table pt1 drop partition for(90); ALTER TABLE sjzt=> select p.relname,c.relname from pg_partition p,pg_class c where p.parentid=c.oid;   relname  |  relname   -----------+-----------  part3     | part_tbl1  part2     | part_tbl1  part1     | part_tbl1  part_tbl1 | part_tbl1  part4     | part_tbl2  part3     | part_tbl2  part2     | part_tbl2  part1_2   | part_tbl2  part1_1   | part_tbl2  part1_0   | part_tbl2  part_tbl2 | part_tbl2  p2        | pt1  p1        | pt1  pt1       | pt1 (14 rows)   -- 增加分区： sjzt=> alter table pt1 add partition p3 values less than (95); ALTER TABLE sjzt=> alter table pt1 add partition p4 values less than (MAXVALUE); ALTER TABLE -- sjzt=> select p.relname,c.relname from pg_partition p,pg_class c where p.parentid=c.oid;   relname  |  relname   -----------+-----------  part3     | part_tbl1  part2     | part_tbl1  part1     | part_tbl1  part_tbl1 | part_tbl1  part4     | part_tbl2  part3     | part_tbl2  part2     | part_tbl2  part1_2   | part_tbl2  part1_1   | part_tbl2  part1_0   | part_tbl2  part_tbl2 | part_tbl2  pt1       | pt1  p4        | pt1  p3        | pt1  p2        | pt1  p1        | pt1 (16 rows)   --分区表修改： --重命名分区 sjzt=> alter table pt1 rename partition p4 to pmax; ALTER TABLE --移动对象到别的表空间。 sjzt=> alter table pt1 move partition pmax tablespace tbs2; ALTER TABLE --在90处分割分区。 sjzt=> alter table pt1 split partition p3 at (90) into (partition p4,partition p5); ALTER TABLE --分离完成后没有P3了。 sjzt=> select p.relname,c.relname from pg_partition p,pg_class c where p.parentid=c.oid;   relname  |  relname   -----------+-----------  part3     | part_tbl1  part2     | part_tbl1  part1     | part_tbl1  part_tbl1 | part_tbl1  part4     | part_tbl2  part3     | part_tbl2  part2     | part_tbl2  part1_2   | part_tbl2  part1_1   | part_tbl2  part1_0   | part_tbl2  part_tbl2 | part_tbl2  p4        | pt1  pt1       | pt1  pmax      | pt1  p5        | pt1  p2        | pt1  p1        | pt1 (17 rows) --合并完成后没有P4,P5了,最后剩下P3; sjzt=> alter table pt1 merge partitions p4,p5 into partition p3; ALTER TABLE sjzt=> select p.relname,c.relname from pg_partition p,pg_class c where p.parentid=c.oid;   relname  |  relname   -----------+-----------  part3     | part_tbl1  part2     | part_tbl1  part1     | part_tbl1  part_tbl1 | part_tbl1  part4     | part_tbl2  part3     | part_tbl2  part2     | part_tbl2  part1_2   | part_tbl2  part1_1   | part_tbl2  part1_0   | part_tbl2  part_tbl2 | part_tbl2  p3        | pt1  pt1       | pt1  pmax      | pt1  p2        | pt1  p1        | pt1 (16 rows)     CREATE TABLE T1 ( id INTEGER, name Varchar(20), score DECIMAL(5,2)  ); sjzt=> insert into t1 values(3,'Jack',85); INSERT 0 1 sjzt=> select * from t1;  id | name | score  ----+------+-------   3 | Jack | 85.00 (1 row)       sjzt=> select * from pt1 partition(p3);  id | name | score  ----+------+------- (0 rows)     -- 普通表与分区表数据交换 --分区交换技术是Oracle特有的,现在GaussDB也实现了。 sjzt=> alter table pt1 exchange partition (p3) with table t1;  ALTER TABLE sjzt=> select * from pt1 partition(p3);  id | name | score  ----+------+-------   3 | Jack | 85.00 (1 row)   -- 查询分区表属性信息   SELECT t1.oid, t1.relname, partstrategy, boundaries, t1.reltablespace, t1.parentid  FROM pg_partition t1, pg_class t2  WHERE t1.parentid = t2.oid AND t2.relname = 'pt1' AND t1.parttype = 'p';      oid  | relname | partstrategy | boundaries | reltablespace | parentid  -------+---------+--------------+------------+---------------+----------  26455 | p3      | r            | {95.00}    |             0 |    26419  26427 | pmax    | r            | {NULL}     |         18203 |    26419  26424 | p2      | r            | {85}       |         18236 |    26419  26423 | p1      | r            | {60}       |         18236 |    26419 (4 rows)  3.总结  GaussDB分区表里面有两个比较有特色的内容:  （1）分区交换技术，这个只在oracle里面出现过，现在GaussDB也实现了这个功能。  （2）保护START,END 关键字创建分区表。这个也是第一次见，比较新鲜。 作者：薛双奇

 1.GaussDB数据库索引的管理包含以下内容  --索引管理 (1)创建唯一索引,必须指定表的分布键,否则无法成功。 (2)创建复合索引 (3)创建部分索引,只在表的一部分数据上创建索引 (4)创建函数索引 (5)创建分区表的本地索引 (6)创建分区表的全局索引,并指定索引所在的表空间。 (7)索引重命名 (8)修改索引的表空间 (9)让索引不可用 (10)重建索引 (11)重建索引的分区 (12)让索引的某个分区不可用 (13)修改索引分区的名字 (14)移动索引的某个分区到另外的表空间 (15)删除索引 (16)重建表上单个索引 (17)重建表上所有索引  2.GaussDB索引管理具体操作如下  --创建测试数据： sjzt=> create table t1 as select oid,* from pg_class limit 10; NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'relallvisible' as the distribution column by default. HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column. INSERT 0 10 -- 创建唯一索引 --创建索引的时候必须指定分布键,否则无法创建成功。 sjzt=> create unique index t1_fn_idx on t1(relfilenode,relallvisible);  CREATE INDEX  -- 创建复合索引 sjzt=> create index t1_owner_tbs_idx on t1(relowner,reltablespace);  CREATE INDEX -- 创建部分索引 sjzt=> create index t1_lttbs_idx on t1(reltablespace) where reltablespace<20; CREATE INDEX -- 创建函数索引 sjzt=> create index t1_upname_idx on t1(upper(relname));  CREATE INDEX -- 创建分区表的本地索引 sjzt=> create index pt1_id_idx on pt1(id) local;  CREATE INDEX -- 创建分区表的全局索引,并指定索引所在的表空间。 sjzt=> create index pt1_score_idx on pt1(score) global tablespace tbs2; CREATE INDEX  --索引重命名 sjzt=> alter index t1_fn_idx rename to t1_fn_idx2; ALTER INDEX ---修改索引的表空间 sjzt=> alter index t1_fn_idx2 set tablespace tbs3; ALTER INDEX --让索引不可用 sjzt=> alter index t1_lttbs_idx unusable; ALTER INDEX --重建索引。 sjzt=> alter index t1_lttbs_idx rebuild; REINDEX --重建索引的分区 sjzt=> alter index pt1_id_idx rebuild partition p1_id_idx; REINDEX --让索引的某个分区不可用 sjzt=> alter index pt1_id_idx modify partition p1_id_idx unusable; ALTER INDEX --修改索引分区的名字 sjzt=> alter index pt1_id_idx rename partition p1_id_idx to p1_id_idx2; ALTER INDEX --移动索引的某个分区到另外的表空间 sjzt=>  alter index pt1_id_idx move partition p1_id_idx2 tablespace tbs3; ALTER INDEX --删除索引： sjzt=> drop index t1_lttbs_idx; DROP INDEX -- 重建表上单个索引 sjzt=> reindex index t1_lttbs_idx; REINDEX -- 重建表上所有索引 sjzt=> reindex table t1; REINDEX  3.总结  GaussDB的索引管理，不仅支持我们常见的索引。还支持Reindex关键字，一键重建所有的索引。  索引的管理非常方便，灵活。索引的每个分区都可以独立管理。包括删除，重建，重命名，修改存储表空间等。非常灵活多变 

 1.视图的管理内容如下  --视图管理 (1)创建视图 (2)创建物化视图 (3)查询视图数据 (4)查询物化视图 (5)查询视图定义pg_get_viewdef (6)重命名视图 (7)修改视图属主 (8)修改视图schema (9)刷新物化视图 (10)删除视图 (11)删除物化视图 2.视图的基本操作步骤   -- 创建视图 sjzt=> create view v1 as SELECT * FROM pg_tablespace WHERE spcname = 'pg_default'; CREATE VIEW    -- 创建物化视图 sjzt=> create materialized view mv1 tablespace tbs2 as SELECT * FROM pg_tablespace WHERE spcname = 'pg_default'; NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'spcmaxsize' as the distribution column by default. HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column. INSERT 0 1  -- 查询视图数据 sjzt=> select * from v1;   spcname   | spcowner | spcacl | spcoptions | spcmaxsize | relative  ------------+----------+--------+------------+------------+----------  pg_default |       10 |        |            |            | f (1 row)  -- 查询物化视图 sjzt=> select * from mv1;    spcname   | spcowner | spcacl | spcoptions | spcmaxsize | relative  ------------+----------+--------+------------+------------+----------  pg_default |       10 |        |            |            | f (1 row) -- 查询视图定义pg_get_viewdef sjzt=> select pg_get_viewdef('v1');                                   pg_get_viewdef                                   ----------------------------------------------------------------------------------  SELECT  * FROM pg_tablespace WHERE (pg_tablespace.spcname = 'pg_default'::name); (1 row) -- 重命名视图 sjzt=> alter view v1 rename to v2;  ALTER VIEW   -- 修改视图属主 sjzt=> alter view v2 owner to jack; ERROR:  must be member of role "jack" --这里修改报错,原因是sjzt用户必须是jack用户的成员。 sjzt=> \c - sjzt Password for user sjzt:  Non-SSL connection (SSL connection is recommended when requiring high-security) You are now connected to database "sjzt" as user "sjzt". sjzt=> grant sjzt to jack;  GRANT ROLE sjzt=> \c - jack Password for user jack:  Non-SSL connection (SSL connection is recommended when requiring high-security) You are now connected to database "sjzt" as user "jack". sjzt=> alter view sjzt.v2 owner to jack; ALTER VIEW   --注意:     这里将视图修改属主,有两个步骤:     1.登陆自己,将自己的角色(sjzt)授予别人(jack):登陆sjzt用户:grant sjzt to jack;     2.登陆别人,将自己的视图属主权限授予别人:登陆jack用户:alter view sjzt.v2 owner to jack;      -- 修改视图schema --当前用户是JACK用户/数据库是SJZT: sjzt=> \c - jack Password for user jack:  Non-SSL connection (SSL connection is recommended when requiring high-security) You are now connected to database "sjzt" as user "jack" sjzt=> create schema jack authorization jack; CREATE SCHEMA sjzt=> alter view sjzt.v2 set schema jack; ALTER VIEW sjzt=> \dv            List of relations  Schema | Name | Type | Owner | Storage  --------+------+------+-------+---------  jack   | v2   | view | jack  |  (1 row)   -- 刷新物化视图 sjzt=> \c - sjzt Password for user sjzt:  Non-SSL connection (SSL connection is recommended when requiring high-security) You are now connected to database "sjzt" as user "sjzt". sjzt=>  sjzt=> refresh materialized view mv1; INSERT 0 1 -- 删除视图,视图现在属于jack,所以需要登陆jack操作。 sjzt=> \c - jack Password for user jack:  Non-SSL connection (SSL connection is recommended when requiring high-security) You are now connected to database "sjzt" as user "jack". sjzt=> \dv            List of relations  Schema | Name | Type | Owner | Storage  --------+------+------+-------+---------  jack   | v2   | view | jack  |  (1 row)   sjzt=> drop view jack.v2; DROP VIEW   -- 删除物化视图,雾化视图MV1属于sjzt用户,所以需要切换用户。 sjzt=> \c - sjzt Password for user sjzt:  Non-SSL connection (SSL connection is recommended when requiring high-security) You are now connected to database "sjzt" as user "sjzt". sjzt=> \d                                          List of relations  Schema |     Name     |       Type        | Owner |                    Storage                      --------+--------------+-------------------+-------+------------------------------------------------  sjzt   | dept         | table             | sjzt  | {orientation=row,compression=no}  sjzt   | emp          | table             | sjzt  | {orientation=row,compression=no}  sjzt   | emp2         | table             | sjzt  | {orientation=row,compression=no}  sjzt   | mv1          | materialized view | sjzt  | {orientation=row,compression=no}  sjzt   | part_tbl1    | table             | sjzt  | {orientation=row,compression=no}  sjzt   | part_tbl2    | table             | sjzt  | {orientation=row,compression=no}  sjzt   | pt1          | table             | sjzt  | {orientation=row,compression=no}  sjzt   | s_seq01      | sequence          | sjzt  |   sjzt   | t1           | table             | sjzt  | {orientation=row,compression=no}  sjzt   | v_1          | view              | sjzt  |   sjzt   | warehouse_t1 | table             | sjzt  | {orientation=row,compression=no}  sjzt   | warehouse_t2 | table             | sjzt  | {orientation=row,fillfactor=70,compression=no}  sjzt   | warehouse_t3 | table             | sjzt  | {orientation=column,compression=high} (13 rows)   sjzt=> drop materialized view mv1; DROP MATERIALIZED VIEW  3.总结  这里有两个点需要注意:  （1）修改视图的属主，需要首选将自己的角色授予对象，然后登陆对方，授予自己的视图权限到对象。  （2）GaussDB实现了物化视图的功能，且比oracle的物理视图更加简单。Oracle的物化视图是需要创建日志表的，但是GaussDB直接省略了创建日志的步骤。直接一句命令就可以创建物化视图。物化视图需要使用refresh进行刷新才可以看到最新的数据。 作者：薛双奇

 1.序列的管理主要包含以下内容  创建管理 (1)创建序列 (2)创建序列并设置最大值，最小值，步长 (3)递增序列并返回新值 (4)设置序列的当前数值 (5)修改序列的属主,同修改视图的属主。 (6)删除序列 (7)使用元命令查看序列 (8)使用序列名称查询序列 (9)修改某个字段的默认值为序列 2.序列的使用演示  --创建序列 sjzt=> create sequence seq01; CREATE SEQUENCE --创建序列并设置最大值，最小值，步长 sjzt=> create sequence seq02 increment by 1 minvalue 1 maxvalue 99999 cache 1 nocycle; CREATE SEQUENCE -- 递增序列并返回新值 sjzt=> select nextval('seq01');  nextval  ---------        1 (1 row) sjzt=>  select seq01.nextval;  nextval  ---------        2 (1 row) -- 最近一次nextval返回的值,currval 函数暂时不支持。 sjzt=> select currval('seq01'); ERROR:  currval function is not supported CONTEXT:  referenced column: currval sjzt=> select seq01.currval; ERROR:  currval function is not supported CONTEXT:  referenced column: currval -- 最近一次nextval返回的值 sjzt=> select lastval(); ERROR:  lastval function is not supported CONTEXT:  referenced column: lastval   -- 设置序列的当前数值 sjzt=> select setval('seq01',1);  setval  --------       1 (1 row)   sjzt=> alter sequence seq01 maxvalue 99999; ALTER SEQUENCE   --修改序列的属主,同修改视图的属主。 --都是需要当前用户是目标属主的成员。然后在目标属主中修改当前序列的属主。 --sjzt是jack的成员; gaussdb=# \du      List of roles  Role name |Attributes|Member of                        -----------+-+--------  jack      | | {sjzt} sjzt=\c - jack Password for user jack:  Non-SSL connection (SSL connection is recommended when requiring high-security) You are now connected to database "sjzt" as user "jack". sjzt=> alter sequence sjzt.seq01 owner to jack; ALTER SEQUENCE sjzt=>  --删除序列 sjzt=> drop sequence seq01; DROP SEQUENCE sjzt=> drop sequence seq02 cascade; DROP SEQUENCE   -- 使用元命令查看序列 sjzt=> create sequence seq01; CREATE SEQUENCE sjzt=>  \d seq01               Sequence "sjzt.seq01"     Column     |  Type   |        Value         ---------------+---------+---------------------  sequence_name | name    | seq01  last_value    | bigint  | -1  start_value   | bigint  | 1  increment_by  | bigint  | 1  max_value     | bigint  | 9223372036854775807  min_value     | bigint  | 1  cache_value   | bigint  | 1  log_cnt       | bigint  | 0  is_cycled     | boolean | f  is_called     | boolean | f  uuid          | bigint  | 11000002   -- 查询序列 sjzt=>  select * from seq01;  sequence_name | last_value | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called |   uuid    ---------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------+----------  seq01         |         -1 |           1 |            1 | 9223372036854775807 |         1 |           1 |       0 | f         | f         | 11000002 (1 row)    --序列应用： sjzt=> create table t2(id serial,name varchar(20),tag int);  NOTICE:  CREATE TABLE will create implicit sequence "t2_id_seq" for serial column "t2.id" NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'id' as the distribution column by default. HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column. CREATE TABLE sjzt=> insert into t2 values(default,'Jerry'); INSERT 0 1 sjzt=> insert into t2 values(default,'Tom'); INSERT 0 1 sjzt=> select * from t2;  id | name  | tag  ----+-------+-----   1 | Jerry |       2 | Tom   |     (2 rows)   --修改某个字段的默认值为序列 sjzt=> alter table t2 alter tag set default nextval('seq01'); ALTER TABLE sjzt=> insert into t2 values(default,'Jack'); INSERT 0 1 sjzt=> insert into t2 values(default,'Jhon'); INSERT 0 1 sjzt=> select * from t2;  id | name  | tag  ----+-------+-----   3 | Jack  |   1   1 | Jerry |       2 | Tom   |       4 | Jhon  |   2 (4 rows) --使用序列名称查询序列 sjzt=> select * from seq01;  sequence_name | last_value | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called |   uuid    ---------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------+----------  seq01         |          2 |           1 |            1 | 9223372036854775807 |         1 |           1 |       0 | f         | t         | 11000002 (1 row)  3.总结  这里需要注意，GaussDB的序列，不支持如下三种：  select currval('seq01');  select seq01.currval;  select lastval();  取而代之的是使用：select * from seq01;   直接使用序列的名称查看序列的最小值，最大值，当前值等详细信息。这个与以往其他数据库的使用略有差异。 

 1.同义词概述 同义词概述 同义词（SYNONYM）是数据库对象的别名，用于记录与其他数据库对象名间的映射关系，用户可以使用同 义词访问关联的数据库对象，支持的数据库对象包括：表、视图、函数和存储过程。 若指定模式名称，则同义词在指定模式中创建。否则，在当前模式创建 使用同义词时，用户需要具有对关联对象的相应权限，包括：    SELECT、INSERT、UPDATE、DELETE、EXPLAIN、CALL 2.同义词管理包含如下内容  (1)创建表的同义词 (2)创建视图的同义词 (3)创建函数的存储过程并调用 (4)创建存储过程的同义词并调用 (5)删除同义词 3.同义词演示如下  -- 创建表的同义词 sjzt=> create synonym syn_t1 for t1; CREATE SYNONYM  -- 创建视图的同义词 sjzt=> create synonym syn_v1 for v_1; CREATE SYNONYM sjzt=> select * from syn_v1;  id | ename | sal  | empno | deptno  ----+-------+------+-------+--------   1 | xsq1  | 2000 |  6379 |     10 (1 row)      -- 创建函数同义词 sjzt=> \df                                                   List of functions  Schema |         Name          | Result data type | Argument data types |  Type  | fencedmode | propackage | prokind  --------+-----------------------+------------------+---------------------+--------+------------+------------+---------  sjzt   | package_func_overload | integer          | col integer         | normal | f          | t          | f (1 row)   --创建函数的存储过程并调用 sjzt=> create synonym syn_f_pfo for package_func_overload; CREATE SYNONYM sjzt=> select package_func_overload(1); one int parameters 122  package_func_overload  -----------------------                      0 (1 row)   --创建存储过程的同义词并调用 sjzt=> create synonym syn_proc_emp for my_proc; CREATE SYNONYM sjzt=> call syn_proc_emp(); inner emp_id =7799 outer_var =6688 outer emp_id =7788  syn_proc_emp  --------------   (1 row)   --删除同义词 sjzt=> drop synonym syn_proc_emp; DROP SYNONYM  4.总结  同义词是非常有用的，使用访问同义词的用户需要有关联对象的 SELECT、INSERT、UPDATE、DELETE、EXPLAIN、CALL 这些权限。同义词能够屏蔽具体的对象，而开放一个不真实的名称进行对外访问，提高了安全性。 作者：薛双奇

1、安装依赖包yum install gawk gcc make which perl crontabs unzip tar numactl rsync cut iplogrotate sed sha256sum wc xargsyum install -y gcc patch libffi-devel python-devel yum install -y zlib-devel yum install -y bzip2-devel yum install -y openssl-devel yum install -y ncurses-devel yum install -y sqlite-devel yum install -y readline-devel yum install -y tk-devel yum install -y gdbm-devel yum install -y db4-devel yum install -y libpcap-devel yum install -y xz-devel useradd ugo passwd ugo 密码:ugo 2、上传包UGO24.1.T0229.zip unzip UGO24.1.T0229.zip cd UGO24.1.T0229tar xvf ThirdPartySoftware.tar.gzcd ThirdPartySoftwarebash install.sh --第三方工具安装输出内容[+] All the required softwares will be installed under root user and required environment variables will be configured for user where UGO will be installed.[?] Enter the username where UGO is going to be installed (User will be created if not exists) : ugoTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT[+] Pre-Check Started[+] Pre-Check system Architecture[+] Current System Architecture : x86_64[+] Pre-Check OS[!] Current OS Type is not compatible.Do you want to continue(y/n)?y[+] Pre-Check gcc[+] current gcc version: gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44)[+] Pre-Check Make command[+] current make version: GNU Make 3.82[+] Pre-Check which[+] Pre-check Perl[+] Current perl Version : 5.16.3[+] Pre-Check cron[+] Pre-Check unzip[+] current unzip version: 6.00[+] Pre-Check numactl libs[+] Pre-Check rsync[+] current rsync version: rsync version 3.1.2 protocol version 31[+] Pre-Check other system softwaresTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT[+] Pre-Check username[+] Pre-Check Memory[+] Current available memory : 14GB[+] Pre-Check Disk[+] Available disk space : 43GB[+] Pre-Check CPU Cores[+] Current CPU Core Count : 4TTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT[+] Installation Started.[+] Start check haveged installed or notwarning: haveged-1.9.1-1.h1.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID 255f3799: NOKEYPreparing... ################################# [100%]Updating / installing... 1:haveged-1.9.1-1.h1 ################################# [100%]Created symlink from /etc/systemd/system/multi-user.target.wants/haveged.service to /usr/lib/systemd/system/haveged.service.[+] Extracting libaio package.[+] Installing libaio.[+] Extracting zlib package.[+] Configuring zlib.[+] Compiling zlib.[+] Installing zlib.[+] Extracting libffi package.[+] Configuring libffi.[+] Compiling libffi.[+] Installing libffi.[+] Extracting openssl package.[+] Configuring openssl.[+] Compiling openssl.[+] Installing openssl.[+] Extracting Python tar.xz package to tar.[+] Extracting Python tar package.[+] Compiling python.[+] Installing python.[+] Installing python modules...[+] Installing six.[+] Installing protobuf.[+] Installing pymysql.[+] Installing pycparser.[+] Installing cffi.[+] Installing cryptography.[+] Installing defusedxml.[+] Installing xlsxwriter.[+] Installing charset_normalizer.[+] Installing certifi.[+] Installing idna.[+] Installing urllib3.[+] Installing requests.[+] Extracting jre package.[+] Installation Completed.TTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT[+] Configuring .bashrc[+] Configuring Crontab[+] User has access to crontab.[+] Configuration Completed.TTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT[+] Verification Started.[+] Verifying Python installation[+] Version : Python 3.10.12[+] Verifying Python module ssl.[+] Verifying Python module defusedxml.[+] Verifying Python module zlib.[+] Verifying Python module gzip.[+] Verifying Python module pymysql.[+] Verifying Python module pycparser.[+] Verifying Python module cffi.[+] Verifying Python module cryptography.[+] Verifying Python module charset_normalizer.[+] Verifying Python module certifi.[+] Verifying Python module idna.[+] Verifying Python module urllib3.[+] Verifying Python module requests.[+] Verification Ended.2、Python3检查[root@oracle11gdg ThirdPartySoftware]# python3 --versionPython 3.10.123、手工创建web签名mkdir /root/ugo keytool -genkey -alias UGOWebKeystoreCRT -validity 3650 -keyalg RSA -keystore "/root/ugo/UGOWebKeystore" -keysize 2048 -storetype PKCS12 [root@oracle11gdg ~]# keytool -genkey -alias UGOWebKeystoreCRT -validity 3650 -keyalg RSA -keystore "/root/ugo/UGOWebKeystore" -keysize 2048 -storetype PKCS12Enter keystore password: Re-enter new password: What is your first and last name? [Unknown]: xueshuangqiWhat is the name of your organizational unit? [Unknown]: attWhat is the name of your organization? [Unknown]: attWhat is the name of your City or Locality? [Unknown]: chengduWhat is the name of your State or Province? [Unknown]: sichuanWhat is the two-letter country code for this unit? [Unknown]: 001Is CN=xueshuangqi, OU=att, O=att, L=chengdu, ST=sichuan, C=001 correct? [no]: yes4、解压安装包cd /root/UGO24.1.T0229/tar xvf UGO.tar.gz cp /root/LICUGO_2_20240327RWY55L.xml /root/UGO24.1.T0229/UGOcd /root/UGO24.1.T0229/UGO LICUGO_2_20240327RWY55L.xml 是向华为申请的LICENSE文件，和MAC地址绑定。 --UGO的数据库安装时设置一个负载的密码:设置一个UGO数据库用户密码: Ugo#1234 python3 install.py install --install-repo-db --license /root/UGO24.1.T0229/UGO/LICUGO_2_20240327RWY55L.xml --web-cert /root/ugo/UGOWebKeystore --os-user ugoPreCheck => Java | OK | Java - ( Version: 1.8.0_392, Distribution: openjdk ) Triggering install for given user ... PreCheck => OS User | OK | Current user verified successfully. PreCheck => Java | OK | Java - ( Version: 1.8.0_392, Distribution: openjdk ) PreCheck => openssl | OK | openssl verified successfully. PreCheck => keytool | OK | keytool verified successfully. PreCheck => unzip | OK | unzip verified successfully. PreCheck => gzip | OK | gzip verified successfully. PreCheck => rsync | OK | rsync verified successfully. PreCheck => cut | OK | cut verified successfully. PreCheck => ip | OK | ip verified successfully. PreCheck => sed | OK | sed verified successfully. PreCheck => sha256sum | OK | sha256sum verified successfully. PreCheck => wc | OK | wc verified successfully. PreCheck => xargs | OK | xargs verified successfully. PreCheck => OS | WARN | Current OS may not be compatible. Current OS : Red Hat Enterprise Linux Server 7.9 (Maipo)PreCheck => Cron job | OK | Cron job verified successfully. PreCheck => Locale | OK | Verified Locale Successfully. PreCheck => CPU Cores | OK | No. of CPU Cores : 4 PreCheck => License | OK | License is valid till 28-May-2024 23:59:59 CST. Enter the password for Certificate : rootroot --输入认证密码，上面创建签名文件的签名密码。PreCheck => WebCert | WARN | Web Certificate is valid for 3649 day(s). Given certificate is self signed.PreCheck => Disk | OK | Available disk space : 41.44GB PreCheck => Ports | OK | Input ports verified successfully. PreCheck => Memory | OK | Available Memory : 14GB Enter DB Name: ugo --输入管理ugo的数据库Enter DB UserName: ugo --输入管理ugo的用户。Enter the password for DB User -> ugo: Ugo#1234 --输入自定义密码,需要稍微复杂的密码。Retype password: Ugo#1234 --输入自定义密码,需要稍微复杂的密码。Enter the password for IAM User -> admin: Ugo#1234 --输入自定义复杂密码,WEB控制台用户密码。Retype password: Ugo#1234 --输入自定义密码,需要稍微复杂的密码。WEB控制台用户密码Installation started. Cron => Python access check. | OK | Cron access check for Python started... Cron => Java access check. | OK | Cron access check for Java started... Create config DB => logrotate | OK | Config file created: /home/ugo/ugoserver/bin/script/mysql.confConfigure => DB | OK | Properties configured. Create config catalina => logrotate | OK | Config file created: /home/ugo/ugoserver/bin/script/catalina.confConfigure => WebUI | OK | Properties configured. Configure => UGO_AppMigration | OK | Properties configured. Configure => UGO_CharsetAssess | OK | Properties configured. Configure => UGO_DbObjectCollection | OK | Properties configured. Configure => UGO_Migration | OK | Properties configured. Configure => UGO_PreMigration | OK | Properties configured. Configure => UGO_Verification | OK | Properties configured. Configure => UGO_IAMService | OK | Properties configured. Start => DB | OK | Service started successfully. Test => DB connection | OK | DB connection tested successfully. Start => Create DataBase | OK | Database created successfully. Start => WebUI | OK | Service started successfully. Configure => IAM User Details | OK | IAM User details Configured. Start => UGO_IAMService | OK | Service started successfully. Cron => Verify Python access Check | OK | Python access Check for Cron Verified. Cron => Cleanup Python access check. | OK | Cleanup done for Python access check Cron. Cron => Verify Java access Check | OK | Java access Check for Cron Verified. Cron => Cleanup Java access check. | OK | Cleanup done for Java access check Cron. URL to access the Web: https://192.168.75.58:8090/ugo/ Note : Started WebUI Services in Backend. Please wait for sometime before accessing WebURL.Configured Application Migration Port: 9040Configured Charset Assess Port: 9040Configured DB Object Collection Port: 9040Configured Migration Port: 9040Configured Pre Migration Port: 9040Configured Verification Port: 9040Configured IAM Port: 9001Please refer installer log for more Information. Installed in below mentioned Directory:-----------------------------------------------------------------path: /home/ugo/ugoserver Total Execution time 0:04:07.577295Installation completed.5、解压UGO进程[ugo@oracle11gdg ~]$ ps -ef |grep ugougo 4815 1 0 10:34 ? 00:00:00 /bin/sh /home/ugo/ugoserver/db/server/bin/mysqld_safe --defaults-file=/home/ugo/ugoserver/db/server/my.cnfugo 5595 4815 4 10:34 ? 00:00:13 /home/ugo/ugoserver/db/server/bin/mysqld --defaults-file=/home/ugo/ugoserver/db/server/my.cnf --basedir=/home/ugo/ugoserver/db/server --datadir=/home/ugo/ugoserver/db/data --plugin-dir=/home/ugo/ugoserver/db/server/lib/plugin --log-error=/home/ugo/ugoserver/logs/db/repodb.err --pid-file=/home/ugo/ugoserver/db/server/mysqld.pid --socket=/home/ugo/ugoserver/db/server/mysql.sock --port=3306ugo 6068 1 99 10:35 ? 00:10:24 /home/ugo/jre1.8.0_392/bin/java -Dnop -Djava.util.logging.manager=org.apache.juli.ClassLoaderLogManager -Dlog4j2.formatMsgNoLookups=true -Djava.security.properties=/home/ugo/ugoserver/web/conf/provider.security -Xms3072m -Xmx5120m -XX:+PrintGCDetails -XX:+PrintGCApplicationStoppedTime -XX:+PrintGCApplicationConcurrentTime -XX:+PrintGCDateStamps -Xloggc:/home/ugo/ugoserver/logs/services/gclog.log -XX:+UseGCLogFileRotation -XX:NumberOfGCLogFiles=10 -XX:GCLogFileSize=5m -XX:+HeapDumpOnOutOfMemoryError -XX:HeapDumpPath=/home/ugo/ugoserver/logs/services/ -Djdk.tls.ephemeralDHKeySize=2048 -Djava.protocol.handler.pkgs=org.apache.catalina.webresources -Dorg.apache.catalina.security.SecurityListener.UMASK=0077 -Djava.net.preferIPv4Stack=false -Djava.net.preferIPv4Addresses -Dorg.apache.catalina.connector.RECYCLE_FACADES=true -Dorg.apache.catalina.STRICT_SERVLET_COMPLIANCE=true -Dorg.apache.tomcat.util.http.ServerCookie.FWD_SLASH_IS_SEPARATOR=false -Dorg.apache.tomcat.util.http.ServerCookie.STRICT_NAMING=false -Dignore.endorsed.dirs= -classpath /home/ugo/ugoserver/web/bin/bootstrap.jar:/home/ugo/ugoserver/web/bin/tomcat-juli.jar -Djava.security.manager -Djava.security.policy==/home/ugo/ugoserver/web/conf/catalina.policy -Dcatalina.base=/home/ugo/ugoserver/web -Dcatalina.home=/home/ugo/ugoserver/web -Djava.io.tmpdir=/home/ugo/ugoserver/web/temp org.apache.catalina.startup.Bootstrap startugo 6102 1 21 10:35 ? 00:00:45 java -Dlog4j2.formatMsgNoLookups=true -XX:+PrintGCDetails -Djava.net.preferIPv4Addresses -Djava.net.preferIPv4Stack=false -XX:+PrintGCApplicationStoppedTime -XX:+PrintGCApplicationConcurrentTime -XX:+PrintGCDateStamps -Xloggc:/home/ugo/ugoserver/logs/services/UGO_IAMService/gclog.log -XX:+UseGCLogFileRotation -XX:NumberOfGCLogFiles=10 -XX:GCLogFileSize=5m -XX:+HeapDumpOnOutOfMemoryError -XX:HeapDumpPath=/home/ugo/ugoserver/logs/services/UGO_IAMService/ -Xms512m -Xmx1024m -DIAM_HOME=/home/ugo/ugoserver/services/UGO_IAMService -DLOGFILE_PATH=/home/ugo/ugoserver/logs/services/UGO_IAMService -cp /home/ugo/ugoserver/services/UGO_IAMService/../../web/shared/3rd_party_lib/*:/home/ugo/ugoserver/services/UGO_IAMService/../../web/shared/UGO_lib/*:/home/ugo/ugoserver/services/UGO_IAMService/lib/* com.huawei.gauss.dsc.iamservice.IAMServiceApplication*/--可以看到，UGO的后台进程，有一个MySQL的后台数据库。 --mysql 的目录假如环境变量。[ugo@oracle11gdg bin]$ cat ~/.bash_profile # .bash_profile# Get the aliases and functionsif [ -f ~/.bashrc ]; then . ~/.bashrcfi# User specific environment and startup programsPATH=/home/ugo/ugoserver/db/server/bin:$PATH:$HOME/.local/bin:$HOME/binexport PATH source ~/.bash_profile 6、等了GUO元数据库 mysql -uugo -pUgo#1234 -S /home/ugo/ugoserver/db/server/mysql.sock -h127.0.0.1--查看UGO相关的数据库元数据库。 mysql -uugo -pUgo#1234 -S /home/ugo/ugoserver/db/server/mysql.sock -h127.0.0.1mysql> use ugo Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -A showDatabase changedmysql> show tables; +-------------------------------------+| Tables_in_ugo |+-------------------------------------+| APPMIG_SOURCEDB_SUPPORT_INFO || APP_CODE_FILE || APP_COLLECTION_TYPE_SUPPORT_INFO || APP_DATA_PROCESSING_PROGRESS || APP_FILE_PARSING_PROGRESS || APP_FILE_PARSING_TASK_PROGRESS_INFO || APP_SOURCESQL_HISTORY || APP_SQL_INFO || APP_TYPE_SUPPORT_INFO || APP_UPLOAD_FILE_INFO || AUDIT_COLLECTION_DATABASE_OBJECTS || AUDIT_DATABASES || AUDIT_ERROR_FILES || AUDIT_PROJECT || AUDIT_RULES || AUDIT_SINGLE_SQL_HISTORY || AUDIT_SQL || AUDIT_SQL_DEPENDENCE || AUDIT_SQL_META_INFO || AUDIT_SQL_NO_MATCH_RULES || AUDIT_SQL_VIOLATION || AUDIT_TASK || AUDIT_TASK_CAPTURE || AUDIT_TASK_SQL || AUDIT_TEMPLATES || AUDIT_TEMPLATE_RULES || CHARSET_ASSESS_TABLE || CHARSET_CONFIG || CHARSET_CONVERT_RECORD || CHARSET_MAL_RECORD || CHARSET_PROJECT || CHARSET_TABLE_COLUMN_STAT || DBAPP_UNZIP_FILE_PROGRESS || DBTYPE_OBJTYPE_MAP || DBTYPE_SQLTYPE_MAP || DB_ALARM || DB_APPMIG_PROJECTS || DB_APPMIG_PROJECT_FILE_INFO || DB_APPMIG_PROJECT_TRAN_SQL_MAP || DB_APP_CPU_INFO || DB_APP_INCR_SQL_INFO || DB_APP_INCR_SQL_SNAPSHOTS_INFO || DB_APP_MEM_INFO || DB_APP_SQLOBJECT_TYPE || DB_APP_SQL_COMPLEX_INFO || DB_APP_SQL_INFO || DB_APP_SQL_INFO_STATS || DB_APP_SYS_INFO || DB_COLLECTION_CHECKPOINT_STATE || DB_COLLECTION_DIFF_DATA || DB_COLLECTION_SCHEMA || DB_COLLECTION_STATUS || DB_COLLECTION_VERIFICATION || DB_COLL_SRCDB_ROUTE_COUNTER || DB_COMMON_TASK || DB_CONNECTIONS || DB_DELTA_COLLECTION_INFO || DB_DELTA_COLLECTION_PROGRESS || DB_DEP_ORDER_PROGRESS || DB_DISTRIBUTED_COLUMN || DB_EVALUATION_PROJECTS || DB_EVAL_PROJECT_STATISTIC || DB_EVAL_PROJECT_STATISTIC_DETAILS || DB_EVA_DB2_TOKEN || DB_IAM_HISTORY || DB_IAM_OPERATION || DB_IAM_PERMISSION || DB_IAM_ROLE || DB_IAM_USER || DB_IAM_USER_ACCESS_CONTROL || DB_IAM_USER_LOGIN_ATTEMPTS || DB_IAM_USER_LOGIN_INFO || DB_IAM_USER_SESSION || DB_MAPPING_TB_MIGRATION || DB_MIGRATION || DB_MIGRATION_CONFIG || DB_MIGRATION_CONFIG_CATEGORY || DB_MIGRATION_DEFAULT_CONFIG || DB_MIGRATION_HISTORY || DB_MIGRATION_OBJECT_BULK_HISTORY || DB_MIGRATION_OBJECT_HISTORY || DB_MIGRATION_PROJECTS || DB_MIGRATION_SPLIT_STATEMENT || DB_MIGRATION_SQL_CONVERT_CONFIG || DB_MIGRATION_STATUS || DB_MIGRATION_TEMPLATE_CONFIG || DB_MIGRATION_TEMPLATE_CONFIG_MAP || DB_MIG_OBJ_UPLOAD_DETAIL || DB_MIG_OBJ_UPLOAD_TASK || DB_MIG_PROJECT_STATISTIC || DB_OBJCOMP_PROJECTS || DB_OFFLINE_PARSE_FILE_INFO || DB_OFFLINE_PROJECT_FILE_INFO || DB_PREMIG_OBJECTSTAT || DB_PREMIG_OBJECT_MATCH || DB_PREMIG_OBJTYPE_SUMMARY || DB_PREMIG_PROGRESS || DB_PREMIG_RISK_ANALYSIS || DB_PREMIG_RISK_CATEGORY || DB_PREMIG_SUBEVAL_MAPPING || DB_PREMIG_SUMMARY || DB_PREMIG_SYSTEMOBJ || DB_PREMIG_SYSTEMOBJ_COLUMNS || DB_PREMIG_TABLE_COMPLEXITY || DB_PRE_MIGRATION || DB_PRE_MIGRATION_DATABASE_ANALYSIS || DB_SCHEMA_COUNT_VERIFICATION || DB_SCHEMA_DELTA_COUNT_PROGRESS || DB_SQL_CONVERSION_STATISTIC || DB_SQL_SIZE || DB_SRC_CATALOG || DB_SRC_DEPENDENCY || DB_SRC_INDEX_DEPENDENCY || DB_SRC_MIGRATION_OBJ || DB_SRC_TABLE_STATS || DB_SRC_TRACE_INFO || DB_SUPPORT_INFO || DB_TYPES || DB_VERIFICATION_CLEANUP || DB_VERIFICATION_ERR_FREQUENCY || DB_VERIFICATION_HISTORY || DB_VERIFICATION_OBJECT_BULK_REQDATA || DB_VERIFICATION_ROLE_GRANT || DB_VERIFICATION_SNAPSHOT || DB_VERIFICATION_STATUS || DOWNLOAD_LOG_TASK || EVAL_PROJECT_INFO_MSG || EVAL_SYSTEM_ROLE_LIST || EVA_DELTACOL_OBJINFO || EVA_INFO_STATS || EVA_METRIC_DATA_CONFIGS || EVA_METRIC_HIS || EVA_METRIC_MIN_MAX || EVA_PACKAGE_OBJECT_TYPES || EVA_PRE_CHECK_ITEMS || EVA_SCHEMA_INFO || EVA_SKIPPED_OBJ || EVA_SQL_DATA || EXPORT_TASK || EXTRACT_SQL_TASK || MIGRATION_EVALUATION_PROJECT_MAP || MIGRATION_SKIP_OBJECTS || MIG_CACHE_STORE || MIG_OBJCOMP_COMPARE_RESULT || MIG_OBJCOMP_OBJLIST || MIG_OBJCOMP_OBJLIST_COUNT || MIG_OBJCOMP_OBJTYPELIST || MIG_OBJCOMP_TRACE_INFO || MIG_ONLINE_FILE_UPLOAD || MIG_ONLINE_FILE_UPLOAD_INFO || MIG_PRE_CHECK_PROGRESS || MIG_PRE_CHECK_RESULT || MIG_USERS_PASSWORD || OBJCOMP_OP_COUNT || OBJCOMP_PROJECT_MAP || OBJCOMP_SCHEMA_TYPE_LIST || OBJECT_NAME_MAPPING || OFFLINE_SOURCEDB_SUPPORT_INFO || REPO_DB_VERSION_INFO || SOURCE_DB_OBJECT_FILTER || SRC_SCHEMA_FILTER_LIST || SRC_SYSTEM_ROLE_LIST_V11 || SRC_TO_TGT_VERSION_MAP || TABLESPACE_DETAIL || TAG_INFO_STATS || UGO_ALARMRECORDS || UGO_ALARMS || UGO_AUDITRECORD || UGO_Configuration || UGO_PROJECT_CONFIG || UGO_SLI_SUMMARY || UGO_WEAK_PWD_DICT || USER_ROLES_COLLECTION || seqgen |+-------------------------------------+174 rows in set (0.01 sec)7、UGO web界面登录网络可联通的浏览器访问UGO WEB界面。https://192.168.75.58:8090/ugo/#/console/dashboardadmin/Ugo#1234 --安装时设置的用户密码。--可以登录控制台查看控制台的内容。目前只有单机UGO,无GaussDB,无法看到其他内容。8、UGO服务重启su - ugo cd /home/ugo/ugoserver/bin/python3 ugoserver.py restart----------------------------------------------------------------- Service Name | Operation Details----------------------------------------------------------------- DB | Service stopped successfully. WebUI | Service stopped successfully. UGO_IAMService | Service stopped successfully. DB | Service started successfully. WebUI | Service started successfully. UGO_IAMService | Service started successfully.-----------------------------------------------------------------Note : Started WebUI Services in Backend. Please wait for sometime before accessing WebURL.9、查看UGO服务状态su - ugo cd /home/ugo/ugoserver/bin/python3 ugoserver.py status----------------------------------------------------------------- Service Name | Status ----------------------------------------------------------------- DB | UP DB | Service listening on port : 3306 WebUI | UP License | License is valid till 28-May-2024 23:59:59 CST. PREMIGRATION - Validity | 2024-05-28 OBJMIGRATION - Validity | 2024-05-28 APPMIGRATION - Validity | 2024-05-28 OBJCOMPARISON - Validity | 2024-05-28 SQLCONV - Validity | 2024-05-28 WebUI | Service listening on port : 8090 UGO_AppMigration | Service listening on port : 9040 UGO_CharsetAssess | Service listening on port : 9040 UGO_DbObjectCollection | Service listening on port : 9040 UGO_Migration | Service listening on port : 9040 UGO_PreMigration | Service listening on port : 9040 UGO_Verification | Service listening on port : 9040 UGO_IAMService | UP UGO_IAMService | Service listening on port : 9001-----------------------------------------------------------------10、检查license状态python3 ugoserver.py status -s webui ----------------------------------------------------------------- Service Name | Status ----------------------------------------------------------------- WebUI | UP License | License is valid till 28-May-2024 23:59:59 CST. PREMIGRATION - Validity | 2024-05-28 OBJMIGRATION - Validity | 2024-05-28 APPMIGRATION - Validity | 2024-05-28 OBJCOMPARISON - Validity | 2024-05-28 SQLCONV - Validity | 2024-05-28 WebUI | Service listening on port : 8090 UGO_AppMigration | Service listening on port : 9040 UGO_CharsetAssess | Service listening on port : 9040 UGO_DbObjectCollection | Service listening on port : 9040 UGO_Migration | Service listening on port : 9040 UGO_PreMigration | Service listening on port : 9040 UGO_Verification | Service listening on port : 9040----------------------------------------------------------------- --执行以下命令，查看UGO服务的License证书状态:python3 ugoserver.py license-status ----------------------------------------------------------------- License | Status ----------------------------------------------------------------- Status | Valid Message | License is valid Validity | 28-May-2024 23:59:59 CST PREMIGRATION - Validity | 2024-05-28 OBJMIGRATION - Validity | 2024-05-28 APPMIGRATION - Validity | 2024-05-28 OBJCOMPARISON - Validity | 2024-05-28 SQLCONV - Validity | 2024-05-28----------------------------------------------------------------- 11、查看特定License证书文件中的License证书的状态：  su - root  cd /root/UGO24.1.T0229/UGO/ python3 install.py license-check --ip 192.168.75.58 --license  /root/UGO24.1.T0229/UGO/LICUGO_2_20240327RWY55L.xml   -----------------------------------------------------------------  License                        | Status     -----------------------------------------------------------------  Status                         | Valid       Message                        | License is valid  Validity                       | 28-May-2024 23:59:59 CST  PREMIGRATION - Validity        | 2024-05-28  OBJMIGRATION - Validity        | 2024-05-28  APPMIGRATION - Validity        | 2024-05-28  OBJCOMPARISON - Validity       | 2024-05-28  SQLCONV - Validity             | 2024-05-28 ----------------------------------------------------------------- 12.Ugo数据库root密码未知，修改数据库root用户的密码。--修改root用户密码。vi /home/ugo/ugoserver/db/server/my.cnfskip-grant-tables = FALSE --修改为:skip-grant-tables = TRUE --重启服务。su - ugo cd /home/ugo/ugoserver/binpython3 ugoserver.py restart --密码登录后修改密码。mysql -uroot -p -S /home/ugo/ugoserver/db/server/mysql.sockmysql> flush privileges; Query OK, 0 rows affected (0.01 sec) mysql> select version();+-----------+| version() |+-----------+| 8.0.35 |+-----------+1 row in set (0.00 sec)mysql> select user,host from mysql.user; +------------------+-----------+| user | host |+------------------+-----------+| ugo | 127.0.0.1 || ugo-root | 127.0.0.1 || ugo-root | ::1 || mysql.infoschema | localhost || mysql.session | localhost || mysql.sys | localhost || ugo-root | localhost |+------------------+-----------+ ALTER USER 'ugo-root'@'localhost' IDENTIFIED BY 'Ugo#1234';ALTER USER 'ugo-root'@'127.0.0.1' IDENTIFIED BY 'Ugo#1234';ALTER USER 'ugo-root'@'::1' IDENTIFIED BY 'Ugo#1234'; --创建一个UGO能够远程登陆的ROOT用户。create user 'ugo-root'@'%' identified by 'Ugo#1234';grant all privileges on *.* to 'ugo-root'@'%'; --修改参数文件设置skip-grant-tables = FALSE ，重启UGO;vi /home/ugo/ugoserver/db/server/my.cnfskip-grant-tables = FALSE su - ugo cd /home/ugo/ugoserver/binpython3 ugoserver.py restart --创建SOCKET软链接。ln -sv /home/ugo/ugoserver/db/server/mysql.sock /tmp/mysql.sock --登录UGO的root管理员账号即可查看和管理UGO;mysql -u'ugo-root' -pUgo#1234 mysql> show databases; +--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sys || ugo |+--------------------+5 rows in set (0.00 sec) --登录UGO;--UGO的用户里面有127.0.0.1的名称。所以登录时需要带h参数。mysql -uugo -pUgo#1234 -h127.0.0.1mysql> show databases; +--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sys || ugo |+--------------------+5 rows in set (0.00 sec) 13.如何修改 UGO 运行内存参数？cd /home/ugo/ugoserver/web/binvim setenv.sh-XmsXXXXm -XmxYYYYmpython3 ugoserver.py restart --当前默认值如下。cd /home/ugo/ugoserver/web/bincat setenv.sh#!/bin/bashumask 0077export LOGGING_HOME=/home/ugo/ugoserver/logs/servicesexport UGO_HOME=/home/ugo/ugoserverexport UGO_DB_DRIVER_LIB=/home/ugo/ugoserver/web/shared/UGO_DBDriver_Libexport JAVA_OPTS="$JAVA_OPTS -Dlog4j2.formatMsgNoLookups=true -Djava.security.properties=$UGO_HOME/web/conf/provider.security -Xms3072m -Xmx5120m -XX:+PrintGCDetails -XX:+PrintGCApplicationStoppedTime -XX:+PrintGCApplicationConcurrentTime -XX:+PrintGCDateStamps -Xloggc:$LOGGING_HOME/gclog.log -XX:+UseGCLogFileRotation -XX:NumberOfGCLogFiles=10 -XX:GCLogFileSize=5m -XX:+HeapDumpOnOutOfMemoryError -XX:HeapDumpPath=$LOGGING_HOME/"export CATALINA_OUT=/home/ugo/ugoserver/logs/web/catalina.outexport CATALINA_PID=/home/ugo/ugoserver/web/HwEoLrLlOd.tcatpid --修改这个值即可。-Xms3072m -Xmx5120m--更新 JVM 内存的规则：● 对于62 GB或更多RAM计算机：maxJVMMemory（或）Xmx● 对于32 GB RAM计算机：maxJVMMemory（或）Xmx● 对于16 GB RAM计算机：maxJVMMemory（或）Xmx● 对于大于16 GB的RAM计算机：maxJVMMemory（或）Xmx作者：薛双奇

1、检查节点状态[omm@gauss001 ~]$ gs_om -t status --detail[ CMServer State ] node node_ip instance state-----------------------------------------------------------------------------1 192.168.0.142 192.168.0.142 1 /data/cluster/data/cm/cm_server Primary [ Cluster State ] cluster_state : Normalredistributing : Nobalanced : Yescurrent_az : AZ_ALL [ Datanode State ] node node_ip instance state---------------------------------------------------------------------------------------1 192.168.0.142 192.168.0.142 6001 /data/cluster/data/dn/dn_6001 P Primary Normal2、节点状态查看 [omm@gauss001 dn_6001]$ gs_om -t status -h 192.168.0.142----------------------------------------------------------------------- cluster_state : Normalredistributing : Nobalanced : Yes-----------------------------------------------------------------------node : 1node_name : 192.168.0.142node : 1instance_id : 1node_ip : 192.168.0.142data_path : /data/cluster/data/cm/cm_servertype : CMServerinstance_state : Primarynode : 1instance_id : 6001node_ip : 192.168.0.142data_path : /data/cluster/data/dn/dn_6001type : Datanodeinstance_state : Primarystatic_connections : 0HA_state : Normalreason : Normalstandby_node : standby_data_path : standby_node : standby_data_path : standby_state : Unknownsender_sent_location : 0/0sender_write_location : 0/0sender_flush_location : 0/0sender_replay_location : 0/0receiver_received_location: 0/0receiver_write_location : 0/0receiver_flush_location : 0/0receiver_replay_location : 0/0sync_state : Unknownsecondary_state : Unknownsender_sent_location : 0/0sender_write_location : 0/0sender_flush_location : 0/0sender_replay_location : 0/0receiver_received_location: 0/0receiver_write_location : 0/0receiver_flush_location : 0/0receiver_replay_location : 0/0sync_state : Unknownnode : 1node_ip : 192.168.0.142type : Fenced UDFstate : Normal-----------------------------------------------------------------------node_state : Normal3、数据库状态cluster_state 数据库状态。•Normal：表示数据库可用，且数据有冗余备份。所有进程都在运行，主备关系正常。•Unavailable：表示数据库不可用。•Degraded：表示数据库可用，但存在故障的数据库节点、数据库主节点实例。4、实例角色•Normal：表示单主机实例。•Primary：表示实例为主实例。•Standby：表示实例为备实例。•Pending：表示该实例在仲裁阶段。•Unknown：表示实例状态未知。•Down：表示实例处于宕机状态。•Abnormal：表示节点处于异常状态。•Manually stopped：表示节点已经被手动停止。5、节点状态说明状态:字段含义Normal:表示节点启动正常。Need repair:当前节点需要修复。Starting:节点正在启动中。Wait promoting:节点正等待升级中。例如备机向主机发送升级请求后，正在等待主机回应时的状态。Promoting:备节点正在升级为主节点的状态Demoting:节点正在降级中，如主机正在降为备机中。Building:备机启动失败，需要重建。Catchup:备节点正在追赶主节点。Coredump:节点程序崩溃。Unknown:节点状态未知。

1、实例主备切换--将备节点正常切换为主节点。gs_ctl switchover -D /home/omm/cluster/dn1/ --主机故障时，可以在备机执行如下命令。强制将其切换为主节点。gs_ctl failover -D /home/omm/cluster/dn1/ 2、脑裂双主如何处理 --出现双主状态后，请按如下步骤恢复成正常的主备状态。否则可能会造成数据丢失。 1).执行以下命令查询数据库当前的实例状态。gs_om -t status --detail 若查询结果显示两个实例的状态都为Primary，这种状态为异常状态。 2).确定降为备机的节点，在节点上执行如下命令关闭服务。gs_ctl stop -D /home/omm/cluster/dn1/ 3).执行以下命令，以standby模式启动备节点。gs_ctl start -D /home/omm/cluster/dn1/ -M standby 4).查看数据库状态，确认实例状态恢复。gs_om -t status --detail

1、检查实例状态 [omm@gauss001 ~]$ cm_ctl query -Cv[ CMServer State ] node instance state---------------------------------1 192.168.0.142 1 Primary [ Cluster State ] cluster_state : Normalredistributing : Nobalanced : Yescurrent_az : AZ_ALL [ Datanode State ] node instance state---------------------------------------------1 192.168.0.142 6001 P Primary Normal2、查询数据库中锁信息  --2.查询数据库中的锁信息SELECT * FROM pg_locks;--查询等待锁的线程状态信息，获取持锁线程block_sessionid。SELECT * FROM pg_thread_wait_status WHERE wait_status = 'acquire lock'; node_name | db_name | thread_name | query_id | tid | sessionid | lwtid | psessionid | tlevel | smpid | wait_status | wait_event | locktag | lockmode | block_sessionid | global_sessionid -----------+---------+-------------+----------+-----+-----------+-------+------------+--------+-------+-------------+------------+---------+----------+-----------------+------------------(0 rows)--查找正在持锁的会话，然后使用pg_terminate_session命令结束此会话。gaussdb=# SELECT pid,sessionid,query FROM pg_stat_activity where sessionid=block_sessionid; gaussdb=# SELECT pg_terminate_session(pid,sessionid);3、活跃会话检查 --SQL语句长时间运行会占用大量系统资源，用户可以通过查看事件发生的时间，占用内存大小来了解现在数据库运行状态。--查询事件的时间--查询事件的线程启动时间、事务启动时间、SQL启动时间以及状态变更时间gaussdb=# SELECT backend_start,xact_start,query_start,state_change FROM pg_stat_activity;--查询当前服务器的会话计数信息SELECT count(*) FROM pg_stat_activity;--查询当前使用内存最多的会话信息。gaussdb=# SELECT sessid, sum(totalsize)/1024/1024 as "totalsize MB", count(1) as count FROM gs_session_memory_context GROUP BY sessid ORDER BY 2 DESC LIMIT 10;3、对象检查 --表、索引、分区、约束等是数据库的核心存储对象，其核心信息和对象维护是DBA重要的日常工作。--查看表的详细信息\d+ table_name --查询表统计信息SELECT * FROM pg_statistic;--查看索引的详细信息\d+ index_name--查询分区表信息SELECT * FROM pg_partition;5、收集统计信息 --使用ANALYZE语句收集数据库相关的统计信息。gaussdb=# ANALYZE;gaussdb=# ANALYZE table_name; 6.使用VACUUM语句可以回收空间并更新统计信息。  gaussdb=# VACUUM; gaussdb=# VACUUM table_name; --查询约束信息 gaussdb=# SELECT * FROM pg_constraint; 7.基本信息检查  --基本信息包括版本、组件、补丁集等信息，定期检查数据库信息并登记在案是数据库生命周期管理的重要内容之一。 --版本信息 gaussdb=# SELECT version(); 8.容量检查：表大小，数据库大小。 gaussdb=# SELECT pg_table_size('table_name');gaussdb=# SELECT pg_database_size('database_name');

1、执行如下命令查看操作系统的日志文件vim /var/log/messages关注其中近一个月出现的kernel、error、fatal等字样，根据系统报警信息进行处理。2.检查数据库运行日志•收集日志的主机网络通畅且未宕机，数据库安装用户互信正常。•日志收集工具依赖操作系统工具如gstack，如果未安装该工具，则提示错误后，跳过该收集项。3.收集某个时间段内的日志。gs_collector --begin-time="20240407 18:00" --end-time="20240408 08:00" -h 192.168.0.142gs_collector --begin-time="20240407 18:00" --end-time="20240408 08:00" [omm@gauss001 ~]$ gs_collector --begin-time="20240407 18:00" --end-time="20240408 08:00"Successfully parsed the configuration file.create Dir.Successfully create dir.do system check interval 0 : count 1Collecting OS information.Failed to collect OS information.do database check interval 0 : count 1Collecting catalog statistics.Failed collected catalog statistics.do log check interval 0 : count 1Collecting Log files.Successfully collected Log files.do Config check 0:1Collecting Config files.Successfully collected Config files.Collecting files.Successfully to collect files.All results are stored in /data/cluster/logs/gaussdb/omm/collector_20240408_093215.tar.gz.4、解压并查看日志[omm@gauss001 ~]$ tar xvf collector_20240408_093215.tar.gz collector_20240408_093215/collector_20240408_093215/Detail.logcollector_20240408_093215/Summary.logcollector_20240408_093215/192.168.0.142.tar.gz (1)Detail.log [omm@gauss001 collector_20240408_093215]$ more Detail.log -----------------------------------------------------------------------| || 192.168.0.142 - Collecting OS information - Failed || |-----------------------------------------------------------------------| SuccessfulTask | hostname ; ps ux ; free -m |-----------------------------------------------------------------------| FailedTask | iostat -xm 2 3 : | --没有安装sysstat软件包。-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| || 192.168.0.142 - Collecting catalog information - Failed || |--------------------------------------------------------------------------------------------------------------------| FailedTask | Collecting catalog information: [GAUSS-53503] : There is no coordinator inst in this host |------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| || 192.168.0.142 - Collecting pg_log information - Success || |----------------------------------------------------------------------------| SuccessfulTask | find log files; copy log files |--------------------------------------------------------------------------------------------------------------------------------------------------------| || 192.168.0.142 - Collecting Config information - Success || |----------------------------------------------------------------------------| SuccessfulTask | collect Config information |-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| || 192.168.0.142 - Copy Collect Info Files - Success || |-------------------------------------------------------------------------------------------------------------------| SuccessfulTask | Check Dir; Copy Collect Info Files; Compress Collect Info Files; Send Collect Info Files |------------------------------------------------------------------------------------------------------------------- (2)Summary.log [omm@gauss001 collector_20240408_093215]$ more Summary.log --------------------------------------------------------------------------------| | | || TASK NAME | SUCCESS HOSTNAME | FAILED HOSTNAME || | | |--------------------------------------------------------------------------------|Collecting OS information-1 | | 192.168.0.142 |--------------------------------------------------------------------------------|Collecting catalog information-1 | | 192.168.0.142 |--------------------------------------------------------------------------------|Collecting pg_log information-1 | 192.168.0.142 | |--------------------------------------------------------------------------------|Collecting Config information-1 | 192.168.0.142 | |--------------------------------------------------------------------------------|Copy Collect Info Files-1 | 192.168.0.142 | |-------------------------------------------------------------------------------- [omm@gauss001 collector_20240408_093215]$ tar xvf 192.168.0.142.tar.gz 192.168.0.142/192.168.0.142/logfiles/192.168.0.142/logfiles/log_20240408_093216907859.tar.gz192.168.0.142/catalogfiles/192.168.0.142/systemfiles/192.168.0.142/systemfiles/OS_information_20240408_093215613953.txt192.168.0.142/systemfiles/database_system_info_20240408_093215643918.txt192.168.0.142/configfiles/192.168.0.142/configfiles/config_20240408_093217458939/192.168.0.142/configfiles/config_20240408_093217458939/dn_6001/192.168.0.142/configfiles/config_20240408_093217458939/dn_6001/pg_hba.conf192.168.0.142/configfiles/config_20240408_093217458939/dn_6001/pg_control192.168.0.142/configfiles/config_20240408_093217458939/dn_6001/gaussdb.state192.168.0.142/configfiles/config_20240408_093217458939/dn_6001/postgresql.conf192.168.0.142/configfiles/config_20240408_093217458939/dn_6001/pg_replslot/192.168.0.142/configfiles/config_20240408_093217458939/dn_6001/pg_ident.conf[omm@gauss001 collector_20240408_093215]$ cd 192.168.0.142[omm@gauss001 192.168.0.142]$ lltotal 16drwx------ 2 omm omm 4096 Apr 8 09:32 catalogfilesdrwx------ 3 omm omm 4096 Apr 8 09:32 configfilesdrwx------ 2 omm omm 4096 Apr 8 09:32 logfilesdrwx------ 2 omm omm 4096 Apr 8 09:32 systemfiles

1、加入主机名称 vim /tmp/mpphosts2、检查时间for ihost in `cat /tmp/mpphosts`; do ssh -n -q $ihost "hostname;date"; done > /tmp/sys_ctl-os1.log 注意:前提是多个节点之间要做免密，否则需要输入密码。时间肯定不一致。 --检查主备是否握手成功。ntpq -p

1、SQL语句查看连接数 gaussdb=> SELECT count(*) FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s; count ------- 40(1 row) 2、查看现有最大连接数 gaussdb=> SHOW max_connections; max_connections ----------------- 1024(1 row)3、异常处理 如果显示的连接数接近数据库的最大连接数max_connections，则需要考虑清理现有连接数或者增加新的连接数。1)执行如下SQL语句，查看state字段等于idle，且state_change字段长时间没有更新过的连接信息。 SELECT * FROM pg_stat_activity where state='idle' order by state_change; 2)释放空闲的连接数。 --查看每个连接，并与此连接的使用者确认是否可以断开连接，或执行如下SQL语句释放连接。--其中，pid为上一步查询中空闲连接所对应的pid字段值。gaussdb=# SELECT pg_terminate_backend(140390132872976); 3)增加最大连接数。gs_guc set [-Z datanode] -D /gaussdb/data/dbnode -c "max_connections= 800" 4)重启数据库服务使新的设置生效gs_om -t stop && gs_om -t start

为了保证数据库有效运行，数据库必须在插入/删除操作后，基于客户场景，定期做VACUUM FULL和ANALYZE，更新统计信息，以便获得更优的性能。1.相关概念使用VACUUM、VACUUM FULL和ANALYZE命令定期对每个表进行维护，主要有以下原因：•VACUUM FULL可回收已更新或已删除的数据所占据的磁盘空间，同时将小数据文件合并。•VACUUM对每个表维护了一个可视化映射来跟踪包含对别的活动事务可见的数组的页。一个普通的索引扫描首先通过可视化映射来获取对应的数组，来检查是否对当前事务可见。若无法获取，再通过堆数组抓取的方式来检查。因此更新表的可视化映射，可加速唯一索引扫描。•VACUUM可避免执行的事务数超过数据库阈值时，事务ID重叠造成的原有数据丢失。•ANALYZE可收集与数据库中表内容相关的统计信息。统计结果存储在系统表PG_STATISTIC中。查询优化器会使用这些统计数据，生成最有效的执行计划。2、使用VACUUM或VACUUM FULL命令，进行磁盘空间回收。VACUUM customer; 可以与数据库操作命令并行运行（执行期间，可正常使用的语句：SELECT、INSERT、UPDATE和DELETE。不可正常使用的语句：ALTER TABLE）。 --对表分区执行VACUUM操作。VACUUM customer_par PARTITION ( P1 ); VACUUM FULL customer;--执行VACUUM FULL customer时会阻塞涉及customer表的所有操作。3.使用ANALYZE语句更新统计信息。 ANALYZE customer; --使用ANALYZE VERBOSE语句更新统计信息，并输出表的相关信息。ANALYZE VERBOSE customer; --也可以同时执行VACUUM ANALYZE命令进行查询优化 VACUUM ANALYZE customer; 注意:VACUUM 和 ANALYZE 会导致I/O流量的大幅增加，这可能会影响其他活动会话的性能。因此，建议通过 vacuum_cost_delay 参数设置.4、删除表 DROP TABLE customer;5、维护建议 •定期对部分大表做VACUUM FULL，在性能下降后为全库做VACUUM FULL，目前暂定每月做一次VACUUM FULL。•定期对系统表做VACUUM FULL，主要是 PG_ATTRIBUTE。•启用系统自动清理线程（AUTOVACUUM）自动执行VACUUM和ANALYZE，回收被标识为删除状态的记录空间，并更新表的统计数据。