VastBase的日常操作记录

---------需要命令启动数据库操作-------------------
[vastbase@dmz-neo4j01 ~]$ vb_ctl start
[2025-05-13 09:41:54.515][10224][][vb_ctl]: vb_ctl started,数据目录是 /home/vastbase/data/vastbase 
[2025-05-13 09:41:54.728][10224][][vb_ctl]: 等待服务端进程启动 ...
。0 日志:  [Alarm Module]无法读取环境变量GAUSS_WARNING_TYPE。0 日志:  [Alarm Module]主机名：dmz-neo4j01 0 日志:  [Alarm Module]主机 IP: 10.14.1.3 0 日志:  [Alarm Module]获取环境变量GS_CLUSTER_NAME失败！0 日志:  [Alarm Module]在AlarmItem文件中有无效数据！读取告警英文名称失败！行：570 警告:  未能打开特性控制文件，请检查它是否存在:FileName=gaussdb.version, Errno=2, Errmessage=没有那个文件或目录。
0 警告:  未能解析特性控制文件: gaussdb.version
0 警告:  未能加载产品控制文件，因此gaussdb无法区分产品版本。
core dump路径是一个无效目录
。0 LOG:  License info: Customer:'Vastbase', Begins On:'2025-02-06 09:39:51', Expires On:'2025-07-31 09:42:46', MAC:'' 
2025-05-13 09:41:55.730 [unknown] [unknown] localhost 140484928199808 0[0:0#0]  0 [BACKEND] LOG:  when starting as multi_standby mode, we couldn't support data replicaton.
2025-05-13 09:41:55.730 [unknown] [unknown] localhost 140484928199808 0[0:0#0]  0 [REDO] LOG:  Recovery parallelism, cpu count = 8, max = 8, actual = 8
2025-05-13 09:41:55.730 [unknown] [unknown] localhost 140484928199808 0[0:0#0]  0 [REDO] LOG:  ConfigRecoveryParallelism, true_max_recovery_parallelism:8, max_recovery_parallelism:8
gaussdb.state does not exist, and skipt setting since it is optional.2025-05-13 09:41:55.779 [unknown] [unknown] localhost 140484928199808 0[0:0#0]  0 [BACKEND] LOG:  [Alarm Module]can not read GAUSS_WARNING_TYPE env.2025-05-13 09:41:55.779 [unknown] [unknown] localhost 140484928199808 0[0:0#0]  0 [BACKEND] LOG:  [Alarm Module]Host Name: dmz-neo4j01 2025-05-13 09:41:55.780 [unknown] [unknown] localhost 140484928199808 0[0:0#0]  0 [BACKEND] LOG:  [Alarm Module]Host IP: 10.14.1.3 2025-05-13 09:41:55.780 [unknown] [unknown] localhost 140484928199808 0[0:0#0]  0 [BACKEND] LOG:  [Alarm Module]Get ENV GS_CLUSTER_NAME failed!2025-05-13 09:41:55.780 [unknown] [unknown] localhost 140484928199808 0[0:0#0]  0 [BACKEND] LOG:  [Alarm Module]Invalid data in AlarmItem file! Read alarm English name failed! line: 572025-05-13 09:41:55.784 [unknown] [unknown] localhost 140484928199808 0[0:0#0]  0 [BACKEND] LOG:  loaded library "security_plugin"
2025-05-13 09:41:55.785 [unknown] [unknown] localhost 140484928199808 0[0:0#0]  0 [BACKEND] WARNING:  could not create any HA TCP/IP sockets
2025-05-13 09:41:55.785 [unknown] [unknown] localhost 140484928199808 0[0:0#0]  0 [BACKEND] WARNING:  could not create any HA TCP/IP sockets
2025-05-13 09:41:55.786 [unknown] [unknown] localhost 140484928199808 0[0:0#0]  0 [BACKEND] WARNING:  No explicit IP is configured for listen_addresses GUC.
2025-05-13 09:41:55.787 [unknown] [unknown] localhost 140484928199808 0[0:0#0]  0 [BACKEND] LOG:  InitNuma numaNodeNum: 1 numa_distribute_mode: none inheritThreadPool: 0.
2025-05-13 09:41:55.787 [unknown] [unknown] localhost 140484928199808 0[0:0#0]  0 [BACKEND] LOG:  reserved memory for backend threads is: 220 MB
2025-05-13 09:41:55.787 [unknown] [unknown] localhost 140484928199808 0[0:0#0]  0 [BACKEND] LOG:  reserved memory for WAL buffers is: 128 MB
2025-05-13 09:41:55.787 [unknown] [unknown] localhost 140484928199808 0[0:0#0]  0 [BACKEND] LOG:  Set max backend reserve memory is: 348 MB, max dynamic memory is: 8715 MB
2025-05-13 09:41:55.787 [unknown] [unknown] localhost 140484928199808 0[0:0#0]  0 [BACKEND] LOG:  shared memory 11711 Mbytes, memory context 9063 Mbytes, max process memory 21287 Mbytes
2025-05-13 09:41:56.514 [unknown] [unknown] localhost 140484928199808 0[0:0#0]  0 [BACKEND] LOG:  Buffer pool start virtual address = 1404754890557442025-05-13 09:41:56.514 [unknown] [unknown] localhost 140484928199808 0[0:0#0]  0 [BACKEND] LOG:  Buffer pool end virtual address = 1404838881495042025-05-13 09:41:56.704 [unknown] [unknown] localhost 140484928199808 0[0:0#0]  0 [CACHE] LOG:  set data cache  size(402653184)
2025-05-13 09:41:56.725 [unknown] [unknown] localhost 140484928199808 0[0:0#0]  0 [CACHE] LOG:  set metadata cache  size(134217728)
2025-05-13 09:41:56.833 [unknown] [unknown] localhost 140484928199808 0[0:0#0]  0 [SEGMENT_PAGE] LOG:  Segment-page constants: DF_MAP_SIZE: 8156, DF_MAP_BIT_CNT: 65248, DF_MAP_GROUP_EXTENTS: 4175872, IPBLOCK_SIZE: 8168, EXTENTS_PER_IPBLOCK: 1021, IPBLOCK_GROUP_SIZE: 4090, BMT_HEADER_LEVEL0_TOTAL_PAGES: 8323072, BktMapEntryNumberPerBlock: 2038, BktMapBlockNumber: 25, BktBitMaxMapCnt: 512
2025-05-13 09:41:56.895 [unknown] [unknown] localhost 140484928199808 0[0:0#0]  0 [BACKEND] LOG:  vastbase: fsync file "/home/vastbase/data/vastbase/gaussdb.state.temp" success
2025-05-13 09:41:56.895 [unknown] [unknown] localhost 140484928199808 0[0:0#0]  0 [BACKEND] LOG:  create gaussdb state file success: db state(STARTING_STATE), server mode(Normal), connection index(1)
2025-05-13 09:41:56.920 [unknown] [unknown] localhost 140484928199808 0[0:0#0]  0 [BACKEND] LOG:  max_safe_fds = 975, usable_fds = 1000, already_open = 15
The core dump path is an invalid directory
2025-05-13 09:41:56.923 [unknown] [unknown] localhost 140484928199808 0[0:0#0]  0 [BACKEND] LOG:  user configure file is not found, it will be created.
2025-05-13 09:41:56.930 [unknown] [unknown] localhost 140484928199808 0[0:0#0]  0 [BACKEND] LOG:  the configure file /home/vastbase/local/vastbase_9104/etc/gscgroup_vastbase.cfg doesn't exist or the size of configure file has changed. Please create it by root user!
2025-05-13 09:41:56.930 [unknown] [unknown] localhost 140484928199808 0[0:0#0]  0 [BACKEND] LOG:  Failed to parse cgroup config file.
2025-05-13 09:41:56.959 [unknown] [unknown] localhost 140484928199808 0[0:0#0]  0 [EXECUTOR] WARNING:  Failed to obtain environment value $GAUSSLOG!
2025-05-13 09:41:56.959 [unknown] [unknown] localhost 140484928199808 0[0:0#0]  0 [EXECUTOR] DETAIL:  N/A
2025-05-13 09:41:56.959 [unknown] [unknown] localhost 140484928199808 0[0:0#0]  0 [EXECUTOR] CAUSE:  Incorrect environment value.
2025-05-13 09:41:56.959 [unknown] [unknown] localhost 140484928199808 0[0:0#0]  0 [EXECUTOR] ACTION:  Please refer to backend log for more details.
。
[2025-05-13 09:41:57.971][10224][][vb_ctl]:  完成
[2025-05-13 09:41:57.971][10224][][vb_ctl]: 服务端进程已经启动 (/home/vastbase/data/vastbase)
---------命令登录-----创建用户,授权,创建数据库表空间,数据库实例等---------
[vastbase@dmz-neo4j01 ~]$ vsql -U vastbase -r
vsql ((Vastbase G100 V2.2 (Build 10) Release) compiled at 2022-09-08 01:29:00 commit 9104 last mr  )
非SSL连接（安全性要求高时，建议使用SSL连接）
输入 "help" 来获取帮助信息。vastbase=# CREATE USER ciom WITH PASSWORD 'Ciom@2025';
CREATE ROLE
vastbase=# GRANT ALL PRIVILEGES TO ciom;
ALTER ROLE
vastbase=# CREATE TABLESPACE ciom_tbs OWNER ciom LOCATION '/home/vastbase/ciomdata';
CREATE TABLESPACE
vastbase=# CREATE DATABASE ciom  OWNER ciom TABLESPACE ciom_tbs  ENCODING 'UTF8';
CREATE DATABASE
-------首次使用需要修改密码
vastbase=#ALTER USER ciom IDENTIFIED BY 'P@ssw0rd' REPLACE 'Ciom@2025';

------日常操作小记如下

-- 1. 本地登录数据库（使用vsql工具）
vsql -d ciom -U vastbase
密码vastbase
vsql -d ciom -U ciomadmin
密码ciom@Pass123
vsql -d ciom -U ciom
密码P@ssw0rd
-- 2. 创建新用户并授予DBA权限
CREATE USER ciomadmin WITH PASSWORD 'ciom@Pass123' CREATEDB CREATEROLE SYSADMIN;-- 3. 验证权限
SELECT rolname, rolsystemadmin, rolcreaterole, rolcreatedb  FROM pg_roles WHERE rolname = 'ciomadmin';# 通过JDBC URL测试（替换实际IP和端口）
jdbc:postgresql://10.14.1.3:5432/ciom?user=ciomadmin&password=Secure@Pass123GRANT CONNECT ON DATABASE ciom TO ciomadmin;-- 连接到ciom数据库后执行
SELECT table_name  FROM information_schema.tables  WHERE table_schema = 'ciom'  AND table_type = 'BASE TABLE'; vsql -d ciomadmin -U ciomadminSELECT table_name  FROM information_schema.tables  WHERE table_schema = 'ciomadmin'  AND table_type = 'BASE TABLE'; ---创建ciom用户然后授权角色
CREATE USER ciom WITH  PASSWORD 'P@ssw0rd' LOGIN CREATEDB CREATEROLE;
---创建数据库ciom并授权​ciom
CREATE DATABASE ciom  OWNER ciomadmin  ENCODING 'UTF8'  TEMPLATE template0;
--------------本模板中使用的ciomadmin
GRANT ALL PRIVILEGES ON DATABASE ciom TO ciomadmin;
----权限检查
SELECT rolname, rolcreatedb, rolcreaterole  FROM pg_roles WHERE rolname = 'ciomadmin';-- 创建schema（若不存在）
CREATE SCHEMA ciomadmin;
GRANT USAGE ON SCHEMA ciomadmin TO ciomadmin;
CREATE SCHEMA quartz;
-- 授予schema操作权限
GRANT CREATE, USAGE ON SCHEMA ciomadmin TO ciomadmin;
GRANT CREATE, USAGE ON SCHEMA quartz TO ciomadmin;
-- 授予数据库级权限（若需跨库操作）
ALTER USER ciomadmin CREATEROLE CREATEDB;
-----------------------------------------------------------------------------------------
服务器su - vastbasevsql -d ciom -U ciomadmin -W 'ciom@Pass123'
- 生成 TRUNCATE 语句并执行
SELECT 'TRUNCATE TABLE ciomadmin.' || tablename || ' CASCADE;'  FROM pg_tables  WHERE schemaname = 'ciomadmin';-- 生成 DROP TABLE 语句
SELECT 'DROP TABLE IF EXISTS ciomadmin.' || tablename || ' CASCADE;' FROM pg_tables  WHERE schemaname = 'ciomadmin';
-------动态drop数据库表
vsql -d 数据库名 -c "SELECT 'DROP TABLE ciomadmin.'||tablename||' CASCADE;' FROM pg_tables WHERE schemaname='ciomadmin'" | vsql -d 数据库名
---------
vsql -d ciom -c "SELECT 'DROP TABLE ciomadmin.'||tablename||' CASCADE;' FROM pg_tables WHERE schemaname='ciomadmin'" | vsql -d ciom
----清库脚本---注意使用su - vastbase执行
vsql -d ciom -c "SELECT 'DROP TABLE ciomadmin.'||tablename||' CASCADE;' AS drop_sql FROM pg_tables WHERE schemaname='ciomadmin'" -t -o /tmp/drop_tables.sql# 生成 DROP TABLE 语句
vsql -d ciom -c "SELECT 'DROP TABLE ciomadmin.' || tablename || ' CASCADE;' AS drop_sql FROM pg_tables WHERE schemaname='ciomadmin' AND tablename LIKE '%esb%'" -t -o /tmp/drop_esb.sql# 执行生成的 SQL
vsql -d ciom -f /tmp/drop_esb.sql

--------------------------------20250514------------------
su - vastbase
vsql -d ciom -U ciomadmin -W 'ciom@Pass123'

一、清理旧环境（若存在）​​
​​1. 终止数据库活跃连接​

-- 终止所有与 ciom 用户相关的会话
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename = 'ciom';

[vastbase@dmz-neo4j01 ~]$ vsql -d ciom -U ciomadmin -W 'ciom@Pass123'
vsql ((Vastbase G100 V2.2 (Build 10) Release) compiled at 2022-09-08 01:29:00 commit 9104 last mr  )
非SSL连接（安全性要求高时，建议使用SSL连接）
输入 "help" 来获取帮助信息。
ciom=> SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename = 'ciom';pg_terminate_backend 
----------------------
(0 行记录)

2. 转移数据库所有权​

-- 将数据库所有权转移至新用户（如 ciomadmin）
ALTER DATABASE dbaciom OWNER TO ciomadmin;
ALTER DATABASE ciom OWNER TO ciomadmin;

ciom=> ALTER DATABASE dbaciom OWNER TO ciomadmin;
ALTER DATABASE
ciom=> ALTER DATABASE ciom OWNER TO ciomadmin;
ALTER DATABASE

3.转移表空间所有权​
-- 转移表空间所有权至新用户
ALTER TABLESPACE ciom_tbs OWNER TO ciomadmin;

ciom=> ALTER TABLESPACE ciom_tbs OWNER TO ciomadmin;
ALTER TABLESPACE

4. 清理用户默认权限​
-- 查看并清除用户的默认权限设置
SELECT pg_get_userbyid(defaclrole) AS user,defaclobjtype AS object_type,defaclacl AS privileges FROM pg_default_acl WHERE defaclrole = (SELECT oid FROM pg_roles WHERE rolname = 'ciom');

ciom=> SELECT pg_get_userbyid(defaclrole) AS user,defaclobjtype AS object_type,defaclacl AS privileges FROM pg_default_acl WHERE defaclrole = (SELECT oid FROM pg_roles WHERE rolname = 'ciom');user | object_type | privileges 
------+-------------+------------
(0 行记录)

5. 级联删除用户对象​
-- 强制删除用户拥有的所有对象（谨慎操作）
DROP OWNED BY ciom CASCADE;

ciom=> DROP OWNED BY ciom CASCADE;
DROP OWNED

6.检查剩余依赖​

-- 查询 pg_shdepend 确认无残留依赖
SELECT classid::regclass AS 对象类型,pg_describe_object(classid, objid, 0) AS 对象描述,deptype AS 依赖类型 FROM pg_shdepend WHERE refobjid = (SELECT oid FROM pg_roles WHERE rolname = 'ciom');
-- 手动删除 pg_shdepend 中无效记录
DELETE FROM pg_shdepend WHERE refobjid = (SELECT oid FROM pg_roles WHERE rolname = 'ciom') AND classid = 'pg_namespace'::regclass  AND objid = 16547;

[vastbase@dmz-neo4j01 ~]$ vsql -d vastbase -U vastbase -W 'vastbase'
vsql ((Vastbase G100 V2.2 (Build 10) Release) compiled at 2022-09-08 01:29:00 commit 9104 last mr  )
非SSL连接（安全性要求高时，建议使用SSL连接）
输入 "help" 来获取帮助信息。vastbase=# DELETE FROM pg_shdepend WHERE refobjid = (SELECT oid FROM pg_roles WHERE rolname = 'ciom') AND classid = 'pg_namespace'::regclass  AND objid = 16547;
DELETE 1

 

7.删除用户
-- 删除用户所有权限及关联对象
DROP OWNED BY ciom CASCADE;

-- 删除用户
DROP USER ciom;

vastbase=# DROP OWNED BY ciom CASCADE;
ERROR:  syntax error at or near "CASCADE"
第1行DROP FUNCTION IF EXISTS your_function CASCADE;^
DROP OWNED
vastbase=# DROP USER ciom;
DROP ROLE

终止活跃链接数
-- 终止所有与 ciom 数据库相关的会话
SELECT pg_terminate_backend(pid) FROM pg_stat_activity  WHERE datname = 'ciom' AND pid <> pg_backend_pid();

vastbase=# SELECT pg_terminate_backend(pid) FROM pg_stat_activity  WHERE datname = 'ciom' AND pid <> pg_backend_pid();pg_terminate_backend 
----------------------tttttttttttttttt
(16 行记录)

二.删除 ciomadmin Schema​
-- 级联删除 Schema 及其所有对象
DROP SCHEMA IF EXISTS ciomadmin CASCADE;

vastbase=# DROP SCHEMA IF EXISTS ciomadmin CASCADE;
NOTICE:  schema "ciomadmin" does not exist, skipping
DROP SCHEMA

-- 强制删除数据库
DROP DATABASE IF EXISTS ciom WITH (FORCE);

vastbase=# DROP DATABASE IF EXISTS ciom WITH (FORCE);
ERROR:  syntax error at or near "WITH ("
第1行DROP DATABASE IF EXISTS ciom WITH (FORCE);^
vastbase=# 

 重新创建数据库ciom 且进行授权操作

-----------重新创建数据库及用户
-- 创建用户并授权
CREATE USER ciom WITH PASSWORD 'P@ssw0rd';
CREATE DATABASE ciom OWNER ciom;
GRANT ALL PRIVILEGES ON DATABASE ciom TO ciom;

vastbase=# CREATE USER ciom WITH PASSWORD 'P@ssw0rd';
CREATE ROLE
vastbase=# CREATE DATABASE ciom OWNER ciom;
CREATE DATABASE
vastbase=# GRANT ALL PRIVILEGES ON DATABASE ciom TO ciom;
GRANT

-- 创建 Schema
\c ciom
CREATE SCHEMA ciom AUTHORIZATION ciom;
CREATE SCHEMA quartz AUTHORIZATION ciom;

vastbase=# \c ciom
非SSL连接（安全性要求高时，建议使用SSL连接）
您现在已经连线到数据库 "ciom",用户 "vastbase".
ciom=# CREATE SCHEMA ciom AUTHORIZATION ciom;
CREATE SCHEMA
ciom=# CREATE SCHEMA quartz AUTHORIZATION ciom;
CREATE SCHEMA