第03篇_Gauss

第01章_Gauss概述

https://docs-opengauss.osinfra.cn/zh/docs/5.0.0/docs/AboutopenGauss/%E5%85%B3%E4%BA%8EopenGauss.html

第一节 Gauss简介

1. openGauss和GaussDB

openGauss是华为基于PostgreSQL代码研发的分布式关系型数据库，采用木兰宽松许可证V2完全开源 ，而GaussDB在OpenGauss基础上添加了额外的专有功能和技术支持，包括更加强大的性能优化、更高的安全性和可靠性等，适用于要求更高的企业级应用场景。

第二节 安装部署

1. 安装须知

1) 运行环境

• 支持ARM服务器和基于x86_64的通用PC服务器。

• 支持openEuler 20.03LTS、麒麟V10、CentOS 7.6等操作系统。

2) 硬件要求

• CPU：开发测试环境2~4核，功能调试最小1×8核2.0GHz，性能测试和商业部署时，建议1×16核2.0GHz。

• 内存：开发测试环境4~8GB，功能调试建议32GB以上，性能测试和商业部署时，单实例部署建议128GB以上，在复杂查询较多的场景应适当提高内存。

• 硬盘：建议系统盘配置为Raid1，数据盘配置为Raid5，同时设置Disk Cache Policy为Disabled。

3) 技术指标

• 单表最大32TB，最大行数为2的32次方，单行数据或单个字段最大1GB，数据库总容量受限于操作系统与硬件。

• 数据库名及其它对象名长度最长63个字符。

• 最大支持10000个连接。

• 最大支持256个分片。

2. 部署方案

• 支持集中式模式或分布式模式，支持单机部署、主备模式、同城双活、两地三中心(异地灾备)、资源池化架构等部署方案。

• https://docs-opengauss.osinfra.cn/zh/docs/5.0.0/docs/DatabaseAdministrationGuide/openGauss常见主备部署方案简介.html

3. 安装步骤

1) 单机服务器安装

详细安装步骤可查阅官方文档，摘选单节点服务器安装重要步骤如下：

62
1
-- 0. 修改操作系统配置(root用户)
2
-- https://docs-opengauss.osinfra.cn/zh/docs/5.0.0/docs/InstallationGuide/%E5%87%86%E5%A4%87%E8%BD%AF%E7%A1%AC%E4%BB%B6%E5%AE%89%E8%A3%85%E7%8E%AF%E5%A2%83.html
3
-- 关闭操作系统防火墙(目前仅支持在防火墙关闭的状态下进行安装)
4
systemctl disable firewalld.service
5
systemctl stop firewalld.service
6
vim /etc/selinux/config
7
SELINUX=disabled
8
reboot
9
systemctl status firewalld
10
-- 设置字符集参数
11
vim /etc/profile
12
export LANG=XXX
13
-- 设置时区和时间(先使用date命令确认时区是否已经一致)
14
cp /usr/share/zoneinfo/$地区/$时区 /etc/localtime
15
date -s "Sat Sep 27 16:00:07 CST 2020"
16
-- 关闭swap交换内存(可选)
17
-- 关闭RemoveIPC(待定)
18
-- 关闭HISTORY记录(待定)
19

20
-- 1. 下载安装包
21
https://opengauss.org/zh/download/
22

23
-- 2. 安装软件依赖(具体版本要求见官方文档)
24
libaio-devel、flex、bison、ncurses-devel、glibc-devel、patch、redhat-lsb-core、readline-devel等
25

26
-- 3. 创建用户组和用户
27
groupadd dbgroup
28
useradd -g dbgroup omm
29
passwd omm
30

31
-- 6. 解压安装包(以omm用户登录，/opt/software/openGauss为建议安装目录)
32
tar -jxf openGauss-x.x.x-操作系统-64bit.tar.bz2 -C /opt/software/openGauss
33

34
-- 7. 执行安装脚本(下面xxxx为初始化数据库密码)
35
cd /opt/software/openGauss/simpleInstall
36
sh install.sh -w xxxx 
37

38
-- 8. 检查是否安装完成
39
ps ux | grep gaussdb
40
gs_ctl query -D /opt/software/openGauss/data/single_node
41

42
-- ps命令输出参考：
43
omm      24209 11.9  1.0 1852000 355816 pts/0  Sl   01:54   0:33 /opt/software/openGauss/bin/gaussdb -D /opt/software/openGauss/single_node
44
omm      20377  0.0  0.0 119880  1216 pts/0    S+   15:37   0:00 grep --color=auto gaussdb
45

46
-- gs_ctl命令输出参考：
47
gs_ctl query ,datadir is /opt/software/openGauss/data/single_node
48
HA state:
49
    local_role                     : Normal
50
    static_connections             : 0
51
    db_state                       : Normal
52
    detail_information             : Normal
53

54
Senders info:
55
    No information
56

57
 Receiver info:
58
No information 
59

60
-- 9. 配置服务端远程连接
61
https://docs-opengauss.osinfra.cn/zh/docs/5.0.0/docs/GettingStarted/%E9%85%8D%E7%BD%AE%E6%9C%8D%E5%8A%A1%E7%AB%AF%E8%BF%9C%E7%A8%8B%E8%BF%9E%E6%8E%A5.html
62

注：

1. 数据库安装完成后，默认生成名称为postgres的数据库。

2. 通过脚本安装方式，在单台物理机只允许部署一个数据库系统，如需在单台物理机部署多个(不推荐)，则需要通过命令行安装。

3. 商业部署环境可联系华为工程师安装！

2) 其它安装

• 一主一备安装：https://docs-opengauss.osinfra.cn/zh/docs/5.0.0/docs/InstallationGuide/%E4%B8%80%E4%B8%BB%E4%B8%80%E5%A4%87%E8%8A%82%E7%82%B9%E5%AE%89%E8%A3%85.html

• 容器安装：https://docs-opengauss.osinfra.cn/zh/docs/5.0.0/docs/InstallationGuide/%E5%8D%95%E8%8A%82%E7%82%B9%E5%AE%89%E8%A3%85_%E5%AE%B9%E5%99%A8.html

• RPM安装(openEuler 22.03 LTS)：https://docs-opengauss.osinfra.cn/zh/docs/5.0.0/docs/InstallationGuide/RPM%E5%AE%89%E8%A3%85.html

• 企业版安装：https://docs-opengauss.osinfra.cn/zh/docs/5.0.0/docs/InstallationGuide/%E4%BC%81%E4%B8%9A%E7%89%88%E5%AE%89%E8%A3%85.html

3) 卸载

https://docs-opengauss.osinfra.cn/zh/docs/5.0.0/docs/InstallationGuide/%E5%8D%B8%E8%BD%BDopenGauss.html

4. 数据库启停

21
1
-- 启动/关闭数据库
2
gs_om -t start
3
gs_om -t stop
4

5
-- 查询openGauss状态
6
-- https://docs-opengauss.osinfra.cn/zh/docs/5.0.0/docs/DatabaseOMGuide
7
--       /%E6%9F%A5%E7%9C%8B%E7%8A%B6%E6%80%81.html
8
gs_om -t status --detail 
9
[   Cluster State   ] 
10
 
11
cluster_state   : Normal 
12
redistributing  : No 
13
current_az      : AZ_ALL 
14
 
15
[  Datanode State   ] 
16
node                           node_ip           port                           instance                                   state 
17
---------------------------------------------------------------------------------------------------------------- 
18
1  pekpopgsci00235  10.244.62.204    5432      6001 /opt/gaussdb/cluster/data/dn1   P Primary Normal 
19
2  pekpopgsci00238  10.244.61.81      5432      6002 /opt/gaussdb/cluster/data/dn1   S Standby Normal
20

21

5. 例行维护

https://docs-opengauss.osinfra.cn/zh/docs/5.0.0/docs/DatabaseOMGuide/例行维护.html

25
1
-- 查询数据库版本
2
SELECT version();
3

4
-- 查询数据库容量
5
SELECT pg_table_size('table_name');
6
SELECT pg_database_size('database_name');
7

8
-- 实例状态检查
9
gs_check -i CheckClusterState
10
gs_check -e inspect
11

12
-- 数据库性能检查
13
gs_checkperf -i pmk -U omm
14
Cluster statistics information:
15
    Host CPU busy time ratio                     :    1.43       %
16
    MPPDB CPU time % in busy time                :    1.88       %
17
    Shared Buffer Hit ratio                      :    99.96      %
18
    In-memory sort ratio                         :    100.00     %
19
    Physical Reads                               :    4
20
    Physical Writes                              :    25
21
    DB size                                      :    70         MB
22
    Total Physical writes                        :    25
23
    Active SQL count                             :    2
24
    Session count                                :    3
25

第三节 客户端

1. 命令行客户端-gsql

1) Linux用户连接

在数据库主节点机器上，可以直接使用Linux用户连接：

8
1
-- Linux用户连接
2
-- 需以omm用户登录数据库主节点，其中postgres为连接的数据库，8000为数据库主节点的端口号
3
gsql -d postgres -p 8000
4

5
-- 也可使用下面格式进行连接
6
gsql postgres://omm:Gauss_234@127.0.0.1:8000/postgres -r 
7
gsql -d "host=127.0.0.1 port=8000 dbname=postgres user=omm password=Gauss_234"
8

注意：

1. 管理员用户omm登录连接时，命令提示符显示DBNAME=#，如果为普通用户则显示DBNAME=>。

2. 客户端连接数据库后，如果空闲时间超过session_timeout，则会自动断开连接，可设置session_timeout为0来关闭超时设置。

2) 数据库用户连接

在其它机器上，可通过数据库用户进行连接，首先需安装gsql工具：

15
1
-- 1. 上传和解压gsql工具包
2
mkdir /opt/software/tools
3
cd /opt/software/tools
4
tar -zxvf openGauss-x.x.x-openEuler-64bit-Libpq.tar.gz
5

6
-- 2. 拷贝数据库主节点的bin目录
7
-- 其中/opt/huawei/install/app为clusterconfig.xml文件中配置的{gaussdbAppPath}路径，10.10.0.30为客户端主机ip
8
scp -r /opt/huawei/install/app/bin root@10.10.0.30:/opt/software
9

10
-- 3. 配置环境变量
11
vi ~/.bashrc
12
export PATH=/opt/software/bin:$PATH
13
export LD_LIBRARY_PATH=/opt/software/lib:$LD_LIBRARY_PATH
14
source ~/.bashrc
15

安装好gsql工具后，远程连接命令如下：

4
1
-- 远程连接数据库
2
-- 其中10.10.0.11为数据库主节点ip，jack为数据库用户名，Test@123为用户密码
3
gsql -d postgres -h 10.10.0.11 -U jack -p 8000 -W Test@123
4

注意：

1. 客户端机器与openGauss不在同一网段时，-h指定的IP地址应为Manager界面上所设的coo.cooListenIp2(应用访问IP)的取值。

2. 禁止使用omm用户进行远程连接数据库。

3) 常用操作

22
1
-- 查看已经存在的数据库
2
openGauss=# \l
3

4
-- 进入已存在数据库
5
openGauss=# \c db_tpcc
6

7
-- 首次登录建议修改密码
8
ALTER ROLE omm IDENTIFIED BY '$$$$$$$$' REPLACE 'XXXXXXXX';
9

10
-- 设置gsql变量
11
-v=var1=xxx
12

13
-- 使用gsql执行脚本
14
gsql -d postgres -p 21013 -f /home/user/sql.txt
15
gsql -h ${DB_IP} -p ${DB_PORT} -d${DB_NAME} -U${DB_USER} -W${DB_PASSWORD} -f "create_tables.sql" >/dev/null  2>./log/create_tables.log
16

17
-- 使用gsql执行SQL语句
18
gsql -d postgres -p 21013 -c="select * from xxx"
19

20
-- 退出
21
openGauss=# \q
22

2. 图形界面客户端-TPDSS

OLTP Development Assistant（简称TPDSS）是OLTP数据库开发助手，帮助数据库开发人员便捷地构建应用程序，以图形化界面形式提供数据库关键特性，展示数据库的主要功能，简化数据库开发和应用构建任务。

3. 应用程序客户端

1) ODBC

详情见：

• https://docs-opengauss.osinfra.cn/zh/docs/5.0.0/docs/GettingStarted/ODBC.html

• https://docs-opengauss.osinfra.cn/zh/docs/5.0.0/docs/DeveloperGuide/基于ODBC开发.html

提示：

1. openGauss也提供了专门的C应用程序接口libpq。

2) JDBC

• JDBC驱动：可以在linux服务器端源代码目录下执行build.sh，获得驱动jar包postgresql.jar；也可从发布包中获取, 包名为openGauss-xxxx-操作系统版本号-64bit-Jdbc.tar.gz，两种jar包功能一致，仅仅是为了解决和PostgreSQL之间的JDBC驱动包名冲突。

  上述两个openGauss JDBC驱动包，相比于PG原版驱动，主要做了以下特性的增强：

  • 支持SHA256加密方式登录。

  • 支持对接实现sf4j接口的第三方日志框架。

  • 支持容灾切换。

• JDBC配置：驱动名为org.opengauss.Driver或org.postgresql.Driver，url前缀为jdbc:opengauss或jdbc:postgresql。

  可参考示例 不同场景下连接数据库参数配置 (osinfra.cn)和示例 Jdbc主备集群负载均衡 (osinfra.cn)

  • 容灾场景：jdbc:postgresql://node1,node2,node3,node4,node5,node6/database?priorityServers=3

  • 负载均衡场景：jdbc:postgresql://node1,node2,node3/database?loadBalanceHosts=true

  • 日志诊断场景：jdbc:postgresql://node1/database?loggerLevel=trace&loggerFile=jdbc.log

  • 预编译SQL：jdbc:postgresql://node1/database?prepareThreshold=5

  • 优化客户端内存：jdbc:postgresql://node1/database?defaultRowFetchSize=50000

  • 批量插入：jdbc:postgresql://node1/database?batchMode=true

• 连接参数：第三方工具通过JDBC连接openGauss时，JDBC向openGauss发起连接请求，会默认添加以下配置参数：

  19
  1
  // ConnectionFactoryImpl
  2
  params = {
  3
  { "user", user },
  4
  { "database", database },
  5
  { "client_encoding", "UTF8" },
  6
  { "DateStyle", "ISO" },
  7
  { "extra_float_digits", "2" },
  8
  { "TimeZone",  createPostgresTimeZone() },
  9
  };
  10
  
  11
  // 其它可选的连接参数
  12
  // https://docs-opengauss.osinfra.cn/zh/docs/5.0.0/docs/DeveloperGuide/%E8%BF%9E%E6%8E%A5%E6%95%B0%E6%8D%AE%E5%BA%93_JDBC.html
  13
  // https://docs-opengauss.osinfra.cn/zh/docs/5.0.0/docs/DeveloperGuide/JDBC%E5%B8%B8%E7%94%A8%E5%8F%82%E6%95%B0%E5%8F%82%E8%80%83.html
  14
  loggerLevel：目前支持 OFF、INFO(默认)、DEBUG、TRACE 这4种级别。
  15
  loggerFile：Logger输出的文件名，默认为客户端运行程序目录。
  16
  fetchsize：执行查询后每次提取的数据行数，默认为0，表示一次性提取所有数据。
  17
             应用：设置为合适的值，可用于优化客户端内存占用，但是会增加网络交互开销。
  18
             注意：该功能依赖游标实现，由于游标在事务内有效，因此必须关闭autocommit才能使用。
  19
      
  

注意：

1. 需在pg_hba.conf文件(安装目录datanode文件夹中)中添加客户端地址，如：host all all 127.0.0.1/32 sha256。

2. 若使用数据库连接池，则必须在归还连接前重置GUC参数和删除临时表，否则可能会产生因为历史会话信息导致的对象冲突。

3. 更多JDBC示例请参考：https://docs-opengauss.osinfra.cn/zh/docs/5.0.0/docs/GettingStarted/Java.html。

3) Psycopg

Psycopg是一种用于执行SQL语句的PythonAPI，可以为PostgreSQL、openGauss数据库提供统一访问接口，应用程序可基于它进行数据操作。Psycopg2对libpq进行封装，部分代码使用C语言实现，既高效又安全。

详细使用步骤请参考：https://docs-opengauss.osinfra.cn/zh/docs/5.0.0/docs/GettingStarted/Python.html。

第四节 体系结构

1. 软件架构

2. 分片架构

单体架构->RAC架构->Shared-Nothing架构对比如下：

Shared-Nothing架构优点：

• 易于扩展：节点支持在线扩展，且无共享磁盘，性能接近线性上升。

• 并行处理：数据分布在多个节点，访问数据库时，每个节点对所属部分数据进行并行处理(CPU计算和IO处理都可并行)。

3. 核心组件

3. 逻辑架构

openGauss的数据节点负责存储数据，包含数据库、表空间、数据文件、表、数据块等。

• 数据库：用于管理各类数据对象，与其他数据库隔离。

• 表空间：在openGauss中，表空间是一个目录，其中是一些物理文件，其管理功能依赖文件系统。

• 模 式：数据库对象集，包括逻辑结构，例如表、视图、序、存储过程、同义名、索引及数据库链接等。

• 数据文件：通常每张表只对应一个数据文件。如果某张表的数据大于1GB，则会分为多个数据文件存储。

注意：

1. 数据库和表空间是交叉的关系，一个数据库的对象可分布在多个表空间，一个表空间可存放多个数据库的对象。

2. 数据库和模式是从属的关系，一个数据库可以创建多个模式，多个模式之间对象可以同名。

3. 表空间是物理存储和逻辑存储之间的一层抽象，向下依赖物理文件，向上抽象为逻辑结构。

4. 内存结构

第02章_实例管理

第一节 数据库

1. 创建数据库

18
1
-- 创建数据库
2
CREATE DATABASE database_name;
3

4
-- 指定兼容模式和字符集-集中式
5
-- 兼容Oracle模式字符集为UTF-8的数据库
6
create database test1_ora encoding='UTF8' dbcompatibility 'A';
7
-- 兼容PG模式字符集为GBK的数据库  
8
create database test4_pg encoding='GBK' dbcompatibility 'PG';   
9

10
-- 指定兼容模式和字符集-分布式
11
-- 兼容Oracle模式字符集为utf8的数据库
12
create database oracle_1 with owner=sunjy01 tablespace=ds_ceshi ENCODING='utf-8' DBCOMPATIBILITY 'ora';
13
-- 兼容PG模式字符集为gbk的数据库
14
create database postgresql_2 with owner=sunjy01 tablespace=ds_ceshi ENCODING='gbk' DBCOMPATIBILITY 'PG'; 
15

16
-- 查询数据库及兼容模式
17
select datname,encoding,datcompatibility from pg_database where datname=('test1_ora','test2_pg');
18

注意：

1. 一个openGauss实例包括多个数据库，但任何与服务器连接的用户都只能访问连接请求里声明的那个数据库。

2. 查看和修改数据库

18
1
-- 查看已经存在的数据库
2
openGauss=# \l
3
SELECT * FROM pg_database;
4

5
-- 查询当前数据库
6
SELECT CURRENT_CATALOG;
7

8
-- 进入已存在数据库
9
openGauss=# \c db_tpcc
10
Non-SSL connection (SSL connection is recommended when requiring high-security)
11
You are now connected to database "db_tpcc" as user "omm".
12

13
-- 数据库重命名
14
ALTER DATABASE database_name RENAME TO new_name;
15

16
-- 删除数据库
17
DROP DATABASE database_name ;
18

3. 数据库参数配置

https://docs-opengauss.osinfra.cn/zh/docs/5.0.0/docs/DatabaseAdministrationGuide/设置参数.html

12
1
-- 查看所有参数
2
SHOW ALL;
3
SELECT * FROM pg_settings;
4

5
-- 查看已配置的参数
6
SHOW server_version;
7
SELECT * FROM pg_settings WHERE NAME='server_version';
8

9
-- 设置参数
10
gs_guc reload -D /gaussdb/data/dbnode -c "failed_login_attempts=10"
11
gs_guc reload -N all -I all -c "password_lock_time=1"
12

第二节 用户

1. 查询用户信息

12
1
-- 查询用户列表
2
SELECT * FROM pg_user; 
3

4
-- 查询用户属性
5
SELECT * FROM pg_authid; 
6

7
-- 查询当前用户
8
SELECT USER;
9
SELECT CURRENT_USER; 
10
SELECT SESSION_USER;
11
SELECT CURRENT_ROLE;
12

2. 创建或删除用户

一个openGauss实例包括多个用户和用户组，这些用户和用户组在实例范围内是共享的。创建用户的语句如下：

10
1
-- 创建管理员用户
2
CREATE USER sysadmin WITH SYSADMIN password "xxxxxxxxx";
3

4
-- 创建普通用户，并授予postgresql_1库的所有权限
5
create user sunjy01 password 'sunjy_123';
6
GRANT ALL PRIVILEGES ON DATABASE postgresql_1 to sunjy01;
7

8
-- 删除用户
9
DROP USER IF EXISTS sunjy01 CASCADE;
10

3. 修改或锁定用户

13
1
-- 修改用户名
2
ALTER USER user_name RENAME TO new_name;
3

4
-- 锁定/解锁用户
5
ALTER USER user_name ACCOUNT { LOCK | UNLOCK };
6

7
-- 修改用户有效期
8
ALTER USER joe WITH VALID BEGIN '2016-11-10 08:00:00' VALID UNTIL '2017-11-10 08:00:00';
9

10
-- 修改或重置与用户相关的会话参数值
11
ALTER USER user_name SET configuration_parameter { { TO | = } { value | DEFAULT } | FROM CURRENT };
12
ALTER USER user_name RESET { configuration_parameter | ALL };
13

注意：

1. 如果用户输入密码次数超过一定次数(failed_login_attempts，默认10次)，系统将自动锁定该帐户，默认1天后解锁。

第三节 表空间

表空间一般用于隔离磁盘IO，openGauss自带了两个表空间：

• pg_default：默认表空间(base目录)，用来存储非共享系统表、用户表、用户表index、临时表、临时表index、内部临时表。

• pg_global：共享表空间(global目录)，用来存放共享系统表的表空间。

1. 创建表空间

12
1
-- 创建表空间
2
-- 其中“tablespace/tablespace_1”是用户拥有读写权限的空目录
3
CREATE TABLESPACE fastspace RELATIVE LOCATION 'tablespace/tablespace_1';
4
-- 将“fastspace”表空间的访问权限赋予数据用户jack
5
GRANT CREATE ON TABLESPACE fastspace TO jack;
6

7
-- 重命名表空间
8
ALTER TABLESPACE fastspace RENAME TO fspace;
9

10
-- 删除表空间
11
DROP TABLESPACE fspace;
12

2. 使用表空间

13
1
-- 查询系统和用户的表空间
2
SELECT spcname FROM pg_tablespace;
3
openGauss=# \db
4

5
-- 查询表空间的当前使用情况(单位为字节)
6
SELECT PG_TABLESPACE_SIZE('example');
7

8
-- 在指定表空间中创建表
9
CREATE TABLE foo(i int) TABLESPACE fastspace;
10

11
-- 设置当前环境的默认表空间
12
SET default_tablespace = 'fastspace';
13

第四节 模式

1. 模式简介

模式(schema)允许多个用户使用同一数据库而不相互干扰，可以将数据库对象组织成易于管理的逻辑组。

• 一个数据库中可以创建多个模式，多个模式中的对象可以重名，用户根据其对schema的权限进行访问。

• 不能创建以PG_为前缀的schema名，该类schema为数据库系统预留的。

• 在每次创建新用户时，系统会在当前登录的数据库中为新用户创建一个同名Schema(注：其它数据库并没有)。

• 数据库创建初始，默认具有一个名为public的Schema，且所有用户都拥有此Schema的usage权限(注：普通用户无CREATE权限)。

• 每个数据库都包含一个pg_catalog模式，它包含系统表和所有内置数据类型、函数、操作符。

12
1
-- 查询所有模式
2
SELECT * FROM pg_namespace;
3

4
-- 查询当前模式
5
SELECT CURRENT_SCHEMA;
6

7
-- 查询模式所有者
8
SELECT s.nspname,u.usename AS nspowner FROM pg_namespace s, pg_user u WHERE nspname='schema_name' AND s.nspowner = u.usesysid;
9

10
-- 查询某个模式下的所有表
11
 SELECT distinct(tablename),schemaname from pg_tables where schemaname = 'pg_catalog';
12
 

2. 创建或删除模式

20
1
-- 创建模式并指定拥有者
2
CREATE SCHEMA myschema;
3
CREATE SCHEMA myschema AUTHORIZATION omm;
4

5
-- 将模式授权给用户/回收用户的模式权限
6
-- 默认情况下，所有角色都拥有在public模式上的USAGE权限，但是普通用户没有在public模式上的CREATE权限
7
GRANT USAGE ON schema myschema TO jack;
8
REVOKE USAGE ON schema myschema FROM jack;
9

10
-- 查询和设置模式搜索路径
11
-- 在不带模式访问对象时，就会用到模式搜索路径，该路径前2位固定为pg_temp和pg_catalog
12
SHOW SEARCH_PATH;
13
SET SEARCH_PATH TO myschema, public;
14

15
-- 删除模式并级联删除所有对象
16
DROP SCHEMA IF EXISTS myschema CASCADE;
17
 
18
-- 删除空模式
19
DROP SCHEMA IF EXISTS nullschema;
20

3. 使用模式

9
1
-- 查询当前模式
2
SELECT current_schema();
3

4
-- 在指定模式下创建表
5
CREATE TABLE myschema.mytable(id int, name varchar(20));
6

7
-- 查询指定模式下的表
8
SELECT * FROM myschema.mytable;
9

4. 修改模式

9
1
-- 重命名模式
2
ALTER SCHEMA ds RENAME TO ds_new;
3

4
-- 修改模式的所有者
5
ALTER SCHEMA ds_new OWNER TO jack;
6

7
-- 修改模式的默认字符集和字符序
8
ALTER SCHEMA ds_new CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
9

第五节 角色

1. 查询和使用角色

5
1
-- 查询所有角色
2
SELECT * FROM PG_ROLES;
3

4
-- 将角色授给用户
5
GRANT lily to joe;

注意：

1. 默认情况下，所有创建的用户和角色默认拥有PUBLIC所拥有的权限。

2. openGauss提供了一组默认角色，以gs_role_开头命名，如gs_role_copy_files、gs_role_tablespace等。

2. 创建或删除角色

9
1
-- 创建角色，名为manager，密码为xxxxxxxxx
2
CREATE ROLE manager IDENTIFIED BY 'xxxxxxxxx';
3

4
-- 创建角色，从2015年1月1日开始生效，到2026年1月1日失效。
5
CREATE ROLE miriam WITH LOGIN PASSWORD 'xxxxxxxxx' VALID BEGIN '2015-01-01' VALID UNTIL '2026-01-01';
6

7
-- 删除角色
8
DROP ROLE IF EXISTS role_name;
9

注意：

1. 角色是拥有数据库对象和权限的实体。在不同的环境中角色可以认为是一个用户，一个组或者兼顾两者。

3. 修改角色

12
1
-- 修改角色名称
2
ALTER ROLE role_name RENAME TO new_name;
3

4
-- 锁定/解锁角色
5
ALTER ROLE role_name ACCOUNT { LOCK | UNLOCK };
6

7
-- 修改角色manager的密码为abcd@123
8
ALTER ROLE manager IDENTIFIED BY '$$$$$$$$' REPLACE 'xxxxxxxxx';
9

10
-- 修改角色manager为系统管理员
11
ALTER ROLE manager SYSADMIN;
12

第六节 权限

1. 权限简介

数据对象创建后，创建用户即对象所有者，默认情况下(未开启三权分立)，只有管理员和该用户才能访问该对象。

管理员包括初始用户(安装用户)、具有SYSADMIN/OPRADMIN等属性的帐户，设置用户为管理员的语句如下：

8
1
-- 创建管理员用户
2
CREATE USER sysadmin WITH SYSADMIN password "xxxxxxxxx";
3

4
-- 将已有用户设置为管理员
5
ALTER USER joe SYSADMIN;
6

7
-- 查询所有权限
8
SELECT * FROM pg_authid;

其它用户则可以按需授权，支持的授权类别有：SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、CREATE、CONNECT、EXECUTE、ALTER、DROP、COMMENT、INDEX、VACUUM和USAGE等。

注意：

1. 为了限制SYSADMIN的权限，在openGauss中，可以禁止其CREATEROLE属性(创建角色和用户的权限)和AUDITADMIN属性(查看和维护数据库审计日志的权限)，请参考三权分立 。

2. 系统权限

6
1
-- 将系统权限(sysadmin)授权给用户或者角色
2
GRANT ALL PRIVILEGES TO joe;
3

4
-- 回收系统权限
5
REVOKE ALL PRIVILEGES FROM joe; 
6

3. 对象权限

7
1
-- 将表customer的所有权限授权给用户joe，并授予模式test的使用权限
2
GRANT ALL PRIVILEGES ON customer TO joe;
3
GRANT USAGE ON SCHEMA test TO joe; 
4

5
-- 将表customer中c_customer_sk、c_customer_id、c_first_name列的查询权限，c_last_name的更新权限授权给joe
6
GRANT select (c_customer_sk,c_customer_id,c_first_name),update (c_last_name) ON customer TO joe;
7

注意：

1. 在授予对象权限时，需一并授予该对象所属模式的USAGE权限，否则只能看到对象名称，而不能实际访问对象

4. 权限传递

4
1
-- 默认情况下不允许权限传递，需添加如下选项
2
GRANT joe TO manager WITH ADMIN OPTION; 
3
GRANT create,connect on database postgres TO joe WITH GRANT OPTION;
4

第七节 事务

1. 事务控制命令

14
1
-- 设置事务隔离级别
2
{ SET [ LOCAL ] TRANSACTION|SET SESSION CHARACTERISTICS AS TRANSACTION }
3
  { ISOLATION LEVEL { READ COMMITTED | SERIALIZABLE | REPEATABLE READ }
4
  | { READ WRITE | READ ONLY } } [, ...];
5

6
-- 开启事务
7
START TRANSACTION(或BEGIN)
8

9
-- 提交事务
10
COMMIT;
11

12
-- 回滚事务
13
ROLLBACK; 
14

注意：

1. GaussDB Kernel 采用了多版本并发控制(MVCC)结合两阶段锁的方式来管理事务， 其特点是事务之间不阻塞。

2. GaussDB Kernel 没有回滚段的概念，采用 VACUUM 线程定期清除历史版本数据。

第八节 锁

1. 查询锁

26
1
-- 查询数据库中的锁信息
2
-- SELECT * FROM pg_locks;
3
select c.datname, b.relname, d.*, a.*, b.*, c.*
4
  from pg_locks a 
5
  left join pg_class b on a.relation = b.oid 
6
  left join pg_database c on a.database = c.oid
7
  left join pg_stat_activity d on a.pid = d.pid
8
 where c.datname = 'kbmsdb8'
9
   and b.relname = 'risk_log_cust_stk'
10
  order by c.datname, b.relname;
11
  
12
-- 查询等待锁的线程状态信息
13
SELECT * FROM pg_thread_wait_status WHERE wait_status = 'acquire lock';
14

15
-- 查询被锁阻塞的会话
16
SELECT w.query AS waiting_query, w.pid AS w_pid, w.usename AS w_user, l.query AS locking_query, l.pid AS l_pid, l.usename AS l_user, t.schemaname||'.'||t.relname AS tablename
17
  FROM pg_stat_activity w
18
  JOIN pg_locks l1 ON w.pid = l1.pid AND NOT l1.granted
19
  JOIN pg_locks l2 ON l1.relation = l2.relation AND l2.granted
20
  JOIN pg_stat_activity l ON l2.pid = l.pid
21
  JOIN pg_stat_user_tables t ON l1.relation = t.relid
22
 WHERE w.waiting = true;
23

24
-- 终止会话进程
25
SELECT pg_terminate_backend(139834762094352);
26

注意：

1. 可以通过 ps ux 查找正在运行的系统进程，然后使用 kill -9 pid 命令结束此进程。

2. 表级锁

1) 自动加锁

当表级锁相互兼容时，才可以加锁成功：

2) 手动加锁

9
1
-- 语法格式
2
-- NOWAIT：不去等待任何冲突的锁释放，如果无法立即获取该锁，该命令退出并且发出一个错误信息
3
LOCK [ TABLE ] name  IN lock_mode MODE [NOWAIT]
4

5
-- 示例
6
START TRANSACTION;
7
LOCK TABLE reason_t1   IN SHARE ROW EXCLUSIVE MODE;
8
LOCK TABLE graderecord IN ACCESS EXCLUSIVE MODE;
9
COMMIT;

注意：

1. LOCK语句必须在事务中执行，否则会报错。没有UNLOCK TABLE命令，锁总是在事务结束时释放。

2. 以ACCESS SHARE模式加锁需要表的SELECT权限，其他形式的LOCK需要UPDATE和/或DELETE权限。

第九节 备份恢复

1. 逻辑备份与恢复

• 基于备份时刻进行数据转储，其对于故障点和备份点之间的数据无能为力。

• 全库恢复时，通常需要重建数据库，导入备份数据来完成，恢复时间较长。

• 适用于数据量小或变化很少的数据，以及进行异构数据库之间的数据迁移。

38
1
-- ------------------- 方式一(gs_dump) ------------------- --
2
-- 灵活导出数据对象，可选择纯文本格式或者归档格式
3
-- 纯文本格式只能使用gsql恢复，恢复时间较长，归档格式只能使用gs_restore恢复，恢复时间中等
4
-- 需由操作系统用户omm执行，在进行数据导出时，其他用户可以访问openGauss数据库（读或写）
5
-- gs_dump在启动时，全局加共享锁(–lock-wait-timeout)，生成全局一致性快照，导出此时刻数据
6

7
-- 以纯文本格式/tar归档格式/目录归档格式/自定义归档格式导出某个库
8
gs_dump -h 127.0.0.1 -U kbms -W SZking@996 -f ./kbmsdb8.sql -p 30100 kbmsdb8 -F p
9
gs_dump -h 127.0.0.1 -U omm -W Bigdata@123 -f backup/MPPDB_backup.tar -p 37300 postgres -F t
10
gs_dump -h 127.0.0.1 -U omm -W Bigdata@123 -f backup/MPPDB_backup -p 37300  postgres -F d
11
gs_dump -h 127.0.0.1 -U omm -W Bigdata@123 -f backup/MPPDB_backup.dmp -p 37300 postgres -F c
12

13
-- 指定用户导出数据库
14
gs_dump dbname -h host_name -p port -f out.sql -U user_name -W password
15

16
-- 导出schema
17
gs_dump dbname -h host_name -p port -n schema_name -f out.sql
18

19
-- 导出table
20
gs_dump dbname -h host_name -p port -t table_name -f out.sql
21

22
-- 导出指定库和模式的表结构
23
gs_dump -h 10.201.69.92 -U kbms -W SZking@996 -p 30100 kbmsdb1 -n kbms -s -F p -f dump_file.sql
24

25
-- ------------------- 方式二(gs_dumpall) ------------------- --
26
-- 导出所有数据库相关信息，包括默认数据库postgres的数据、自定义数据库的数据、以及所有数据库公共的全局对象等
27
-- 只能导出为纯文本格式，只能使用gsql恢复
28
gs_dumpall -f backup/bkp2.sql -p 37300
29

30
-- ------------------- 逻辑恢复(gsql/gs_restore) ------------------- --
31
-- 恢复gs_dump和gs_dumpall导出的纯文本格式数据
32
gsql -h 127.0.0.1 -U kbms -d kbmsdb8 -p 30100 -W Bigdata@123 -f ./kbmsdb8.sql
33

34
-- 恢复gs_dump导出的归档格式数据
35
gs_restore -W Bigdata@123 backup/MPPDB_backup.dmp -p 15400 -d postgres
36
gs_restore backup/MPPDB_backup.tar -p 15400 -d postgres 
37
gs_restore backup/MPPDB_backup -p 15400 -d postgres
38

参考：

1. 概述：逻辑备份与恢复

2. 备份：使用gs Dump和gs Dumpall命令导出数据

3. 恢复：使用gs Restore命令导入数据

2. 物理备份与恢复

• 通过物理文件拷贝的方式对数据文件及归档日志等文件进行备份，数据库可以进行完全恢复。

• 物理备份速度快，适用于数据量大的场景，主要用于全量数据备份恢复

32
1
-- ------------------- 方式一(gs_backup) ------------------- --
2
-- 导出数据库参数文件和二进制文件
3

4
-- 备份数据库主机
5
gs_backup -t backup --backup-dir=/opt/software/gaussdb/backup_dir -h plat1 --parameter
6

7
-- 恢复数据库主机
8
gs_backup -t restore --backup-dir=/opt/software/gaussdb/backup_dir -h plat1 --parameter
9

10

11
-- ------------------- 方式二(gs_basebackup) ------------------- --
12
-- 对服务器数据库文件的二进制进行全量拷贝，只能对数据库某一个时间点的时间作备份
13
-- 结合PITR恢复，可恢复全量备份时间点后的某一时间点。
14
-- 恢复时可以直接拷贝替换原有的文件， 或者直接在备份的库上启动数据库，恢复时间快
15

16
-- 备份
17
gs_basebackup -D /home/test/trunk/install/data/backup -h 127.0.0.1 -p 21233
18

19
-- ------------------- 方式三(gs_probackup) ------------------- --
20
-- 对openGauss 实例进行定期物理备份，支持增量备份、定期备份和远程备份
21
-- 恢复时可以直接恢复到某个备份点，在备份的库上启动数据库，恢复时间快
22

23
-- 初始化备份目录
24
gs_probackup init -B backup_dir
25
-- 添加一个新的备份实例
26
gs_probackup add-instance -B backup_dir -D data_dir --instance instance_name
27
-- 创建指定实例的备份
28
gs_probackup backup -B backup_dir --instance instance_name -b backup_mode
29

30
-- 从指定实例的备份中恢复数据
31
gs_probackup restore -B backup_dir --instance instance_name -D pgdata-path -i backup_id
32

3. 闪回恢复

• 基于MVCC和回收站机制，闪回恢复历史数据或删除的表。

• 适用于误删除表的场景或需要将表中的数据恢复到指定时间点或者CSN。

• 恢复时间非常快(只需要秒级)，而且恢复时间和数据库大小无关。

22
1
-- https://docs-opengauss.osinfra.cn/zh/docs/5.0.0/docs/DatabaseOMGuide/%E9%97%AA%E5%9B%9E%E6%81%A2%E5%A4%8D.html
2

3
-- 闪回查询
4
SELECT * FROM t1 TIMECAPSULE TIMESTAMP to_timestamp ('2020-02-11 10:13:22.724718', 'YYYY-MM-DD HH24:MI:SS.FF');
5
SELECT * FROM t1 TIMECAPSULE CSN 9617;
6

7
-- 闪回表
8
-- 需开启enable_recyclebin参数，启用回收站
9
-- recyclebin_retention_time参数用于设置回收站对象保留时间，超过该时间的回收站对象将被自动清理
10
TIMECAPSULE TABLE t1 TO BEFORE DROP;
11
TIMECAPSULE TABLE t1 TO BEFORE DROP RENAME TO new_t1;
12
TIMECAPSULE TABLE "BIN$04LhcpndanfgMAAAAAANPw==$0" TO BEFORE DROP;
13
TIMECAPSULE TABLE "BIN$04LhcpndanfgMAAAAAANPw==$0" TO BEFORE DROP RENAME TO new_t1;
14

15
-- 清理回收站
16
DROP TABLE t1 PURGE;
17
PURGE TABLE t1;
18
PURGE TABLE "BIN$04LhcpndanfgMAAAAAANPw==$0";
19
PURGE INDEX i1;
20
PURGE INDEX "BIN$04LhcpndanfgMAAAAAANPw==$0";
21
PURGE RECYCLEBIN;
22

4. 数据导入导出

• 导出数据可使用gs_dump或gs_dumpall来进行，支持按实例、数据库、模式、表等粒度进行导出。可参考：导出数据

• 导入数据可使用gsql、gs_restore、\copy元命令、COPY FROM STDIN、CopyManager(Java)等方式。可参考：导入数据

• 示例：

  • 示例1 通过本地文件导入导出数据

  • 示例2 从 My向open Gauss数据库进行数据迁移

5
1
# 服务端导出
2
COPY evt_creditdebts TO '/home/lptest/hyx/evt_creditdebts.csv' WITH (FORMAT 'csv', DELIMITER ',');
3

4
# 客户端导出
5
\COPY evt_creditdebts TO '/home/lptest/hyx/evt_creditdebts.csv' WITH (FORMAT 'csv', DELIMITER ',');

第十节 数据字典

1. 查询对象信息

35
1
-- 查询对象信息
2
-- 存储数据库对象信息及其之间的关系， 记录几乎 所有的数据库对象 信息
3
SELECT * FROM pg_class;
4

5
-- 查询所有表及描述
6
select a.relname as name, b.description as value
7
  from pg_class a
8
  left join (select * from pg_description where objsubid = 0 ) b on a.oid = b.objoid
9
 where a.relname in(select tablename from pg_tables where schemaname = 'kfms')
10
 order by a.relname asc
11

12
-- 查询数据库信息
13
SELECT * FROM pg_database;
14

15
-- 查询表空间信息
16
SELECT * FROM pg_tablespace;
17

18
-- 查询模式信息
19
SELECT * FROM pg_namespace;
20

21
-- 查询类型信息
22
SELECT * FROM pg_type;
23

24
-- 查询表字段信息
25
SELECT * FROM pg_attribute;
26

27
-- 查询视图信息
28
SELECT * FROM pg_views;
29

30
-- 查询索引信息
31
SELECT * FROM pg_index;
32

33
-- 查询角色信息
34
SELECT * FROM pg_roles;
35

2. 查询分布式对象信息

15
1
-- 查看分区表
2
SELECT * FROM pgxc_class;
3

4
-- 查询集群节点信息
5
SELECT * FROM pgxc_node;
6

7
-- 查询节点组信息
8
SELECT * FROM pgxc_group;
9

10
-- 表复制或分布信息
11
SELECT * FROM pgxc_class;
12

13
-- 查询LIST和RANGE分布的具体分布信息
14
SELECT * FROM pgxc_slice;
15

3. 查询统计信息

25
1
-- 查询统计信息
2
SELECT * FROM pg_stats;
3

4
-- 查询当前活动
5
-- 状态：active(正在执行SQL)、idle(等待命令)、idle in transaction(等待命令且处于事务中)、
6
-- idle in transaction (aborted)(事务中的SQL报错)、fastpath  function call(正在执行fast-call函数)
7
-- disabled(track_activities被禁用)
8
SELECT * FROM pg_stat_activity;
9

10
-- 当前连接用户和机器
11
SELECT datname,usename,client_addr,client_port  FROM pg_stat_activity; 
12
-- 当前活跃连接数
13
SELECT count(*) FROM pg_stat_activity E WHERE state <> 'idle';  -- SHOW max_connections; -- 最大连接数
14
-- 当前正在执行的SQL
15
SELECT datname,usename,query M FROM pg_stat_activity;  
16
-- 查询最耗时的SQL
17
SELECT current_timestamp - query_start as runtime, datname, usename, sessionid, query
18
  FROM pg_stat_activity E
19
 WHERE state != 'idle'
20
 ORDER BY 1 DESC;
21
 
22
 -- 查询当前阻塞
23
 -- wait_status：none(无等待)、wait io(等待IO完成)、wait cmd(等待读取网络通信包)、acquire lock(等待加锁)、acquire lwlock(等待获取轻量级锁)
24
 SELECT * FROM pg_thread_wait_status;
25
 

4. 系统视图

• MY_：包含当前数据库用户所拥有的所有的模式对象的信息。

• DB_：包含当前数据库用户可以访问的所有的模式对象的信息。

• ADM_：包含所有数据库对象信息，只有具有 DBA 角色/系统管理员权限的用户才能够访问这些视图。

第03章_数据对象

第一节 数据类型

1. 字符型

注意：

1. PG兼容性下，CHAR和VARCHAR以字符为计数单位，其它兼容性以字节为计数单位，即CHAR(3)只能存放一个UTF-8编码字符。

2. ORA兼容性下，数据库将空字符串作为NULL处理。

2. 数值型

注意：

1. 将字符串转换成整数类型时，如果输入不合法，MYSQL兼容性会将输入转换为0，而其它兼容性则会报错。

3. 日期/时间

注意：

1. ORA兼容性下，数据类型DATE会被替换为TIMESTAMP(0) WITHOUT TIME ZONE。

4. 其它数据类型

注意：

1. 高斯开发规范中不建议使用序列整型。

第二节 数据表

1. 查询表信息

12
1
-- 查询所有表
2
SELECT * FROM pg_tables;
3

4
-- 查询表字段
5
SELECT * FROM pg_attribute; 
6

7
-- 查询表的结构信息
8
openGauss=# \d+ table_name 
9

10
-- 查询表的统计信息
11
SELECT * FROM pg_statistic;
12

2. 创建表

30
1

2
-- 创建普通表，并指定表空间
3
CREATE TABLE IF NOT EXISTS customer_t1
4
(
5
    c_customer_sk             integer,
6
    c_customer_id             char(5) PRIMARY KEY,
7
    c_first_name              char(6),
8
    c_last_name               char(8),
9
    Amount                    integer
10
) TABLESPACE DS_TABLESPACE1;
11

12
-- 创建临时表01
13
-- 临时表不能指定schema
14
CREATE TEMPORARY TABLE warehouse_t3
15
(
16
    W_WAREHOUSE_SK INTEGER NOT NULL,
17
    W_WAREHOUSE_ID CHAR(16) NOT NULL,
18
    CONSTRAINT PK_01 PRIMARY KEY(W_WAREHOUSE_SK)
19
) ON COMMIT DELETE ROWS; -- 提交事务时删除该临时表数据
20

21
-- 创建临时表02
22
CREATE TEMP TABLE customer_temp AS SELECT * FROM customer;
23

24
-- 表/字段注释
25
COMMENT ON TABLE AUTH_INFO IS '用户密码信息表';
26
COMMENT ON COLUMN AUTH_INFO.USER_CODE IS '用户代码';
27

28
-- 删除表
29
DROP TABLE IF EXISTS tpcds.web_returns_p2 CASCADE;
30