KingbaseES运维案例之---迁移自定义表空间存储目录

案例说明：
在数据库自定义表空间后，原存储空间不足，需要将表空间存储迁移到新的位置。

适用版本：
KingbaseES V8R6

1、查看表空间信息

prod=# \db+
                                               List of tablespaces
    Name     | Owner  |             Location              | Access privileges | Options |   Size   | Descr
iption
-------------+--------+-----------------------------------+-------------------+---------+----------+------
-------
 sys_default | system |                                   |                   |         | 102 MB   |
 sys_global  | system |                                   |                   |         | 737 kB   |
 sysaudit    | system |                                   |                   |         | 32 kB    |
 users       | system | /home/kingbase/db/r6_c8/tps/users |                   |         | 18 bytes |
(4 rows)

# 创建测试数据
prod=# create table tu1 (id int ) tablespace users;
CREATE TABLE
prod=# insert into tu1 values (10);
INSERT 0 1
prod=# select * from tu1;
 id
----
 10
(1 row)

prod=# \d+ tu1;
                                    Table "public.tu1"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
 id     | integer |           |          |         | plain   |              |
Tablespace: "users"
Access method: heap

2、停止数据库服务

[kingbase@node201 bin]$ ./sys_ctl stop -D /home/kingbase/db/r6_c8/data/
waiting for server to shut down.... done
server stopped

3、拷贝表空间文件到新的目录下

[kingbase@node201 bin]$ ls -lh /home/kingbase/db/r6_c8/tps/users
total 0
drwx------ 4 kingbase kingbase 30 Jun 26 16:32 SYS_12_202305151
[kingbase@node201 bin]$ cp -r  /home/kingbase/db/r6_c8/tps/users/* /home/kingbase/db/tps/c8/users/
[kingbase@node201 bin]$ ls -lh  /home/kingbase/db/tps/c8/users/
total 0
drwx------ 4 kingbase kingbase 30 Jun 26 16:38 SYS_12_202305151

# 移除原表空间存储目录：

[kingbase@node201 bin]$ cd /home/kingbase/db/r6_c8/tps/
[kingbase@node201 tps]$ ls
users
[kingbase@node201 tps]$ mv users users.bk

4、创建新的表空间链接
在data/sys_tplspc目录下创建到新目录的链接：

[kingbase@node201 bin]$ cd /home/kingbase/db/r6_c8/data/sys_tblspc/
[kingbase@node201 sys_tblspc]$ ls -lh
total 0
lrwxrwxrwx 1 kingbase kingbase 33 Jun 11 11:25 16384 -> /home/kingbase/db/r6_c8/tps/users

[kingbase@node201 sys_tblspc]$ ln -sf /home/kingbase/db/tps/c8/users /home/kingbase/db/r6_c8/data/sys_tblspc/16384
[kingbase@node201 sys_tblspc]$ ls -lh
total 0
lrwxrwxrwx 1 kingbase kingbase 30 Jun 26 16:46 16384 -> /home/kingbase/db/tps/c8/users

创建新的链接：

5、启动数据库验证

# 启动数据库服务
[kingbase@node201 bin]$ ./sys_ctl start -D /home/kingbase/db/r6_c8/data/
.....
server started

# 访问表空间数据
[kingbase@node201 bin]$ ./ksql -U system prod -p 54325
Type "help" for help.

prod=# \db+
                                              List of tablespaces
    Name     | Owner  |            Location            | Access privileges | Options |    Size    | Descri
ption
-------------+--------+--------------------------------+-------------------+---------+------------+-------
------
 sys_default | system |                                |                   |         | 102 MB     |
 sys_global  | system |                                |                   |         | 737 kB     |
 sysaudit    | system |                                |                   |         | 32 kB      |
 users       | system | /home/kingbase/db/tps/c8/users |                   |         | 8228 bytes |
(4 rows)


prod=# \d+ tu1;
                                    Table "public.tu1"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
 id     | integer |           |          |         | plain   |              |
Tablespace: "users"
Access method: heap

prod=# select * from tu1;
 id
----
 10
(1 row)

如下所示，表空间存储路径自动迁移到新的目录：
原存储路径：

新存储路径：