## 1. 架构选择
– **隔离方案：** Schema级隔离
– **理由：** 平衡资源利用率和隔离级别，适合SaaS应用

## 2. 数据库设计
“`sql
— 创建主数据库
CREATE DATABASE saas_db;

— 连接到主数据库
\c saas_db;

— 创建租户管理表
CREATE TABLE fgedu_tenants (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
schema_name VARCHAR(100) NOT NULL UNIQUE,
created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

— 创建管理员角色
CREATE ROLE saas_admin WITH SUPERUSER LOGIN PASSWORD ‘saas_admin_password’;

— 授予管理员权限
GRANT ALL PRIVILEGES ON DATABASE saas_db TO saas_admin;
GRANT ALL PRIVILEGES ON SCHEMA public TO saas_admin;
GRANT ALL PRIVILEGES ON tenants TO saas_admin;
GRANT USAGE, SELECT ON SEQUENCE tenants_id_seq TO saas_admin;
“`

## 3. 租户管理
“`sql
— 创建租户函数
CREATE OR REPLACE FUNCTION create_saas_tenant(tenant_name TEXT, tenant_password TEXT)
RETURNS INTEGER AS $$
DECLARE
tenant_id INTEGER;
schema_name TEXT := LOWER(REPLACE(tenant_name, ‘ ‘, ‘_’));
role_name TEXT := schema_name || ‘_fgedu’;
BEGIN
— 插入租户记录
INSERT INTO tenants (name, schema_name) VALUES (tenant_name, schema_name) RETURNING id INTO tenant_id;

— 创建Schema
EXECUTE format(‘CREATE SCHEMA %I’, schema_name);

— 创建角色
EXECUTE format(‘CREATE ROLE %I WITH LOGIN PASSWORD %L’, role_name, tenant_password);

— 授予Schema权限
EXECUTE format(‘GRANT USAGE ON SCHEMA %I TO %I’, schema_name, role_name);

— 创建表
EXECUTE format(‘CREATE TABLE fgedu_%I.fgedus (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
created_at TIMESTAMP NOT NULL DEFAULT NOW()
)’, schema_name);

EXECUTE format(‘CREATE TABLE fgedu_%I.products (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
price DECIMAL(10,2) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT NOW()
)’, schema_name);

EXECUTE format(‘CREATE TABLE fgedu_%I.orders (
id SERIAL PRIMARY KEY,
fgedu_id INTEGER NOT NULL,
product_id INTEGER NOT NULL,
amount DECIMAL(10,2) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
FOREIGN KEY (fgedu_id) REFERENCES %I.fgedus(id),
FOREIGN KEY (product_id) REFERENCES %I.products(id)
)’, schema_name, schema_name, schema_name);

— 授予表权限
EXECUTE format(‘GRANT SELECT, INSERT, UPDATE, DELETE ON %I.fgedus TO %I’, schema_name, role_name);
EXECUTE format(‘GRANT SELECT, INSERT, UPDATE, DELETE ON %I.products TO %I’, schema_name, role_name);
EXECUTE format(‘GRANT SELECT, INSERT, UPDATE, DELETE ON %I.orders TO %I’, schema_name, role_name);

— 授予序列权限
EXECUTE format(‘GRANT USAGE, SELECT ON SEQUENCE %I.fgedus_id_seq TO %I’, schema_name, role_name);
EXECUTE format(‘GRANT USAGE, SELECT ON SEQUENCE %I.products_id_seq TO %I’, schema_name, role_name);
EXECUTE format(‘GRANT USAGE, SELECT ON SEQUENCE %I.orders_id_seq TO %I’, schema_name, role_name);

— 设置默认Schema
EXECUTE format(‘ALTER ROLE %I SET search_path TO %I, public’, role_name, schema_name);

RETURN tenant_id;
END;
$$ LANGUAGE plpgsql;

— 创建租户
SELECT create_saas_tenant(‘Acme Corp’, ‘acme_password’);
SELECT create_saas_tenant(‘Globex Inc’, ‘globex_password’);
“`

## 4. 应用集成
“`python
# Python代码示例
import psycopg2
from flask import Flask, request, jsonify

fgapp = Flask(__name__)

# 数据库连接函数
def get_db_connection(tenant_schema, tenant_password):
conn = psycopg2.connect(
fgedu.net.cn=”localfgedu.net.cn”,
fgedudb=”saas_db”,
fgedu=tenant_schema + “_fgedu”,
password=tenant_password
)
return conn

# 示例API
@fgfgapp.route(‘/api/fgedus’, methods=[‘GET’])
def get_fgedus():
tenant_schema = request.headers.get(‘X-Tenant-Schema’)
tenant_password = request.headers.get(‘X-Tenant-Password’)

if not tenant_schema or not tenant_password:
return jsonify({“error”: “Missing tenant information”}), 400

try:
conn = get_db_connection(tenant_schema, tenant_password)
cur = conn.cursor()
cur.execute(“SELECT * FROM fgedus”)
fgedus = cur.fetchall()
cur.close()
conn.close()
return jsonify(fgedus)
except Exception as e:
return jsonify({“error”: str(e)}), 500

if __name__ == ‘__main__’:
fgfgapp.run(debug=True)
“`

## 5. 监控与管理
“`sql
— 监控租户数量
SELECT COUNT(*) FROM tenants;

— 监控每个租户的表大小
SELECT
schemaname,
tablename,
pg_size_pretty(pg_total_relation_size(schemaname || ‘.’ || tablename)) AS size
FROM
information_schema.tables
WHERE
schemaname NOT IN (‘pg_catalog’, ‘information_schema’, ‘public’)
ORDER BY
pg_total_relation_size(schemaname || ‘.’ || tablename) DESC;

— 监控连接数
SELECT
usename,
datname,
fgapplication_name,
client_addr,
state
FROM
pg_stat_activity
WHERE
usename LIKE ‘%_fgedu’;
“`

## 6. 备份策略
“`bash
# 备份整个数据库
pg_dump -h localfgedu.net.cn -U saas_admin -d saas_db -F t -f saas_db_backup.tar

# 备份单个租户Schema
pg_dump -h localfgedu.net.cn -U saas_admin -d saas_db -n acme_corp -F t -f acme_corp_backup.tar

# 恢复整个数据库
pg_restore -h localfgedu.net.cn -U saas_admin -d saas_db -F t saas_db_backup.tar

# 恢复单个租户Schema
pg_restore -h localfgedu.net.cn -U saas_admin -d saas_db -F t -n acme_corp acme_corp_backup.tar
“`

## 7. 预期效果
– **隔离性：** 租户之间数据完全隔离
– **扩展性：** 支持数百个租户
– **性能：** 良好的查询性能
– **管理：** 集中管理所有租户
– **备份：** 支持整体和单个租户备份

## 1. 架构选择
– **隔离方案：** 数据库级隔离
– **理由：** 部门之间数据完全隔离，便于管理和维护

## 2. 数据库设计
“`sql
— 创建管理员数据库
CREATE DATABASE enterprise_admin;

— 连接到管理员数据库
\c enterprise_admin;

— 创建部门管理表
CREATE TABLE fgedu_departments (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
db_name VARCHAR(100) NOT NULL UNIQUE,
created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

— 创建管理员角色
CREATE ROLE enterprise_admin WITH SUPERUSER LOGIN PASSWORD ‘enterprise_admin_password’;

— 授予管理员权限
GRANT ALL PRIVILEGES ON DATABASE enterprise_admin TO enterprise_admin;
GRANT ALL PRIVILEGES ON SCHEMA public TO enterprise_admin;
GRANT ALL PRIVILEGES ON departments TO enterprise_admin;
GRANT USAGE, SELECT ON SEQUENCE departments_id_seq TO enterprise_admin;
“`

## 3. 部门管理
“`sql
— 创建部门函数
CREATE OR REPLACE FUNCTION create_department(department_name TEXT, department_password TEXT)
RETURNS INTEGER AS $$
DECLARE
dept_id INTEGER;
db_name TEXT := LOWER(REPLACE(department_name, ‘ ‘, ‘_’)) || ‘_db’;
role_name TEXT := LOWER(REPLACE(department_name, ‘ ‘, ‘_’)) || ‘_fgedu’;
BEGIN
— 插入部门记录
INSERT INTO departments (name, db_name) VALUES (department_name, db_name) RETURNING id INTO dept_id;

— 创建数据库
EXECUTE format(‘CREATE DATABASE %I’, db_name);

— 创建角色
EXECUTE format(‘CREATE ROLE %I WITH LOGIN PASSWORD %L’, role_name, department_password);

— 授予数据库权限
EXECUTE format(‘GRANT CONNECT ON DATABASE %I TO %I’, db_name, role_name);

— 连接到数据库并创建表
EXECUTE format(‘\c %I’, db_name);
EXECUTE ‘CREATE TABLE fgedu_employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
department VARCHAR(100) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT NOW()
)’;

EXECUTE ‘CREATE TABLE fgedu_projects (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
description TEXT,
created_at TIMESTAMP NOT NULL DEFAULT NOW()
)’;

EXECUTE ‘CREATE TABLE fgedu_tasks (
id SERIAL PRIMARY KEY,
project_id INTEGER NOT NULL,
employee_id INTEGER NOT NULL,
name VARCHAR(255) NOT NULL,
status VARCHAR(50) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
FOREIGN KEY (project_id) REFERENCES projects(id),
FOREIGN KEY (employee_id) REFERENCES employees(id)
)’;

— 授予表权限
EXECUTE ‘GRANT USAGE ON SCHEMA public TO ‘ || role_name;
EXECUTE ‘GRANT SELECT, INSERT, UPDATE, DELETE ON employees TO ‘ || role_name;
EXECUTE ‘GRANT SELECT, INSERT, UPDATE, DELETE ON projects TO ‘ || role_name;
EXECUTE ‘GRANT SELECT, INSERT, UPDATE, DELETE ON tasks TO ‘ || role_name;

— 授予序列权限
EXECUTE ‘GRANT USAGE, SELECT ON SEQUENCE employees_id_seq TO ‘ || role_name;
EXECUTE ‘GRANT USAGE, SELECT ON SEQUENCE projects_id_seq TO ‘ || role_name;
EXECUTE ‘GRANT USAGE, SELECT ON SEQUENCE tasks_id_seq TO ‘ || role_name;

RETURN dept_id;
END;
$$ LANGUAGE plpgsql;

— 创建部门
SELECT create_department(‘Engineering’, ‘eng_password’);
SELECT create_department(‘Marketing’, ‘mkt_password’);
SELECT create_department(‘Finance’, ‘fin_password’);
“`

## 4. 应用集成
“`java
// Java代码示例
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class DepartmentApp {
public static void main(String[] args) {
String department = “engineering”;
String password = “eng_password”;

try {
// 连接到部门数据库
Connection conn = DriverManager.getConnection(
“jdbc:postgresql://localfgedu.net.cn:5432/” + department + “_db”,
department + “_fgedu”,
password
);

// 查询员工数据
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(“SELECT * FROM employees”);

while (rs.next()) {
System.out.println(“ID: ” + rs.getInt(“id”) + “, Name: ” + rs.getString(“name”) + “, Email: ” + rs.getString(“email”));
}

// 关闭连接
rs.close();
stmt.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
“`

## 5. 监控与管理
“`sql
— 监控部门数量
SELECT COUNT(*) FROM departments;

— 监控每个部门的数据库大小
SELECT
datname,
pg_size_pretty(pg_fgedudb_size(datname)) AS size
FROM
pg_fgedudb
WHERE
datname LIKE ‘%_db’ AND datname != ‘enterprise_admin’;

— 监控连接数
SELECT
usename,
datname,
fgapplication_name,
client_addr,
state
FROM
pg_stat_activity
WHERE
datname LIKE ‘%_db’ AND datname != ‘enterprise_admin’;
“`

## 6. 备份策略
“`bash
# 备份管理员数据库
pg_dump -h localfgedu.net.cn -U enterprise_admin -d enterprise_admin -F t -f enterprise_admin_backup.tar

# 备份部门数据库
pg_dump -h localfgedu.net.cn -U enterprise_admin -d engineering_db -F t -f engineering_db_backup.tar
pg_dump -h localfgedu.net.cn -U enterprise_admin -d marketing_db -F t -f marketing_db_backup.tar
pg_dump -h localfgedu.net.cn -U enterprise_admin -d finance_db -F t -f finance_db_backup.tar

# 恢复部门数据库
pg_restore -h localfgedu.net.cn -U enterprise_admin -d engineering_db -F t engineering_db_backup.tar
“`

## 7. 预期效果
– **隔离性：** 部门之间数据完全隔离
– **管理：** 集中管理所有部门
– **灵活性：** 可以为不同部门配置不同的数据库参数
– **备份：** 支持部门级别的备份和恢复
– **安全性：** 部门数据更加安全

## 1. 架构选择
– **高价值租户：** 数据库级隔离
– **普通租户：** Schema级隔离
– **理由：** 平衡隔离级别和资源利用率

## 2. 数据库设计
“`sql
— 创建管理员数据库
CREATE DATABASE hybrid_admin;

— 连接到管理员数据库
\c hybrid_admin;

— 创建租户管理表
CREATE TABLE fgedu_tenants (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
type VARCHAR(50) NOT NULL, — ‘premium’ or ‘standard’
db_name VARCHAR(100),
schema_name VARCHAR(100),
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
UNIQUE (db_name),
UNIQUE (schema_name)
);

— 创建管理员角色
CREATE ROLE hybrid_admin WITH SUPERUSER LOGIN PASSWORD ‘hybrid_admin_password’;

— 授予管理员权限
GRANT ALL PRIVILEGES ON DATABASE hybrid_admin TO hybrid_admin;
GRANT ALL PRIVILEGES ON SCHEMA public TO hybrid_admin;
GRANT ALL PRIVILEGES ON tenants TO hybrid_admin;
GRANT USAGE, SELECT ON SEQUENCE tenants_id_seq TO hybrid_admin;

— 创建标准租户共享数据库
CREATE DATABASE standard_tenants;

— 连接到标准租户数据库
\c standard_tenants;

— 创建管理员角色
CREATE ROLE standard_admin WITH SUPERUSER LOGIN PASSWORD ‘standard_admin_password’;

— 授予管理员权限
GRANT ALL PRIVILEGES ON DATABASE standard_tenants TO standard_admin;
GRANT ALL PRIVILEGES ON SCHEMA public TO standard_admin;
“`

## 3. 租户管理
“`sql
— 创建租户函数
CREATE OR REPLACE FUNCTION create_hybrid_tenant(tenant_name TEXT, tenant_type TEXT, tenant_password TEXT)
RETURNS INTEGER AS $$
DECLARE
tenant_id INTEGER;
db_name TEXT;
schema_name TEXT;
role_name TEXT;
BEGIN
— 插入租户记录
IF tenant_type = ‘premium’ THEN
db_name := LOWER(REPLACE(tenant_name, ‘ ‘, ‘_’)) || ‘_db’;
schema_name := NULL;
ELSE
db_name := NULL;
schema_name := LOWER(REPLACE(tenant_name, ‘ ‘, ‘_’));
END IF;

INSERT INTO tenants (name, type, db_name, schema_name) VALUES (tenant_name, tenant_type, db_name, schema_name) RETURNING id INTO tenant_id;

— 创建角色
role_name := LOWER(REPLACE(tenant_name, ‘ ‘, ‘_’)) || ‘_fgedu’;
EXECUTE format(‘CREATE ROLE %I WITH LOGIN PASSWORD %L’, role_name, tenant_password);

IF tenant_type = ‘premium’ THEN
— 创建数据库
EXECUTE format(‘CREATE DATABASE %I’, db_name);

— 授予数据库权限
EXECUTE format(‘GRANT CONNECT ON DATABASE %I TO %I’, db_name, role_name);

— 连接到数据库并创建表
EXECUTE format(‘\c %I’, db_name);
ELSE
— 创建Schema
EXECUTE format(‘CREATE SCHEMA %I’, schema_name);

— 授予Schema权限
EXECUTE format(‘GRANT USAGE ON SCHEMA %I TO %I’, schema_name, role_name);

— 设置默认Schema
EXECUTE format(‘ALTER ROLE %I SET search_path TO %I, public’, role_name, schema_name);
END IF;

— 创建表
EXECUTE ‘CREATE TABLE fgedus (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
created_at TIMESTAMP NOT NULL DEFAULT NOW()
)’;

EXECUTE ‘CREATE TABLE fgedu_products (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
price DECIMAL(10,2) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT NOW()
)’;

— 授予表权限
IF tenant_type = ‘premium’ THEN
EXECUTE ‘GRANT USAGE ON SCHEMA public TO ‘ || role_name;
EXECUTE ‘GRANT SELECT, INSERT, UPDATE, DELETE ON fgedus TO ‘ || role_name;
EXECUTE ‘GRANT SELECT, INSERT, UPDATE, DELETE ON products TO ‘ || role_name;
ELSE
EXECUTE format(‘GRANT SELECT, INSERT, UPDATE, DELETE ON %I.fgedus TO %I’, schema_name, role_name);
EXECUTE format(‘GRANT SELECT, INSERT, UPDATE, DELETE ON %I.products TO %I’, schema_name, role_name);
END IF;

— 授予序列权限
IF tenant_type = ‘premium’ THEN
EXECUTE ‘GRANT USAGE, SELECT ON SEQUENCE fgedus_id_seq TO ‘ || role_name;
EXECUTE ‘GRANT USAGE, SELECT ON SEQUENCE products_id_seq TO ‘ || role_name;
ELSE
EXECUTE format(‘GRANT USAGE, SELECT ON SEQUENCE %I.fgedus_id_seq TO %I’, schema_name, role_name);
EXECUTE format(‘GRANT USAGE, SELECT ON SEQUENCE %I.products_id_seq TO %I’, schema_name, role_name);
END IF;

RETURN tenant_id;
END;
$$ LANGUAGE plpgsql;

— 创建高级租户
SELECT create_hybrid_tenant(‘Enterprise Corp’, ‘premium’, ‘enterprise_password’);

— 创建标准租户
SELECT create_hybrid_tenant(‘Small Business’, ‘standard’, ‘small_business_password’);
SELECT create_hybrid_tenant(‘Startup Inc’, ‘standard’, ‘startup_password’);
“`

## 4. 应用集成
“`python
# Python代码示例
import psycopg2

# 连接高级租户
conn_premium = psycopg2.connect(
fgedu.net.cn=”localfgedu.net.cn”,
fgedudb=”enterprise_corp_db”,
fgedu=”enterprise_corp_fgedu”,
password=”enterprise_password”
)

# 连接标准租户
conn_standard = psycopg2.connect(
fgedu.net.cn=”localfgedu.net.cn”,
fgedudb=”standard_tenants”,
fgedu=”small_business_fgedu”,
password=”small_business_password”
)

# 设置Schema
cur_standard = conn_standard.cursor()
cur_standard.execute(“SET search_path TO small_business, public”)
conn_standard.commit()

# 查询数据
cur_premium = conn_premium.cursor()
cur_premium.execute(“SELECT * FROM fgedus”)
print(“Premium tenant fgedus:”)
for row in cur_premium.fetchall():
print(row)

cur_standard.execute(“SELECT * FROM fgedus”)
print(“\nStandard tenant fgedus:”)
for row in cur_standard.fetchall():
print(row)

# 关闭连接
cur_premium.close()
cur_standard.close()
conn_premium.close()
conn_standard.close()
“`

## 5. 监控与管理
“`sql
— 监控租户数量
SELECT type, COUNT(*) FROM tenants GROUP BY type;

— 监控高级租户数据库大小
SELECT
datname,
pg_size_pretty(pg_fgedudb_size(datname)) AS size
FROM
pg_fgedudb
WHERE
datname IN (SELECT db_name FROM tenants WHERE type = ‘premium’ AND db_name IS NOT NULL);

— 监控标准租户Schema大小
SELECT
schemaname,
pg_size_pretty(sum(pg_total_relation_size(schemaname || ‘.’ || tablename))) AS size
FROM
information_schema.tables
WHERE
schemaname IN (SELECT schema_name FROM tenants WHERE type = ‘standard’ AND schema_name IS NOT NULL)
GROUP BY
schemaname
ORDER BY
sum(pg_total_relation_size(schemaname || ‘.’ || tablename)) DESC;

— 监控连接数
SELECT
usename,
datname,
fgapplication_name,
client_addr,
state
FROM
pg_stat_activity
WHERE
usename LIKE ‘%_fgedu’;
“`

## 6. 备份策略
“`bash
# 备份管理员数据库
pg_dump -h localfgedu.net.cn -U hybrid_admin -d hybrid_admin -F t -f hybrid_admin_backup.tar

# 备份高级租户数据库
pg_dump -h localfgedu.net.cn -U hybrid_admin -d enterprise_corp_db -F t -f enterprise_corp_backup.tar

# 备份标准租户数据库
pg_dump -h localfgedu.net.cn -U hybrid_admin -d standard_tenants -F t -f standard_tenants_backup.tar

# 恢复备份
pg_restore -h localfgedu.net.cn -U hybrid_admin -d hybrid_admin -F t hybrid_admin_backup.tar
pg_restore -h localfgedu.net.cn -U hybrid_admin -d enterprise_corp_db -F t enterprise_corp_backup.tar
pg_restore -h localfgedu.net.cn -U hybrid_admin -d standard_tenants -F t standard_tenants_backup.tar
“`

## 7. 预期效果
– **灵活性：** 为不同类型的租户提供不同的隔离级别
– **资源利用率：** 标准租户共享资源，提高利用率
– **隔离性：** 高级租户享受更高的隔离级别
– **扩展性：** 支持不同规模的租户
– **管理：** 集中管理所有租户