@SovietPower 2022-04-21T00:24:21.000000Z 字数 13182 阅读 1089

数据库系统实验1 安装命令

DB

数据库系统实验1 安装命令

某些问题解答：https://support.huaweicloud.com/intl/zh-cn/trouble-dws/dws-trouble.pdf

Docker安装

下载Docker Desktop for Windows：https://hub.docker.com/editions/community/docker-ce-desktop-windows
https://docs.docker.com/desktop/windows/install/
安装wsl：https://docs.microsoft.com/zh-cn/windows/wsl/install

网上教程基本都需要使用Hyper-V，但是Hyper-V会导致虚拟机无法启动（最新版本没事？）和其它问题？
最新的Docker使用Windows 10 Home 64-bit with WSL 2即可启动，不需要Hyper-V，且WSL 2 provides better performance than the legacy Hyper-V backend。
安装wsl后，安装Docker Desktop即可。

注意安装前可修改安装位置（默认为C盘）
删除"C:\Program Files\Docker"目录，创建"E:\Programs\Docker"目录，用cmd运行mklink /j "C:\Program Files\Docker" "E:\Programs\Docker"。
然后安装docker。
注意powershell不支持mklink，要先在powershell中运行cmd。

修改Docker镜像目录
https://www.jianshu.com/p/e79f4c938000

PS E:\> wsl --list -v
  NAME                   STATE           VERSION
* Ubuntu                 Stopped         2
  docker-desktop-data    Stopped         2
  docker-desktop         Stopped         2
PS E:\> wsl --export docker-desktop-data "E:\Docker\docker-desktop-data.tar"
PS E:\> dir "E:\Docker"
    目录: E:\Docker
Mode                 LastWriteTime         Length Name
----                 -------------         ------ ----
d-----          2022/3/1     10:39                Image
-a----          2022/3/1     12:51      665978880 docker-desktop-data.tar
PS E:\> wsl --unregister docker-desktop-data
正在注销...
PS E:\> wsl --list -v
  NAME              STATE           VERSION
* Ubuntu            Stopped         2
  docker-desktop    Stopped         2
PS E:\> wsl --import docker-desktop-data "E:\Docker" "E:\Docker\docker-desktop-data.tar" --version 2
PS E:\> wsl --list -v
  NAME                   STATE           VERSION
* Ubuntu                 Stopped         2
  docker-desktop-data    Stopped         2
  docker-desktop         Stopped         2

备份文件"E:\Docker\docker-desktop-data.tar"可以删除。
导入完成后在新目录下会存在一个ext4.vhdx的虚拟磁盘路径。

安装vim
在root下（而不是omm下）：

apt-get update
apt-get install vim

修改容器中文件的方法
https://blog.csdn.net/m0_67391377/article/details/123871347

docker命令

docker run 创建容器
docker ps 列出正在运行的容器。-a列出所有（含未运行的）。
docker start [OPTIONS] [CONTAINER] [CONTAINER...] 运行已停止运行的容器
docker exec -it CONTAINER bash 进入正在运行的容器
docker stop [CONTAINER] 停止正在运行的容器
docker rm [CONTAINER] 删除容器

使用问题

Error response from daemon: open \.\pipe\docker_engine_linux: The system cannot find the file specified.
重新运行了一下命令就好了。

openGuass安装

使用前需开启 Docker Desktop。
安装：
从dockerhub可拉取最新的openGauss镜像：

PS E:\> docker pull enmotech/opengauss
Using default tag: latest
latest: Pulling from enmotech/opengauss
284055322776: Pull complete
a7ca82b898d7: Pull complete
3842013b7685: Pull complete
6bc7e92855e3: Pull complete
39c9c4e5b487: Pull complete
1f9d76df94b5: Pull complete
44db1c59ef84: Pull complete
63ab02376fd3: Pull complete
cf751b0b3be9: Pull complete
9dc428e2c8b4: Pull complete
Digest: sha256:d5a3e38fa2553a44e7fa1cd5cad0b4f0845a679858764067d7b0052a228578a0
Status: Downloaded newer image for enmotech/opengauss:latest
docker.io/enmotech/opengauss:latest

创建并运行openGuass镜像的一个容器：

PS E:\> docker run --name opengauss --privileged=true -d -e GS_PASSWORD=Password@123 -p 15432:5432 enmotech/opengauss:latest
0c4bfd4e2ab4769a86a68bf6b9219279b935d010e2e19d076b509c5458e44031
PS E:\> docker ps
CONTAINER ID   IMAGE                       COMMAND                  CREATED          STATUS         PORTS                     NAMES
0c4bfd4e2ab4   enmotech/opengauss:latest   "entrypoint.sh gauss…"   13 seconds ago   Up 5 seconds   0.0.0.0:15432->5432/tcp   opengauss
PS E:\> docker exec -it opengauss bash
root@0c4bfd4e2ab4:/# su omm
omm@0c4bfd4e2ab4:/$ ls
bin   dev                         entrypoint.sh  home  lib64  mnt  proc  run   srv  tmp  var
boot  docker-entrypoint-initdb.d  etc            lib   media  opt  root  sbin  sys  usr
omm@0c4bfd4e2ab4:/$ exit
exit
root@0c4bfd4e2ab4:/# exit
exit
PS E:\>

第一条，使用enmotech/opengauss:latest镜像，创建并启动一个openGauss数据库的实例，为名为opengauss的容器。
第二条，docker ps查看当前系统中的正在运行的容器。
第三条，在容器内部启动一个Bash Shell 进程，并连接到当前PowerShell窗口。
su omm切换到openGauss的超级用户omm。

在 Docker 容器退出时，默认容器内部的文件系统仍然被保留，以方便调试并保留用户数据。此时容器状态为exited。
若要短期运行而不保留数据，可在docker run后加--rm参数。

点击容器可查看详细信息（日志等）。

openGuass命令

gsql命令参数

切换到omm用户：
su omm

使用omm连接：
gsql

使用测试用户连接openGuass数据库（切换到omm后）：
gsql -d postgres -U gaussdb -W 'Password@123'

可能的错误：
failed to connect Unknown:5432. 重启一下该容器，再运行。
gsql: FATAL: Invalid username/password,login denied. 未解决
重新建立容器后解决？

使用omm用户连接openGuass数据库（切换到omm后）：
gsql -d postgres -r

内部命令
\?：查看openGauss内部命令的帮助信息
\help：查看SQL语句的帮助信息
\l：查看所有数据库对象
\q：关闭
\du：显示所有用户及权限
\c [db_name]：连接另一个数据库
\c - [username]：切换到另一个用户
\d或\dt：显示当前数据库中的关系（表）
\di：查看当前数据库中的所有索引对象
\d [table_name]查看表的结构和属性
\dn[S+] [PATTERN] list schemas
\dt[S+] [PATTERN] list tables
\dv[S+] [PATTERN] list views
\sf[+] FUNCNAME show a function's definition

在查看对象的命令后加+，可查看更详细信息。

数据库命令

连接后=#表示输入命令，-#表示继续输入上一行未完成的命令（缺少;）。

创建新用户
create user [username] with password '[password]';
password需用引号包括。至少需有三种字符（如大小写字母+数字）。

修改用户密码
alter user gxb with password 'Gxb11111';
密码不能和之前使用的相同。

授权
username为public时，表示所有用户。
grant all privileges to [username]; 为某用户授予所有权限（仅含增删改查）
grant all privileges on [db_name] [,db_name2, ...] to [username] [,username2, ...]; 为某用户授予某数据库上的所有权限（仅含增删改查）
grant select/insert/update/delete on [db] to [username]; 为某用户授予某数据库上的某些权限
alter role [username] [privilege] 授予某用户某权限（更高级）
[privilege]可为：createdb等。

创建数据库
create database [db_name];
create database [db_name] owner [username]; 指定owner

删除数据库
drop database [db_name];

函数

函数定义

CREATE [OR REPLACE] FUNCTION function_name (arguments)  
RETURNS return_datatype AS $variable_name$ 
 DECLARE
  declaration; 
  [...] 
 BEGIN
  < function_body > 
  [...] 
  RETURN { variable_name | value } 
 END; LANGUAGE plpgsql;

function_name:指定函数的名称。
DECLARE:定义参数(参数名写在前面类型写在后面)。
BEGIN~END: 在中间写方法主体。
RETURN:指定要从函数返回的数据类型(它可以是基础，复合或域类型，或者也可以引用表列的类型)。
LANGUAGE:它指定实现该函数的语言的名称。
例（一般不用 $return_value$ ？）：

create or replace function gradeToPoint(in g varchar(2))
returns numeric(2,1) as $$
    declare
        res numeric(2,1);
    begin
        select point into res
        from grade_point
        where grade=g;
        return res;
    end;
       $$ LANGUAGE plpgsql;

视图

更新视图
不能直接更新，必须声明有instead of的触发器，指定insert等时要干什么。然后可以返回新的faculty值（不能返回原来的faculty），也可以不返回。
例：

create view faculty as
    select ID, name, dept_name
    from instructor;
create or replace rule rl_faculty_insert as
    on insert to faculty do instead
    insert into instructor values(new.ID, new.name, new.dept_name)
    returning instructor.ID, instructor.name, instructor.dept_name;
create or replace rule r1_CSinstructors_insert as
    on insert to CSinstructors do instead
    insert into instructor values(new.ID, new.name, new.dept_name, new.salary);

update例：

CREATE OR REPLACE RULE rl_test_view_update AS
    ON UPDATE TO test_view DO INSTEAD
    UPDATE test SET a = NEW.a, b = NEW.b
    WHERE test.a = old.a AND test.b = old.b;

创建并更新数据库

创建数据库

先新建一个用户。远程登录不允许使用omm用户？
然后创建数据库，并授予新用户权限。

openGauss=# create user gxb with password "Gxb12345";
CREATE ROLE
openGauss=# create database db2022 owner gxb;
CREATE DATABASE
openGauss=# grant all privileges to GXB
openGauss-# ;
ALTER ROLE
openGauss=# \c db2022
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "db2022" as user "omm".
db2022=# \du
                                                              List of roles
 Role name |                                                    Attributes
      | Member of
-----------+------------------------------------------------------------------------------------------------------------
------+-----------
 gxb       | Sysadmin
      | {}
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin,
UseFT | {}
db2022=# \c - gxb
Password for user gxb:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "db2022" as user "gxb".
db2022=> \l
                              List of databases
   Name    | Owner | Encoding |   Collate   |    Ctype    | Access privileges
-----------+-------+----------+-------------+-------------+-------------------
 db2022    | gxb   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/gxb          +
           |       |          |             |             | gxb=CTc/gxb      +
           |       |          |             |             | gxb=APm/gxb
 postgres  |       | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 |       | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/omm           +
           |       |          |             |             | omm=CTc/omm
 template1 |       | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/omm           +
           |       |          |             |             | omm=CTc/omm
(6 rows)

更新数据库

直接输入SQL语句。
创建表

db2022=> \d
No relations found.
db2022=> create table classroom
db2022-> (building varchar(15),
db2022(> room_number varchar(7),
db2022(> capacity numeric(4,0),
db2022(> primary key (building, room_number)
db2022(> );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "classroom_pkey" for table "classroom"
CREATE TABLE
db2022=> create table department
db2022-> (dept_name varchar(20),
db2022(> building varchar(15),
db2022(> budget numeric(12,2) check (budget > 0),
db2022(> primary key (dept_name)
db2022(> );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "department_pkey" for table "department"
CREATE TABLE
db2022=> \d
                           List of relations
 Schema |    Name    | Type  | Owner |             Storage
--------+------------+-------+-------+----------------------------------
 public | classroom  | table | gxb   | {orientation=row,compression=no}
 public | department | table | gxb   | {orientation=row,compression=no}
(2 rows)
...

插入数据

db2022=> insert into classroom values ('Packard', '101', '500');
INSERT 0 1
db2022=> insert into classroom values ('Painter', '514', '10');
INSERT 0 1
db2022=> insert into classroom values ('Taylor', '3128', '70');
INSERT 0 1
...

查询数据库

db2022=> select * from advisor;
 s_id  | i_id
-------+-------
 00128 | 45565
 12345 | 10101
 23121 | 76543
 44553 | 22222
 45678 | 22222
 76543 | 45565
 76653 | 98345
 98765 | 98345
 98988 | 76766
(9 rows)

查看数据库信息
\d查看当前数据库所有数据表对象：

db2022=> \d
                           List of relations
 Schema |    Name    | Type  | Owner |             Storage
--------+------------+-------+-------+----------------------------------
 public | advisor    | table | gxb   | {orientation=row,compression=no}
 public | classroom  | table | gxb   | {orientation=row,compression=no}
 public | course     | table | gxb   | {orientation=row,compression=no}
 public | department | table | gxb   | {orientation=row,compression=no}
 public | instructor | table | gxb   | {orientation=row,compression=no}
 public | prereq     | table | gxb   | {orientation=row,compression=no}
 public | section    | table | gxb   | {orientation=row,compression=no}
 public | student    | table | gxb   | {orientation=row,compression=no}
 public | takes      | table | gxb   | {orientation=row,compression=no}
 public | teaches    | table | gxb   | {orientation=row,compression=no}
 public | time_slot  | table | gxb   | {orientation=row,compression=no}
(11 rows)

\d [table_name]查看表的结构和属性。
\d+ [table_name]查看更详细的表的结构和属性。

db2022=> \d+ advisor
                              Table "public.advisor"
 Column |         Type         | Modifiers | Storage  | Stats target | Description
--------+----------------------+-----------+----------+--------------+-------------
 s_id   | character varying(5) | not null  | extended |              |
 i_id   | character varying(5) |           | extended |              |
Indexes:
    "advisor_pkey" PRIMARY KEY, btree (s_id) TABLESPACE pg_default
Foreign-key constraints:
    "advisor_i_id_fkey" FOREIGN KEY (i_id) REFERENCES instructor(id) ON DELETE SET NULL
    "advisor_s_id_fkey" FOREIGN KEY (s_id) REFERENCES student(id) ON DELETE CASCADE
Has OIDs: no
Options: orientation=row, compression=no

\di：查看当前数据库中的所有索引对象。

db2022=> \di
                        List of relations
 Schema |      Name       | Type  | Owner |   Table    | Storage
--------+-----------------+-------+-------+------------+---------
 public | advisor_pkey    | index | gxb   | advisor    |
 public | classroom_pkey  | index | gxb   | classroom  |
 public | course_pkey     | index | gxb   | course     |
 public | department_pkey | index | gxb   | department |
 public | instructor_pkey | index | gxb   | instructor |
 public | prereq_pkey     | index | gxb   | prereq     |
 public | section_pkey    | index | gxb   | section    |
 public | student_pkey    | index | gxb   | student    |
 public | takes_pkey      | index | gxb   | takes      |
 public | teaches_pkey    | index | gxb   | teaches    |
 public | time_slot_pkey  | index | gxb   | time_slot  |
(11 rows)

-q退出数据库。

使用Data Studio

Data Studio为openGauss官方图形客户端工具。
按照如下输入新建连接：

无法连接外部数据库

server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.

https://stackoverflow.com/questions/9764126/how-to-configure-postgresql-postgresql-conf-listen-addresses-for-multiple-ip-add#

没有将外部的IP添加到配置listen_addresses中，所以会不监听外部的请求。
gsql连接任一数据库，用show listen_addresses;查看当前配置，默认只有localhost（PostgreSQL数据库为了安全，它不会监听除本地以外的所有连接请求）。

通过alter system set listen_addresses = '*';将其改为允许任意IP访问。
或是直接修改文件/var/lib/opengauss/data/postgresql.conf，改为listen_addresses = '*'。

FATAL: no pg_hba.conf entry for host "172.17.0.1".
修改listen_addresses后又出现了该错误。
没有配置pg_hba。除了上述的修改监听地址外，还需在此添加允许建立连接的地址。
修改文件/var/lib/opengauss/data/pg_hba.conf：
在# IPv4 local connections:下面，添加host all all 172.17.0.1/32 md5。md5表示用密码连接，也可以用sha256，改为trust表示不需要密码（但数据库禁止openGauss内部其他节点无认证接入，除本地外不能用trust，否则会出Forbid remote connection with trust method）。
或者直接：

vim /var/lib/opengauss/data/pg_hba.conf
#TYPE   DATABASE        USER            ADDRESS                 METHOD
    host    all             all             0.0.0.0/0               md5

表示允许所有主机通过TCP/IP建立连接，认证方式为MD5，允许使用所有用户，连接所有的数据库。

fe_ sendauth: invalid authentication request from server: AUTH REQ SASL CONT
omm用户进入数据库，使用alter system set password_encryption_type=1;。
然后使用其它用户连接数据库（不允许使用omm远程登录）。

然后需要重启容器！

none of the server's SASL authentication mechanisms are supported
用alter system set password_encryption_type=1;将password_encryption_type设为0或1。
然后更新一下登录用户的密码。

其他错误

切换用户时 No passwd entry for user
https://unix.stackexchange.com/questions/308668/using-su-to-change-user-gives-no-passwd-entry-for-user
不存在当前用户。可以通过cut -d: -f1 /etc/passwd查看用户，adduser [name]添加用户。

将Docker容器保存为镜像文件，并重新创建容器

docker commit opengauss mygauss:2022-03-14 将容器opengauss打包成名为mygauss的镜像。
docker images 查看本地仓库中的镜像。
docker save -o mygauss.tar mygauss 将镜像保存为文件。镜像文件可以用于在不同系统间实现迁移镜像。
docker image rm mygauss:2022-03-14 将本地仓库中的mygauss镜像删除。
docker load --input mygauss.tar 将镜像文件mygauss.tar加载到本地仓库。

PS H:\> docker commit opengauss mygauss:2022-03-14
sha256:b1d3144a639eb7a347ff1c2206baba702e2f43f83c02cda00ae41aa50247daa3
PS H:\> docker images
REPOSITORY           TAG          IMAGE ID       CREATED              SIZE
mygauss              2022-03-14   b1d3144a639e   About a minute ago   1.21GB
enmotech/opengauss   latest       b4dd24d09223   5 months ago         383MB
PS H:\> docker save -o mygauss.tar mygauss

根据打包的镜像，创建新的一样的容器：

PS H:\> docker run --name opengauss3 --privileged=true -d -e GS_PASSWORD=Password@123 -p 15432:5432 mygauss:2022-03-14
97375c3acbd1046ba2b6ccf5d24b7e758bab7bcf067c0869f745d6acfb125b8f
PS H:\> docker exec -it opengauss3 bash
root@97375c3acbd1:/# su - omm
omm@97375c3acbd1:~$ gsql
gsql: FATAL:  database "omm" does not exist
omm@97375c3acbd1:~$ gsql -d postgres -r
gsql ((openGauss 2.1.0 build 590b0f8e) compiled at 2021-09-30 14:29:04 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
openGauss=# \l
                              List of databases
   Name    | Owner | Encoding |   Collate   |    Ctype    | Access privileges
-----------+-------+----------+-------------+-------------+-------------------
 db2022    | gxb   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/gxb          +
           |       |          |             |             | gxb=CTc/gxb      +
           |       |          |             |             | gxb=APm/gxb
 postgres  | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/omm           +
           |       |          |             |             | omm=CTc/omm
 template1 | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/omm           +
           |       |          |             |             | omm=CTc/omm
(4 rows)