PostgreSQL 数据库设计与管理（四）

1. 数据库设计原则

1.1 规范化

规范化是组织数据库结构的一种方法，旨在减少数据冗余并提高数据完整性。常用的规范化范式包括：

• 第一范式（1NF）： 确保每列都是原子的，不可再分。
• 第二范式（2NF）： 满足1NF，且每个非主属性完全依赖于主键。
• 第三范式（3NF）： 满足2NF，且每个非主属性不传递依赖于主键。

1.2 反规范化

在某些情况下，出于性能考虑，可以进行反规范化。反规范化是适当地引入冗余数据，以减少查询时的联接操作，从而提高性能。

1.3 实体关系模型（ER模型）

ER模型用于描述数据和数据之间的关系。它包括实体（如用户、订单）、属性（如用户名、订单金额）和关系（如用户与订单之间的一对多关系）。

1.4 数据完整性

数据完整性是指数据库中数据的准确性和一致性。常见的数据完整性约束包括：

• 实体完整性： 每个表都有主键，且主键唯一且不为空。
• 参照完整性： 外键约束，确保引用的记录在外键表中存在。
• 域完整性： 限制列中的数据类型和取值范围。

2. 数据库管理

2.1 事务与并发控制

事务是一个或多个SQL操作的集合，具有以下四个特性（ACID）：

• 原子性（Atomicity）： 事务要么全部执行，要么全部回滚。
• 一致性（Consistency）： 事务执行前后，数据库保持一致性状态。
• 隔离性（Isolation）： 各事务之间相互隔离，互不影响。
• 持久性（Durability）： 事务一旦提交，数据将永久保存。

PostgreSQL 使用多版本并发控制（MVCC）来实现高效的并发控制。

2.2 备份与恢复

备份是保护数据免受损失的重要手段。PostgreSQL 提供了多种备份和恢复方法：

2.2.1 逻辑备份

使用 pg_dump 工具进行逻辑备份：

pg_dump dbname > dbname_backup.sql

恢复逻辑备份：

psql dbname < dbname_backup.sql

2.2.2 物理备份

使用 pg_basebackup 工具进行物理备份：

pg_basebackup -D /path/to/backupdir -Fp -Xs -P

恢复物理备份涉及到将备份文件复制回数据目录，并启动数据库。

2.3 安全管理

确保数据库的安全性非常重要。以下是一些常见的安全管理措施：

2.3.1 用户和权限管理

创建用户和角色：

CREATE ROLE readaccess;
CREATE USER alice WITH PASSWORD 'password';
GRANT readaccess TO alice;

授予权限：

GRANT SELECT ON TABLE users TO readaccess;

2.3.2 数据加密

PostgreSQL 支持 TLS/SSL 加密通信。配置SSL证书以启用加密：

编辑 postgresql.conf 文件，设置 ssl = on：

ssl = on

配置 pg_hba.conf 文件，启用加密连接：

hostssl all all 0.0.0.0/0 md5

2.3.3 审计和日志

配置审计日志记录用户活动和SQL查询。使用 pgaudit 扩展来实现审计功能：

安装 pgaudit：

CREATE EXTENSION pgaudit;

配置审计选项：

pgaudit.log = 'all'

3. 实战演练

3.1 练习题目

1. 设计一个图书管理系统，包括以下实体：

   • 用户（id，username，email）
   • 图书（id，title，author，published_date）
   • 借阅记录（id，user_id，book_id，borrow_date，return_date）

2. 为上述系统定义外键约束，以确保数据完整性。

3. 实现用户借阅图书的事务操作，确保数据一致性。

4. 配置数据库用户权限，使得普通用户只能查询图书信息，而管理员可以执行所有操作。

3.2 示例答案

1. 设计图书管理系统：

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    author VARCHAR(100) NOT NULL,
    published_date DATE
);

CREATE TABLE borrow_records (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    book_id INTEGER REFERENCES books(id),
    borrow_date DATE NOT NULL,
    return_date DATE
);

1. 定义外键约束（已在上面的表定义中体现）。

2. 用户借阅图书的事务操作：

BEGIN;

INSERT INTO borrow_records (user_id, book_id, borrow_date)
VALUES (1, 2, CURRENT_DATE);

UPDATE books
SET status = 'borrowed'
WHERE id = 2;

COMMIT;

1. 配置数据库用户权限：

创建角色和用户：

CREATE ROLE librarian;
CREATE ROLE member;
CREATE USER admin WITH PASSWORD 'adminpassword';
CREATE USER user1 WITH PASSWORD 'userpassword';

GRANT librarian TO admin;
GRANT member TO user1;

授予权限：

GRANT SELECT ON TABLE books TO member;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO librarian;

---

系统文章目录：

PostgreSQL 简介与基础（一）

PostgreSQL 基本SQL语法（二）

PostgreSQL 高级SQL查询（三）

PostgreSQL 数据库设计与管理（四）

PostgreSQL 高级功能（五）

PostgreSQL 性能优化与调优（六）

PostgreSQL 高可用性与灾难恢复策略（七）

PostgreSQL 安全性与权限管理（八）

PostgreSQL 高级功能与扩展（九）

PostgreSQL 分区表与并行查询（十）

PostgreSQL 索引优化与性能调优（十一）

PostgreSQL 日志管理与故障排查（十二）

PostgreSQL 高可用性与容错性（十三）