openGauss数据库实验操作手册：SQL、权限、事务与备份恢复

简介面向计算机科学与技术、软件工程专业学生的《数据库系统原理实验》课程配套报告围绕 openGauss 平台展开九个实验的完整实践记录可用于课程实验提交参考、期末复盘与数据库操作入门训练。压缩包内仅含 1 个 docx 文件大小约 3.23MB正文按实验目的、内容、类型、学时与仪器材料等栏目组织并附有关键操作截图与结果分析目录从认识 DBMS、创建数据库表和索引、交互式 SQL、视图、完整性控制一直延伸到安全性控制、事务与并发控制、备份恢复及 JDBC 连接数据库。已有 1525 人学习适合需要对照实验步骤、梳理 openGauss 常用命令、理解权限与并发机制或补齐报告排版的读者参考也可作为教师检查实验覆盖面的样例。报告中的实验步骤、命令示例与问题更正记录相互对应便于按章节检索和二次整理。1. 一份数据库实验报告为什么值得当成操作手册读很多人对“数据库实验报告”的第一印象是交作业用的模板抄一遍 SQL 就归档。但这份以 openGauss 为主线的报告书不太一样九次实验被串成一条从装库、建表、写查询到权限、事务、备份、JDBC 编程的完整链路表空间、schema、约束、视图、角色、锁每一环都留了能直接粘贴执行的 gsql 命令和 SQL 语句。它解决的不是“数据库是什么”而是“第一条命令从哪敲、报错去哪查”。适合三类人要完整复现一遍数据库实验的学生、想把实验改造成课程设计的助教以及从 MySQL 转过来、想快速摸清 openGauss 差异的开发者。数据库这门课光看理论不动手考完就忘照着这份报告走一遍至少能把 SQL 从纸面搬到终端里。2. openGauss 起步从 gsql 连上库到把表空间、数据库、schema 立起来这一章对应报告里的实验一和实验二是整个后续工作的地基。地基没打对后面建视图、加约束、开事务全都会连锁报错而且报错信息往往指向的是表层现象真正的原因在更早的一步。2.1 三种装法怎么选第一次连库该敲哪几条报告里列了 openGauss 的三种安装模式云上安装、虚拟机一键安装、先在 openEuler 上装好系统再装数据库。我的建议很直接——如果是本地练习或者课程实验走虚拟机一键安装如果是要验证部署流程本身才值得折腾第三种。第一种最省事但你对实例目录、端口、安装用户的感知会被平台隐藏掉后面排查问题时容易变成黑匣子。装完之后所有操作都要先切到安装时创建的数据库操作系统用户默认是omm这一点和 MySQL 直接用 root 敲命令完全不同很多人第一次连不上就是因为还在自己的普通用户下。# 切换到数据库安装用户openGauss 安装后默认生成不加 - 只切目录不切环境 su - omm # 启动数据库实例-t 后跟动作start 为启动stop 为停止status 查状态 gs_om -t start # 连接默认库 postgres26000 是数据库主节点端口-r 开启逃逸字符转义 gsql -d postgres -p 26000 -rgs_om是 openGauss 的集群管理工具-t是它的动作参数除了 start/stop还用-t status看实例是否在跑。gsql是命令行客户端和 PostgreSQL 的 psql 同源-d指定库名-p指定端口-r让反斜线转义生效。这里有个容易被忽略的点su - omm中间的横杠不能省省掉之后环境变量不会重新加载gsql可能直接提示 command not found。连上之后默认落在postgres库。这个库是安装时自动生成的可以拿它当跳板去建新库但不要往里塞业务表——报告里的做法是新建独立的数据库和表空间这个习惯值得保留。2.2 表空间、数据库、schema三层容器的创建顺序openGauss 的容器层级是「表空间 → 数据库 → schema → 表」顺序反了就会报依赖错误。报告里用tpcds_local做表空间名我按同样的层级重写一遍顺便补上几个正文里没细说但一定会踩的参数。-- 表空间必须指向一个已经存在的空目录路径相对于实例的数据目录 CREATE TABLESPACE tpcds_local RELATIVE LOCATION tablespace/tablespace_1; -- 建库时挂到指定表空间上不写 WITH TABLESPACE 就落到默认表空间 CREATE DATABASE db_sc WITH TABLESPACE tpcds_local; -- 库改名注意改名不会改变库的 OID已有连接不受影响 ALTER DATABASE db_sc RENAME TO db_school;RELATIVE LOCATION是相对路径写法相对于实例的数据目录也可以写LOCATION /绝对路径但绝对路径的目录所有者必须是数据库运行用户否则报权限错误。目录不存在会直接失败CREATE TABLESPACE不会帮你创建目录这一点和某些数据库的自动化行为不同。建完库要切换过去元命令\c db_school是最短路径。切成 SQL 的话就是重新执行gsql -d db_school -p 26000。切换之后建 schema-- 在当前库里创建一个独立命名空间避免和 public 下的对象混在一起 CREATE SCHEMA book_eg; -- 在 schema 下建对象必须带前缀或者先 SET search_path CREATE TABLE book_eg.teachers (tno CHAR(20) PRIMARY KEY); -- 级联删除schema 下的对象一并删掉不加 CASCADE 会因非空而失败 DROP SCHEMA book_eg CASCADE;schema这个概念在报告里辨析得很清楚它既是概念层的一组 DDL 语句集也是物理层的一个命名空间。落到实操上你只需要记住一点——建对象时要么写全schema.table要么执行SET search_path TO book_eg, public;否则对象默认建在public下后面\dt找不到表就是这么来的。2.3 六张表怎么落约束写在列级还是表级报告里建的是教学管理场景的六张表Teachers、Departments、Students、Courses、SC、Teaches。这套表的难点不在于字段多而在于 Teachers 和 Departments 互相引用——教师表里有院系号院系表里有系主任工号。这种环状参照如果一次性写进 CREATE TABLE无论先建哪张都会因为对方不存在而失败。我的处理顺序是先建不带外键的骨架再用ALTER TABLE补约束。报告里也是这么做的正文里那句「由于关系中存在相互参照需要通过修改表添加表级约束」说的就是这个。-- 先建表只保留主键、非空、CHECK 这类不依赖其他表的约束 CREATE TABLE Teachers ( Tno CHAR(20) PRIMARY KEY, Tname CHAR(20) NOT NULL, Sex VARCHAR(2) CHECK (Sex IN (男,女)), Birthday DATE, Title CHAR(20), Dno CHAR(20) ); CREATE TABLE Departments ( Dno CHAR(20) PRIMARY KEY, Dname CHAR(20), Dheadno CHAR(20) ); -- 外键后补ON DELETE 决定主表删行时从表怎么办 ALTER TABLE Departments ADD CONSTRAINT fk_dept_head FOREIGN KEY (Dheadno) REFERENCES Teachers(Tno) ON DELETE SET NULL ON UPDATE CASCADE; ALTER TABLE Teachers ADD CONSTRAINT fk_teacher_dept FOREIGN KEY (Dno) REFERENCES Departments(Dno) ON DELETE SET NULL;ON DELETE有四个选项NO ACTION默认有引用就拒绝删除、RESTRICT立即拒绝、CASCADE级联删除从表行、SET NULL把从表外键置空。这里选SET NULL是因为系主任离职不应该连带删掉整个院系。ON UPDATE CASCADE则是工号变更时自动同步教学场景里工号一般不变写上属于防御性设计。列级约束和表级约束的区别落到实操就一句话涉及多列的约束必须写成表级。单列约束两种写法都能过但列级约束没法起名字出问题时错误信息里只有一个系统生成的名字排查起来很费劲。我在建表时习惯把所有约束都写成表级并显式命名代价是多敲几行收益是\d 表名时一眼能看出是哪个约束在拦数据。2.4 索引主键自动建其余自己来DBMS 会自动为主码建索引这一点报告里写明了。除此之外的索引要手动建比如学生表的院系号字段因为按院系查学生是高频操作。-- 在 Students 的 Dno 上建普通索引命名带上前缀便于识别 CREATE INDEX idx_students_dno ON Students(Dno); -- \di 列出当前库所有索引包括系统自动生成的主键索引 \di索引不是越多越好。每建一个索引插入和更新都要多维护一棵 B 树写多读少的表上堆索引纯属给自己挖坑。判断标准很朴素这个字段会不会出现在 WHERE、JOIN ON 或者 ORDER BY 里会就建纯粹为了展示的话不建。另外注意CHAR(20)这种定长类型建出来的索引体积比VARCHAR大字段长度别随手写 20按实际需求给。3. 交互式 SQL 与视图把查询写成能验收的脚本实验三和实验四的重点是交互式查询和视图。这两块单独看都不难难在把它们组织成一个能重复执行、出错能定位的脚本而不是在终端里一条条手敲、敲错了往上翻历史。3.1 元命令与系统表查库、查表、查索引的两条路gsql 的元命令是提效的关键报告里列了一串\l列数据库、\dt列表、\d 表名看表属性、\di看索引、\dn看 schema、\db看表空间。这些命令背后其实都是查系统表知道对应关系之后你就能把结果接进脚本做自动化。元命令等价系统表查询用途\lSELECT datname FROM pg_database;列出所有数据库\dbSELECT spcname FROM pg_tablespace;列出所有表空间\dnSELECT nspname FROM pg_namespace;列出所有 schema\duSELECT * FROM pg_roles;列出角色与权限\dtSELECT * FROM pg_tables;列出当前 schema 的表系统表查询的好处是可以加条件、可以导出、可以嵌进应用代码。比如要统计某个 schema 下每张表的行数估算用pg_class.reltuples一条 SQL 就能出结果比挨个count(*)快得多。-- 查当前库里所有用户表的估算行数reltuples 是统计信息里的估值 SELECT relname, reltuples FROM pg_class c JOIN pg_namespace n ON n.oid c.relnamespace WHERE c.relkind r AND n.nspname book_eg;relkind r表示普通表排除视图、索引、序列。reltuples是 ANALYZE 之后的估算值不是精确行数但对判断表规模够用了。要精确值还是得count(*)大表上这个操作很贵别在验收现场跑。3.2 视图能建不等于能改可更新性有边界视图在报告里的定位是「数据的抽象化」。落到实操建视图本身没什么难度坑在更新。-- 单表视图带 WHERE 条件默认允许通过视图做 INSERT/UPDATE/DELETE CREATE VIEW v_teachers_cs AS SELECT Tno, Tname, Title, Dno FROM Teachers WHERE Dno D01; -- WITH CHECK OPTION 保证通过视图写入的行仍然满足视图条件 CREATE VIEW v_teachers_cs_checked AS SELECT Tno, Tname, Title, Dno FROM Teachers WHERE Dno D01 WITH CHECK OPTION;WITH CHECK OPTION的作用是拦截“写进去就看不见”的数据。比如往v_teachers_cs_checked里插一条Dno D02的记录会被直接拒绝不加这个选项的话插入成功但视图里查不到排查时非常迷惑。多表连接的视图可更新性要弱得多。含 JOIN、聚合函数、DISTINCT、GROUP BY、UNION 的视图大多数情况下只能查不能改具体能不能改取决于数据库版本和视图定义。工程上的稳妥做法是视图只用来读写操作回到基表。非要通过视图写先在小数据量上验证一遍别在验收时才发现 update 影响行数是 0。-- 多表连接视图用于报表展示不要指望它能更新 CREATE VIEW v_student_dept AS SELECT s.Sno, s.Sname, d.Dname, s.Enrollyear FROM Students s JOIN Departments d ON s.Dno d.Dno;3.3 把实验脚本组织成能重跑的文件在终端里手敲 SQL 有两个后果一是敲错了要重来二是没法证明自己真的跑过。我的习惯是把每个实验写成独立的.sql文件用 gsql 的-f参数批量执行再配ON_ERROR_STOP让脚本遇到第一个错误就停下。#!/usr/bin/env bash set -euo pipefail # GAUSSDB_PASSWORD 从环境变量读不要硬编码在脚本里 gsql -d db_school -p 26000 -U omm -W ${GAUSSDB_PASSWORD} \ -v ON_ERROR_STOP1 \ -f ./lab03_query.sql-v ON_ERROR_STOP1是关键参数默认情况下 gsql 执行脚本遇到错误会继续往下跑最后回滚或留下半截数据日志里一堆 ERROR 但脚本退出码是 0自动化流水线里这就是个定时炸弹。-W传密码注意有的版本要求密码写在等号后面不带空格。-U指定用户名不指定就是当前操作系统用户同名账号。脚本内部建议在开头统一设置搜索路径和输出格式-- 固定 search_path避免对象建到 public 下 SET search_path TO book_eg, public; -- 让查询结果带列名、按列对齐导出时好看 \pset border 2 \pset format aligned -- 关键查询之间加分隔方便肉眼比对 \echo --- 查询选修了数据库课程的学生 --- SELECT s.Sno, s.Sname FROM Students s JOIN SC ON s.Sno SC.Sno JOIN Courses c ON SC.Cno c.Cno WHERE c.Cname 数据库系统原理;\pset系列是输出格式控制\echo在结果里插分隔行。这些小命令不改变查询逻辑但能让你在几十页输出里快速定位到想看的段落把报告截图的工作量砍掉一半。4. 完整性、安全性与并发控制三类控制的参数与验收点实验五到实验七是整份报告里最抽象的部分。完整性和安全性还好都是 DDL 和 DCL敲下去立刻有反馈事务与并发控制就不一样了很多现象要开两个会话才能观察到单会话永远是“一切正常”。4.1 完整性四类约束的触发时机不一样完整性控制落到 SQL 上就是约束主键实体完整性、外键参照完整性、NOT NULL 和 CHECK 和 UNIQUE用户定义完整性。四类约束的触发时机有差别搞清楚这个差别排查时能省不少时间。主键和 UNIQUE 在插入、更新时检查冲突立即报错。NOT NULL 也是即时检查。外键分两种时机插入从表时检查主表是否存在对应行删除主表时检查从表是否有引用。CHECK 在行写入前求值值为 UNKNOWN 时放行——这一条最容易误解CHECK (Grade BETWEEN 0 AND 100)遇到Grade IS NULL时结果是 UNKNOWN不等于 FALSE所以不会拦截。-- 建表时加 CHECK注意 NULL 会绕过检查 ALTER TABLE SC ADD CONSTRAINT ck_sc_grade CHECK (Grade BETWEEN 0 AND 100); -- 想连 NULL 一起拦就得显式写出来 ALTER TABLE SC DROP CONSTRAINT IF EXISTS ck_sc_grade, ADD CONSTRAINT ck_sc_grade CHECK (Grade IS NOT NULL AND Grade BETWEEN 0 AND 100);DROP CONSTRAINT IF EXISTS让脚本可以重复执行这在反复调试约束条件时很实用。不加的话第二次执行报“约束已存在”脚本就断了。另一个容易被忽略的点是约束的验证开销。给一张有几十万行的表加 CHECK 约束openGauss 会扫描全表验证现有数据期间持有较重的锁。实验数据量小感觉不到真到生产环境要在低峰期做。4.2 安全性用户、角色、GRANT 三件套openGauss 的权限模型是 RBAC用户是具体账号角色是权限集合把角色授给用户用户就获得这批权限。报告里辨析过这一点落到操作上要注意CREATE USER和CREATE ROLE的默认差异——前者默认带 LOGIN 属性后者默认不能登录。-- 建一个只能读的角色NOLOGIN 是默认值显式写出来更清楚 CREATE ROLE r_select NOLOGIN; -- 建一个具体账号密码要满足复杂度策略 CREATE USER u_read WITH PASSWORD Read2024abc; -- 先给库级连接权限没有这一步后面全白搭 GRANT CONNECT ON DATABASE db_school TO r_select; -- 再给 schema 的使用权限缺这个也查不了表 GRANT USAGE ON SCHEMA book_eg TO r_select; -- 表级 SELECT 权限 GRANT SELECT ON ALL TABLES IN SCHEMA book_eg TO r_select; -- 把角色授给用户用户继承角色的全部权限 GRANT r_select TO u_read;这段的顺序不能乱CONNECT → USAGE ON SCHEMA → 表权限。很多人只授了表权限就去连库结果连不上或者提示表不存在原因就是缺前两级。GRANT ... ON ALL TABLES只对当前已存在的表生效之后新建的表不会自动继承要在建表后补授或者用默认权限ALTER DEFAULT PRIVILEGES。密码复杂度是另一个高频卡点。openGauss 默认的密码策略要求长度不低于 8、同时包含大小写字母、数字和特殊字符且不能和用户名相同。随手写个123456会直接报错报错信息里会说明违反了哪条规则。回收权限用REVOKE语法和GRANT对称要临时禁用账号用ALTER USER u_read ACCOUNT LOCK;比删用户再重建安全得多。4.3 事务与并发单会话看不到的那些现象并发控制的实验必须开两个终端。一个会话开事务改数据不提交另一个会话去读同一行才能观察到锁等待。这类实验在单会话里永远跑不出效果我第一次做的时候在这卡了很久。-- 会话 A BEGIN; UPDATE SC SET Grade Grade 5 WHERE Sno 2021001 AND Cno C01; -- 此处先不提交切到会话 B -- 会话 B这条语句会被阻塞直到 A 提交或回滚 UPDATE SC SET Grade Grade 5 WHERE Sno 2021001 AND Cno C01;会话 B 的 update 会一直挂着直到会话 A 执行COMMIT或ROLLBACK。想验证是不是锁等待而不是语句写错了另开一个会话查锁-- 查看当前所有锁mode 列显示锁类型granted 为 false 表示在等 SELECT locktype, relation::regclass, mode, granted, pid FROM pg_locks WHERE NOT granted; -- 查看活跃会话state 为 active 且 wait_status 非空的就是在等锁 SELECT pid, state, wait_status, query FROM pg_stat_activity WHERE state idle;pg_locks里granted false的行就是处于等待状态的锁请求relation::regclass把 OID 转成表名可读性好很多。pg_stat_activity的wait_status字段会显示具体在等什么。这两个视图结合起来基本能定位所有“卡住了”的场景。死锁的处理由数据库自动完成openGauss 有一个deadlock_timeout参数默认 1 秒超过这个时间还没拿到锁就触发死锁检测检测到环就牺牲其中一个事务并报错。隔离级别用SET TRANSACTION ISOLATION LEVEL设置openGauss 支持 READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。实验里最直观的验证方式是在 REPEATABLE READ 下开事务另一个会话改数据并提交回到第一个会话再查读到的还是旧值。这个现象在 READ COMMITTED 下不会出现对比着跑一遍就记住了。注意实验结束一定要把事务提交或回滚挂着一个未结束的事务后面所有涉及同一行的操作都会排队。5. 排查记录这套实验里最容易翻车的五个地方下面五条都是照着报告实操时会真实撞上的问题每条按现象、原因、解决三个层次写。遇到报错别急着重装环境先对照一遍。5.1 建表时外键报“引用的关系不存在”现象写CREATE TABLE Departments (... FOREIGN KEY (Dheadno) REFERENCES Teachers(Tno))报错说 Teachers 不存在或者约束无法创建。原因Teachers 和 Departments 互相引用无论先建哪张对方都还没落地。这是环状参照的必然结果不是语法问题。解决拆成两步。先建两张不含外键的表再用ALTER TABLE ... ADD CONSTRAINT补。如果表已经建好了只是漏了约束同样用 ALTER 补不要 DROP 重建重建会丢数据。5.2 CREATE USER 反复失败提示密码复杂度不满足现象CREATE USER u1 WITH PASSWORD 123456;报错换了好几个密码都不行。原因openGauss 默认启用了密码复杂度校验要求长度、大小写、数字、特殊字符同时满足且不能包含用户名。纯数字、纯字母、长度不够都会被拒。解决用形如Read2024abc的组合长度 8 位以上特殊字符放中间。如果只是本地练习想放宽策略可以调整密码策略参数但报告提交环境一般不建议改容易被判为不合规。5.3 CREATE TABLESPACE 报目录不存在或权限不足现象执行建表空间语句提示路径无效或者没有权限。原因RELATIVE LOCATION的路径是相对实例数据目录的目录必须事先存在用绝对路径时目录所有者必须是数据库运行用户通常是 omm。解决先用mkdir建目录并chown omm:omm改属主再执行建表空间。相对路径要注意是相对数据目录而不是当前工作目录拿不准就用绝对路径。5.4 脚本跑了一半没报错但后面的表全是空的现象用gsql -f执行实验脚本屏幕上刷过一堆输出最后表里没数据。原因gsql 默认遇到错误继续执行。前面某条语句失败比如 schema 不存在后续 INSERT 全部作用在错误的上下文里最后看起来“跑完了”其实什么也没做。解决脚本执行时加-v ON_ERROR_STOP1让第一个错误就中断。另外在脚本头部显式SET search_path别依赖默认值。5.5 第二个会话一直卡住以为数据库挂了现象开了两个终端做并发实验会话 B 的 update 一直没有返回等了几分钟也没动静。原因会话 A 执行了 BEGIN UPDATE 但没提交持有行锁会话 B 在等同一行。这不是死锁是正常的锁等待数据库不会主动超时。解决回会话 A 执行COMMIT或ROLLBACK。若找不到是哪个会话持有锁查pg_locks里granted false的记录用pg_terminate_backend(pid)强制结束那个连接。养成习惯每开一个事务先想好什么时候结束它。6. 备份恢复与 JDBC验收前我固定跑的三步最后两个实验是备份恢复和 JDBC 连接前者决定数据丢了能不能救回来后者决定程序能不能连上库。这两个环节的共同点是光看文档觉得很简单真跑一遍全是参数对齐的问题。备份用gs_dump逻辑备份导出的是 SQL 文本或自定义归档。参数里最容易错的是-F格式和-p端口参数含义常见取值-F输出格式p纯文本、c自定义归档、ttar-f输出文件路径绝对路径更稳妥-p数据库端口默认 26000-n只导指定 schema如book_eg-s只导结构不导数据建表脚本专用# 导出自定义格式恢复时可用 gs_restore 选择性还原 gs_dump -U omm -W ${GAUSSDB_PASSWORD} -p 26000 \ -F c -f /backup/db_school_$(date %Y%m%d).dump db_school # 恢复前先建空库gs_restore 不会帮你建库 gs_restore -U omm -W ${GAUSSDB_PASSWORD} -p 26000 \ -d db_school_restore /backup/db_school_20240601.dump恢复顺序不能反先建空库再 restore。直接往有数据的库里灌主键冲突会让整个恢复中断。纯文本格式-F p用gsql -f灌回去自定义格式必须用gs_restore格式和工具对不上会报“文件头无法识别”。JDBC 这边驱动包版本必须和数据库版本匹配URL 前缀是jdbc:opengauss://不是jdbc:postgresql://。连接串里端口写错、库名写错、驱动没放进 classpath报的错分别是“连接被拒绝”“数据库不存在”“No suitable driver”三个错误信息指向三个完全不同的排查方向别混为一谈。// 加载 openGauss 驱动注意是 org.opengauss 包名 Class.forName(org.opengauss.Driver); // 连接串主机、端口 26000、库名三处任一写错都连不上 String url jdbc:opengauss://127.0.0.1:26000/db_school; try (Connection conn DriverManager.getConnection(url, omm, password); PreparedStatement ps conn.prepareStatement( SELECT Sname FROM Students WHERE Dno ?)) { ps.setString(1, D01); // 用占位符不要拼字符串避免注入也避免类型问题 try (ResultSet rs ps.executeQuery()) { while (rs.next()) System.out.println(rs.getString(1)); } }从那以后我每次交实验报告前都强制按「备份 → 删库 → 恢复 → JDBC 查一条数据」这个顺序走一遍因为只有走完全流程才能证明这套脚本不是靠环境残留蒙混过关的。希望帮到你。本文还有配套的精品资源点击获取