opengauss 数据库学习笔记

体系架构

逻辑结构

查看数据库进程

数据库启停

gs_ctl restart -D /opt/software/opengauss/data/single_node/

配置环境变量

cat /home/opengauss/.bashrc

export GAUSSHOME=/opt/software/opengauss

export PATH=$GAUSSHOME/bin:$PATH

export LD_LIBRARY_PATH=$GAUSSHOME/lib:$LD_LIBRARY_PATH

export GS_CLUSTER_NAME=dbCluster

export PGDATA=/opt/software/opengauss/data/single_node

环境变量配置好之后，直接使用 gs_ctl restart 启动即可

登录数据库

gsql -d postgres -p 5432 / gsql -d postgres -U opengauss -p 5432 -W openGauss123

执行sql语句和sql文件

更多帮助 gsql --help

--创建表空间

create tablespace tbs1 location '/data/tbs1';

--创建用户

create user user01 password 'openGauss123';

--创建数据库，并指定表空间

CREATE DATABASE db01 TABLESPACE tbs1;

--修改数据库db01所属用户为user01

alter database db01 owner to user01;

--切换数据库 \c db01

--切换用户 \c - user01

创建模式

create schema scott;

确认数据库服务器是否配置为监听连接请求。查看 OpenGauss 的配置文件 postgresql.conf，检查 listen_addresses 参数是否设置为 * 或指定了您的客户端 IP 地址。

修改OpenGauss 的配置文件 postgresql.conf，修改ssl = off，同时修改pg_hba.conf 添加其他远程访问连接

# TYPE DATABASE USER ADDRESS METHOD

host all all 192.168.1.0/24 md5

说明：

TYPE 是连接类型（通常是 host）。

DATABASE 是数据库名，all 表示所有数据库。

USER 是用户名，all 表示所有用户。

ADDRESS 是 IP 地址或 CIDR 块。例如，0.0.0.0/0 允许所有 IPv4 地址，而 192.168.1.0/24 仅允许该子网内的地址。

METHOD 是认证方法，md5 是常用的密码散列认证方法。

常用显示元命令

列出所有模式 \dn

--查询当前模式

select current_schema();

--切换模式

set search_path=scott;

创建表

列出当前模式下面的表

列出指定表的结构

\d t1

列出表空间

\db

列出所有数据库角色

\du

列出所有数据库名称

列出所有系统表

\dt PG_* 或 \dt GS_*

列出所有系统视图

\dv PG_* 或 \dv GS_*

opengauss 高级特性索引推荐

--创建测试表

CREATE TABLE emp (

emp_id INT PRIMARY KEY,

emp_name VARCHAR(50) NOT NULL,

dept_id INT,

salary DECIMAL(10, 2),

hire_date DATE

);

--批量插入数据

DECLARE

i INT;

BEGIN

FOR i IN 1..10000 LOOP

INSERT INTO emp (emp_id, emp_name, dept_id, salary, hire_date)

VALUES

( i, 'Employee_' || i, (10 + (i % 5) * 10), (5000.00 + (i % 5000)), DATE '2020-01-01' + (i % 1000) );

END LOOP;

END;

本功能依赖函数gs_index_advise实现对用户输入的单条查询语句生成推荐的索引

select "table", "column" from gs_index_advise('SELECT *from emp where salary = 10');

select "table", "column" from gs_index_advise('SELECT hire_date from emp where emp_name=''Employee_14'' and salary = 10');

表空间

在openGauss中，表空间是一个目录，在物理数据和逻辑数据间提供了抽象的一层，为所有的数据库对象分配存储空间，里面存储的是它所包含的数据库的各种物理文件。由于表空间是一个目录，仅是起到了物理隔离的作用，其管理功能依赖于文件系统。openGauss自带了两个表空间：pg_default和pg_global。

默认表空间pg_default：用来存储非共享系统表、用户表、用户表index、临时表、临时表index、内部临时表的默认表空间。对应存储目录为实例数据目录下的base目录。
共享表空间pg_global：用来存放共享系统表的表空间。对应存储目录为实例数据目录下的global目录。

--创建表空间指定绝对路径，需要提前创建好绝对路径的目录

create tablespace tbs3 location '/data/tbs3';

--创建表空间相对路径则不需要创建目录，表空间默认在实例目录下面

CREATE TABLESPACE tbs4 RELATIVE LOCATION 'data/tbs4';

两种方法对应目录如下

--创建表时指定表空间

CREATE TABLE ot(i int) TABLESPACE tbs3;

--设置默认表空间再创建表

SET default_tablespace = 'tbs4';

查看表空间sql，或者使用元命令\db

SELECT spcname FROM pg_tablespace;

查看表空间大小

SELECT PG_TABLESPACE_SIZE('tbs1')/1024/1024 as sizeM;

--重命名表空间

alter tablespace tbs4 rename to tbs5;

--删除表空间

drop tablespace tbs5;

--查询表空间有哪些对象

SELECT relname AS object_name,

CASE relkind

WHEN 'r' THEN 'table'

WHEN 'i' THEN 'index'

ELSE 'other'

END AS object_type

FROM pg_class

WHERE reltablespace = ( SELECT oid FROM pg_tablespace WHERE spcname = 'tbs5');

数据库用于管理各类数据对象，与其他数据库隔离。创建数据对象时可以指定对应的表空间，如果不指定相应的表空间，相关的对象会默认保存在PG_DEFAULT空间中。数据库管理的对象可分布在多个表空间上。初始时，openGauss包含两个模板数据库template0、template1，以及一个默认的用户数据库

--创建数据库指定表空间

CREATE DATABASE db03 TABLESPACE tbs3;

--查询数据库或元命令 \l

SELECT datname FROM pg_database;

数据库设置默认的模式搜索路径

ALTER DATABASE db03 SET search_path TO pa_catalog,public;

修改数据库表空间

ALTER DATABASE db03 SET TABLESPACE tbs1;

重命名数据库

ALTER DATABASE db03 rename to db05;

查看数据库所属表空间

select d.datname,t.spcname from pg_database d ,pg_tablespace t where d.dattablespace=t.oid;

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

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

2 在每次创建新用户时，系统会在当前登录的数据库中为新用户创建一个同名Schema。

3 没加模式的表时，系统会通过search_path来判断该表是哪个schema下的表。search_path是一个schema名列表，在其中找到的第一个表就是目标表，如果没有找到则报错。（某个表即使存在，如果它的schema不在search_path中，依然会查找失败）在搜索路径中的第一个schema叫做“当前schema”。它是搜索时查询的第一个schema，同时在没有声明schema名时，新创建的数据库对象会默认存放在该schema下。

4 每个数据库都包含一个pg_catalog schema，它包含系统表和所有内置数据类型、函数、操作符。pg_catalog是搜索路径中的一部分，始终在临时表所属的模式后面，并在search_path中所有模式的前面，即具有第二搜索优先级。这样确保可以搜索到数据库内置对象。如果用户需要使用和系统内置对象重名的自定义对象时，可以在操作自定义对象时带上自己的模式。

创建schema时指定所属用户。

CREATE SCHEMA myschema AUTHORIZATION user01;

可以设置search_path配置参数指定寻找对象可用schema的顺序。在搜索路径列出的第一个schema会变成默认的schema。如果在创建对象时不指定schema，则会创建在默认的schema中。

SHOW SEARCH_PATH;

SET SEARCH_PATH TO myschema, public;

SELECT current_schema();

元命令查看所有模式 /dn

opengauss 可以使用SHOW命令或pg_settings查看相关参数

查看版本

SELECT * FROM pg_settings WHERE NAME='server_version';

检查最大连接数

SHOW max_connections;

openGauss提供了多种修改GUC参数的方法，用户可以方便的针对数据库、用户、会话进行设置。系统视图 pg_settings中的 context字段。这个字段说明了参数生效所需的操作级别

参数类型	说明	设置方式
internal	固定参数，在创建数据库的时候确定，用户无法修改，只能通过show语法或者pg_settings视图进行查看	此类参数通常在编译时或初始化时确定，无法通过常规配置方式修改
postmaster	数据库服务端参数，在数据库启动时确定，可以通过配置文件指定。必须重启 PostgreSQL 服务	方式一和方式四修改
sighup	数据库全局参数，可在数据库启动时设置或者在数据库启动后，发送指令重新加载。	支持方式一、方式二、方式四。
backend	修改后需重新加载配置，但新值仅对新建立的连接会话生效，不影响现有连接。	支持方式一、方式二、方式四。说明：设置该参数后，下一次建立会话连接时生效。
superuser	超级用户可以在自己的会话中随时使用 SET命令修改，且仅影响当前会话。重新加载配置后对新连接生效	支持方式一、方式二或由数据库管理员通过方式三设置。
user	任何用户都可以在自己的会话中随时使用 SET命令修改，且仅影响当前会话。重新加载配置后对新连接生效	支持方式一、方式二或方式三设置。