PostgreSQL教程FG071-PG数据库/角色/表空间管理：全流程实操

本文档风哥主要介绍PostgreSQL数据库、角色和表空间的全流程管理，包括基础概念、生产环境规划、实施方案、实战案例和经验总结。风哥教程参考PostgreSQL官方文档Server Administration内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。

Part01-基础概念与理论知识

1.1 PostgreSQL数据库的概念

PostgreSQL数据库是一个包含表、视图、索引等数据库对象的集合，是数据存储的基本单位。每个PostgreSQL实例可以包含多个数据库，数据库之间相互隔离，数据不能直接共享。更多视频教程www.fgedu.net.cn

PostgreSQL数据库的特点：

每个实例可以创建多个数据库
数据库之间相互隔离
每个数据库有独立的系统表和用户表
数据库是逻辑上的隔离单位
默认包含template0、template1和postgres三个系统数据库

1.2 PostgreSQL角色的概念

PostgreSQL角色是数据库的用户和权限管理单位，在PostgreSQL中，角色可以是用户也可以是组。角色可以拥有数据库对象的权限，也可以继承其他角色的权限。

from oracle:www.itpux.com

PostgreSQL角色的特点：

角色可以是用户或组
角色可以拥有权限
角色可以继承其他角色的权限
默认包含postgres超级用户角色
角色可以被授予登录权限，成为可登录用户

1.3 PostgreSQL表空间的概念

PostgreSQL表空间是数据存储的物理位置，用于管理数据库对象的存储位置。表空间允许将不同的数据库对象存储在不同的磁盘或存储设备上，从而优化存储和性能。学习交流加群风哥微信: itpux-com

PostgreSQL表空间的特点：

表空间是物理存储位置
可以将不同的数据库对象存储在不同的表空间
默认包含pg_default和pg_global两个表空间
可以根据数据类型和访问模式选择不同的存储设备
表空间可以提高存储管理的灵活性

Part02-生产环境规划与建议

2.1 PostgreSQL数据库规划

PostgreSQL数据库规划要点：

# 数据库数量规划
– 小型系统：1-5个数据库
– 中型系统：5-20个数据库
– 大型系统：20+个数据库
– 建议：根据业务模块划分数据库，避免单数据库过大

# 数据库命名规范
– 使用小写字母、数字和下划线
– 长度不超过63个字符
– 避免使用保留字
– 示例：fgedu_business, fgedu_analytics, fgedu_logging

# 数据库编码规划
– 推荐使用UTF8编码
– 支持多语言和国际化
– 避免编码转换问题

# 数据库参数规划
– 针对不同数据库设置合适的参数
– 考虑内存、连接数、查询性能等因素

2.2 PostgreSQL角色规划

PostgreSQL角色规划要点：

# 角色类型规划
– 超级用户：数据库管理，仅用于管理任务
– 应用用户：应用程序连接，最小权限原则
– 只读用户：报表查询，仅授予SELECT权限
– 运维用户：日常维护，授予必要的管理权限

# 角色命名规范
– 超级用户：postgres（默认）
– 应用用户：fgapp_业务模块
– 只读用户：read_业务模块
– 运维用户：ops_用户名

# 权限规划
– 最小权限原则：只授予必要的权限
– 权限分离：不同角色拥有不同权限
– 定期审查：定期检查和更新权限

# 密码策略
– 使用强密码
– 定期更换密码
– 密码加密存储

2.3 PostgreSQL表空间规划

PostgreSQL表空间规划要点：

# 表空间类型规划
– 系统表空间：存储系统对象
– 数据文件表空间：存储用户数据
– 索引表空间：存储索引数据
– 临时表空间：存储临时数据

# 存储设备规划
– 系统表空间：使用高速存储
– 数据文件表空间：根据数据量选择合适的存储
– 索引表空间：使用高速存储
– 临时表空间：使用高速存储

# 表空间命名规范
– 系统表空间：使用默认名称
– 数据文件表空间：data_业务模块
– 索引表空间：index_业务模块
– 临时表空间：temp_实例名

# 表空间大小规划
– 预估数据量和增长趋势
– 预留足够的存储空间
– 定期监控表空间使用情况

风哥提示：生产环境中，合理的数据库、角色和表空间规划是系统稳定运行的基础。建议根据业务需求和数据量进行详细规划，避免后期调整带来的风险。

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

3.1 PostgreSQL数据库管理

3.1.1 创建数据库

# 以超级用户登录
$ psql -U pgsql

# 创建数据库
postgres=# CREATE DATABASE fgedu_business
postgres-# WITH
postgres-# OWNER = pgsql postgres-# ENCODING = ‘UTF8’
postgres-# LC_COLLATE = ‘zh_CN.UTF-8’
postgres-# LC_CTYPE = ‘zh_CN.UTF-8’
postgres-# TABLESPACE = pg_default
postgres-# CONNECTION LIMIT = -1;

CREATE DATABASE

# 查看数据库
postgres=# \l

3.1.2 修改数据库

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

ALTER DATABASE

# 修改数据库连接限制
postgres=# ALTER DATABASE fgedu_business CONNECTION LIMIT = 100;

ALTER DATABASE

# 查看数据库详情
postgres=# \l+ fgedu_business

3.1.3 删除数据库

# 删除数据库（注意：会删除所有数据）
postgres=# DROP DATABASE IF EXISTS fgedu_test;

DROP DATABASE

# 确认数据库已删除
postgres=# \l

3.2 PostgreSQL角色管理

3.2.1 创建角色

# 创建应用用户角色
postgres=# CREATE ROLE fgapp_business WITH
postgres-# LOGIN
postgres-# PASSWORD ‘StrongPassword123’
postgres-# NOSUPERUSER
postgres-# NOCREATEDB
postgres-# NOCREATEROLE
postgres-# INHERIT
postgres-# CONNECTION LIMIT 50;

CREATE ROLE

# 创建只读用户角色
postgres=# CREATE ROLE read_business WITH
postgres-# LOGIN
postgres-# PASSWORD ‘ReadPassword123’
postgres-# NOSUPERUSER
postgres-# NOCREATEDB
postgres-# NOCREATEROLE
postgres-# INHERIT
postgres-# CONNECTION LIMIT 20;

CREATE ROLE

# 查看角色
postgres=# \du

List of roles
Role name | Attributes | Member of
———–+————————————————————+———–
fgapp_business | Password valid until infinity | {}
pgsql | Superfgedu, Create role, Create DB, Replication, Bypass RLS | {}
read_business | Password valid until infinity | {}

3.2.2 修改角色

# 修改角色密码
postgres=# ALTER ROLE fgapp_business PASSWORD ‘NewStrongPassword456’;

ALTER ROLE

# 修改角色连接限制
postgres=# ALTER ROLE fgapp_business CONNECTION LIMIT 100;

ALTER ROLE

# 查看角色详情
postgres=# \du+ fgapp_business

List of roles
Role name | Attributes | Member of | Description
———–+————————————————————+———–+————-
fgapp_business | Password valid until infinity | {}

3.2.3 删除角色

# 删除角色（注意：确保角色没有正在使用）
postgres=# DROP ROLE IF EXISTS test_fgedu;

DROP ROLE

# 确认角色已删除
postgres=# \du

3.3 PostgreSQL表空间管理

3.3.1 创建表空间

# 创建表空间目录
$ mkdir -p /postgresql/fgedutbss/data_business
$ mkdir -p /postgresql/fgedutbss/index_business
$ chown -R pgsql: pgsql /postgresql/fgedutbss/

# 以超级用户登录
$ psql -U pgsql

# 创建数据文件表空间
postgres=# CREATE TABLESPACE data_business
postgres-# OWNER pgsql postgres-# LOCATION ‘/postgresql/fgedutbss/data_business’;

CREATE TABLESPACE

# 创建索引表空间
postgres=# CREATE TABLESPACE index_business
postgres-# OWNER pgsql postgres-# LOCATION ‘/postgresql/fgedutbss/index_business’;

CREATE TABLESPACE

# 查看表空间
postgres=# \db

3.3.2 修改表空间

# 修改表空间所有者
postgres=# ALTER TABLESPACE data_business OWNER TO postgres;

ALTER TABLESPACE

# 查看表空间详情
postgres=# \db+

3.3.3 删除表空间

# 删除表空间（注意：表空间必须为空）
postgres=# DROP TABLESPACE IF EXISTS test_fgedutbs;

DROP TABLESPACE

# 确认表空间已删除
postgres=# \db

风哥提示：在生产环境中，表空间的创建和删除需要谨慎操作，确保表空间中没有数据或已做好数据迁移。学习交流加群风哥QQ113257174

Part04-生产案例与实战讲解

4.1 PostgreSQL数据库管理案例

4.1.1 案例：创建业务数据库并配置权限

# 1. 创建业务数据库
postgres=# CREATE DATABASE fgedu_analytics
postgres-# WITH
postgres-# OWNER = pgsql postgres-# ENCODING = ‘UTF8’
postgres-# LC_COLLATE = ‘zh_CN.UTF-8’
postgres-# LC_CTYPE = ‘zh_CN.UTF-8’
postgres-# TABLESPACE = data_business
postgres-# CONNECTION LIMIT = 200;

CREATE DATABASE

# 2. 为应用用户授予数据库权限
postgres=# GRANT CONNECT ON DATABASE fgedu_analytics TO fgapp_business;

GRANT

# 3. 为只读用户授予数据库权限
postgres=# GRANT CONNECT ON DATABASE fgedu_analytics TO read_business;

GRANT

# 4. 切换到新数据库
postgres=# \c fgedu_analytics

You are now connected to fgedudb “fgedu_analytics” as fgedu “postgres”.

# 5. 为应用用户授予schema权限
fgedu_analytics=# GRANT USAGE ON SCHEMA public TO fgapp_business;

GRANT

# 6. 为应用用户授予表权限
fgedu_analytics=# GRANT CREATE ON SCHEMA public TO fgapp_business;

GRANT

# 7. 为只读用户授予表权限
fgedu_analytics=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_business;

GRANT

4.2 PostgreSQL角色管理案例

4.2.1 案例：创建角色组并管理权限

# 1. 创建角色组
postgres=# CREATE ROLE group_business;

CREATE ROLE

# 2. 将应用用户添加到角色组
postgres=# GRANT group_business TO fgapp_business;

GRANT

# 3. 为角色组授予权限
postgres=# GRANT CONNECT ON DATABASE fgedu_business TO group_business;

GRANT

# 4. 切换到业务数据库
postgres=# \c fgedu_business

You are now connected to fgedudb “fgedu_business” as fgedu “postgres”.

# 5. 为角色组授予schema权限
fgedu_business=# GRANT USAGE, CREATE ON SCHEMA public TO group_business;

GRANT

# 6. 查看角色组成员
fgedu_business=# \du

List of roles
Role name | Attributes | Member of
———–+————————————————————+———–
fgapp_business | Password valid until infinity | {group_business}
group_business | Cannot login | {}
pgsql | Superfgedu, Create role, Create DB, Replication, Bypass RLS | {}
read_business | Password valid until infinity | {}

4.3 PostgreSQL表空间管理案例

4.3.1 案例：在指定表空间创建表和索引

# 1. 切换到业务数据库
postgres=# \c fgedu_business

You are now connected to fgedudb “fgedu_business” as fgedu “postgres”.

# 2. 在数据文件表空间创建表
fgedu_business=# CREATE TABLE fgedu_fgedus (
fgedu_business(# id SERIAL PRIMARY KEY,
fgedu_business(# name VARCHAR(100) NOT NULL,
fgedu_business(# email VARCHAR(255) UNIQUE NOT NULL,
fgedu_business(# created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
fgedu_business(# ) TABLESPACE data_business;

CREATE TABLE fgedu_# 3. 在索引表空间创建索引
fgedu_business=# CREATE INDEX idx_fgedu_fgedus_email
fgedu_business-# ON fgedu_fgedus(email)
fgedu_business-# TABLESPACE index_business;

CREATE INDEX

# 4. 查看表和索引的表空间
fgedu_business=# \d+ fgedu_fgedus

# 5. 插入测试数据
fgedu_business=# INSERT INTO fgedu_fgedus (name, email) VALUES
fgedu_business-# (‘风哥1号’, ‘zhangsan@fgedu.net.cn’),
fgedu_business-# (‘风哥2号’, ‘lisi@fgedu.net.cn’),
fgedu_business-# (‘王五’, ‘wangwu@fgedu.net.cn’);

INSERT 0 3

# 6. 验证数据
fgedu_business=# SELECT * FROM fgedu_fgedus;

id | name | email | created_at
—-+——+———————–+——————————-
1 | 风哥1号 | zhangsan@fgedu.net.cn | 2026-04-02 10:00:00.000000
2 | 风哥2号 | lisi@fgedu.net.cn | 2026-04-02 10:00:00.000000
3 | 王五 | wangwu@fgedu.net.cn | 2026-04-02 10:00:00.000000
(3 rows)

风哥教程针对风哥教程针对风哥教程针对生产环境建议：在生产环境中，建议将表和索引存储在不同的表空间，以提高IO性能。同时，根据数据的访问模式和重要性，选择合适的存储设备。更多学习教程公众号风哥教程itpux_com

Part05-风哥经验总结与分享

5.1 PostgreSQL数据库/角色/表空间管理最佳实践

PostgreSQL数据库/角色/表空间管理最佳实践：

数据库管理：

根据业务模块合理划分数据库
使用统一的命名规范
配置合适的数据库参数
定期备份数据库

角色管理：

遵循最小权限原则
使用角色组管理权限
定期审查和更新权限
使用强密码策略

表空间管理：

根据数据类型和访问模式选择表空间
将表和索引存储在不同的表空间
使用高速存储设备存储频繁访问的数据
定期监控表空间使用情况

5.2 PostgreSQL数据库/角色/表空间常见问题

PostgreSQL数据库/角色/表空间常见问题及解决方案：

数据库连接失败：检查数据库是否存在、用户权限是否正确、连接字符串是否正确
角色权限不足：检查角色权限设置，确保授予必要的权限
表空间满：监控表空间使用情况，及时扩容或清理数据
表空间权限问题：确保表空间所有者有正确的权限
数据库性能问题：合理规划表空间，优化存储布局

5.3 PostgreSQL数据库/角色/表空间管理工具推荐

PostgreSQL数据库/角色/表空间管理常用工具：

psql：命令行工具，用于管理数据库、角色和表空间
pgAdmin4：图形化管理工具，提供直观的管理界面
PostgreSQL Control Center：企业级管理工具
SQL Shell：跨平台命令行工具
自定义脚本：用于自动化管理任务

风哥提示：PostgreSQL的数据库、角色和表空间管理是系统运维的核心任务，需要建立规范的管理流程和监控机制。定期审查和优化管理策略，确保系统的稳定运行。from PostgreSQL:www.itpux.com

持续改进：数据库、角色和表空间管理是一个持续的过程，需要根据业务需求和系统变化不断调整和优化。建议建立定期审查机制，持续改进管理策略。

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

PostgreSQL教程FG071-PG数据库/角色/表空间管理：全流程实操

Part01-基础概念与理论知识

1.1 PostgreSQL数据库的概念

1.2 PostgreSQL角色的概念

1.3 PostgreSQL表空间的概念

Part02-生产环境规划与建议

2.1 PostgreSQL数据库规划

2.2 PostgreSQL角色规划

2.3 PostgreSQL表空间规划

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

3.1 PostgreSQL数据库管理

3.1.1 创建数据库

3.1.2 修改数据库

3.1.3 删除数据库

3.2 PostgreSQL角色管理

3.2.1 创建角色

3.2.2 修改角色

3.2.3 删除角色

3.3 PostgreSQL表空间管理

3.3.1 创建表空间

3.3.2 修改表空间

3.3.3 删除表空间

Part04-生产案例与实战讲解

4.1 PostgreSQL数据库管理案例

4.1.1 案例：创建业务数据库并配置权限

4.2 PostgreSQL角色管理案例

4.2.1 案例：创建角色组并管理权限

4.3 PostgreSQL表空间管理案例

4.3.1 案例：在指定表空间创建表和索引

Part05-风哥经验总结与分享

5.1 PostgreSQL数据库/角色/表空间管理最佳实践

5.2 PostgreSQL数据库/角色/表空间常见问题

5.3 PostgreSQL数据库/角色/表空间管理工具推荐

相关推荐

联系我们