# 1. 连接数据库
[fgedu@fgedu.net.cn ~]$ psql -h localhost -p 5432 -U fgedu -d postgres

# 2. 创建多租户数据库
postgres=> CREATE DATABASE fgedu_multitenant;
CREATE DATABASE

# 3. 连接多租户数据库
学习交流加群风哥微信: itpux-com
[fgedu@fgedu.net.cn ~]$ psql -h localhost -p 5432 -U fgedu -d fgedu_multitenant

# 4. 为每个租户创建模式和用户
# 租户1
fgedu_multitenant=> CREATE SCHEMA tenant1;
CREATE SCHEMA
fgedu_multitenant=> CREATE USER tenant1 WITH PASSWORD ‘Tenant1@123’ LOGIN;
CREATE ROLE
fgedu_multitenant=> GRANT USAGE ON SCHEMA tenant1 TO tenant1;
GRANT
fgedu_multitenant=> ALTER DEFAULT PRIVILEGES IN SCHEMA tenant1 GRANT ALL ON TABLES TO tenant1;
ALTER DEFAULT PRIVILEGES

# 租户2
fgedu_multitenant=> CREATE SCHEMA tenant2;
CREATE SCHEMA
fgedu_multitenant=> CREATE USER tenant2 WITH PASSWORD ‘Tenant2@123’ LOGIN;
CREATE ROLE
fgedu_multitenant=> GRANT USAGE ON SCHEMA tenant2 TO tenant2;
GRANT
fgedu_multitenant=> ALTER DEFAULT PRIVILEGES IN SCHEMA tenant2 GRANT ALL ON TABLES TO tenant2;
ALTER DEFAULT PRIVILEGES

# 租户3
fgedu_multitenant=> CREATE SCHEMA tenant3;
CREATE SCHEMA
fgedu_multitenant=> CREATE USER tenant3 WITH PASSWORD ‘Tenant3@123’ LOGIN;
CREATE ROLE
fgedu_multitenant=> GRANT USAGE ON SCHEMA tenant3 TO tenant3;
GRANT 学习交流加群风哥QQ113257174
fgedu_multitenant=> ALTER DEFAULT PRIVILEGES IN SCHEMA tenant3 GRANT ALL ON TABLES TO tenant3;
ALTER DEFAULT PRIVILEGES

# 5. 验证租户隔离
# 以租户1身份连接
[fgedu@fgedu.net.cn ~]$ psql -h localhost -p 5432 -U tenant1 -d fgedu_multitenant

# 创建表
fgedu_multitenant=> CREATE TABLE tenant1.users (id serial PRIMARY KEY, name varchar(100), email varchar(100));
CREATE TABLE

# 插入数据
fgedu_multitenant=> INSERT INTO tenant1.users (name, email) VALUES (‘User1’, ‘user1@example.com’);
INSERT 0 1

# 尝试访问其他租户的表（应该失败）
fgedu_multitenant=> SELECT * FROM tenant2.users;
ERROR: permission denied for schema tenant2

fgedu_multitenant=> \q

# 1. 连接数据库
[fgedu@fgedu.net.cn ~]$ psql -h localhost -p 5432 -U fgedu -d fgedu_multitenant

# 2. 为每个租户创建专用表空间
# 创建表空间目录
[fgedu@fgedu.net.cn ~]$ mkdir -p /gauss/fgdata/tbs/tenant1
[fgedu@fgedu.net.cn ~]$ mkdir -p /gauss/fgdata/tbs/tenant2
[fgedu@fgedu.net.cn ~]$ mkdir -p /gauss/fgdata/tbs/tenant3

# 创建表空间
fgedu_multitenant=> CREATE TABLESPACE tenant1_tbs LOCATION ‘/gauss/fgdata/tbs/tenant1’;
CREATE TABLESPACE
fgedu_multitenant=> CREATE TABLESPACE tenant2_tbs LOCATION ‘/gauss/fgdata/tbs/tenant2’;
CREATE TABLESPACE
fgedu_multitenant=> CREATE TABLESPACE tenant3_tbs LOCATION ‘/gauss/fgdata/tbs/tenant3’;
CREATE TABLESPACE

# 3. 为每个租户设置默认表空间
fgedu_multitenant=> ALTER USER tenant1 SET default_tablespace = ‘tenant1_tbs’;
ALTER ROLE
fgedu_multitenant=> ALTER USER tenant2 SET default_tablespace = ‘tenant2_tbs’;
ALTER ROLE
fgedu_multitenant=> ALTER USER tenant3 SET default_tablespace = ‘tenant3_tbs’;
ALTER ROLE

# 4. 测试表空间隔离
# 以租户1身份连接
[fgedu@fgedu.net.cn ~]$ psql -h localhost -p 5432 -U tenant1 -d fgedu_multitenant
更多学习教程公众号风哥教程itpux_com

# 创建表（默认使用tenant1_tbs表空间）
fgedu_multitenant=> CREATE TABLE tenant1.orders (id serial PRIMARY KEY, product varchar(100), quantity integer);
CREATE TABLE

# 查看表所在的表空间
fgedu_multitenant=> SELECT table_name, tablespace_name FROM information_schema.tables WHERE table_schema = ‘tenant1’;
table_name | tablespace_name
————+—————–
users |
orders | tenant1_tbs
(2 rows)

fgedu_multitenant=> \q

# 1. 连接数据库
[fgedu@fgedu.net.cn ~]$ psql -h localhost -p 5432 -U fgedu -d fgedu_multitenant

# 2. 创建资源组
from DB视频:www.itpux.com
fgedu_multitenant=> CREATE RESOURCE GROUP tenant1_group WITH (cpu_percent=30, memory_limit=30);
CREATE RESOURCE GROUP
fgedu_multitenant=> CREATE RESOURCE GROUP tenant2_group WITH (cpu_percent=30, memory_limit=30);
CREATE RESOURCE GROUP
fgedu_multitenant=> CREATE RESOURCE GROUP tenant3_group WITH (cpu_percent=40, memory_limit=40);
CREATE RESOURCE GROUP

# 3. 将租户用户分配到资源组
fgedu_multitenant=> ALTER USER tenant1 RESOURCE GROUP tenant1_group;
ALTER ROLE
fgedu_multitenant=> ALTER USER tenant2 RESOURCE GROUP tenant2_group;
ALTER ROLE
fgedu_multitenant=> ALTER USER tenant3 RESOURCE GROUP tenant3_group;
ALTER ROLE

# 4. 查看资源组配置
fgedu_multitenant=> SELECT * FROM pg_resource_group;
rgname | rggid | cpu_percent | memory_limit | active_statements | max_dynamic_memory
——————-+——-+————-+————–+——————-+——————-
default_group | 0 | 10 | 10 | 0 | 0
sysadmin_group | 1 | 10 | 10 | 0 | 0
tenant1_group | 16384 | 30 | 30 | 0 | 0
tenant2_group | 16385 | 30 | 30 | 0 | 0
tenant3_group | 16386 | 40 | 40 | 0 | 0
(5 rows)

# 5. 测试资源限制
# 以租户1身份连接并执行CPU密集型查询
[fgedu@fgedu.net.cn ~]$ psql -h localhost -p 5432 -U tenant1 -d fgedu_multitenant

# 创建测试表
fgedu_multitenant=> CREATE TABLE tenant1.test_data (id serial PRIMARY KEY, value integer);
CREATE TABLE

# 插入大量数据
fgedu_multitenant=> INSERT INTO tenant1.test_data (value) SELECT generate_series(1, 1000000);
INSERT 0 1000000

# 执行CPU密集型查询
fgedu_multitenant=> EXPLAIN ANALYZE SELECT SUM(value) FROM tenant1.test_data GROUP BY value % 1000;
# 观察查询执行情况，CPU使用率应该被限制在30%左右

fgedu_multitenant=> \q

多租户管理脚本：

#!/bin/bash
# tenant_management.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`

# 多租户管理脚本

# 数据库信息
DB_HOST=”localhost”
DB_PORT=”5432″
DB_USER=”fgedu”
DB_NAME=”fgedu_multitenant”

# 租户信息
TENANTS=(“tenant1” “tenant2” “tenant3”)
PASSWORDS=(“Tenant1@123” “Tenant2@123” “Tenant3@123”)

echo “开始多租户管理…”

# 创建租户
create_tenant() {
local tenant=$1
local password=$2

echo “创建租户 $tenant…”

# 创建模式
psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -c “CREATE SCHEMA IF NOT EXISTS $tenant;”

# 创建用户
psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -c “CREATE USER IF NOT EXISTS $tenant WITH PASSWORD ‘$password’ LOGIN;”

# 授权
psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -c “GRANT USAGE ON SCHEMA $tenant TO $tenant;”
psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -c “ALTER DEFAULT PRIVILEGES IN SCHEMA $tenant GRANT ALL ON TABLES TO $tenant;”

# 创建表空间
mkdir -p /gauss/fgdata/tbs/$tenant
psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -c “CREATE TABLESPACE IF NOT EXISTS ${tenant}_tbs LOCATION ‘/gauss/fgdata/tbs/$tenant’;”
psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -c “ALTER USER $tenant SET default_tablespace = ‘${tenant}_tbs’;”

echo “租户 $tenant 创建完成!”
}

# 删除租户
delete_tenant() {
local tenant=$1

echo “删除租户 $tenant…”

# 断开租户连接
psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -c “SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename = ‘$tenant’;”

# 删除用户
psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -c “DROP USER IF EXISTS $tenant;”

# 删除模式（级联删除模式下的所有对象）
psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -c “DROP SCHEMA IF EXISTS $tenant CASCADE;”

# 删除表空间
psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -c “DROP TABLESPACE IF EXISTS ${tenant}_tbs;”
rm -rf /gauss/fgdata/tbs/$tenant

echo “租户 $tenant 删除完成!”
}

# 查看租户信息
list_tenants() {
echo “查看租户信息…”

# 查看租户用户
psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -c “SELECT usename FROM pg_user WHERE usename LIKE ‘tenant%’;”

# 查看租户模式
psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -c “SELECT schema_name FROM information_schema.schemata WHERE schema_name LIKE ‘tenant%’;”

# 查看租户表空间
psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -c “SELECT spcname FROM pg_tablespace WHERE spcname LIKE ‘tenant%’;”
}

# 主菜单
echo “1. 创建租户”
echo “2. 删除租户”
echo “3. 查看租户信息”
echo “4. 退出”

read -p “请选择操作: ” choice

case $choice in
1)
for i in “${!TENANTS[@]}”; do
create_tenant “${TENANTS[$i]}” “${PASSWORDS[$i]}”
done
;;
2)
read -p “请输入要删除的租户名称: ” tenant
delete_tenant “$tenant”
;;
3)
list_tenants
;;
4)
echo “退出脚本”
exit 0
;;
*)
echo “无效选择”
;;
esac

echo “多租户管理完成!”

运行多租户管理脚本：

[fgedu@fgedu.net.cn ~]$ chmod +x tenant_management.sh
[fgedu@fgedu.net.cn ~]$ ./tenant_management.sh
开始多租户管理…
1. 创建租户
2. 删除租户
3. 查看租户信息
4. 退出
请选择操作: 1
创建租户 tenant1…
CREATE SCHEMA
CREATE ROLE
GRANT
ALTER DEFAULT PRIVILEGES
CREATE TABLESPACE
ALTER ROLE
租户 tenant1 创建完成!
创建租户 tenant2…
CREATE SCHEMA
CREATE ROLE
GRANT
ALTER DEFAULT PRIVILEGES
CREATE TABLESPACE
ALTER ROLE
租户 tenant2 创建完成!
创建租户 tenant3…
CREATE SCHEMA
CREATE ROLE
GRANT
ALTER DEFAULT PRIVILEGES
CREATE TABLESPACE
ALTER ROLE
租户 tenant3 创建完成!
多租户管理完成!