📚 课程目录第4章数据库选型与部署

4. 数据库选型与部署：MySQL/PostgreSQL对比、数据库安装与初始化、表结构设计

数据库选型这事儿，我见过太多团队拍脑袋决定了。说白了，选错了后面改起来成本极高。咱们这个补丁分发系统，数据库是核心中的核心——补丁元数据、客户端信息、任务状态全得靠它存。我个人的习惯是，先想清楚场景，再挑工具。

4.1 MySQL vs PostgreSQL：到底选哪个？

先别急着站队。咱们系统对数据库的核心诉求其实就三条：高并发写入（客户端上报状态）、强一致性（任务状态不能丢）、运维简单（毕竟不是专职DBA）。

我直接给结论：选PostgreSQL。为什么？

对比维度	MySQL 8.0	PostgreSQL 16
JSON支持	有，但索引和查询能力一般	原生JSONB，支持GIN索引，查询灵活
并发写入	依赖InnoDB行锁，高并发下死锁概率高	MVCC机制成熟，读写互不阻塞
复杂查询	CTE支持有限，窗口函数性能一般	CTE、窗口函数、递归查询都很强
扩展性	主从复制为主，分库分表需要中间件	逻辑复制、分区表、外部数据包装器
运维成本	工具链成熟，但参数调优复杂	默认配置即可用，pg_stat_statements排查方便

嗯，这里要注意一点。MySQL在互联网场景下确实用得多，但咱们这个系统有个特殊需求——补丁元数据里经常要存JSON格式的依赖关系。PostgreSQL的JSONB类型配合GIN索引，查起来特别顺手。我曾经在一个项目里用MySQL存JSON，结果查询性能惨不忍睹，最后不得不拆表重做。

我的建议：如果团队里没有专职DBA，或者未来可能有复杂查询需求，直接上PostgreSQL。如果团队对MySQL特别熟，且业务场景简单，MySQL也能用。但咱们这个课程，我按PostgreSQL来讲。

4.2 数据库安装与初始化

安装其实不复杂，但有几个坑得提前避开。我直接给生产环境的推荐做法。

4.2.1 安装PostgreSQL 16

以CentOS 7/8为例，用官方yum源安装：

# 安装官方yum源
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# 安装PostgreSQL 16
yum install -y postgresql16-server postgresql16-contrib

# 初始化数据库
/usr/pgsql-16/bin/postgresql-16-setup initdb

# 启动并设置开机自启
systemctl start postgresql-16
systemctl enable postgresql-16

初始化完成后，第一件事是改密码：

# 切换到postgres用户
su - postgres

# 进入psql
psql

# 设置密码
ALTER USER postgres WITH PASSWORD '你的强密码';

# 创建补丁系统专用用户
CREATE USER patch_admin WITH PASSWORD '另一个强密码' CREATEDB;

# 创建数据库
CREATE DATABASE patch_db OWNER patch_admin;

# 退出
\q

避坑指南：我曾经在生产环境里直接用默认的postgres用户跑业务，结果审计的时候被骂惨了。一定要创建专用用户，并且按最小权限原则来。另外，密码别用弱口令，这玩意儿暴露在外网就完了。

4.2.2 关键配置调优

安装完别急着用，先改几个关键参数。编辑 /var/lib/pgsql/16/data/postgresql.conf：

# 监听所有网卡（按需修改）
listen_addresses = '*'

# 内存相关（根据服务器内存调整）
shared_buffers = 1GB          # 物理内存的25%
work_mem = 64MB               # 每个排序操作的内存
maintenance_work_mem = 256MB  # 维护操作内存

# 连接数
max_connections = 200

# 日志
log_line_prefix = '%t [%p] %u@%d '
log_statement = 'ddl'         # 只记录DDL，避免日志爆炸

# 开启统计信息
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = 'all'

改完重启：

systemctl restart postgresql-16

嗯，这里有个小技巧。很多新手喜欢把 max_connections 设得特别大，比如1000。其实没必要，连接数越大，上下文切换越严重。我一般控制在200以内，配合连接池使用。

4.3 表结构设计

这是本章的重头戏。咱们的补丁分发系统，核心就三张表：补丁库、客户端库、任务库。我直接给DDL，然后逐条解释设计思路。

4.3.1 补丁库（patches）

这张表存所有补丁的元数据。注意，补丁文件本身不存数据库，只存路径或对象存储的URL。

CREATE TABLE patches (
    id BIGSERIAL PRIMARY KEY,
    patch_name VARCHAR(128) NOT NULL,
    patch_version VARCHAR(32) NOT NULL,
    patch_type VARCHAR(16) NOT NULL CHECK (patch_type IN ('security', 'bugfix', 'feature', 'config')),
    os_type VARCHAR(32) NOT NULL,
    arch VARCHAR(16) NOT NULL DEFAULT 'x86_64',
    file_path VARCHAR(512) NOT NULL,
    file_checksum VARCHAR(64) NOT NULL,
    file_size BIGINT NOT NULL,
    dependencies JSONB DEFAULT '[]',
    release_notes TEXT,
    status VARCHAR(16) NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'testing', 'released', 'deprecated')),
    created_by VARCHAR(64) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    
    -- 唯一约束：同一版本不能重复
    UNIQUE(patch_name, patch_version)
);

-- 索引
CREATE INDEX idx_patches_status ON patches(status);
CREATE INDEX idx_patches_os_type ON patches(os_type);
CREATE INDEX idx_patches_dependencies ON patches USING GIN(dependencies);
CREATE INDEX idx_patches_created_at ON patches(created_at DESC);

说几个设计要点：

dependencies用JSONB：补丁之间可能有依赖关系，比如A补丁依赖B补丁。JSONB存数组，查询时用 @> 操作符，效率很高。
file_checksum：这个字段很重要。我遇到过补丁文件传输过程中损坏的情况，有了checksum，客户端下载完可以校验。
status状态机：从draft到released，中间经过testing，确保补丁不会直接被推送到生产环境。

4.3.2 客户端库（clients）

这张表记录所有注册的客户端信息。注意，客户端第一次连接时自动注册，不需要人工录入。

CREATE TABLE clients (
    id BIGSERIAL PRIMARY KEY,
    client_id VARCHAR(64) NOT NULL UNIQUE,
    hostname VARCHAR(256) NOT NULL,
    ip_address INET NOT NULL,
    os_type VARCHAR(32) NOT NULL,
    os_version VARCHAR(32) NOT NULL,
    arch VARCHAR(16) NOT NULL DEFAULT 'x86_64',
    agent_version VARCHAR(16) NOT NULL,
    tags JSONB DEFAULT '[]',
    status VARCHAR(16) NOT NULL DEFAULT 'online' CHECK (status IN ('online', 'offline', 'disabled')),
    last_heartbeat TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    registered_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 索引
CREATE INDEX idx_clients_status ON clients(status);
CREATE INDEX idx_clients_os_type ON clients(os_type);
CREATE INDEX idx_clients_tags ON clients USING GIN(tags);
CREATE INDEX idx_clients_last_heartbeat ON clients(last_heartbeat DESC);

这里有个设计细节：tags字段。我建议用JSONB数组存标签，比如 ["production", "web-server"]。这样后续按标签批量分发补丁时，查询非常灵活。

个人经验：tags字段别用逗号分隔的字符串，那玩意儿查询起来要命。JSONB数组配合GIN索引，查"所有production环境的机器"就是一条SQL的事。

4.3.3 任务库（tasks）

这张表是核心中的核心。每次分发补丁，都会生成一条任务记录，然后每个客户端对应一条子任务。

CREATE TABLE tasks (
    id BIGSERIAL PRIMARY KEY,
    task_uuid UUID NOT NULL UNIQUE DEFAULT gen_random_uuid(),
    patch_id BIGINT NOT NULL REFERENCES patches(id),
    target_type VARCHAR(16) NOT NULL CHECK (target_type IN ('all', 'os', 'tag', 'client')),
    target_value VARCHAR(256) NOT NULL,
    strategy VARCHAR(16) NOT NULL DEFAULT 'rolling' CHECK (strategy IN ('rolling', 'batch', 'immediate')),
    batch_size INT NOT NULL DEFAULT 20,
    batch_interval INT NOT NULL DEFAULT 300,
    status VARCHAR(16) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'running', 'paused', 'completed', 'failed', 'cancelled')),
    created_by VARCHAR(64) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    started_at TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 子任务表：每个客户端一条
CREATE TABLE task_items (
    id BIGSERIAL PRIMARY KEY,
    task_id BIGINT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
    client_id BIGINT NOT NULL REFERENCES clients(id),
    status VARCHAR(16) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'downloading', 'installing', 'rebooting', 'completed', 'failed', 'skipped')),
    error_message TEXT,
    started_at TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    
    UNIQUE(task_id, client_id)
);

-- 索引
CREATE INDEX idx_tasks_status ON tasks(status);
CREATE INDEX idx_tasks_created_at ON tasks(created_at DESC);
CREATE INDEX idx_task_items_task_id ON task_items(task_id);
CREATE INDEX idx_task_items_client_id ON task_items(client_id);
CREATE INDEX idx_task_items_status ON task_items(status);

这个设计有几个关键点：

任务和子任务分离：主任务记录分发策略和整体状态，子任务记录每个客户端的执行情况。这样查询"某个补丁分发到哪了"非常快。
strategy字段：支持滚动更新、分批更新、立即更新三种策略。滚动更新是生产环境最常用的，一批一批来，出问题能及时止损。
ON DELETE CASCADE：主任务删除时，子任务自动清理。但实际生产环境我建议软删除，别真删数据。

4.4 整体架构图

下面这张图展示了三张核心表之间的关系，以及它们在整个系统里的位置：

4.5 初始化脚本

最后，给一个完整的初始化脚本，方便你一键部署：

#!/bin/bash
# 数据库初始化脚本
# 用法：bash init_db.sh

DB_NAME="patch_db"
DB_USER="patch_admin"
DB_PASS="你的密码"

# 创建数据库
psql -U postgres -c "CREATE DATABASE $DB_NAME OWNER $DB_USER;"

# 执行建表语句
psql -U $DB_USER -d $DB_NAME <<EOF
-- 补丁库
CREATE TABLE patches (
    id BIGSERIAL PRIMARY KEY,
    patch_name VARCHAR(128) NOT NULL,
    patch_version VARCHAR(32) NOT NULL,
    patch_type VARCHAR(16) NOT NULL CHECK (patch_type IN ('security', 'bugfix', 'feature', 'config')),
    os_type VARCHAR(32) NOT NULL,
    arch VARCHAR(16) NOT NULL DEFAULT 'x86_64',
    file_path VARCHAR(512) NOT NULL,
    file_checksum VARCHAR(64) NOT NULL,
    file_size BIGINT NOT NULL,
    dependencies JSONB DEFAULT '[]',
    release_notes TEXT,
    status VARCHAR(16) NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'testing', 'released', 'deprecated')),
    created_by VARCHAR(64) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(patch_name, patch_version)
);

-- 客户端库
CREATE TABLE clients (
    id BIGSERIAL PRIMARY KEY,
    client_id VARCHAR(64) NOT NULL UNIQUE,
    hostname VARCHAR(256) NOT NULL,
    ip_address INET NOT NULL,
    os_type VARCHAR(32) NOT NULL,
    os_version VARCHAR(32) NOT NULL,
    arch VARCHAR(16) NOT NULL DEFAULT 'x86_64',
    agent_version VARCHAR(16) NOT NULL,
    tags JSONB DEFAULT '[]',
    status VARCHAR(16) NOT NULL DEFAULT 'online' CHECK (status IN ('online', 'offline', 'disabled')),
    last_heartbeat TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    registered_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 任务库
CREATE TABLE tasks (
    id BIGSERIAL PRIMARY KEY,
    task_uuid UUID NOT NULL UNIQUE DEFAULT gen_random_uuid(),
    patch_id BIGINT NOT NULL REFERENCES patches(id),
    target_type VARCHAR(16) NOT NULL CHECK (target_type IN ('all', 'os', 'tag', 'client')),
    target_value VARCHAR(256) NOT NULL,
    strategy VARCHAR(16) NOT NULL DEFAULT 'rolling' CHECK (strategy IN ('rolling', 'batch', 'immediate')),
    batch_size INT NOT NULL DEFAULT 20,
    batch_interval INT NOT NULL DEFAULT 300,
    status VARCHAR(16) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'running', 'paused', 'completed', 'failed', 'cancelled')),
    created_by VARCHAR(64) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    started_at TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 子任务表
CREATE TABLE task_items (
    id BIGSERIAL PRIMARY KEY,
    task_id BIGINT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
    client_id BIGINT NOT NULL REFERENCES clients(id),
    status VARCHAR(16) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'downloading', 'installing', 'rebooting', 'completed', 'failed', 'skipped')),
    error_message TEXT,
    started_at TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(task_id, client_id)
);

-- 索引
CREATE INDEX idx_patches_status ON patches(status);
CREATE INDEX idx_patches_os_type ON patches(os_type);
CREATE INDEX idx_patches_dependencies ON patches USING GIN(dependencies);
CREATE INDEX idx_patches_created_at ON patches(created_at DESC);
CREATE INDEX idx_clients_status ON clients(status);
CREATE INDEX idx_clients_os_type ON clients(os_type);
CREATE INDEX idx_clients_tags ON clients USING GIN(tags);
CREATE INDEX idx_clients_last_heartbeat ON clients(last_heartbeat DESC);
CREATE INDEX idx_tasks_status ON tasks(status);
CREATE INDEX idx_tasks_created_at ON tasks(created_at DESC);
CREATE INDEX idx_task_items_task_id ON task_items(task_id);
CREATE INDEX idx_task_items_client_id ON task_items(client_id);
CREATE INDEX idx_task_items_status ON task_items(status);

-- 插入测试数据（可选）
INSERT INTO patches (patch_name, patch_version, patch_type, os_type, file_path, file_checksum, file_size, created_by)
VALUES ('kernel-security-fix', '1.0.0', 'security', 'CentOS 7', '/patches/kernel-fix-1.0.0.rpm', 'abc123...', 204800, 'admin');
EOF

echo "数据库初始化完成！"

最后说一句：表结构设计不是一锤子买卖。我建议上线前先做一轮压力测试，看看索引是否命中，查询是否够快。如果发现某个查询慢，别急着加索引，先分析执行计划。pg_stat_statements这个插件就是干这个用的。