GreenPlum教程FG004-GreenPlum数据库与Schema管理实战

本文档风哥主要介绍GreenPlum数据库与Schema管理，包括数据库概念、Schema概念、表空间概念、数据库创建删除、Schema管理、表空间管理、用户权限管理等内容，风哥教程参考GreenPlum官方文档Administration、Security等内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。

Part01-基础概念与理论知识

1.1 GreenPlum数据库概念与架构

GreenPlum数据库是一个逻辑上的数据库实例，包含多个Schema、表、视图、索引等对象。每个GreenPlum集群可以创建多个数据库，但每个数据库之间是相互隔离的。更多视频教程www.fgedu.net.cn

GreenPlum数据库特点：

每个数据库是独立的命名空间
数据库之间不能直接访问对象
每个数据库有自己的系统目录
数据库存储在所有Segment节点上

1.1.1 系统默认数据库

GreenPlum系统默认数据库：

1. postgres
– 默认管理数据库
– 用于日常管理和维护
– 建议不要存储业务数据

2. template0
– 纯净模板数据库
– 不可修改
– 用于创建新数据库的模板

3. template1
– 可修改模板数据库
– 可以添加公共对象
– 创建数据库时的默认模板

1.1.2 数据库对象层次结构

GreenPlum数据库对象层次结构：

Cluster（集群）
└── Database（数据库）
└── Schema（模式）
├── Table（表）
├── View（视图）
├── Index（索引）
├── Sequence（序列）
├── Function（函数）
└── Type（类型）

说明：
– 一个集群可以有多个数据库
– 一个数据库可以有多个Schema
– 一个Schema可以有多个对象
– 不同Schema中可以有同名对象

1.2 GreenPlum数据库Schema概念

Schema是数据库中的命名空间，用于组织和管理数据库对象。通过Schema可以实现对象的逻辑分组，方便权限管理和对象隔离。学习交流加群风哥微信: itpux-com

1.2.1 Schema的作用

Schema的主要作用：

1. 命名空间隔离
– 不同Schema中可以有同名表
– 避免对象命名冲突
– 便于对象分类管理

2. 权限管理
– 可以对Schema设置权限
– 控制用户对Schema的访问
– 实现细粒度权限控制

3. 业务隔离
– 不同业务使用不同Schema
– 便于多租户架构实现
– 支持数据仓库分层设计

4. 对象组织
– 按功能模块组织对象
– 按业务领域组织对象
– 按开发阶段组织对象

1.2.2 系统默认Schema

GreenPlum系统默认Schema：

1. pg_catalog
– 系统目录Schema
– 存储系统表和视图
– 存储系统函数和类型

2. public
– 默认用户Schema
– 所有用户默认有CREATE权限
– 建议生产环境撤销public权限

3. information_schema
– 标准信息Schema
– 提供标准视图查询元数据
– 兼容SQL标准

4. pg_toast
– TOAST表存储Schema
– 存储大字段数据
– 系统自动管理

5. gp_toolkit
– GreenPlum工具Schema
– 提供管理视图和函数
– 用于监控和诊断

1.3 GreenPlum数据库表空间概念

表空间是GreenPlum数据库中存储对象的物理位置映射，通过表空间可以将不同类型的数据存储在不同的存储介质上，实现存储的灵活管理。学习交流加群风哥QQ113257174

1.3.1 表空间的作用

表空间的主要作用：

1. 存储分离
– 热数据存储在快速存储
– 冷数据存储在廉价存储
– 索引和数据分离存储

2. 性能优化
– IO负载分散
– 减少IO竞争
– 提升查询性能

3. 容量管理
– 控制表的大小
– 分配存储配额
– 监控空间使用

4. 备份恢复
– 按表空间备份
– 部分恢复
– 异构存储恢复

1.3.2 系统默认表空间

GreenPlum系统默认表空间：

1. pg_default
– 默认表空间
– 位于数据目录下
– 未指定表空间时使用

2. pg_global
– 全局表空间
– 存储共享系统表
– 所有数据库共享

创建自定义表空间示例：
CREATE TABLESPACE fgedutbs
LOCATION ‘/GreenPlum/fgedutbs’;

使用表空间示例：
CREATE TABLE fgedu.fgedu_orders (
id SERIAL,
order_name VARCHAR(100)
) TABLESPACE fgedutbs;

Part02-生产环境规划与建议

2.1 GreenPlum数据库规划建议

2.1.1 数据库规划原则

GreenPlum数据库规划原则：

1. 按业务域划分
– 每个业务域一个数据库
– 避免跨数据库关联查询
– 便于权限管理和维护

2. 按环境划分
– 开发环境数据库
– 测试环境数据库
– 生产环境数据库
– 不同环境使用不同集群

3. 按数据类型划分
– 操作型数据库（OLTP）
– 分析型数据库（OLAP）
– 归档数据库

4. 命名规范
– 使用小写字母
– 使用下划线分隔
– 包含业务标识
– 示例：fgedudb, fgedudb_ods, fgedudb_dw

2.1.2 数据库配置建议

数据库配置建议：

1. 编码设置
CREATE DATABASE fgedudb
WITH ENCODING ‘UTF8′
LC_COLLATE=’en_US.UTF-8′
LC_CTYPE=’en_US.UTF-8’;

2. 连接限制
ALTER DATABASE fgedudb
WITH CONNECTION LIMIT 200;

3. 默认表空间
ALTER DATABASE fgedudb
SET default_tablespace = ‘fgedutbs’;

4. 参数设置
ALTER DATABASE fgedudb
SET work_mem = ‘512MB’;

2.2 GreenPlum数据库Schema规划建议

2.2.1 Schema规划原则

Schema规划原则：

1. 数据仓库分层设计
– ods：原始数据层
– dwd：明细数据层
– dws：汇总数据层
– ads：应用数据层
– dim：维度数据层

2. 业务模块划分
– sales：销售模块
– inventory：库存模块
– finance：财务模块
– customer：客户模块

3. 开发阶段划分
– dev：开发环境
– test：测试环境
– prod：生产环境

4. 功能模块划分
– etl：ETL相关对象
– report：报表相关对象
– config：配置相关对象
– tmp：临时对象

2.2.2 Schema命名规范

风哥提示：Schema命名应遵循以下规范：

使用小写字母和下划线
名称长度不超过63个字符
以字母开头，可包含数字
避免使用系统保留字
示例：ods_sales, dwd_order, dws_summary

2.3 GreenPlum数据库表空间规划建议

2.3.1 表空间规划原则

表空间规划原则：

1. 按存储介质划分
– ssd_tbs：SSD存储表空间（热数据）
– hdd_tbs：HDD存储表空间（冷数据）
– archive_tbs：归档存储表空间

2. 按数据类型划分
– data_tbs：数据表空间
– index_tbs：索引表空间
– temp_tbs：临时表空间

3. 按业务模块划分
– sales_tbs：销售业务表空间
– finance_tbs：财务业务表空间
– log_tbs：日志表空间

4. 存储路径规划
/GreenPlum/fgedutbs/data # 数据表空间
/GreenPlum/fgedutbs/index # 索引表空间
/GreenPlum/fgedutbs/temp # 临时表空间

2.3.2 表空间容量规划

表空间容量规划示例：

小型系统（总数据量100GB以内）：
– data_tbs：200GB
– index_tbs：50GB
– temp_tbs：20GB

中型系统（总数据量100GB-1TB）：
– data_tbs：2TB
– index_tbs：500GB
– temp_tbs：100GB

大型系统（总数据量1TB以上）：
– data_tbs：根据实际需求
– index_tbs：数据的20%-30%
– temp_tbs：数据的5%-10%

注意事项：
– 预留30%以上的空间
– 监控空间使用率
– 定期清理历史数据

2.4 GreenPlum数据库用户权限规划

2.4.1 用户角色规划

用户角色规划示例：

1. 管理员角色
– gpadmin：超级管理员
– dba_admin：数据库管理员
– security_admin：安全管理员

2. 开发角色
– dev_read：开发只读角色
– dev_write：开发读写角色
– dev_admin：开发管理角色

3. 业务角色
– etl_user：ETL用户
– report_user：报表用户
– query_user：查询用户

4. 租户角色
– tenant_a：租户A用户
– tenant_b：租户B用户
– tenant_c：租户C用户

2.4.2 权限分配原则

权限分配原则：

1. 最小权限原则
– 只授予必要的权限
– 避免过度授权
– 定期审计权限

2. 角色继承原则
– 使用角色管理权限
– 用户继承角色权限
– 便于权限管理

3. 分级授权原则
– 系统级权限由管理员授予
– Schema级权限由Schema所有者授予
– 表级权限由表所有者授予

4. 权限审计原则
– 记录权限变更
– 定期审查权限
– 及时回收不需要的权限

Part03-生产环境项目实施方案

3.1 GreenPlum数据库管理实战

3.1.1 创建数据库

# 连接到GreenPlum
$ psql -d postgres

# 创建简单数据库
postgres=# CREATE DATABASE fgedudb;
CREATE DATABASE

# 创建带参数的数据库
postgres=# CREATE DATABASE fgedudb01
WITH
OWNER = fgedu
ENCODING = ‘UTF8’
LC_COLLATE = ‘en_US.UTF-8’
LC_CTYPE = ‘en_US.UTF-8’
TABLESPACE = fgedutbs
CONNECTION LIMIT = 200;
CREATE DATABASE

# 从模板创建数据库
postgres=# CREATE DATABASE fgedudb02 TEMPLATE fgedudb01;
CREATE DATABASE

3.1.2 修改数据库

# 修改数据库名称
postgres=# ALTER DATABASE fgedudb02 RENAME TO fgedudb_test;
ALTER DATABASE

# 修改数据库所有者
postgres=# ALTER DATABASE fgedudb OWNER TO fgedu;
ALTER DATABASE

# 修改数据库连接限制
postgres=# ALTER DATABASE fgedudb WITH CONNECTION LIMIT 300;
ALTER DATABASE

# 修改数据库默认表空间
postgres=# ALTER DATABASE fgedudb SET TABLESPACE fgedutbs;
ALTER DATABASE

# 设置数据库参数
postgres=# ALTER DATABASE fgedudb SET work_mem = ‘512MB’;
ALTER DATABASE

# 查看数据库配置
postgres=# SELECT
datname,
datdba,
encoding,
datconnlimit,
dattablespace
FROM pg_database
WHERE datname = ‘fgedudb’;

datname | datdba | encoding | datconnlimit | dattablespace
———+——–+———-+————–+—————
fgedudb | 16384 | 6 | 300 | 16385
(1 row)

3.1.3 删除数据库

# 断开所有连接
postgres=# SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = ‘fgedudb_test’ AND pid <> pg_backend_pid();
pg_terminate_backend
———————-
(0 rows)

# 删除数据库
postgres=# DROP DATABASE fgedudb_test;
DROP DATABASE

# 查看数据库大小
postgres=# SELECT
datname,
pg_size_pretty(pg_database_size(datname)) AS db_size
FROM pg_database
WHERE datname NOT IN (‘template0’, ‘template1’)
ORDER BY pg_database_size(datname) DESC;

datname | db_size
———–+———
fgedudb01 | 150 MB
fgedudb | 80 MB
postgres | 7 MB
(3 rows)

3.2 GreenPlum数据库Schema管理实战

3.2.1 创建Schema

# 连接到数据库
$ psql -d fgedudb

# 创建简单Schema
fgedudb=# CREATE SCHEMA ods;
CREATE SCHEMA

# 创建带所有者的Schema
fgedudb=# CREATE SCHEMA dwd AUTHORIZATION fgedu;
CREATE SCHEMA

# 创建带注释的Schema
fgedudb=# CREATE SCHEMA dws;
CREATE SCHEMA
fgedudb=# COMMENT ON SCHEMA dws IS ‘汇总数据层’;
COMMENT

# 查看Schema详细信息
fgedudb=# SELECT
n.nspname AS schema_name,
pg_catalog.pg_get_userbyid(n.nspowner) AS owner,
obj_description(n.oid, ‘pg_namespace’) AS comment
FROM pg_catalog.pg_namespace n
WHERE n.nspname NOT LIKE ‘pg_%’
AND n.nspname NOT LIKE ‘information%’
ORDER BY n.nspname;

3.2.2 Schema权限管理

# 授予Schema使用权限
fgedudb=# GRANT USAGE ON SCHEMA ods TO fgedu;
GRANT

# 授予Schema创建权限
fgedudb=# GRANT CREATE ON SCHEMA ods TO fgedu;
GRANT

# 授予Schema所有权限
fgedudb=# GRANT ALL ON SCHEMA dwd TO fgedu;
GRANT

# 授予Schema下所有表的查询权限
fgedudb=# GRANT SELECT ON ALL TABLES IN SCHEMA ods TO fgedu;
GRANT

# 授予Schema下所有表的写入权限
fgedudb=# GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA dwd TO fgedu;
GRANT

# 设置默认权限（新创建的表自动授权）
fgedudb=# ALTER DEFAULT PRIVILEGES IN SCHEMA ods
GRANT SELECT ON TABLES TO fgedu;
ALTER DEFAULT PRIVILEGES

# 查看Schema权限
fgedudb=# SELECT
n.nspname AS schema_name,
pg_catalog.array_to_string(n.nspacl, E’\n’) AS privileges
FROM pg_catalog.pg_namespace n
WHERE n.nspname = ‘ods’;

schema_name | privileges
————-+———————————–
ods | gpadmin=UC/gpadmin +
| fgedu=U/gpadmin
(1 row)

3.2.3 删除Schema

# 查看Schema下的对象
fgedudb=# SELECT
schemaname,
tablename,
tableowner
FROM pg_tables
WHERE schemaname = ‘tmp’;

schemaname | tablename | tableowner
————+———–+————
tmp | test_tbl | gpadmin
(1 row)

# 删除空Schema
fgedudb=# DROP SCHEMA tmp_empty;
DROP SCHEMA

# 删除非空Schema（级联删除）
fgedudb=# DROP SCHEMA tmp CASCADE;
NOTICE: drop cascades to table tmp.test_tbl
DROP SCHEMA

# 查看Schema大小
fgedudb=# SELECT
schemaname,
pg_size_pretty(SUM(pg_total_relation_size(schemaname || ‘.’ || tablename))::bigint) AS schema_size
FROM pg_tables
WHERE schemaname NOT IN (‘pg_catalog’, ‘information_schema’, ‘gp_toolkit’)
GROUP BY schemaname
ORDER BY SUM(pg_total_relation_size(schemaname || ‘.’ || tablename)) DESC;

3.3 GreenPlum数据库表空间管理实战

3.3.1 创建表空间

# 创建表空间目录（所有节点执行）
$ mkdir -p /GreenPlum/fgedutbs/data
$ mkdir -p /GreenPlum/fgedutbs/index
$ chown -R gpadmin:gpadmin /GreenPlum/fgedutbs

# 创建表空间
fgedudb=# CREATE TABLESPACE fgedutbs
LOCATION ‘/GreenPlum/fgedutbs/data’;
CREATE TABLESPACE

# 创建带所有者的表空间
fgedudb=# CREATE TABLESPACE fgedu_index
OWNER fgedu
LOCATION ‘/GreenPlum/fgedutbs/index’;
CREATE TABLESPACE

# 查看表空间详细信息
fgedudb=# SELECT
spcname AS tablespace_name,
pg_catalog.pg_get_userbyid(spcowner) AS owner,
spclocation AS location,
pg_size_pretty(pg_tablespace_size(oid)) AS size
FROM pg_tablespace;

3.3.2 表空间权限管理

# 授予表空间使用权限
fgedudb=# GRANT CREATE ON TABLESPACE fgedutbs TO fgedu;
GRANT

# 查看表空间权限
fgedudb=# SELECT
spcname,
spcacl
FROM pg_tablespace
WHERE spcname = ‘fgedutbs’;

spcname | spcacl
———-+———————–
fgedutbs | {gpadmin=C/gpadmin,fgedu=C/gpadmin}
(1 row)

# 设置数据库默认表空间
fgedudb=# ALTER DATABASE fgedudb SET default_tablespace = ‘fgedutbs’;
ALTER DATABASE

# 设置用户默认表空间
fgedudb=# ALTER USER fgedu SET default_tablespace = ‘fgedutbs’;
ALTER ROLE

3.3.3 表空间维护

# 查看表空间使用情况
fgedudb=# SELECT
spcname AS tablespace_name,
pg_size_pretty(pg_tablespace_size(oid)) AS used_size,
pg_size_pretty(pg_tablespace_location(oid)) AS total_size
FROM pg_tablespace
WHERE spcname NOT IN (‘pg_default’, ‘pg_global’);

# 查看表空间中的表
fgedudb=# SELECT
schemaname,
tablename,
pg_size_pretty(pg_total_relation_size(schemaname || ‘.’ || tablename)) AS table_size
FROM pg_tables
WHERE tablespace = ‘fgedutbs’
ORDER BY pg_total_relation_size(schemaname || ‘.’ || tablename) DESC
LIMIT 10;

# 移动表到其他表空间
fgedudb=# ALTER TABLE ods.fgedu_orders SET TABLESPACE fgedutbs;
ALTER TABLE

# 移动索引到其他表空间
fgedudb=# ALTER INDEX ods.idx_orders_id SET TABLESPACE fgedu_index;
ALTER INDEX

风哥提示：表空间管理需要考虑存储容量和性能，建议将热数据和冷数据分开存储，索引和数据分开存储。更多学习教程公众号风哥教程itpux_com

Part04-生产案例与实战讲解

4.1 GreenPlum数据库多租户架构案例

4.1.1 案例背景

案例背景：
– 企业：SaaS服务提供商
– 需求：为多个租户提供数据隔离
– 要求：租户之间数据完全隔离
– 方案：使用Schema实现多租户隔离

架构设计：
– 每个租户使用独立Schema
– 公共数据使用公共Schema
– 使用角色控制租户访问权限

4.1.2 实施步骤

# 创建租户Schema
fgedudb=# CREATE SCHEMA tenant_a;
CREATE SCHEMA
fgedudb=# CREATE SCHEMA tenant_b;
CREATE SCHEMA
fgedudb=# CREATE SCHEMA tenant_c;
CREATE SCHEMA

# 创建公共Schema
fgedudb=# CREATE SCHEMA common;
CREATE SCHEMA

# 创建租户角色
fgedudb=# CREATE ROLE tenant_a_user WITH LOGIN PASSWORD ‘tenant_a123’;
CREATE ROLE
fgedudb=# CREATE ROLE tenant_b_user WITH LOGIN PASSWORD ‘tenant_b123’;
CREATE ROLE
fgedudb=# CREATE ROLE tenant_c_user WITH LOGIN PASSWORD ‘tenant_c123’;
CREATE ROLE

# 授予租户Schema权限
fgedudb=# GRANT ALL ON SCHEMA tenant_a TO tenant_a_user;
GRANT
fgedudb=# GRANT USAGE ON SCHEMA common TO tenant_a_user;
GRANT

fgedudb=# GRANT ALL ON SCHEMA tenant_b TO tenant_b_user;
GRANT
fgedudb=# GRANT USAGE ON SCHEMA common TO tenant_b_user;
GRANT

fgedudb=# GRANT ALL ON SCHEMA tenant_c TO tenant_c_user;
GRANT
fgedudb=# GRANT USAGE ON SCHEMA common TO tenant_c_user;
GRANT

# 设置默认权限
fgedudb=# ALTER DEFAULT PRIVILEGES IN SCHEMA tenant_a
GRANT ALL ON TABLES TO tenant_a_user;
ALTER DEFAULT PRIVILEGES

# 验证租户隔离
$ psql -d fgedudb -U tenant_a_user
fgedudb=> SELECT current_user;
current_user
————–
tenant_a_user
(1 row)

fgedudb=> SET search_path TO tenant_a;
SET

fgedudb=> CREATE TABLE test_table (id INT);
CREATE TABLE

fgedudb=> SET search_path TO tenant_b;
ERROR: permission denied for schema tenant_b

4.2 GreenPlum数据库数仓分层案例

4.2.1 案例背景

案例背景：
– 企业：电商数据仓库
– 需求：构建数仓分层架构
– 要求：ODS-DWD-DWS-ADS分层
– 方案：使用Schema实现分层管理

分层设计：
– ods：原始数据层（贴源层）
– dwd：明细数据层（清洗转换）
– dws：汇总数据层（聚合汇总）
– ads：应用数据层（报表应用）
– dim：维度数据层（维度表）

4.2.2 实施步骤

# 创建分层Schema
fgedudb=# CREATE SCHEMA ods AUTHORIZATION etl_user;
CREATE SCHEMA
fgedudb=# CREATE SCHEMA dwd AUTHORIZATION etl_user;
CREATE SCHEMA
fgedudb=# CREATE SCHEMA dws AUTHORIZATION etl_user;
CREATE SCHEMA
fgedudb=# CREATE SCHEMA ads AUTHORIZATION report_user;
CREATE SCHEMA
fgedudb=# CREATE SCHEMA dim AUTHORIZATION etl_user;
CREATE SCHEMA

# 添加注释
fgedudb=# COMMENT ON SCHEMA ods IS ‘原始数据层-贴源数据’;
COMMENT
fgedudb=# COMMENT ON SCHEMA dwd IS ‘明细数据层-清洗转换’;
COMMENT
fgedudb=# COMMENT ON SCHEMA dws IS ‘汇总数据层-聚合汇总’;
COMMENT
fgedudb=# COMMENT ON SCHEMA ads IS ‘应用数据层-报表应用’;
COMMENT
fgedudb=# COMMENT ON SCHEMA dim IS ‘维度数据层-维度表’;
COMMENT

# 创建各层表
fgedudb=# CREATE TABLE ods.fgedu_orders (
order_id VARCHAR(50),
customer_id VARCHAR(50),
order_amount DECIMAL(18,2),
order_time TIMESTAMP,
etl_time TIMESTAMP DEFAULT NOW()
) DISTRIBUTED BY (order_id);
CREATE TABLE

fgedudb=# CREATE TABLE dwd.fgedu_orders (
order_id VARCHAR(50),
customer_id VARCHAR(50),
order_amount DECIMAL(18,2),
order_time TIMESTAMP,
customer_name VARCHAR(100),
province VARCHAR(50),
etl_time TIMESTAMP DEFAULT NOW()
) DISTRIBUTED BY (order_id);
CREATE TABLE

fgedudb=# CREATE TABLE dws.fgedu_order_summary (
province VARCHAR(50),
order_date DATE,
order_count BIGINT,
order_amount DECIMAL(18,2),
etl_time TIMESTAMP DEFAULT NOW()
) DISTRIBUTED BY (province);
CREATE TABLE

# 查看分层结构
fgedudb=# SELECT
n.nspname AS schema_name,
obj_description(n.oid, ‘pg_namespace’) AS description,
pg_catalog.pg_get_userbyid(n.nspowner) AS owner
FROM pg_catalog.pg_namespace n
WHERE n.nspname IN (‘ods’, ‘dwd’, ‘dws’, ‘ads’, ‘dim’)
ORDER BY n.nspname;

4.3 GreenPlum数据库权限管理案例

4.3.1 案例背景

案例背景：
– 企业：金融数据仓库
– 需求：精细化权限管理
– 要求：按角色分配权限
– 方案：使用角色实现权限管理

角色设计：
– etl_role：ETL开发角色
– report_role：报表开发角色
– query_role：查询用户角色
– admin_role：管理角色

4.3.2 实施步骤

# 创建角色
fgedudb=# CREATE ROLE etl_role WITH NOLOGIN;
CREATE ROLE
fgedudb=# CREATE ROLE report_role WITH NOLOGIN;
CREATE ROLE
fgedudb=# CREATE ROLE query_role WITH NOLOGIN;
CREATE ROLE
fgedudb=# CREATE ROLE admin_role WITH NOLOGIN;
CREATE ROLE

# 授予角色权限
fgedudb=# GRANT ALL ON SCHEMA ods TO etl_role;
GRANT
fgedudb=# GRANT ALL ON SCHEMA dwd TO etl_role;
GRANT
fgedudb=# GRANT ALL ON SCHEMA dws TO etl_role;
GRANT

fgedudb=# GRANT USAGE ON SCHEMA dws TO report_role;
GRANT
fgedudb=# GRANT USAGE ON SCHEMA ads TO report_role;
GRANT
fgedudb=# GRANT SELECT ON ALL TABLES IN SCHEMA dws TO report_role;
GRANT
fgedudb=# GRANT SELECT ON ALL TABLES IN SCHEMA ads TO report_role;
GRANT

fgedudb=# GRANT USAGE ON SCHEMA ads TO query_role;
GRANT
fgedudb=# GRANT SELECT ON ALL TABLES IN SCHEMA ads TO query_role;
GRANT

# 创建用户并分配角色
fgedudb=# CREATE USER etl_dev WITH LOGIN PASSWORD ‘etl123’ IN ROLE etl_role;
CREATE ROLE
fgedudb=# CREATE USER report_dev WITH LOGIN PASSWORD ‘report123’ IN ROLE report_role;
CREATE ROLE
fgedudb=# CREATE USER query_user WITH LOGIN PASSWORD ‘query123′ IN ROLE query_role;
CREATE ROLE

# 查看角色权限
fgedudb=# SELECT
r.rolname AS role_name,
n.nspname AS schema_name,
pg_catalog.array_to_string(n.nspacl, E’\n’) AS privileges
FROM pg_roles r
JOIN pg_catalog.pg_namespace n ON TRUE
WHERE r.rolname IN (‘etl_role’, ‘report_role’, ‘query_role’)
AND n.nspname IN (‘ods’, ‘dwd’, ‘dws’, ‘ads’)
ORDER BY r.rolname, n.nspname;

role_name | schema_name | privileges
————–+————-+———————————–
etl_role | ads |
etl_role | dwd | etl_role=UC/gpadmin
etl_role | dws | etl_role=UC/gpadmin
etl_role | ods | etl_role=UC/gpadmin
query_role | ads | query_role=U/gpadmin
query_role | dwd |
query_role | dws |
query_role | ods |
report_role | ads | report_role=U/gpadmin
report_role | dwd |
report_role | dws | report_role=U/gpadmin
report_role | ods |
(12 rows)

Part05-风哥经验总结与分享

5.1 GreenPlum数据库管理最佳实践

5.1.1 数据库管理最佳实践

数据库管理最佳实践：

1. 命名规范
– 数据库：小写字母，下划线分隔
– Schema：小写字母，下划线分隔
– 表空间：小写字母，下划线分隔
– 用户：小写字母，下划线分隔

2. 权限管理
– 使用角色管理权限
– 最小权限原则
– 定期审计权限
– 记录权限变更

3. 空间管理
– 监控空间使用
– 定期清理数据
– 合理规划表空间
– 预留扩展空间

4. 备份恢复
– 定期备份数据库
– 测试恢复流程
– 保留多个备份版本
– 异地备份

5.1.2 Schema管理最佳实践

风哥提示：Schema管理应遵循以下最佳实践：

按业务模块或数据分层创建Schema
为每个Schema设置合适的所有者
使用注释说明Schema用途
定期清理不需要的Schema
监控Schema空间使用情况

5.2 GreenPlum数据库管理脚本

5.2.1 数据库信息查询脚本

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

echo “==========================================”
echo “GreenPlum数据库信息报告”
echo “生成时间: $(date)”
echo “==========================================”

# 查询数据库列表
echo “”
echo “=== 数据库列表 ===”
psql -d postgres -c ”
SELECT
datname AS database_name,
pg_catalog.pg_get_userbyid(datdba) AS owner,
pg_encoding_to_char(encoding) AS encoding,
datconnlimit AS conn_limit,
pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
WHERE datname NOT IN (‘template0’, ‘template1’)
ORDER BY pg_database_size(datname) DESC;
”

# 查询Schema列表
echo “”
echo “=== Schema列表 ===”
psql -d fgedudb -c ”
SELECT
n.nspname AS schema_name,
pg_catalog.pg_get_userbyid(n.nspowner) AS owner,
obj_description(n.oid, ‘pg_namespace’) AS comment
FROM pg_catalog.pg_namespace n
WHERE n.nspname NOT LIKE ‘pg_%’
AND n.nspname NOT LIKE ‘information%’
ORDER BY n.nspname;
”

# 查询表空间列表
echo “”
echo “=== 表空间列表 ===”
psql -d fgedudb -c ”
SELECT
spcname AS tablespace_name,
pg_catalog.pg_get_userbyid(spcowner) AS owner,
spclocation AS location,
pg_size_pretty(pg_tablespace_size(oid)) AS size
FROM pg_tablespace
ORDER BY spcname;
”

echo “”
echo “==========================================”
echo “报告生成完成”
echo “==========================================”

5.2.2 权限审计脚本

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

echo “==========================================”
echo “GreenPlum权限审计报告”
echo “审计时间: $(date)”
echo “==========================================”

# 查询用户列表
echo “”
echo “=== 用户列表 ===”
psql -d fgedudb -c ”
SELECT
usename AS username,
usesuper AS is_superuser,
usecreatedb AS can_create_db,
valuntil AS password_expiry
FROM pg_user
ORDER BY usename;
”

# 查询角色权限
echo “”
echo “=== 角色权限 ===”
psql -d fgedudb -c ”
SELECT
r.rolname AS role_name,
r.rolsuper AS is_superuser,
r.rolcreaterole AS can_create_role,
r.rolcreatedb AS can_create_db,
r.rolcanlogin AS can_login
FROM pg_roles r
WHERE r.rolname NOT LIKE ‘pg_%’
ORDER BY r.rolname;
”

# 查询Schema权限
echo “”
echo “=== Schema权限 ===”
psql -d fgedudb -c ”
SELECT
n.nspname AS schema_name,
pg_catalog.array_to_string(n.nspacl, E’\n’) AS privileges
FROM pg_catalog.pg_namespace n
WHERE n.nspname NOT LIKE ‘pg_%’
AND n.nspname NOT LIKE ‘information%’
AND n.nspacl IS NOT NULL
ORDER BY n.nspname;
”

echo “”
echo “==========================================”
echo “审计完成”
echo “==========================================”

5.3 GreenPlum数据库管理检查清单

GreenPlum数据库管理检查清单：

日常检查：
[ ] 检查数据库连接数
[ ] 检查数据库大小变化
[ ] 检查Schema空间使用
[ ] 检查表空间空间使用
[ ] 检查用户权限变更

周检查：
[ ] 检查数据库备份状态
[ ] 检查表统计信息更新
[ ] 检查索引使用情况
[ ] 检查慢查询日志
[ ] 检查权限审计报告

月检查：
[ ] 检查数据库容量规划
[ ] 检查表空间容量规划
[ ] 检查用户密码过期
[ ] 检查权限合理性
[ ] 检查备份恢复测试

季度检查：
[ ] 全面权限审计
[ ] 数据库性能评估
[ ] 存储容量评估
[ ] 安全合规检查
[ ] 灾备演练

风哥提示：定期执行数据库管理检查清单，确保数据库安全稳定运行。from GreenPlum视频:www.itpux.com

更多视频教程www.fgedu.net.cn

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html