在实际开发中，我们经常会遇到树形结构或图结构的数据需求，比如：组织架构（部门 → 子部门）商品分类（一级类目 → 二级类目 → …）评论回复（评论 → 回复 → 回复的回复）权限继承（角色 → 子角色）路径查找（最短路径、依赖关系）这些场景的核心问题是：如何高效查询具有层级/递归关系的数据？PostgreSQL 提供了强大的 WITH RECURSIVE（公共表表达式递归） 功能，是处理此类问题的标准 SQL 解决方案。本文将从基础到实战，手把手教你掌握递归查询的精髓。一、递归查询基础：CTE 与WITH RECURSIVE1.1 什么是 CTE（Common Table Expression）？CTE 是一种临时结果集，可被主查询引用，语法如下：1234WITH cte_name AS (    -- 查询语句)SELECT * FROM cte_name;优点：提升 SQL 可读性、避免重复子查询、支持递归1.2 递归 CTE 的基本结构12345678910WITH RECURSIVE cte_name AS (    -- 1. 初始查询（锚点成员 Anchor Member）    SELECT ... FROM table WHERE ...     UNION [ALL]     -- 2. 递归查询（递归成员 Recursive Member）    SELECT ... FROM table, cte_name WHERE ...)SELECT * FROM cte_name;核心三要素：部分作用注意事项初始查询定义递归起点（如根节点）必须能终止递归UNION [ALL]合并结果集UNION 去重，UNION ALL 保留重复（性能更高）递归查询引用自身 CTE，向下/向上遍历必须有连接条件，避免无限循环1.3 递归查询的建议场景推荐方案标准树形查询（上下级）WITH RECURSIVE + UNION ALL防循环记录访问路径 ARRAY[id] + != ALL(path)限制深度添加 depth 字段 + WHERE depth < N高性能读物化路径 / 闭包表（写少读多）返回树形 JSON自底向上聚合 + jsonb_build_objectPython 集成直接执行原生 SQL（SQLAlchemy 支持 CTE）终极建议：“90% 的树形查询，一个精心设计的 WITH RECURSIVE 就够了。”只有在性能成为瓶颈时，才考虑物化路径等复杂模型。二、经典场景实战：组织架构查询假设有一张部门表 departments：123456789101112131415CREATE TABLE departments (    id SERIAL PRIMARY KEY,    name VARCHAR(100) NOT NULL,    parent_id INTEGER REFERENCES departments(id)); -- 插入示例数据INSERT INTO departments (name, parent_id) VALUES('总公司', NULL),('技术部', 1),('产品部', 1),('前端组', 2),('后端组', 2),('iOS组', 2),('设计组', 3);2.1 查询“技术部”及其所有子部门（向下递归）123456789101112131415161718WITH RECURSIVE dept_tree AS (    -- 锚点：找到“技术部”    SELECT id, name, parent_id, 0 AS level    FROM departments    WHERE name = '技术部'     UNION ALL     -- 递归：找子部门    SELECT d.id, d.name, d.parent_id, dt.level + 1    FROM departments d    INNER JOIN dept_tree dt ON d.parent_id = dt.id)SELECT    LPAD('', level * 4, ' ') || name AS hierarchy,  -- 缩进显示层级    id, parent_id, levelFROM dept_treeORDER BY level;输出结果：123456hierarchy        | id | parent_id | level-----------------|----|-----------|------技术部           | 2  | 1         | 0    前端组       | 4  | 2         | 1    后端组       | 5  | 2         | 1    iOS组        | 6  | 2         | 1技巧：LPAD('', level * 4, ' ') 生成缩进，直观展示树形结构2.2 查询“后端组”的完整上级路径（向上递归）123456789101112131415161718WITH RECURSIVE dept_path AS (    -- 锚点：从“后端组”开始    SELECT id, name, parent_id, 0 AS level    FROM departments    WHERE name = '后端组'     UNION ALL     -- 递归：找父部门    SELECT d.id, d.name, d.parent_id, dp.level + 1    FROM departments d    INNER JOIN dept_path dp ON d.id = dp.parent_id    WHERE dp.parent_id IS NOT NULL  -- 避免 NULL 连接)SELECT    REPEAT(' → ', level) || name AS path_from_rootFROM dept_pathORDER BY level DESC;  -- 从根到当前节点输出结果：123path_from_root---------------------------总公司 → 技术部 → 后端组三、高级技巧：控制递归深度与防环3.1 限制递归深度（防止无限循环）12345678910111213WITH RECURSIVE dept_limited AS (    SELECT id, name, parent_id, 1 AS depth    FROM departments    WHERE parent_id IS NULL  -- 从根开始     UNION ALL     SELECT d.id, d.name, d.parent_id, dl.depth + 1    FROM departments d    INNER JOIN dept_limited dl ON d.parent_id = dl.id    WHERE dl.depth < 3  -- 最多查3层)SELECT * FROM dept_limited;3.2 检测并避免循环引用（图结构必备）如果数据存在循环（如 A→B→C→A），递归会无限进行。解决方案：记录访问路径。123456789101112131415161718192021222324252627WITH RECURSIVE graph_traversal AS (    -- 锚点    SELECT        id,         name,         parent_id,        ARRAY[id] AS path,      -- 记录已访问节点        1 AS depth    FROM departments    WHERE name = '技术部'     UNION ALL     -- 递归    SELECT        d.id,        d.name,        d.parent_id,        gt.path || d.id,        -- 追加当前节点        gt.depth + 1    FROM departments d    INNER JOIN graph_traversal gt ON d.parent_id = gt.id    WHERE        d.id != ALL(gt.path)    -- 关键：当前节点不在已访问路径中        AND gt.depth < 10       -- 安全兜底)SELECT * FROM graph_traversal;d.id != ALL(gt.path) 确保不重复访问节点，彻底解决循环问题3.3 反向应用：扁平数据转树形 JSONPostgreSQL 支持将递归结果直接转为 嵌套 JSON，适合 API 返回。如使用 jsonb_build_object 构建树12345678910111213141516171819202122232425262728293031WITH RECURSIVE tree AS (    -- 叶子节点（无子节点）    SELECT        id,        name,        parent_id,        jsonb_build_object('id', id, 'name', name, 'children', '[]'::jsonb) AS node    FROM categories c1    WHERE NOT EXISTS (        SELECT 1 FROM categories c2 WHERE c2.parent_id = c1.id    )     UNION ALL     -- 非叶子节点（聚合子节点）    SELECT        p.id,        p.name,        p.parent_id,        jsonb_build_object(            'id', p.id,            'name', p.name,            'children', jsonb_agg(t.node)        ) AS node    FROM categories p    INNER JOIN tree t ON t.parent_id = p.id    GROUP BY p.id, p.name, p.parent_id)SELECT node FROM tree WHERE parent_id IS NULL;  -- 返回根节点输出 JSON：123456789101112131415161718192021{  "id": 1,  "name": "电子产品",  "children": [    {      "id": 2,      "name": "手机",      "children": [        {"id": 3, "name": "iPhone", "children": []},        {"id": 4, "name": "华为", "children": []}      ]    },    {      "id": 5,      "name": "电脑",      "children": [        {"id": 6, "name": "笔记本", "children": []}      ]    }  ]}此方法利用 自底向上聚合，天然避免循环，但要求数据为严格树形（无环）四、实战案例：商品分类树4.1 场景：电商商品分类（多级类目）123456789101112131415CREATE TABLE categories (    id SERIAL PRIMARY KEY,    name VARCHAR(100) NOT NULL,    parent_id INTEGER REFERENCES categories(id),    is_leaf BOOLEAN DEFAULT false  -- 是否叶子节点); -- 插入数据INSERT INTO categories (name, parent_id, is_leaf) VALUES('电子产品', NULL, false),('手机', 1, false),('iPhone', 2, true),('华为', 2, true),('电脑', 1, false),('笔记本', 5, true);4.2 查询“电子产品”下所有叶子类目（带完整路径）1234567891011121314151617181920212223242526272829WITH RECURSIVE category_tree AS (    -- 锚点：根类目    SELECT        id,         name,         parent_id,        name::TEXT AS full_path,  -- 路径字符串        1 AS level    FROM categories    WHERE name = '电子产品'     UNION ALL     -- 递归：拼接路径    SELECT        c.id,        c.name,        c.parent_id,        ct.full_path || ' > ' || c.name,  -- 路径拼接        ct.level + 1    FROM categories c    INNER JOIN category_tree ct ON c.parent_id = ct.id)SELECT    full_path,    id,    levelFROM category_treeWHERE is_leaf = true;  -- 只查叶子节点输出：12345full_path                   | id | level----------------------------|----|------电子产品 > 手机 > iPhone   | 3  | 3电子产品 > 手机 > 华为     | 4  | 3电子产品 > 电脑 > 笔记本   | 6  | 34.3 Python + SQLAlchemy 实战在 Python 中使用递归查询：12345678910111213141516171819202122232425from sqlalchemy import textfrom sqlalchemy.orm import sessionmaker def get_dept_tree(session, root_name):    query = text("""        WITH RECURSIVE dept_tree AS (            SELECT id, name, parent_id, 0 AS level            FROM departments            WHERE name = :root_name            UNION ALL            SELECT d.id, d.name, d.parent_id, dt.level + 1            FROM departments d            INNER JOIN dept_tree dt ON d.parent_id = dt.id        )        SELECT * FROM dept_tree ORDER BY level;    """)         result = session.execute(query, {"root_name": root_name})    return result.fetchall() # 使用with Session() as session:    tree = get_dept_tree(session, "技术部")    for row in tree:        print(f"{'  ' * row.level}{row.name}")五、性能优化：索引与执行计划5.1 必建索引12345-- 对 parent_id 建索引（递归连接的关键）CREATE INDEX idx_departments_parent_id ON departments(parent_id); -- 如果常按 name 查询根节点CREATE INDEX idx_departments_name ON departments(name);5.2 查看执行计划12EXPLAIN (ANALYZE, BUFFERS)WITH RECURSIVE ... ;  -- 你的递归查询关键观察点：是否使用了 Index Scan（而非 Seq Scan）递归深度是否合理内存使用（Buffers）5.3 大数据量优化建议问题解决方案递归太深（>100层）限制 depth < N，业务上通常不需要过深层级数据量大（百万级）分页查询（先查ID再关联）、物化路径（见下文）频繁查询使用 物化路径（Materialized Path） 或 闭包表（Closure Table）六、替代方案对比：何时不用递归？虽然 WITH RECURSIVE 很强大，但在某些场景下，其他模型更高效：6.1 物化路径（Materialized Path）在每条记录中存储完整路径：12345ALTER TABLE categories ADD COLUMN path TEXT; -- 如 "/1/2/3/" -- 查询“手机”下所有子类目SELECT * FROM categories WHERE path LIKE '/1/2/%';✅ 优点：查询极快（走索引）❌ 缺点：移动节点时需更新大量 path6.2 闭包表（Closure Table）额外建一张表存储所有祖先-后代关系：1234567891011CREATE TABLE category_closure (    ancestor_id INT,    descendant_id INT,    depth INT); -- 查询“手机”(id=2)的所有后代SELECT c.* FROM categories cJOIN category_closure cl ON c.id = cl.descendant_idWHERE cl.ancestor_id = 2;✅ 优点：查询快，支持任意深度❌ 缺点：写操作复杂，存储空间大选择建议：读多写少 + 深度固定 → 物化路径频繁查询全路径 → 闭包表通用场景 + 中小数据量 → WITH RECURSIVE七、常见陷阱与避坑指南陷阱 1：忘记WHERE条件导致无限循环123-- 错误：缺少终止条件SELECT ... FROM table, cte WHERE table.parent_id = cte.id-- 如果存在循环引用，永远停不下来！✅ 解决：始终加上 depth < N 或路径检测陷阱 2：使用UNION而非UNION ALLUNION 会去重，但递归中通常不需要（父子ID唯一）性能损失高达 30%+✅ 解决：除非明确需要去重，否则用 UNION ALL陷阱 3：在递归部分使用聚合函数12-- 错误：递归成员不能包含聚合SELECT ..., COUNT(*) FROM ... JOIN cte ... ✅ 解决：先递归，再在外层聚合

一、为什么选择 PostgreSQL 全文检索？1.1 对比外部搜索引擎特性PostgreSQL FTSElasticsearch部署复杂度无需额外组件需维护集群数据一致性强一致性（ACID）最终一致性延迟毫秒级（同库查询）网络 + 索引延迟功能完整性支持词干、停用词、权重、短语更丰富（高亮、聚合等）运维成本低（集成于数据库）高适用场景：中小规模数据（< 1 亿文档）、强一致性要求、简化架构1.2 PostgreSQL FTS 的核心优势内置支持：无需安装插件（9.6+ 功能完备）事务安全：搜索结果与数据写入原子一致灵活配置：支持多语言、自定义词典、权重控制高效索引：GIN/GiST 索引支持快速检索SQL 集成：可与其他条件（JOIN、WHERE、ORDER BY）无缝组合1.3 实践 checklist持久化 tsvector 列：避免运行时解析使用触发器自动同步：保证数据一致性合理设置权重：标题 > 内容 > 标签选择 GIN 索引：读多写少场景最优限制结果集：避免无 LIMIT 的排序多语言按需配置：英文用内置，中文用 zhparser监控索引健康：大小、膨胀率、使用率结合业务需求：短语、前缀、模糊搜索按需启用PostgreSQL 全文检索虽不如 Elasticsearch 功能全面，但在架构简洁性、数据一致性、运维成本上具有显著优势。对于大多数 Web 应用，它已足够强大。掌握上述技巧，你完全可以在单一数据库内构建出高效、可靠的搜索系统。二、全文检索基础：核心概念与数据类型2.1 核心数据类型PostgreSQL 提供两种关键数据类型：tsvector：文档向量化表示将文本解析为 词位（lexeme） 列表，并记录位置信息示例：12SELECT to_tsvector('english', 'The quick brown fox jumps over the lazy dog');-- 结果: 'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2tsquery：查询表达式表示搜索条件，支持布尔操作示例：123SELECT to_tsquery('english', 'quick & fox');  -- 同时包含SELECT to_tsquery('english', 'quick | fox');  -- 包含其一SELECT to_tsquery('english', 'jump & !lazy'); -- 包含 jump 但不含 lazy2.2 匹配操作符@@：判断 tsvector 是否匹配 tsquery12SELECT to_tsvector('english', 'a fat cat') @@ to_tsquery('english', 'fat & cat');-- true三、基础用法：从简单搜索到生产部署3.1 直接查询（不推荐用于生产）123SELECT title, contentFROM articlesWHERE to_tsvector('english', content) @@ to_tsquery('english', 'database & performance');问题：每次查询都需解析文本，CPU 开销大无法使用索引，全表扫描3.2 持久化 tsvector 列（推荐方式）步骤 1：添加专用列1ALTER TABLE articles ADD COLUMN content_ts tsvector;步骤 2：初始化数据12UPDATE articles SET content_ts = to_tsvector('english', coalesce(content, ''));步骤 3：创建 GIN 索引1CREATE INDEX idx_articles_content_ts ON articles USING GIN(content_ts);步骤 4：查询123SELECT title, contentFROM articlesWHERE content_ts @@ to_tsquery('english', 'database & performance');优势：索引加速，避免重复解析3.3 自动同步 tsvector（触发器）为确保 content_ts 与 content 一致，创建触发器：1234CREATE TRIGGER tsvector_update_triggerBEFORE INSERT OR UPDATE OF content ON articlesFOR EACH ROW EXECUTE FUNCTIONtsvector_update_trigger(content_ts, 'pg_catalog.english', content);注意：tsvector_update_trigger 是 PostgreSQL 内置函数，自动处理 NULL 和更新。四、高级功能：提升搜索体验4.1 多字段搜索与权重控制不同字段重要性不同（如标题 > 内容）。PostgreSQL 支持 权重（A/B/C/D）：12345678910-- 构建带权重的 tsvectorUPDATE articles SET content_ts =     setweight(to_tsvector('english', coalesce(title, '')), 'A') ||    setweight(to_tsvector('english', coalesce(content, '')), 'B'); -- 查询（权重影响排序）SELECT title, ts_rank(content_ts, query) AS rankFROM articles, to_tsquery('english', 'database') queryWHERE content_ts @@ queryORDER BY rank DESC;权重等级：A：最高（默认 1.0）B：高（默认 0.4）C：中（默认 0.2）D：低（默认 0.1）可通过 ts_rank 的 normalization 参数调整。4.2 短语搜索（Phrase Search）普通 FTS 不保证词序和邻近性。使用 phraseto_tsquery：123-- 搜索 "quick brown" 作为短语SELECT * FROM articles WHERE content_ts @@ phraseto_tsquery('english', 'quick brown');要求：tsvector 必须包含位置信息（默认已包含）4.3 前缀匹配与模糊搜索前缀匹配（PostgreSQL 11+）123-- 搜索以 "run" 开头的词（running, runner）SELECT * FROM articles WHERE content_ts @@ to_tsquery('english', 'run:*');模糊匹配（需 pg_trgm）若需拼写容错，结合 pg_trgm：123456CREATE EXTENSION pg_trgm;CREATE INDEX idx_articles_title_trgm ON articles USING GIN(title gin_trgm_ops); -- 搜索相似词SELECT title FROM articles WHERE title % 'databse';  -- 匹配 "database"建议：FTS 用于主搜索，pg_trgm 用于“您是不是要找…”建议。4.4 多语言支持PostgreSQL 支持 20+ 种语言的词干提取和停用词：123456-- 中文需额外配置（见下文）SELECT to_tsvector('french', 'Les données sont importantes');-- 结果: 'donn':2 'import':4 -- 查看支持的语言SELECT cfgname FROM pg_ts_config;常用语言配置：'english''simple'（仅小写，无词干）'german', 'french', 'spanish' 等五、中文全文检索解决方案PostgreSQL 默认不支持中文分词。需借助扩展：5.1 使用 zhparser + scws（推荐）步骤 1：安装扩展12345# Ubuntu/Debiansudo apt install postgresql-contribgit clone https://github.com/amutu/zhparser.gitcd zhparsermake && sudo make install步骤 2：创建扩展123CREATE EXTENSION zhparser;CREATE TEXT SEARCH CONFIGURATION chinese (PARSER = zhparser);ALTER TEXT SEARCH CONFIGURATION chinese ADD MAPPING FOR n,v,a,i,e,l,x WITH simple;步骤 3：使用12345SELECT to_tsvector('chinese', '中华人民共和国成立70周年');-- 结果: '中华':1 '人民':2 '共和国':3 '成立':4 '70':5 '周年':6 -- 创建索引CREATE INDEX idx_articles_chinese ON articles USING GIN(to_tsvector('chinese', content));5.2 使用 jieba（Python 扩展）若环境支持 Python：12CREATE EXTENSION jiebacfg;-- 用法类似 zhparser注意：中文分词效果取决于词典质量，需定期更新。六、性能优化：从毫秒到亚毫秒6.1 索引选择：GIN vs GiST特性GINGiST查询速度快慢（约 3x）索引大小大小写入速度慢快适用场景读多写少写多读少建议：全文检索通常读多写少，优先选择 GIN。6.2 避免重复解析始终使用持久化 tsvector 列 + 触发器，而非运行时 to_tsvector()。6.3 限制结果集大小123456-- 先按 rank 排序，再 LIMITSELECT *, ts_rank(content_ts, q) AS rankFROM articles, to_tsquery('english', 'database') qWHERE content_ts @@ qORDER BY rank DESCLIMIT 20;警告：若无 LIMIT，ORDER BY rank 可能导致全表扫描。6.4 使用覆盖索引（PostgreSQL 11+）若只需返回 tsvector 相关列：1CREATE INDEX idx_articles_covering ON articles USING GIN(content_ts) INCLUDE (title, id);可实现 Index Only Scan，避免回表。6.5 分区表 + 局部索引对超大表（如日志），按时间分区：12CREATE TABLE logs_2026_01 PARTITION OF logs FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');-- 每个分区独立建 FTS 索引查询时仅扫描相关分区。七、实战案例：电商商品搜索7.1 需求支持关键词搜索（标题、描述、品牌）标题权重高于描述支持短语和前缀匹配返回相关度排序7.2 实现1、表结构1234567CREATE TABLE products (    id SERIAL PRIMARY KEY,    title TEXT NOT NULL,    description TEXT,    brand TEXT,    search_vector tsvector);2、触发器12345678CREATE TRIGGER product_search_updateBEFORE INSERT OR UPDATE OF title, description, brand ON productsFOR EACH ROW EXECUTE FUNCTIONtsvector_update_trigger(    search_vector,     'pg_catalog.english',     title, description, brand);注意：tsvector_update_trigger 支持多列，自动拼接。3、权重调整（手动构建）若需精细控制权重：12345678910111213CREATE OR REPLACE FUNCTION update_product_search() RETURNS trigger AS $$BEGIN    NEW.search_vector :=        setweight(to_tsvector('english', coalesce(NEW.title, '')), 'A') ||        setweight(to_tsvector('english', coalesce(NEW.description, '')), 'B') ||        setweight(to_tsvector('english', coalesce(NEW.brand, '')), 'A');    RETURN NEW;END$$ LANGUAGE plpgsql; CREATE TRIGGER product_search_updateBEFORE INSERT OR UPDATE ON productsFOR EACH ROW EXECUTE FUNCTION update_product_search();4、查询接口1234567891011121314-- 基础搜索SELECT id, title, ts_rank(search_vector, q) AS rankFROM products, websearch_to_tsquery('english', 'wireless headphones') qWHERE search_vector @@ qORDER BY rank DESCLIMIT 20; -- 短语搜索SELECT * FROM products WHERE search_vector @@ phraseto_tsquery('english', 'noise cancelling'); -- 前缀搜索SELECT * FROM products WHERE search_vector @@ to_tsquery('english', 'headphon:*');使用 websearch_to_tsquery 支持自然语言输入（如 "wireless headphones" -cheap）。八、监控与维护8.1 监控索引大小123456SELECT    tablename,    indexname,    pg_size_pretty(pg_relation_size(indexname::regclass)) AS sizeFROM pg_indexesWHERE indexname LIKE '%ts%';8.2 更新统计信息1ANALYZE products;  -- 确保优化器准确估算8.3 定期重建索引（防膨胀）1REINDEX INDEX idx_products_search;  -- 在低峰期执行九、局限性与应对策略9.1 不支持高亮（Highlighting）PostgreSQL FTS 不直接返回匹配片段。解决方案：应用层使用正则高亮或结合 ts_headline 函数：123SELECT ts_headline('english', content, q, 'StartSel=<b>, StopSel=</b>') FROM articles, to_tsquery('english', 'database') qWHERE content_ts @@ q;9.2 无拼写纠错方案 1：前端集成拼写建议（如使用 pg_trgm）方案 2：后端返回“相似词”供用户选择9.3 中文分词精度有限定期更新 scws 词典对专业领域，自定义词典：12-- zhparser 支持自定义词典ALTER TEXT SEARCH CONFIGURATION chinese ADD MAPPING FOR ...;

在 PostgreSQL 中，默认的端口号是 5432。这意味着，当你安装 PostgreSQL 数据库服务时，它会尝试在这个端口上监听连接请求。如果你需要更改默认端口号，你可以在 PostgreSQL 的配置文件 postgresql.conf 中找到并修改 port 参数的值。例如，如果你想将端口号更改为 5433，你可以这样设置：port = 5433 更改配置后，需要重启 PostgreSQL 服务来使更改生效。这可以通过以下命令完成（取决于你的操作系统和安装方式）：对于大多数 Linux 系统：sudo systemctl restart postgresql对于 macOS（使用 Homebrew 安装）：brew services restart postgresql对于 Windows：打开“服务”管理工具。找到 PostgreSQL 服务。右键点击并选择“重启”。注意：更改端口号后，如果你使用 psql 命令行工具或其他客户端连接到数据库，你需要指定新的端口号。例如：psql -h localhost -p 5433 -U yourusername -d yourdatabase确保新的端口没有被其他服务占用。你可以使用 netstat 或 lsof 命令来检查端口的使用情况。例如，在 Linux 上：sudo netstat -tuln | grep 5433 或sudo lsof -i :5433这样，你就可以成功更改并使用新的 PostgreSQL 端口号了。如果你只是想临时测试而不重启服务，也可以直接在命令行中使用 -p 参数指定端口号进行连接。例如：psql -h localhost -p 5433 -U yourusername -d yourdatabase这将允许你连接到使用新端口号的 PostgreSQL 实例。

PostgreSQL 调整临时表存储空间在 PostgreSQL 中，临时表（包括显式创建的临时表和查询过程中生成的临时数据）的存储空间主要通过以下几个参数进行配置：主要配置参数temp_buffers控制每个数据库会话使用的临时缓冲区大小默认值通常为 8MB只在会话开始时设置有效，之后修改不会影响当前会话示例：SET temp_buffers = '64MB';work_mem控制内部排序操作和哈希表使用的内存量影响临时文件生成前的内存使用量默认值通常为 4MB示例：SET work_mem = '16MB';maintenance_work_mem维护操作（如VACUUM、CREATE INDEX等）使用的内存间接影响临时表操作默认值通常为 64MBtemp_file_limit限制会话可以使用的临时文件总量默认值为 -1（无限制）示例：SET temp_file_limit = '1GB';临时表存储位置PostgreSQL 的临时文件通常存储在：数据目录的 base/pgsql_tmp 子目录中或由 temp_tablespaces 参数指定的表空间中配置临时表空间可以指定专门的表空间用于临时文件：-- 设置临时表空间（可以指定多个，循环使用） SET temp_tablespaces = 'temp_tablespace1,temp_tablespace2'; -- 或者在postgresql.conf中设置 temp_tablespaces = 'temp_tablespace1' 监控临时表使用查看当前会话的临时文件使用情况：SELECT * FROM pg_stat_database WHERE datname = current_database(); 查看临时文件生成情况：SELECT * FROM pg_statio_user_tables WHERE schemaname = 'pg_temp'; 在日志中记录临时文件生成（在postgresql.conf中）：log_temp_files = 0 -- 记录所有临时文件生成最佳实践对于大量临时表操作，适当增加 work_mem 可以避免使用磁盘临时文件为临时表创建专门的表空间，最好使用快速的存储设备监控临时文件生成，优化查询以减少临时表使用在会话开始时设置 temp_buffers，因为之后修改无效示例配置调整-- 在会话开始时设置 SET temp_buffers = '128MB'; SET work_mem = '32MB'; SET temp_tablespaces = 'fast_temp_space'; -- 或者在postgresql.conf中全局设置 ALTER SYSTEM SET work_mem = '32MB'; ALTER SYSTEM SET temp_buffers = '64MB'; 通过合理调整这些参数，可以显著提高涉及大量临时表操作的查询性能。

在PostgreSQL中，在临时表（temporary tables）的使用上，你可以创建两种类型的临时表：局部临时表（local temporary tables）和全局临时表（global temporary tables）。1. 局部临时表局部临时表只在创建它们的会话中可见，并且当该会话结束时，表会自动被删除。创建局部临时表时，需要在表名前加上CREATE TEMPORARY关键字。示例：‌sqlCopy CodeCREATE TEMPORARY TABLE temp_table (    id SERIAL PRIMARY KEY,    data TEXT);在这个例子中，temp_table是一个局部临时表，只能在当前数据库会话中访问。当会话结束时（例如，客户端断开连接），temp_table会自动被删除。2. 全局临时表全局临时表在所有数据库会话中都是可见的，但是它们只在创建它们的数据库会话中可以被写入。当第一个创建该表的会话结束时，表及其数据会被删除。其他会话仍然可以访问这个表，但不能写入数据。创建全局临时表时，需要在表名前加上CREATE TEMPORARY TABLE ON COMMIT关键字。示例：‌sqlCopy CodeCREATE TEMPORARY TABLE global_temp_table ON COMMIT DELETE ROWS (    id SERIAL PRIMARY KEY,    data TEXT);在这个例子中，global_temp_table是一个全局临时表，它在所有会话中可见，但只有在创建它的会话中可以写入数据。当创建这个表的会话结束时，表中的数据会被删除，但表结构仍然存在直到数据库重启或者直到执行DROP TABLE命令。注意事项：性能‌：使用临时表可以提高查询性能，尤其是在处理大量数据和需要多次访问相同数据集的情况下。持久性‌：局部临时表只在当前会话中存在，而全局临时表则在第一个创建它的会话结束时删除数据，但在整个数据库生命周期内保持结构不变。权限‌：临时表遵循与普通表相同的权限规则，即用户需要有相应的权限才能访问或修改这些表。清理‌：虽然PostgreSQL在会话结束时会自动清理局部临时表，但在长时间运行的脚本或应用程序中，最好明确地在不需要时显式地删除临时表以避免潜在的资源占用问题。可以使用DROP TABLE IF EXISTS temp_table;来安全地删除临时表。使用临时表是处理大量数据或在多个查询步骤中重用相同数据集时的有效策略。

第一次用postgres数据库，为什么间隔性的出现一个以curl 开始的进程curl -fksSLA- 。偶尔出现、消失。还总是告警，什么情况如下：

余年方十七，习编程已有数载，虽非初学，然深知学无止境，技艺之精进，如逆水行舟，不进则退。 技艺渐精 昔日初学时，常为类型错误而苦恼，今已能熟练运用多种语言。ts之强大，Python之优雅，前端三剑客之灵动，后端框架之稳健，皆有所涉猎。数据结构与算法，亦略有心得，排序查找，动态规划，虽不敢言精通，然基本功底尚算扎实。 项目实战 近年来，陆续完成数个项目：网站开发、移动应用、数据分析工具等。每一次从需求分析到架构设计，从编码实现到测试部署，皆亲力亲为。虽过程艰辛，然收获颇丰。始知纸上谈兵易，实际开发难。用户体验、性能优化、代码维护，诸般考量，方显编程之深奥。 开源贡献 偶有小作，贡献于开源社区，虽微不足道，然能为他人所用，心甚慰藉。GitHub上星标虽少，然每一个fork，每一个issue，皆是对余之鞭策与鼓励。 技术视野 关注前沿技术，人工智能、区块链、云计算等，皆有所了解。深知技术日新月异，昨日之新技术，今日或已过时。唯有保持学习之心，方能不被时代所抛弃。 反思与展望 回首来路，虽有所成，然亦深感不足。代码质量有待提升，系统设计能力尚需磨练，团队协作经验亦显匮乏。他日若能进入知名企业，与高手切磋，定能更上一层楼。 夫编程者，匠心独运之艺也。既要有扎实之基础，又需具创新之思维。余虽年少，然志在千里，愿以代码改变世界，以技术服务社会。作者写此文时正值十七岁生日前夕，虽习编程数年，然经验尚浅，见解有限，往有不足之处，请在评论区指出。案例简介：本案例选择bun + Next.js作为示例编写了学生成绩管理系统，并借助开发者空间云开发环境提供的免费 GaussDB数据库和HCE2.0开发环境进行本地部署Next.js生态组件、轻松部署上云，直观地展示在实际应用开发中为开发者带来的便利。一、概述1.1 案例介绍Bun + Next.js + tRPC 是一个现代化的全栈 TypeScript Web 开发技术栈，可以快速开发高性能和可维护的网站。Bun 作为超快的 JavaScript 运行时和包管理器，Next.js 负责处理前端和后端开发中的复杂部分，tRPC 提供端到端类型安全的 API 层。在数据库操作方面，本技术栈支持直接使用原生 SQL 语句进行数据库交互，无需额外的 ORM 抽象层，既保证了查询性能的最优化，又提供了最大的灵活性。让开发者可以专注于编写业务逻辑，而无需重复造轮子。本案例借助开发者空间云开发环境部署 Next.js 项目，通过 tRPC 构建类型安全的 API 接口，并直接使用 SQL 语句与开发者空间提供的免费 GaussDB 数据库实例对接，完成一个现代化 Web 应用构建。通过实际操作，让大家深入了解如何利用 Bun + Next.js + tRPC + 原生 SQL 技术栈开发并部署一个高性能的全栈 Web 应用。华为开发者空间，是为全球开发者打造的专属开发者空间，致力于为每位开发者提供一台 云开发环境、一套开发工具和云上存储空间，汇聚昇腾、鸿蒙、鲲鹏、GaussDB、欧拉 等华为各项根技术的开发工具资源，并提供配套案例指导开发者 从开发编码到应用调 测，基于华为根技术生态高效便捷的知识学习、技术体验、应用创新。1.2 适用对象企业个人开发者初高中学生和高校学生1.3 案例时间本案例总时长预计120分钟。1.4 案例流程本案例将通过三个主要步骤，带领大家在华为云开发者空间上构建一个完整的 Bun + Next.js + tRPC + 原生 SQL 全栈应用：第一步：申请华为云开发者空间的 GaussDB 数据库注册并登录华为云开发者空间申请免费的 GaussDB 数据库实例获取数据库连接配置信息第二步：进行华为云开发者空间的云开发环境进行 Bun + Next.js + tRPC 安装与配置创建云开发环境实例安装 Bun 运行时环境初始化 Next.js TypeScript 项目集成 tRPC 框架，配置端到端类型安全的 API设置数据库连接和环境变量第三步：启用 Next.js Web 服务，适配 GaussDB，在客户端测试原生 SQL 的建表和数据操作编写原生 SQL 建表脚本通过 tRPC 封装数据库操作接口启动 Next.js 开发服务器在客户端测试数据库的增删改查功能验证端到端类型安全和性能表现通过这三个步骤，您将完整体验从环境搭建到应用部署的全流程，深入理解 Bun + Next.js + tRPC + 原生 SQL 技术栈在华为云开发者空间上的强大能力。1.5 资源总览本案例预计花费0.8元。资源名称规格单价（元）时长（分钟）华为开发者空间 - 云开发环境鲲鹏通用计算增强型 kc1 2vCPUs 4G免费40华为开发者空间 - 生态版GaussDB单副本集中式版 4 vCPUs 16G HCE OS 64bit (200GB)免费40弹性公网IP按流量计费 5Mbit/s0.8元/GB40二、案例准备工作2.1 配置云开发环境根据案例《开发者空间 - 云开发环境使用指导》，请查看下面链接，配置云开发环境，并通过xshell等链接工具登录云开发环境。开发者空间 - 云开发环境使用指导2.2 领取GaussDB数据库免费领取GaussDB在线试用版（2025年 06月 21日 - 2025年 12月 31日）。华为开发者空间-GaussDB云数据库领取与使用指导注：部署的Django项目需要对接GaussDB，因此GaussDB需要绑定EIP，参考上述指导中领取部分第(5)步bunx create-next-app@latest2.3 验证云开发环境与GaussDB互通通过xshell或windows命令终端登录云开发环境，使用ping [ip]测试GaussDB能否连接，如下是通的2.4 新建数据库及用户修改dn:password_encryption_type字段为1。0表示采用md5方式对密码加密。1表示采用sha256和md5两种方式分别对密码加密，2表示采用sha256方式对密码加密，3表示采用sm3方式对密码加密。MD5加密算法安全性低，存在安全风险，不建议使用。点击右上角登录数据库管理界面创建数据库使用如下图配置。通过右上角登录GaussDB的SQL操作界面，用如下SQL创建Django链接的登录用户。create user myuser with sysadmin password 'GaussDB@123'; 三、Bun + Next.js + Prisma 安装配置指南3.1 安装 Bun# 全局安装 bun sudo npm install -g bun # 验证安装 bun --version # 检查安装位置 which bun3.2 创建 Next.js 项目# 交互式创建next.js项目 bunx create-next-app@latest看到如下输出即为成功创建Next.js项目3.3测试Next.js是否安装成功# 查看项目结构 ls -la # 启动开发服务器测试 bun dev # 在另一个终端测试（如果需要） curl http://localhost:3000 3.4 安装trpc步骤 1: 安装依赖bun add @trpc/server @trpc/client @trpc/react-query @trpc/next @tanstack/react-query zod superjson依赖说明@trpc/server - 服务器端 tRPC 核心@trpc/client - 客户端 tRPC 核心@trpc/react-query - React Query 集成@trpc/next - Next.js 集成@tanstack/react-query - 数据获取和缓存zod - 类型验证superjson - 序列化（支持 Date、BigInt 等）步骤 2: 创建服务器端配置创建 app/lib/trpc.tsimport { initTRPC } from '@trpc/server'; import superjson from 'superjson'; import { ZodError } from 'zod'; const t = initTRPC.create({ transformer: superjson, errorFormatter({ shape, error }) { return { ...shape, data: { ...shape.data, zodError: error.cause instanceof ZodError ? error.cause.issues : null, }, }; }, }); export const createTRPCRouter = t.router; export const publicProcedure = t.procedure; 配置说明transformer: superjson - 使用 SuperJSON 进行序列化errorFormatter - 自定义错误格式化，支持 Zod 验证错误createTRPCRouter - 创建路由的工厂函数publicProcedure - 公开的 API 过程（无需认证）步骤 3: 创建路由tRPC 路由（Router）是服务端定义 API 端点和业务逻辑的核心组件，它通过类型安全的方式组织和管理所有的远程过程调用，包括查询（query）、变更（mutation）和订阅（subscription），并自动生成 TypeScript 类型定义供客户端使用，实现了从后端到前端的完整类型推断和 API 结构管理。创建主路由 app/lib/routers/_app.tsimport { createTRPCRouter } from '../trpc'; export const appRouter = createTRPCRouter({ }); export type AppRouter = typeof appRouter; 步骤 4: 创建 API 路由处理创建 app/api/trpc/[trpc]/route.tsimport { fetchRequestHandler } from '@trpc/server/adapters/fetch'; import { appRouter } from '../../../lib/routers/_app'; const handler = (req: Request) => fetchRequestHandler({ endpoint: '/api/trpc', req, router: appRouter, createContext: () => ({}), }); export { handler as GET, handler as POST }; 说明使用 Next.js App Router 的 API 路由fetchRequestHandler 处理 HTTP 请求createContext 创建请求上下文（当前为空对象）步骤 5: 创建客户端配置创建 utils/trpc.tsimport { createTRPCReact } from '@trpc/react-query'; import { type AppRouter } from '../app/lib/routers/_app'; export const trpc = createTRPCReact<AppRouter>(); 创建独立客户端 utils/trpc-client.tsimport { createTRPCProxyClient, httpBatchLink } from '@trpc/client'; import superjson from 'superjson'; import { type AppRouter } from '../app/lib/routers/_app'; export const trpcClient = createTRPCProxyClient<AppRouter>({ links: [ httpBatchLink({ url: '/api/trpc', transformer: superjson, }), ], }); 步骤 6: 设置提供者创建 app/providers.tsx'use client'; import { QueryClient, QueryClientProvider } from '@tanstack/react-query'; import { httpBatchLink } from '@trpc/client'; import React, { useState } from 'react'; import superjson from 'superjson'; import { trpc } from '../utils/trpc'; export function TRPCProvider({ children }: { children: React.ReactNode }) { const [queryClient] = useState(() => new QueryClient()); const [trpcClient] = useState(() => trpc.createClient({ links: [ httpBatchLink({ url: '/api/trpc', transformer: superjson, }), ], }) ); return ( <trpc.Provider client={trpcClient} queryClient={queryClient}> <QueryClientProvider client={queryClient}>{children}</QueryClientProvider> </trpc.Provider> ); } tRPC Provider 是一个配置组件，用于在客户端应用中设置 tRPC 客户端实例，它负责配置 API 端点、管理请求状态和缓存，并提供端到端的 TypeScript 类型安全，让前端能够直接调用后端函数而无需手动定义 API 接口类型。步骤 7: 集成到应用更新 app/layout.tsximport type { Metadata } from "next"; import { Geist, Geist_Mono } from "next/font/google"; import "./globals.css"; import { TRPCProvider } from "./providers"; const geistSans = Geist({ variable: "--font-geist-sans", subsets: ["latin"], }); const geistMono = Geist_Mono({ variable: "--font-geist-mono", subsets: ["latin"], }); export const metadata: Metadata = { title: "Create Next App", description: "Generated by create next app", }; export default function RootLayout({ children, }: Readonly<{ children: React.ReactNode; }>) { return ( <html lang="en"> <body className={`${geistSans.variable} ${geistMono.variable} antialiased`} > <TRPCProvider>{children}</TRPCProvider> </body> </html> ); } 3.5 连接和初始化数据库并编写相关方法步骤1：安装sql相关依赖bun add pg dotenv bun add -d @types/bun @types/pg tsxdotenv 是一个零依赖的模块，它可以从 .env 文件中加载环境变量到 process.env 中。步骤2：设置数据库连接创建 .env# GaussDB 数据库配置 DB_HOST=your-gaussdb-host.com DB_PORT=3306 DB_USER=your-username DB_PASSWORD=your-password DB_NAME=your-database-name # 可选配置 DB_MAX_CONNECTIONS=20 说明DB_HOST就是你数据库IPDB_PORT为数据库端口默认是8000DB_USER和DB_PASSWORD就是你刚刚创建的用户的密码步骤3：编写数据库相关代码创建 scripts/init-database.ts 用来初始化数据库import { Client } from 'pg'; import dotenv from 'dotenv'; dotenv.config(); //这里连接到数据库，如果连接失败就去检查你的.env文件 const client = new Client({ host: process.env.DB_HOST || 'localhost', port: parseInt(process.env.DB_PORT || '5432'), database: process.env.DB_NAME || 'student_management', user: process.env.DB_USER || 'postgres', password: process.env.DB_PASSWORD || 'password', }); //这里开始初始化数据库 async function initDatabase() { try { console.log('连接数据库...'); await client.connect(); console.log('数据库连接成功'); // 创建学生表 console.log('创建学生表...'); await client.query(` CREATE TABLE IF NOT EXISTS students ( id SERIAL PRIMARY KEY, student_id VARCHAR(20) UNIQUE NOT NULL, name VARCHAR(100) NOT NULL, gender VARCHAR(10) CHECK (gender IN ('男', '女')), birth_date DATE, class_name VARCHAR(50), phone VARCHAR(20), email VARCHAR(100), address TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); `); // 创建课程表 console.log('创建课程表...'); await client.query(` CREATE TABLE IF NOT EXISTS courses ( id SERIAL PRIMARY KEY, course_code VARCHAR(20) UNIQUE NOT NULL, course_name VARCHAR(100) NOT NULL, credits DECIMAL(3,1) NOT NULL, teacher_name VARCHAR(100), description TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); `); // 创建成绩表 console.log('创建成绩表...'); await client.query(` CREATE TABLE IF NOT EXISTS grades ( id SERIAL PRIMARY KEY, student_id INTEGER REFERENCES students(id) ON DELETE CASCADE, course_id INTEGER REFERENCES courses(id) ON DELETE CASCADE, semester VARCHAR(20) NOT NULL, score DECIMAL(5,2) CHECK (score >= 0 AND score <= 100), grade VARCHAR(2) CHECK (grade IN ('A+', 'A', 'A-', 'B+', 'B', 'B-', 'C+', 'C', 'C-', 'D+', 'D', 'F')), exam_date DATE, remarks TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE(student_id, course_id, semester) ); `); // 插入测试数据 console.log('插入测试数据...'); // 学生数据 实在想不出来什么好名字了。。。就张三李四吧，为啥都是高三呢因为作者高三了 await client.query(` INSERT INTO students (student_id, name, gender, birth_date, class_name, phone, email) VALUES ('2024001', '张三', '男', '2006-03-15', '高三(1)班', '13800138001', 'zhangsan@example.com'), ('2024002', '李四', '女', '2006-07-22', '高三(1)班', '13800138002', 'lisi@example.com'), ('2024003', '王五', '男', '2006-01-10', '高三(1)班', '13800138003', 'wangwu@example.com'), ON CONFLICT (student_id) DO NOTHING; `); // 课程数据 让高中生学高数是不是很好玩 await client.query(` INSERT INTO courses (course_code, course_name, credits, teacher_name, description) VALUES ('MATH101', '数学', 4.0, '陈老师', '高等数学基础课程'), ('CHI101', '语文', 4.0, '刘老师', '语文基础课程'), ('ENG101', '英语', 4.0, '王老师', '英语基础课程'), ('PHY101', '物理', 3.5, '李老师', '物理基础课程'), ('CHE101', '化学', 3.5, '张老师', '化学基础课程'), ('BIO101', '生物', 3.0, '赵老师', '生物基础课程') ON CONFLICT (course_code) DO NOTHING; `); // 成绩数据 await client.query(` INSERT INTO grades (student_id, course_id, semester, score, grade, exam_date) VALUES (1, 1, '2024-春季', 85.5, 'B+', '2024-06-15'), (1, 2, '2024-春季', 92.0, 'A', '2024-06-16'), (1, 3, '2024-春季', 78.5, 'C+', '2024-06-17'), (2, 1, '2024-春季', 95.0, 'A+', '2024-06-15'), (2, 2, '2024-春季', 88.5, 'B+', '2024-06-16'), (2, 3, '2024-春季', 91.0, 'A', '2024-06-17'), (3, 1, '2024-春季', 72.0, 'C', '2024-06-15'), (3, 2, '2024-春季', 85.0, 'B+', '2024-06-16'), (3, 3, '2024-春季', 79.5, 'C+', '2024-06-17') ON CONFLICT (student_id, course_id, semester) DO NOTHING; `); // 创建索引 console.log('创建索引...'); await client.query(` CREATE INDEX IF NOT EXISTS idx_students_student_id ON students(student_id); CREATE INDEX IF NOT EXISTS idx_students_class_name ON students(class_name); CREATE INDEX IF NOT EXISTS idx_courses_course_code ON courses(course_code); CREATE INDEX IF NOT EXISTS idx_grades_student_id ON grades(student_id); CREATE INDEX IF NOT EXISTS idx_grades_course_id ON grades(course_id); CREATE INDEX IF NOT EXISTS idx_grades_semester ON grades(semester); `); console.log('数据库初始化完成！'); // 显示统计 const stats = await client.query(` SELECT (SELECT COUNT(*) FROM students) as student_count, (SELECT COUNT(*) FROM courses) as course_count, (SELECT COUNT(*) FROM grades) as grade_count; `); console.log(`学生数量: ${stats.rows[0].student_count}`); console.log(`课程数量: ${stats.rows[0].course_count}`); console.log(`成绩记录: ${stats.rows[0].grade_count}`); } catch (error) { console.error('数据库初始化失败:', error); throw error; } finally { await client.end(); console.log('数据库连接已关闭'); } } if (require.main === module) { initDatabase() .then(() => { console.log('数据库初始化完成！'); process.exit(0); }) .catch((error) => { console.error('初始化失败:', error); process.exit(1); }); } export { initDatabase }; 创建 types/database.ts 类型文件// 学生信息 export interface Student { id: number; student_id: string; name: string; gender: '男' | '女'; birth_date: Date | null; class_name: string | null; phone: string | null; email: string | null; address: string | null; created_at: Date; updated_at: Date; } // 课程信息 export interface Course { id: number; course_code: string; course_name: string; credits: number; teacher_name: string | null; description: string | null; created_at: Date; updated_at: Date; } // 成绩信息 export interface Grade { id: number; student_id: number; course_id: number; semester: string; score: number | null; grade: 'A+' | 'A' | 'A-' | 'B+' | 'B' | 'B-' | 'C+' | 'C' | 'C-' | 'D+' | 'D' | 'F' | null; exam_date: Date | null; remarks: string | null; created_at: Date; updated_at: Date; } // 成绩详情（包含学生和课程信息） export interface GradeWithDetails { id: number; student_id: number; course_id: number; semester: string; score: number | null; grade: string | null; exam_date: Date | null; remarks: string | null; student_name: string; student_id_code: string; course_name: string; course_code: string; teacher_name: string | null; created_at: Date; updated_at: Date; } // 学生成绩统计 export interface StudentGradeStats { student_id: number; student_name: string; student_id_code: string; class_name: string | null; total_courses: number; average_score: number; total_credits: number; gpa: number; } // 课程成绩统计 export interface CourseGradeStats { course_id: number; course_name: string; course_code: string; teacher_name: string | null; total_students: number; average_score: number; highest_score: number; lowest_score: number; pass_rate: number; } // 查询参数 export interface StudentQueryParams { page?: number; limit?: number; search?: string; class_name?: string; gender?: '男' | '女'; } export interface GradeQueryParams { page?: number; limit?: number; student_id?: number; course_id?: number; semester?: string; min_score?: number; max_score?: number; } // 创建学生参数 export interface CreateStudentParams { student_id: string; name: string; gender: '男' | '女'; birth_date?: Date; class_name?: string; phone?: string; email?: string; address?: string; } // 更新学生参数 export interface UpdateStudentParams { name?: string; gender?: '男' | '女'; birth_date?: Date; class_name?: string; phone?: string; email?: string; address?: string; } // 创建成绩参数 export interface CreateGradeParams { student_id: number; course_id: number; semester: string; score?: number; grade?: string; exam_date?: Date; remarks?: string; } // 更新成绩参数 export interface UpdateGradeParams { score?: number; grade?: string; exam_date?: Date; remarks?: string; } // 创建课程参数 export interface CreateCourseParams { course_code: string; course_name: string; credits: number; teacher_name?: string; description?: string; } // 更新课程参数 export interface UpdateCourseParams { course_name?: string; credits?: number; teacher_name?: string; description?: string; } 创建lib/database.ts提供相关数据库操作方法import { Pool, PoolClient } from 'pg'; import dotenv from 'dotenv'; import { Student, Course, Grade, GradeWithDetails, StudentGradeStats, CourseGradeStats, StudentQueryParams, GradeQueryParams, CreateStudentParams, UpdateStudentParams, CreateGradeParams, UpdateGradeParams, CreateCourseParams, UpdateCourseParams, } from '../types/database'; dotenv.config(); class DatabaseService { private pool: Pool; constructor() { this.pool = new Pool({ host: process.env.DB_HOST || 'localhost', port: parseInt(process.env.DB_PORT || '5432'), database: process.env.DB_NAME || 'student_management', user: process.env.DB_USER || 'postgres', password: process.env.DB_PASSWORD || 'password', max: 20, idleTimeoutMillis: 30000, connectionTimeoutMillis: 2000, }); // 监听连接错误 this.pool.on('error', (err) => { console.error('数据库连接池错误:', err); }); } // 获取数据库连接 async getClient(): Promise<PoolClient> { return await this.pool.connect(); } // 关闭连接池 async close(): Promise<void> { await this.pool.end(); } // 学生相关方法 async getStudents(params: StudentQueryParams = {}): Promise<Student[]> { const { page = 1, limit = 10, search, class_name, gender } = params; const offset = (page - 1) * limit; let query = 'SELECT * FROM students WHERE 1=1'; const values: any[] = []; let paramIndex = 1; if (search) { query += ` AND (name ILIKE $${paramIndex} OR student_id ILIKE $${paramIndex})`; values.push(`%${search}%`); paramIndex++; } if (class_name) { query += ` AND class_name = $${paramIndex}`; values.push(class_name); paramIndex++; } if (gender) { query += ` AND gender = $${paramIndex}`; values.push(gender); paramIndex++; } query += ` ORDER BY created_at DESC LIMIT $${paramIndex} OFFSET $${paramIndex + 1}`; values.push(limit, offset); const client = await this.getClient(); try { const result = await client.query(query, values); return result.rows; } finally { client.release(); } } async getStudentById(id: number): Promise<Student | null> { const client = await this.getClient(); try { const result = await client.query('SELECT * FROM students WHERE id = $1', [id]); return result.rows[0] || null; } finally { client.release(); } } async getStudentByStudentId(studentId: string): Promise<Student | null> { const client = await this.getClient(); try { const result = await client.query('SELECT * FROM students WHERE student_id = $1', [studentId]); return result.rows[0] || null; } finally { client.release(); } } async createStudent(params: CreateStudentParams): Promise<Student> { const client = await this.getClient(); try { const result = await client.query(` INSERT INTO students (student_id, name, gender, birth_date, class_name, phone, email, address) VALUES ($1, $2, $3, $4, $5, $6, $7, $8) RETURNING * `, [ params.student_id, params.name, params.gender, params.birth_date, params.class_name, params.phone, params.email, params.address, ]); return result.rows[0]; } finally { client.release(); } } async updateStudent(id: number, params: UpdateStudentParams): Promise<Student | null> { const client = await this.getClient(); try { const fields = Object.keys(params).filter(key => params[key as keyof UpdateStudentParams] !== undefined); if (fields.length === 0) return null; const setClause = fields.map((field, index) => `${field} = $${index + 2}`).join(', '); const values = [id, ...fields.map(field => params[field as keyof UpdateStudentParams])]; const result = await client.query(` UPDATE students SET ${setClause}, updated_at = CURRENT_TIMESTAMP WHERE id = $1 RETURNING * `, values); return result.rows[0] || null; } finally { client.release(); } } async deleteStudent(id: number): Promise<boolean> { const client = await this.getClient(); try { const result = await client.query('DELETE FROM students WHERE id = $1', [id]); return (result.rowCount || 0) > 0; } finally { client.release(); } } // 课程相关方法 async getCourses(): Promise<Course[]> { const client = await this.getClient(); try { const result = await client.query('SELECT * FROM courses ORDER BY course_code'); return result.rows; } finally { client.release(); } } async getCourseById(id: number): Promise<Course | null> { const client = await this.getClient(); try { const result = await client.query('SELECT * FROM courses WHERE id = $1', [id]); return result.rows[0] || null; } finally { client.release(); } } async createCourse(params: CreateCourseParams): Promise<Course> { const client = await this.getClient(); try { const result = await client.query(` INSERT INTO courses (course_code, course_name, credits, teacher_name, description) VALUES ($1, $2, $3, $4, $5) RETURNING * `, [ params.course_code, params.course_name, params.credits, params.teacher_name, params.description, ]); return result.rows[0]; } finally { client.release(); } } async updateCourse(id: number, params: UpdateCourseParams): Promise<Course | null> { const client = await this.getClient(); try { const fields = Object.keys(params).filter(key => params[key as keyof typeof params] !== undefined); if (fields.length === 0) return null; const setClause = fields.map((field, index) => `${field} = $${index + 2}`).join(', '); const values = [id, ...fields.map(field => params[field as keyof typeof params])]; const result = await client.query(` UPDATE courses SET ${setClause}, updated_at = CURRENT_TIMESTAMP WHERE id = $1 RETURNING * `, values); return result.rows[0] || null; } finally { client.release(); } } // 成绩相关方法 async getGrades(params: GradeQueryParams = {}): Promise<GradeWithDetails[]> { const { page = 1, limit = 10, student_id, course_id, semester, min_score, max_score } = params; const offset = (page - 1) * limit; let query = ` SELECT g.*, s.name as student_name, s.student_id as student_id_code, c.course_name, c.course_code, c.teacher_name FROM grades g JOIN students s ON g.student_id = s.id JOIN courses c ON g.course_id = c.id WHERE 1=1 `; const values: any[] = []; let paramIndex = 1; if (student_id) { query += ` AND g.student_id = $${paramIndex}`; values.push(student_id); paramIndex++; } if (course_id) { query += ` AND g.course_id = $${paramIndex}`; values.push(course_id); paramIndex++; } if (semester) { query += ` AND g.semester = $${paramIndex}`; values.push(semester); paramIndex++; } if (min_score !== undefined) { query += ` AND g.score >= $${paramIndex}`; values.push(min_score); paramIndex++; } if (max_score !== undefined) { query += ` AND g.score <= $${paramIndex}`; values.push(max_score); paramIndex++; } query += ` ORDER BY g.created_at DESC LIMIT $${paramIndex} OFFSET $${paramIndex + 1}`; values.push(limit, offset); const client = await this.getClient(); try { const result = await client.query(query, values); return result.rows; } finally { client.release(); } } async getGradeById(id: number): Promise<GradeWithDetails | null> { const client = await this.getClient(); try { const result = await client.query(` SELECT g.*, s.name as student_name, s.student_id as student_id_code, c.course_name, c.course_code, c.teacher_name FROM grades g JOIN students s ON g.student_id = s.id JOIN courses c ON g.course_id = c.id WHERE g.id = $1 `, [id]); return result.rows[0] || null; } finally { client.release(); } } async createGrade(params: CreateGradeParams): Promise<Grade> { const client = await this.getClient(); try { const result = await client.query(` INSERT INTO grades (student_id, course_id, semester, score, grade, exam_date, remarks) VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING * `, [ params.student_id, params.course_id, params.semester, params.score, params.grade, params.exam_date, params.remarks, ]); return result.rows[0]; } finally { client.release(); } } async updateGrade(id: number, params: UpdateGradeParams): Promise<Grade | null> { const client = await this.getClient(); try { const fields = Object.keys(params).filter(key => params[key as keyof UpdateGradeParams] !== undefined); if (fields.length === 0) return null; const setClause = fields.map((field, index) => `${field} = $${index + 2}`).join(', '); const values = [id, ...fields.map(field => params[field as keyof UpdateGradeParams])]; const result = await client.query(` UPDATE grades SET ${setClause}, updated_at = CURRENT_TIMESTAMP WHERE id = $1 RETURNING * `, values); return result.rows[0] || null; } finally { client.release(); } } async deleteGrade(id: number): Promise<boolean> { const client = await this.getClient(); try { const result = await client.query('DELETE FROM grades WHERE id = $1', [id]); return (result.rowCount || 0) > 0; } finally { client.release(); } } // 统计方法 async getStudentGradeStats(studentId: number): Promise<StudentGradeStats | null> { const client = await this.getClient(); try { const result = await client.query(` SELECT s.id as student_id, s.name as student_name, s.student_id as student_id_code, s.class_name, COUNT(g.id) as total_courses, AVG(g.score) as average_score, SUM(c.credits) as total_credits, AVG(CASE WHEN g.grade = 'A+' THEN 4.3 WHEN g.grade = 'A' THEN 4.0 WHEN g.grade = 'A-' THEN 3.7 WHEN g.grade = 'B+' THEN 3.3 WHEN g.grade = 'B' THEN 3.0 WHEN g.grade = 'B-' THEN 2.7 WHEN g.grade = 'C+' THEN 2.3 WHEN g.grade = 'C' THEN 2.0 WHEN g.grade = 'C-' THEN 1.7 WHEN g.grade = 'D+' THEN 1.3 WHEN g.grade = 'D' THEN 1.0 WHEN g.grade = 'F' THEN 0.0 ELSE NULL END) as gpa FROM students s LEFT JOIN grades g ON s.id = g.student_id LEFT JOIN courses c ON g.course_id = c.id WHERE s.id = $1 GROUP BY s.id, s.name, s.student_id, s.class_name `, [studentId]); return result.rows[0] || null; } finally { client.release(); } } async getCourseGradeStats(courseId: number): Promise<CourseGradeStats | null> { const client = await this.getClient(); try { const result = await client.query(` SELECT c.id as course_id, c.course_name, c.course_code, c.teacher_name, COUNT(g.id) as total_students, AVG(g.score) as average_score, MAX(g.score) as highest_score, MIN(g.score) as lowest_score, (COUNT(CASE WHEN g.score >= 60 THEN 1 END) * 100.0 / COUNT(g.id)) as pass_rate FROM courses c LEFT JOIN grades g ON c.id = g.course_id WHERE c.id = $1 GROUP BY c.id, c.course_name, c.course_code, c.teacher_name `, [courseId]); return result.rows[0] || null; } finally { client.release(); } } } // 创建单例实例 const databaseService = new DatabaseService(); export default databaseService; 步骤4：初始化数据库在package.json的scripts中添加 "db:init": "tsx scripts/init-database.ts", 然后执行bun run db:init看到输出数据库初始化完成！即为成功初始化初始化后可以在华为云的数据库管理界面看到有三张表被创建了!3.6 编写业务代码步骤1：trpc路由创建app/lib/routers/course.tsimport { z } from 'zod'; import { createTRPCRouter, publicProcedure } from '../trpc'; import databaseService from '../../../lib/database'; export const courseRouter = createTRPCRouter({ // 获取所有课程 getAll: publicProcedure .query(async () => { return await databaseService.getCourses(); }), // 根据ID获取课程 getById: publicProcedure .input(z.number()) .query(async ({ input }) => { return await databaseService.getCourseById(input); }), // 创建课程 create: publicProcedure .input(z.object({ course_code: z.string(), course_name: z.string(), credits: z.number(), teacher_name: z.string().optional(), description: z.string().optional(), })) .mutation(async ({ input }) => { return await databaseService.createCourse(input); }), // 更新课程 update: publicProcedure .input(z.object({ id: z.number(), data: z.object({ course_name: z.string().optional(), credits: z.number().optional(), teacher_name: z.string().optional(), description: z.string().optional(), }), })) .mutation(async ({ input }) => { return await databaseService.updateCourse(input.id, input.data); }), // 获取课程成绩统计 getGradeStats: publicProcedure .input(z.number()) .query(async ({ input }) => { return await databaseService.getCourseGradeStats(input); }), }); 创建app/lib/routers/grade.tsimport { z } from 'zod'; import { createTRPCRouter, publicProcedure } from '../trpc'; import databaseService from '../../../lib/database'; export const gradeRouter = createTRPCRouter({ // 获取所有成绩 getAll: publicProcedure .input(z.object({ page: z.number().optional(), limit: z.number().optional(), student_id: z.number().optional(), course_id: z.number().optional(), semester: z.string().optional(), min_score: z.number().optional(), max_score: z.number().optional(), }).optional()) .query(async ({ input }) => { return await databaseService.getGrades(input); }), // 根据ID获取成绩 getById: publicProcedure .input(z.number()) .query(async ({ input }) => { return await databaseService.getGradeById(input); }), // 创建成绩 create: publicProcedure .input(z.object({ student_id: z.number(), course_id: z.number(), semester: z.string(), score: z.number().optional(), grade: z.string().optional(), exam_date: z.date().optional(), remarks: z.string().optional(), })) .mutation(async ({ input }) => { return await databaseService.createGrade(input); }), // 更新成绩 update: publicProcedure .input(z.object({ id: z.number(), data: z.object({ score: z.number().optional(), grade: z.string().optional(), exam_date: z.date().optional(), remarks: z.string().optional(), }), })) .mutation(async ({ input }) => { return await databaseService.updateGrade(input.id, input.data); }), // 删除成绩 delete: publicProcedure .input(z.number()) .mutation(async ({ input }) => { return await databaseService.deleteGrade(input); }), // 获取学生成绩 getByStudent: publicProcedure .input(z.object({ student_id: z.number(), semester: z.string().optional(), })) .query(async ({ input }) => { return await databaseService.getGrades({ student_id: input.student_id, semester: input.semester, }); }), // 获取课程成绩 getByCourse: publicProcedure .input(z.object({ course_id: z.number(), semester: z.string().optional(), })) .query(async ({ input }) => { return await databaseService.getGrades({ course_id: input.course_id, semester: input.semester, }); }), }); 创建app/lib/routers/student.tsimport { z } from 'zod'; import { createTRPCRouter, publicProcedure } from '../trpc'; import databaseService from '../../../lib/database'; export const studentRouter = createTRPCRouter({ // 获取所有学生 getAll: publicProcedure .input(z.object({ page: z.number().optional(), limit: z.number().optional(), search: z.string().optional(), class_name: z.string().optional(), gender: z.enum(['男', '女']).optional(), }).optional()) .query(async ({ input }) => { return await databaseService.getStudents(input); }), // 根据ID获取学生 getById: publicProcedure .input(z.number()) .query(async ({ input }) => { return await databaseService.getStudentById(input); }), // 根据学号获取学生 getByStudentId: publicProcedure .input(z.string()) .query(async ({ input }) => { return await databaseService.getStudentByStudentId(input); }), // 创建学生 create: publicProcedure .input(z.object({ student_id: z.string(), name: z.string(), gender: z.enum(['男', '女']), birth_date: z.date().optional(), class_name: z.string().optional(), phone: z.string().optional(), email: z.string().email().optional(), address: z.string().optional(), })) .mutation(async ({ input }) => { return await databaseService.createStudent(input); }), // 更新学生 update: publicProcedure .input(z.object({ id: z.number(), data: z.object({ name: z.string().optional(), gender: z.enum(['男', '女']).optional(), birth_date: z.date().optional(), class_name: z.string().optional(), phone: z.string().optional(), email: z.string().email().optional(), address: z.string().optional(), }), })) .mutation(async ({ input }) => { return await databaseService.updateStudent(input.id, input.data); }), // 删除学生 delete: publicProcedure .input(z.number()) .mutation(async ({ input }) => { return await databaseService.deleteStudent(input); }), // 获取学生成绩统计 getGradeStats: publicProcedure .input(z.number()) .query(async ({ input }) => { return await databaseService.getStudentGradeStats(input); }), }); 更新主路由文件app/lib/routers/_app.tsimport { createTRPCRouter } from '../trpc'; import { studentRouter } from './student'; import { courseRouter } from './course'; import { gradeRouter } from './grade'; export const appRouter = createTRPCRouter({ student: studentRouter, course: courseRouter, grade: gradeRouter, }); export type AppRouter = typeof appRouter; 步骤2：编写前端页面app/page.tsx'use client'; import { useState } from 'react'; import { trpc } from "../utils/trpc"; import Navigation from './components/Navigation'; import StudentManager from './components/StudentManager'; import CourseManager from './components/CourseManager'; import GradeManager from './components/GradeManager'; import ReportManager from './components/ReportManager'; function StatCard({ title, value, icon }: { title: string; value: string | number; icon: string }) { return ( <div className="bg-white rounded-lg shadow p-6"> <div className="flex items-center"> <div className="flex-1"> <p className="text-sm text-black">{title}</p> <p className="text-2xl font-bold text-black">{value}</p> </div> <div className="text-3xl">{icon}</div> </div> </div> ); } function SimpleTable({ title, data, columns }: { title: string; data: any[]; columns: { key: string; label: string }[]; }) { if (!data || data.length === 0) { return ( <div className="bg-white rounded-lg shadow p-6"> <h3 className="text-lg font-semibold mb-4 text-black">{title}</h3> <p className="text-black">暂无数据</p> </div> ); } return ( <div className="rounded-lg shadow p-6 bg-white"> <h3 className="text-lg font-semibold mb-4 text-black">{title}</h3> <div className="overflow-x-auto"> <table className="w-full"> <thead> <tr className="border-b"> {columns.map((col) => ( <th key={col.key} className="text-left py-2 px-4 font-medium text-black"> {col.label} </th> ))} </tr> </thead> <tbody> {data.slice(0, 5).map((row, index) => ( <tr key={index} className="border-b hover:bg-gray-50 text-black"> {columns.map((col) => ( <td key={col.key} className="py-2 px-4 text-black"> {row[col.key] || '-'} </td> ))} </tr> ))} </tbody> </table> </div> {data.length > 5 && ( <p className="text-sm text-gray-500 mt-2">显示前5条，共{data.length}条</p> )} </div> ); } export default function HomePage() { const [currentPage, setCurrentPage] = useState('dashboard'); const students = trpc.student.getAll.useQuery(); const courses = trpc.course.getAll.useQuery(); const grades = trpc.grade.getAll.useQuery({ limit: 10 }); const allGrades = trpc.grade.getAll.useQuery(); const totalStudents = students.data?.length || 0; const totalCourses = courses.data?.length || 0; const totalGrades = allGrades.data?.length || 0; const validGrades = allGrades.data?.filter(grade => grade.score !== null && grade.score !== undefined) || []; const avgScore = validGrades.length > 0 ? (validGrades.reduce((sum, grade) => sum + (parseFloat(String(grade.score)) || 0), 0) / validGrades.length).toFixed(1) : '0.0'; const showPage = () => { switch (currentPage) { case 'students': return <StudentManager />; case 'courses': return <CourseManager />; case 'grades': return <GradeManager />; case 'reports': return <ReportManager />; default: return ( <div className="space-y-6"> <div className="grid grid-cols-2 md:grid-cols-4 gap-4"> <StatCard title="学生总数" value={totalStudents} icon="👥" /> <StatCard title="课程总数" value={totalCourses} icon="📚" /> <StatCard title="成绩记录" value={totalGrades} icon="📊" /> <StatCard title="平均分" value={avgScore} icon="⭐" /> </div> <div className="grid grid-cols-1 lg:grid-cols-2 gap-6"> <SimpleTable title="学生列表" data={students.data || []} columns={[ { key: 'name', label: '姓名' }, { key: 'student_id', label: '学号' }, { key: 'class_name', label: '班级' } ]} /> <SimpleTable title="课程列表" data={courses.data || []} columns={[ { key: 'course_name', label: '课程名称' }, { key: 'teacher_name', label: '教师' }, { key: 'credits', label: '学分' } ]} /> </div> <SimpleTable title="最新成绩" data={grades.data || []} columns={[ { key: 'student_name', label: '学生' }, { key: 'course_name', label: '课程' }, { key: 'score', label: '分数' }, { key: 'semester', label: '学期' } ]} /> </div> ); } }; return ( <div className="min-h-screen bg-gray-50"> <Navigation currentPage={currentPage} onPageChange={setCurrentPage} /> <main className="max-w-7xl mx-auto px-4 py-8"> {showPage()} </main> <footer className="bg-white border-t mt-12 py-6"> <div className="max-w-7xl mx-auto px-4 text-center text-gray-500 text-sm"> <p>学生成绩管理系统 作者dylan su</p> </div> </footer> </div> ); } app/components/Navigation.tsx'use client'; import { useState } from 'react'; interface NavigationProps { currentPage: string; onPageChange: (page: string) => void; } export default function Navigation({ currentPage, onPageChange }: NavigationProps) { const [isMobileMenuOpen, setIsMobileMenuOpen] = useState(false); const menuItems = [ { id: 'dashboard', label: '仪表板' }, { id: 'students', label: '学生管理' }, { id: 'courses', label: '课程管理'}, { id: 'grades', label: '成绩管理' }, { id: 'reports', label: '统计报表'}, ]; return ( <nav className="bg-white shadow-sm border-b"> <div className="max-w-7xl mx-auto px-4 sm:px-6 lg:px-8"> <div className="flex justify-between h-16"> <div className="flex items-center"> <div className="flex-shrink-0 flex items-center"> <span className="text-xl font-bold text-gray-900">学生管理系统</span> </div> </div> <div className="hidden md:flex items-center space-x-8"> {menuItems.map((item) => ( <button key={item.id} onClick={() => onPageChange(item.id)} className={`flex items-center px-3 py-2 rounded-md text-sm font-medium transition-colors ${ currentPage === item.id ? 'bg-blue-100 text-blue-700' : 'text-gray-600 hover:text-gray-900 hover:bg-gray-50' }`} > {item.label} </button> ))} </div> <div className="md:hidden flex items-center"> <button onClick={() => setIsMobileMenuOpen(!isMobileMenuOpen)} className="inline-flex items-center justify-center p-2 rounded-md text-gray-400 hover:text-gray-500 hover:bg-gray-100 focus:outline-none focus:ring-2 focus:ring-inset focus:ring-blue-500" > <span className="sr-only">打开主菜单</span> {isMobileMenuOpen ? ( <svg className="block h-6 w-6" fill="none" viewBox="0 0 24 24" stroke="currentColor"> <path strokeLinecap="round" strokeLinejoin="round" strokeWidth={2} d="M6 18L18 6M6 6l12 12" /> </svg> ) : ( <svg className="block h-6 w-6" fill="none" viewBox="0 0 24 24" stroke="currentColor"> <path strokeLinecap="round" strokeLinejoin="round" strokeWidth={2} d="M4 6h16M4 12h16M4 18h16" /> </svg> )} </button> </div> </div> </div> {isMobileMenuOpen && ( <div className="md:hidden"> <div className="px-2 pt-2 pb-3 space-y-1 sm:px-3 bg-white border-t"> {menuItems.map((item) => ( <button key={item.id} onClick={() => { onPageChange(item.id); setIsMobileMenuOpen(false); }} className={`flex items-center w-full px-3 py-2 rounded-md text-base font-medium transition-colors ${ currentPage === item.id ? 'bg-blue-100 text-blue-700' : 'text-gray-600 hover:text-gray-900 hover:bg-gray-50' }`} > {item.label} </button> ))} </div> </div> )} </nav> ); } app/components/StudentManager.tsx'use client'; import { useState } from 'react'; import { trpc } from '../../utils/trpc'; export default function StudentManager() { const [showForm, setShowForm] = useState(false); const [editingId, setEditingId] = useState<number | null>(null); const [form, setForm] = useState({ student_id: '', name: '', gender: '男' as '男' | '女', class_name: '', phone: '', email: '' }); const students = trpc.student.getAll.useQuery(); const createStudent = trpc.student.create.useMutation({ onSuccess: () => { students.refetch(); resetForm(); } }); const updateStudent = trpc.student.update.useMutation({ onSuccess: () => { students.refetch(); resetForm(); } }); const deleteStudent = trpc.student.delete.useMutation({ onSuccess: () => students.refetch() }); const resetForm = () => { setForm({ student_id: '', name: '', gender: '男', class_name: '', phone: '', email: '' }); setEditingId(null); setShowForm(false); }; const handleSubmit = (e: React.FormEvent) => { e.preventDefault(); if (editingId) { updateStudent.mutate({ id: editingId, data: form }); } else { createStudent.mutate(form); } }; const handleEdit = (student: any) => { setEditingId(student.id); setForm({ student_id: student.student_id, name: student.name, gender: student.gender, class_name: student.class_name || '', phone: student.phone || '', email: student.email || '' }); setShowForm(true); }; const handleDelete = (id: number) => { if (confirm('确定删除这个学生？')) { deleteStudent.mutate(id); } }; return ( <div className="space-y-6 text-black"> <div className="flex justify-between items-center"> <h1 className="text-2xl font-bold">学生管理</h1> <button onClick={() => setShowForm(true)} className="bg-blue-500 text-white px-4 py-2 rounded hover:bg-blue-600" > 添加学生 </button> </div> <div className="bg-white rounded-lg shadow p-6"> <h2 className="text-lg font-semibold mb-4">学生列表</h2> {students.isLoading ? ( <p>加载中...</p> ) : students.error ? ( <p className="text-red-500">加载失败</p> ) : ( <div className="overflow-x-auto"> <table className="w-full"> <thead> <tr className="border-b"> <th className="text-left py-2 px-4">学号</th> <th className="text-left py-2 px-4">姓名</th> <th className="text-left py-2 px-4">性别</th> <th className="text-left py-2 px-4">班级</th> <th className="text-left py-2 px-4">操作</th> </tr> </thead> <tbody> {students.data?.map((student) => ( <tr key={student.id} className="border-b hover:bg-gray-50"> <td className="py-2 px-4">{student.student_id}</td> <td className="py-2 px-4">{student.name}</td> <td className="py-2 px-4">{student.gender}</td> <td className="py-2 px-4">{student.class_name || '-'}</td> <td className="py-2 px-4"> <button onClick={() => handleEdit(student)} className="text-blue-600 hover:text-blue-800 mr-2" > 编辑 </button> <button onClick={() => handleDelete(student.id)} className="text-red-600 hover:text-red-800" > 删除 </button> </td> </tr> ))} </tbody> </table> </div> )} </div> {showForm && ( <div className="fixed inset-0 bg-black bg-opacity-50 flex items-center justify-center"> <div className="bg-white rounded-lg p-6 w-full max-w-md"> <h2 className="text-lg font-semibold mb-4"> {editingId ? '编辑学生' : '添加学生'} </h2> <form onSubmit={handleSubmit} className="space-y-4"> <div> <label className="block text-sm font-medium mb-1">学号</label> <input type="text" value={form.student_id} onChange={(e) => setForm({...form, student_id: e.target.value})} className="w-full px-3 py-2 border rounded focus:outline-none focus:ring-2 focus:ring-blue-500" required /> </div> <div> <label className="block text-sm font-medium mb-1">姓名</label> <input type="text" value={form.name} onChange={(e) => setForm({...form, name: e.target.value})} className="w-full px-3 py-2 border rounded focus:outline-none focus:ring-2 focus:ring-blue-500" required /> </div> <div> <label className="block text-sm font-medium mb-1">性别</label> <select value={form.gender} onChange={(e) => setForm({...form, gender: e.target.value as '男' | '女'})} className="w-full px-3 py-2 border rounded focus:outline-none focus:ring-2 focus:ring-blue-500" > <option value="男">男</option> <option value="女">女</option> </select> </div> <div> <label className="block text-sm font-medium mb-1">班级</label> <input type="text" value={form.class_name} onChange={(e) => setForm({...form, class_name: e.target.value})} className="w-full px-3 py-2 border rounded focus:outline-none focus:ring-2 focus:ring-blue-500" /> </div> <div> <label className="block text-sm font-medium mb-1">电话</label> <input type="text" value={form.phone} onChange={(e) => setForm({...form, phone: e.target.value})} className="w-full px-3 py-2 border rounded focus:outline-none focus:ring-2 focus:ring-blue-500" /> </div> <div> <label className="block text-sm font-medium mb-1">邮箱</label> <input type="email" value={form.email} onChange={(e) => setForm({...form, email: e.target.value})} className="w-full px-3 py-2 border rounded focus:outline-none focus:ring-2 focus:ring-blue-500" /> </div> <div className="flex gap-2 pt-4"> <button type="submit" className="flex-1 bg-blue-500 text-white py-2 rounded hover:bg-blue-600" disabled={createStudent.isPending || updateStudent.isPending} > {createStudent.isPending || updateStudent.isPending ? '保存中...' : '保存'} </button> <button type="button" onClick={resetForm} className="flex-1 bg-gray-500 text-white py-2 rounded hover:bg-gray-600" > 取消 </button> </div> </form> </div> </div> )} </div> ); } app/components/ReportManager.tsx'use client'; import { trpc } from '../../utils/trpc'; export default function ReportManager() { const students = trpc.student.getAll.useQuery(); const courses = trpc.course.getAll.useQuery(); const grades = trpc.grade.getAll.useQuery(); // 计算统计数据 const totalStudents = students.data?.length || 0; const totalCourses = courses.data?.length || 0; const totalGrades = grades.data?.length || 0; // 计算平均分 - 只计算有效的成绩，处理字符串类型的分数 const validGrades = grades.data?.filter(grade => grade.score !== null && grade.score !== undefined) || []; const avgScore = validGrades.length > 0 ? (validGrades.reduce((sum, grade) => sum + (parseFloat(String(grade.score)) || 0), 0) / validGrades.length).toFixed(1) : '0.0'; // 分数分布 - 只使用有效的成绩，处理字符串类型的分数 const scoreDistribution = validGrades.length > 0 ? { excellent: validGrades.filter(g => parseFloat(String(g.score)) >= 90).length, good: validGrades.filter(g => parseFloat(String(g.score)) >= 80 && parseFloat(String(g.score)) < 90).length, average: validGrades.filter(g => parseFloat(String(g.score)) >= 70 && parseFloat(String(g.score)) < 80).length, poor: validGrades.filter(g => parseFloat(String(g.score)) < 70).length } : { excellent: 0, good: 0, average: 0, poor: 0 }; return ( <div className="space-y-6 text-black"> <h1 className="text-2xl font-bold">统计报表</h1> <div className="grid grid-cols-2 md:grid-cols-4 gap-4"> <div className="bg-white rounded-lg shadow p-6"> <div className="flex items-center"> <div className="flex-1"> <p className="text-sm text-black">学生总数</p> <p className="text-2xl font-bold text-black">{totalStudents}</p> </div> <div className="text-3xl">👥</div> </div> </div> <div className="bg-white rounded-lg shadow p-6"> <div className="flex items-center"> <div className="flex-1"> <p className="text-sm text-black">课程总数</p> <p className="text-2xl font-bold text-black">{totalCourses}</p> </div> <div className="text-3xl">📚</div> </div> </div> <div className="bg-white rounded-lg shadow p-6"> <div className="flex items-center"> <div className="flex-1"> <p className="text-sm text-black">成绩记录</p> <p className="text-2xl font-bold text-black">{totalGrades}</p> </div> <div className="text-3xl">📊</div> </div> </div> <div className="bg-white rounded-lg shadow p-6"> <div className="flex items-center"> <div className="flex-1"> <p className="text-sm text-black">平均分</p> <p className="text-2xl font-bold text-black">{avgScore}</p> </div> <div className="text-3xl">⭐</div> </div> </div> </div> <div className="bg-white rounded-lg shadow p-6"> <h2 className="text-lg font-semibold mb-4">分数分布</h2> <div className="grid grid-cols-2 md:grid-cols-4 gap-4"> <div className="text-center"> <div className="text-2xl font-bold text-green-600">{scoreDistribution.excellent}</div> <div className="text-sm text-gray-600">优秀 (90+)</div> </div> <div className="text-center"> <div className="text-2xl font-bold text-blue-600">{scoreDistribution.good}</div> <div className="text-sm text-gray-600">良好 (80-89)</div> </div> <div className="text-center"> <div className="text-2xl font-bold text-yellow-600">{scoreDistribution.average}</div> <div className="text-sm text-gray-600">中等 (70-79)</div> </div> <div className="text-center"> <div className="text-2xl font-bold text-red-600">{scoreDistribution.poor}</div> <div className="text-sm text-gray-600">需努力 (&lt;70)</div> </div> </div> </div> <div className="bg-white rounded-lg shadow p-6"> <h2 className="text-lg font-semibold mb-4">课程平均分</h2> {courses.isLoading ? ( <p>加载中...</p> ) : ( <div className="space-y-3"> {courses.data?.map((course) => { const courseGrades = grades.data?.filter(g => g.course_id === course.id) || []; const avg = courseGrades.length > 0 ? (courseGrades.reduce((sum, g) => sum + (parseFloat(String(g.score)) || 0), 0) / courseGrades.length).toFixed(1) : '0.0'; return ( <div key={course.id} className="flex justify-between items-center py-2 border-b"> <span>{course.course_name}</span> <span className="font-semibold">{avg}分</span> </div> ); })} </div> )} </div> </div> ); } app/components/GradeManager.tsx'use client'; import { useState } from 'react'; import { trpc } from '../../utils/trpc'; export default function GradeManager() { const [showForm, setShowForm] = useState(false); const [editingId, setEditingId] = useState<number | null>(null); const [form, setForm] = useState({ student_id: 0, course_id: 0, score: 0, semester: '' }); const grades = trpc.grade.getAll.useQuery(); const students = trpc.student.getAll.useQuery(); const courses = trpc.course.getAll.useQuery(); const createGrade = trpc.grade.create.useMutation({ onSuccess: () => { grades.refetch(); resetForm(); } }); const updateGrade = trpc.grade.update.useMutation({ onSuccess: () => { grades.refetch(); resetForm(); } }); const deleteGrade = trpc.grade.delete.useMutation({ onSuccess: () => grades.refetch() }); const resetForm = () => { setForm({ student_id: 0, course_id: 0, score: 0, semester: '' }); setEditingId(null); setShowForm(false); }; const handleSubmit = (e: React.FormEvent) => { e.preventDefault(); if (editingId) { updateGrade.mutate({ id: editingId, data: form }); } else { createGrade.mutate(form); } }; const handleEdit = (grade: any) => { setEditingId(grade.id); setForm({ student_id: grade.student_id, course_id: grade.course_id, score: grade.score, semester: grade.semester }); setShowForm(true); }; const handleDelete = (id: number) => { if (confirm('确定删除这个成绩？')) { deleteGrade.mutate(id); } }; const getGradeLevel = (score: number) => { if (score >= 90) return { text: '优秀', color: 'text-green-600' }; if (score >= 80) return { text: '良好', color: 'text-blue-600' }; if (score >= 70) return { text: '中等', color: 'text-yellow-600' }; return { text: '需努力', color: 'text-red-600' }; }; return ( <div className="space-y-6 text-black"> <div className="flex justify-between items-center"> <h1 className="text-2xl font-bold">成绩管理</h1> <button onClick={() => setShowForm(true)} className="bg-purple-500 text-white px-4 py-2 rounded hover:bg-purple-600" > 录入成绩 </button> </div> <div className="bg-white rounded-lg shadow p-6"> <h2 className="text-lg font-semibold mb-4">成绩列表</h2> {grades.isLoading ? ( <p>加载中...</p> ) : grades.error ? ( <p className="text-red-500">加载失败</p> ) : ( <div className="overflow-x-auto"> <table className="w-full"> <thead> <tr className="border-b"> <th className="text-left py-2 px-4">学生</th> <th className="text-left py-2 px-4">课程</th> <th className="text-left py-2 px-4">分数</th> <th className="text-left py-2 px-4">等级</th> <th className="text-left py-2 px-4">学期</th> <th className="text-left py-2 px-4">操作</th> </tr> </thead> <tbody> {grades.data?.map((grade) => { const level = getGradeLevel(grade.score || 0); return ( <tr key={grade.id} className="border-b hover:bg-gray-50"> <td className="py-2 px-4">{grade.student_name}</td> <td className="py-2 px-4">{grade.course_name}</td> <td className="py-2 px-4">{grade.score}</td> <td className={`py-2 px-4 ${level.color}`}>{level.text}</td> <td className="py-2 px-4">{grade.semester}</td> <td className="py-2 px-4"> <button onClick={() => handleEdit(grade)} className="text-blue-600 hover:text-blue-800 mr-2" > 编辑 </button> <button onClick={() => handleDelete(grade.id)} className="text-red-600 hover:text-red-800" > 删除 </button> </td> </tr> ); })} </tbody> </table> </div> )} </div> {showForm && ( <div className="fixed inset-0 bg-black bg-opacity-50 flex items-center justify-center"> <div className="bg-white rounded-lg p-6 w-full max-w-md"> <h2 className="text-lg font-semibold mb-4"> {editingId ? '编辑成绩' : '录入成绩'} </h2> <form onSubmit={handleSubmit} className="space-y-4"> <div> <label className="block text-sm font-medium mb-1">学生</label> <select value={form.student_id} onChange={(e) => setForm({...form, student_id: parseInt(e.target.value)})} className="w-full px-3 py-2 border rounded focus:outline-none focus:ring-2 focus:ring-blue-500" required > <option value="">选择学生</option> {students.data?.map((student) => ( <option key={student.id} value={student.id}> {student.name} ({student.student_id}) </option> ))} </select> </div> <div> <label className="block text-sm font-medium mb-1">课程</label> <select value={form.course_id} onChange={(e) => setForm({...form, course_id: parseInt(e.target.value)})} className="w-full px-3 py-2 border rounded focus:outline-none focus:ring-2 focus:ring-blue-500" required > <option value="">选择课程</option> {courses.data?.map((course) => ( <option key={course.id} value={course.id}> {course.course_name} ({course.course_code}) </option> ))} </select> </div> <div> <label className="block text-sm font-medium mb-1">分数</label> <input type="number" min="0" max="100" value={form.score} onChange={(e) => setForm({...form, score: parseInt(e.target.value)})} className="w-full px-3 py-2 border rounded focus:outline-none focus:ring-2 focus:ring-blue-500" required /> </div> <div> <label className="block text-sm font-medium mb-1">学期</label> <input type="text" value={form.semester} onChange={(e) => setForm({...form, semester: e.target.value})} className="w-full px-3 py-2 border rounded focus:outline-none focus:ring-2 focus:ring-blue-500" placeholder="如：2024春季" required /> </div> <div className="flex gap-2 pt-4"> <button type="submit" className="flex-1 bg-purple-500 text-white py-2 rounded hover:bg-purple-600" disabled={createGrade.isPending || updateGrade.isPending} > {createGrade.isPending || updateGrade.isPending ? '保存中...' : '保存'} </button> <button type="button" onClick={resetForm} className="flex-1 bg-gray-500 text-white py-2 rounded hover:bg-gray-600" > 取消 </button> </div> </form> </div> </div> )} </div> ); } app/components/CourseManager.tsx'use client'; import { useState } from 'react'; import { trpc } from '../../utils/trpc'; export default function CourseManager() { const [showForm, setShowForm] = useState(false); const [editingId, setEditingId] = useState<number | null>(null); const [form, setForm] = useState({ course_code: '', course_name: '', credits: 0, teacher_name: '', description: '' }); const courses = trpc.course.getAll.useQuery(); const createCourse = trpc.course.create.useMutation({ onSuccess: () => { courses.refetch(); resetForm(); } }); const updateCourse = trpc.course.update.useMutation({ onSuccess: () => { courses.refetch(); resetForm(); } }); const resetForm = () => { setForm({ course_code: '', course_name: '', credits: 0, teacher_name: '', description: '' }); setEditingId(null); setShowForm(false); }; const handleSubmit = (e: React.FormEvent) => { e.preventDefault(); if (editingId) { updateCourse.mutate({ id: editingId, data: form }); } else { createCourse.mutate(form); } }; const handleEdit = (course: any) => { setEditingId(course.id); setForm({ course_code: course.course_code, course_name: course.course_name, credits: course.credits, teacher_name: course.teacher_name || '', description: course.description || '' }); setShowForm(true); }; return ( <div className="space-y-6 text-black"> <div className="flex justify-between items-center"> <h1 className="text-2xl font-bold">课程管理</h1> <button onClick={() => setShowForm(true)} className="bg-green-500 text-white px-4 py-2 rounded hover:bg-green-600" > 添加课程 </button> </div> <div className="bg-white rounded-lg shadow p-6"> <h2 className="text-lg font-semibold mb-4">课程列表</h2> {courses.isLoading ? ( <p>加载中...</p> ) : courses.error ? ( <p className="text-red-500">加载失败</p> ) : ( <div className="overflow-x-auto"> <table className="w-full"> <thead> <tr className="border-b"> <th className="text-left py-2 px-4">课程代码</th> <th className="text-left py-2 px-4">课程名称</th> <th className="text-left py-2 px-4">学分</th> <th className="text-left py-2 px-4">教师</th> <th className="text-left py-2 px-4">操作</th> </tr> </thead> <tbody> {courses.data?.map((course) => ( <tr key={course.id} className="border-b hover:bg-gray-50"> <td className="py-2 px-4">{course.course_code}</td> <td className="py-2 px-4">{course.course_name}</td> <td className="py-2 px-4">{course.credits}</td> <td className="py-2 px-4">{course.teacher_name || '-'}</td> <td className="py-2 px-4"> <button onClick={() => handleEdit(course)} className="text-blue-600 hover:text-blue-800" > 编辑 </button> </td> </tr> ))} </tbody> </table> </div> )} </div> {showForm && ( <div className="fixed inset-0 bg-black bg-opacity-50 flex items-center justify-center"> <div className="bg-white rounded-lg p-6 w-full max-w-md"> <h2 className="text-lg font-semibold mb-4"> {editingId ? '编辑课程' : '添加课程'} </h2> <form onSubmit={handleSubmit} className="space-y-4"> <div> <label className="block text-sm font-medium mb-1">课程代码</label> <input type="text" value={form.course_code} onChange={(e) => setForm({...form, course_code: e.target.value})} className="w-full px-3 py-2 border rounded focus:outline-none focus:ring-2 focus:ring-blue-500" required /> </div> <div> <label className="block text-sm font-medium mb-1">课程名称</label> <input type="text" value={form.course_name} onChange={(e) => setForm({...form, course_name: e.target.value})} className="w-full px-3 py-2 border rounded focus:outline-none focus:ring-2 focus:ring-blue-500" required /> </div> <div> <label className="block text-sm font-medium mb-1">学分</label> <input type="number" value={form.credits} onChange={(e) => setForm({...form, credits: parseInt(e.target.value)})} className="w-full px-3 py-2 border rounded focus:outline-none focus:ring-2 focus:ring-blue-500" required /> </div> <div> <label className="block text-sm font-medium mb-1">教师</label> <input type="text" value={form.teacher_name} onChange={(e) => setForm({...form, teacher_name: e.target.value})} className="w-full px-3 py-2 border rounded focus:outline-none focus:ring-2 focus:ring-blue-500" /> </div> <div> <label className="block text-sm font-medium mb-1">描述</label> <textarea value={form.description} onChange={(e) => setForm({...form, description: e.target.value})} className="w-full px-3 py-2 border rounded focus:outline-none focus:ring-2 focus:ring-blue-500" rows={3} /> </div> <div className="flex gap-2 pt-4"> <button type="submit" className="flex-1 bg-green-500 text-white py-2 rounded hover:bg-green-600" disabled={createCourse.isPending || updateCourse.isPending} > {createCourse.isPending || updateCourse.isPending ? '保存中...' : '保存'} </button> <button type="button" onClick={resetForm} className="flex-1 bg-gray-500 text-white py-2 rounded hover:bg-gray-600" > 取消 </button> </div> </form> </div> </div> )} </div> ); } 3.6 运行项目执行bun dev然后看到终端返回打开http://localhost:3000即可看到项目运行的状况4.1 页面展示4.1.1仪表盘这是学生管理系统的仪表板页面，作为系统的主控制面板，它通过四个关键数据卡片（学生总数6人、课程总数10门、成绩记录10条、平均分77.0）快速展示系统整体状况，同时以表格形式分别列出了学生基本信息、课程设置和最新成绩记录，让管理员能够一目了然地掌握当前教学管理的核心数据和最新动态。4.1.2 学生管理这是学生管理系统的学生管理页面，专门用于学生信息的维护和管理。页面顶部设有"添加学生"按钮用于新增学生记录，主体部分以表格形式展示了6名学生的详细信息，包括学号（2024001-2024006）、姓名、性别和所属班级，每行末尾都提供"编辑"和"删除"操作按钮，方便管理员对学生信息进行实时的增删改查操作。4.1.3 课程管理这是学生管理系统的课程管理页面，用于管理学校的课程信息。页面顶部提供"添加课程"按钮用于新增课程，主体部分以表格形式展示了10门课程的详细信息，包括：核心课程：数学、语文、英语、物理、化学、生物等主要学科每门课程都配有相应的任课教师和学分设置课程信息包含：课程代码（如MATH101、CHI101等标准化编码）课程名称学分（2.0-4.0学分不等）任课教师编辑操作按钮4.1.4 成绩管理这是学生管理系统的成绩管理页面，用于记录和管理学生的考试成绩。页面功能包括：主要功能录入成绩：页面右上角提供"录入成绩"按钮，用于添加新的成绩记录成绩查看：以表格形式展示所有学生的成绩信息成绩信息展示页面显示了3名学生（张三、李四、王五）在2024春季学期的各科成绩：学生成绩概况：张三：语文92分（优秀）、英语78.5分（中等）、数学85.5分（良好）李四：数学95分（优秀）、语文88.5分（良好）、英语91分（优秀）王五：数学72分（中等）、语文85分（良好）、英语79.5分（中等）每条成绩记录都提供"编辑"和"删除"操作，方便教师对成绩进行修改和维护。整个界面设计清晰，便于教务人员进行成绩管理工作。4.1.5 统计报表5.1总结本项目基于华为开发者空间的云开发环境，采用 Bun + Next.js + tRPC 现代化全栈技术栈，成功构建了一个功能完善的学生成绩管理系统。系统包含课程管理和成绩管理两大核心模块，实现了标准化的课程编码体系和科学的成绩评价机制。通过 Bun 超快运行时提升构建效率，Next.js 处理前后端复杂逻辑，tRPC 确保端到端类型安全，原生 SQL 直接对接华为 GaussDB 数据库实现最优查询性能。项目充分利用华为开发者空间提供的免费数据库实例和完整开发工具链，体验了从开发编码到应用调测的全流程云开发模式。系统界面简洁直观，支持课程信息维护、成绩录入编辑、智能等级评定等功能，为学校教务管理提供了高效便捷的数字化解决方案。通过实际开发实践，深入了解了现代全栈开发最佳实践和华为根技术生态的强大能力，为未来进一步集成昇腾AI、鸿蒙等技术打下了坚实基础。至此本案例关于在开发者空间–远程开发环境中部署Bun + Next.js + tRPC与开发者空间生态版GaussDB实践操作并编写学生成绩管理系统完毕。我正在参加【案例共创】第6期 开发者空间-基于云开发环境和GaussDB构建应用(https://bbs.huaweicloud.com/forum/thread-0229189398343651003-1-1.html)

PostgreSQL语法解析：从基础到进阶的完整指南PostgreSQL作为全球最先进的开源关系型数据库之一，其语法体系既遵循SQL标准又具备独特扩展性。本文将从语法解析的底层原理出发，结合实际开发场景，深入剖析PostgreSQL的语法特性与优化实践。一、语法解析引擎架构PostgreSQL采用经典的LALR(1)语法分析器，其核心解析流程分为三个阶段：词法分析：通过Flex生成的scan.l文件将SQL语句拆解为Token流语法分析：Bison生成的gram.y文件定义了完整的SQL语法规则树语义分析：在parser.c中完成类型检查、权限验证等逻辑以SELECT * FROM users WHERE id=1为例，解析过程会生成如下语法树：SelectStmt ├── targetList: [*] ├── fromClause: [users] └── whereClause: [OpExpr(=, id, 1)] 二、核心语法特性深度解析1. 表表达式处理PostgreSQL的表表达式支持五种高级操作：WITH子句（CTE）：WITH regional_sales AS ( SELECT region, SUM(amount) AS total_sales FROM orders GROUP BY region ) SELECT region, total_sales FROM regional_sales WHERE total_sales > (SELECT AVG(total_sales) FROM regional_sales); LATERAL连接：SELECT d.dept_name, e.emp_name FROM departments d, LATERAL ( SELECT emp_name FROM employees WHERE dept_id = d.id ORDER BY hire_date DESC LIMIT 1 ) e; 窗口函数：SELECT emp_id, salary, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) as dept_rank, PERCENT_RANK() OVER (ORDER BY salary) as company_percentile FROM employees; 2. 数据类型系统PostgreSQL支持90+种内置数据类型，其中特色类型包括：JSONB：二进制存储的JSON，支持GIN索引-- 创建JSONB索引 CREATE INDEX idx_products_attrs ON products USING gin (attributes); -- JSON路径查询 SELECT name FROM products WHERE attributes @> '{"color": "red"}'; 几何类型：-- 空间查询示例 SELECT * FROM locations WHERE ST_DWithin( geom, ST_GeomFromText('POINT(-71.060316 48.432044)', 4326), 1000 -- 1公里范围 ); 3. 事务隔离增强PostgreSQL通过MVCC实现四种隔离级别，其特色包括：可序列化快照隔离(SSI)：SET default_transaction_isolation = 'serializable'; BEGIN; -- 并发事务不会因序列化失败而中止 UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT; ** advisory locks**：-- 获取会话级建议锁 SELECT pg_advisory_xact_lock(12345); -- 业务逻辑处理... 三、性能优化实践1. 查询重写技巧物化视图自动更新：CREATE MATERIALIZED VIEW mv_sales_summary AS SELECT date_trunc('day', order_date) as day, SUM(amount) as total_sales FROM orders GROUP BY 1; -- 创建刷新策略 CREATE INDEX idx_mv_sales_day ON mv_sales_summary (day); CREATE UNIQUE INDEX ON mv_sales_summary (day); 部分索引优化：-- 仅索引活跃用户 CREATE INDEX idx_users_active ON users (email) WHERE is_active = true; 2. 执行计划分析使用EXPLAIN (ANALYZE, BUFFERS)获取详细执行信息：EXPLAIN (ANALYZE, BUFFERS) SELECT u.name, o.order_date FROM users u JOIN orders o ON u.id = o.user_id WHERE u.reg_date > '2025-01-01' ORDER BY o.order_date DESC LIMIT 100; 典型优化方向：调整work_mem参数影响排序性能通过parallel_setup_cost控制并行查询使用cluster命令按索引物理排序数据四、扩展语法特性1. 存储过程与函数PostgreSQL 11+支持的存储过程特性：CREATE OR REPLACE PROCEDURE transfer_funds( sender_id INT, receiver_id INT, amount NUMERIC ) LANGUAGE plpgsql AS $$ BEGIN -- 事务控制 UPDATE accounts SET balance = balance - amount WHERE id = sender_id AND balance >= amount; IF NOT FOUND THEN RAISE EXCEPTION 'Insufficient funds'; END IF; UPDATE accounts SET balance = balance + amount WHERE id = receiver_id; -- 记录审计日志 INSERT INTO transactions VALUES (...); END; $$; -- 调用存储过程 CALL transfer_funds(1, 2, 100.00); 2. 逻辑解码与CDC通过pg_logical扩展实现变更数据捕获：-- 创建发布端 SELECT * FROM pg_create_logical_replication_slot('my_slot', 'test_decoding'); -- 消费变更 SELECT data FROM pg_logical_slot_get_changes('my_slot', NULL, NULL); 五、未来趋势展望PostgreSQL 16+版本正在引入以下语法增强：并行哈希连接：提升大数据量连接性能增量排序：优化ORDER BY + LIMIT场景JSON路径查询增强：支持$[*]?(@.price > 100)语法Zheap存储引擎：实验性支持无MVCC开销的更新

PostgreSQL数据库高可用实现指南：从原理到生产实践在金融交易、物联网数据采集等场景中，数据库宕机每分钟可能造成数十万元损失。PostgreSQL凭借其强大的复制机制和丰富的生态工具，已成为企业级高可用架构的核心选择。本文将结合生产环境实践经验，深度解析PostgreSQL高可用的技术实现路径。一、高可用架构核心指标构建高可用系统需满足以下关键指标：RTO（故障恢复时间）：核心系统要求<30秒自动切换RPO（数据丢失量）：同步复制实现零数据丢失扩展能力：支持读写分离和在线扩容脑裂防护：具备自动仲裁机制某跨境电商平台通过Patroni+ETCD方案，将数据库可用性从99.9%提升至99.999%，全年故障恢复时间从8小时缩短至12分钟。二、原生流复制技术详解1. 基础配置示例# 主库postgresql.conf配置 wal_level = replica max_wal_senders = 10 synchronous_commit = remote_apply # 同步复制模式 hot_standby = on # 备库recovery.conf（PostgreSQL 12+使用standby.signal替代） primary_conninfo = 'host=192.168.1.101 port=5432 user=repl password=SecurePass123 application_name=node2' restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p' 2. 复制状态监控-- 实时监控复制延迟 SELECT pid, state, sync_state, pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS send_delay, pg_wal_lsn_diff(sent_lsn, write_lsn) AS write_delay, pg_wal_lsn_diff(write_lsn, flush_lsn) AS flush_delay, pg_wal_lsn_diff(flush_lsn, replay_lsn) AS replay_delay FROM pg_stat_replication; 3. 同步复制优化级联复制：通过中间节点降低跨机房带宽消耗复制插槽：防止WAL日志被过早回收-- 创建物理复制插槽 SELECT * FROM pg_create_physical_replication_slot('slot_node2'); 三、自动化故障转移方案1. Patroni+ETCD集群架构# patroni.yml核心配置 scope: pg-cluster namespace: /service/ name: node1 restapi: listen: 0.0.0.0:8008 connect_address: 192.168.1.101:8008 etcd: hosts: etcd1:2379,etcd2:2379,etcd3:2379 bootstrap: dcs: ttl: 30 loop_wait: 10 retry_timeout: 10 maximum_lag_on_failover: 1048576 # 1MB复制延迟阈值 生产环境优化点：集成pg_rewind实现异常节点自动修复通过watchdog硬件模块防止脑裂支持滚动升级和配置动态更新2. HAProxy负载均衡配置# haproxy.cfg示例 frontend pg-frontend bind *:5432 mode tcp default_backend pg-backend backend pg-backend mode tcp balance roundrobin option httpchk GET /master server node1 192.168.1.101:5432 check port 8008 server node2 192.168.1.102:5432 check port 8008 backup健康检查优化：使用/master接口检测主节点状态配置inter 2s实现秒级故障检测通过rise/fall参数控制切换灵敏度四、云原生高可用实践1. AWS RDS Multi-AZ部署架构特点：存储级同步复制（纳秒级延迟）内置健康检查API端点自动DNS切换机制按秒计费的日志传送带宽监控指标：DatabaseConnections：连接数突增预警ReplicationLag：复制延迟监控CPUUtilization：CPU使用率阈值告警2. Kubernetes Operator方案# PostgreSQLCluster CRD示例 apiVersion: postgres-operator.crunchydata.com/v1beta1 kind: PostgresCluster metadata: name: hippo spec: image: registry.developers.crunchydata.com/crunchydata/crunchy-postgres:ubi8-15.6-0 postgresVersion: 15 instances: - name: instance1 replicas: 3 dataVolumeClaimSpec: accessModes: - ReadWriteOnce resources: requests: storage: 1Ti backups: pgbackrest: image: registry.developers.crunchydata.com/crunchydata/crunchy-pgbackrest:ubi8-2.45-0 repos: - name: repo1 volume: volumeClaimSpec: accessModes: - ReadWriteOnce resources: requests: storage: 1Ti优势特性：自动化的备份恢复流程动态扩容存储卷基于PDB的优雅驱逐自定义监控指标导出五、故障处理实战案例案例1：主库脑裂处置-- 1. 识别异常节点 SELECT usename, application_name, client_addr, state FROM pg_stat_activity WHERE state = 'active' AND usename != 'postgres'; -- 2. 强制终止异常连接 SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid <> pg_backend_pid() AND backend_type = 'walsender'; 案例2：复制链路重建# 1. 基础备份重建 pg_basebackup -h new_primary -D /data/pg/standby -P -v -R -X stream -C -S slot_node2 # 2. 配置recovery.signal echo "standby_mode = 'on'" > /data/pg/standby/recovery.signal # 3. 启动备库 systemctl start postgresql-15案例3：DCS集群故障恢复# Patroni DCS故障重试机制 import etcd3 from tenacity import retry, stop_after_attempt, wait_exponential @retry(stop=stop_after_attempt(3), wait=wait_exponential(multiplier=1)) def update_leader_key(key, value): etcd = etcd3.client(host='etcd1', port=2379) try: etcd.put(key, value) except etcd3.exceptions.EtcdConnectionFailed: raise # 触发重试机制 六、未来演进趋势AI运维集成：通过机器学习预测故障发生，实现预防性维护Service Mesh整合：利用Istio实现数据库流量的智能路由新硬件加速：采用NVMe SSD和RDMA网络降低复制延迟量子安全加密：部署PostgreSQL 16的量子抗性加密模块某银行核心系统改造实践显示，采用Patroni+Rook-Ceph存储方案后，数据库性能提升300%，同时将运维人力成本降低65%。建议企业每季度执行完整的容灾演练，确保高可用机制的有效性。构建高可用PostgreSQL集群需要综合考虑业务需求、技术储备和成本因素。通过合理选择技术方案，结合自动化运维工具，可以打造出满足金融级要求的数据库环境，为业务发展提供坚实的数据支撑。

深度解析 PG 数据库主备切换：从原理到实战在数据库管理领域，高可用性是保障业务连续性的关键指标。对于 PostgreSQL（简称 PG）数据库而言，主备切换是实现高可用的核心技术之一。当主数据库出现故障，如硬件损坏、软件崩溃或计划内维护时，通过主备切换，备库能够迅速接管业务，确保数据服务不间断。本文将深入探讨 PG 数据库主备切换的原理、适用场景、切换方法以及实际操作案例，助你全面掌握这一重要技术。一、PG 数据库主备切换概述PG 数据库的主备架构由一个主数据库和多个备数据库组成。主库负责处理所有的写操作，并将事务日志（WAL，Write-Ahead Log）发送给备库。备库则持续接收并应用这些日志，保持与主库数据的同步。主备切换，即打破原有的主备关系，将备库提升为主库，同时让其他备库或新加入的数据库与之建立新的主备关系。主备切换主要分为两类：手动切换和自动切换。手动切换通常用于计划内的维护场景，如升级主库版本、迁移硬件等；自动切换则依赖于监控和故障检测机制，当主库发生故障时，系统自动触发备库接管，适用于突发故障场景。二、主备切换适用场景计划内维护：当需要对主数据库进行版本升级、硬件更换或配置调整时，为避免服务中断，可通过手动主备切换，将业务转移到备库，完成维护后再切换回来。突发故障处理：当主库因硬件故障、软件崩溃、网络中断等原因无法正常工作时，自动主备切换能够快速恢复服务，将影响降至最低。负载均衡与容灾：通过合理配置多个备库，可将读操作分流到备库，减轻主库压力。同时，异地备库可作为容灾节点，在主库所在区域发生灾难时实现业务快速迁移。三、主备切换方法详解（一）手动主备切换手动主备切换需借助pg_ctl和pg_basebackup等工具，具体步骤如下：检查备库状态：确保备库已完全同步主库数据，可通过查询pg_stat_replication和pg_stat_wal_receiver视图确认。终止主库服务：使用pg_ctl stop -m immediate命令停止主库，-m immediate参数表示立即停止，不进行事务提交检查。提升备库为主库：在备库执行pg_ctl promote命令，备库将停止接收 WAL 日志，并转变为主库状态，开始接受读写请求。重新配置新备库：若有其他备库，需重新配置它们连接到新的主库，并使用pg_basebackup重新初始化数据同步。（二）自动主备切换自动主备切换依赖于高可用（HA）软件，如 Patroni、pg_auto_failover 等。以 Patroni 为例，其工作流程如下：监控与选举：Patroni 集群中的每个节点通过 etcd 或 Consul 等分布式协调工具进行心跳检测。当主库心跳丢失，其他节点将发起选举，选出新的主库。故障转移：当选节点执行pg_ctl promote操作，将自身提升为主库，并更新集群配置，通知其他节点连接新主库。重新同步：原备库自动调整为新主库的备库，重新建立 WAL 日志同步。四、实际操作案例（一）手动切换示例假设当前主库为node1，备库为node2，执行手动切换：在node1上停止主库服务：pg_ctl stop -m immediate在node2上提升备库为主库：pg_ctl promote重新配置其他备库（若有）连接到node2，并初始化数据同步：pg_basebackup -h node2 -U postgres -D /var/lib/postgresql/data -P -Xs（二）Patroni 自动切换示例配置 Patroni 集群，在每个节点的patroni.yml文件中定义主库选举策略和 etcd 连接信息：scope: postgres namespace: /db/ name: node1 restapi: listen: 0.0.0.0:8008 etcd: host: 127.0.0.1:2379 bootstrap: dcs: ttl: 30 loop_wait: 10 retry_timeout: 10 maximum_lag_on_failover: 1048576 启动 Patroni 服务：systemctl start patroni模拟主库故障（如关闭主库节点），Patroni 将自动触发选举和故障转移，新主库接管业务。五、注意事项与常见问题数据一致性：切换前务必确认备库已完全同步主库数据，避免数据丢失或不一致。可通过监控pg_stat_replication视图中的write_lag、flush_lag和replay_lag参数判断同步状态。网络与权限：确保主备库之间网络通畅，且数据库用户具备必要的权限（如复制权限）。切换后检查：切换完成后，需验证新主库的读写功能、备库的同步状态，以及应用程序是否正确连接到新主库。HA 软件配置：使用自动切换工具时，需仔细配置选举策略、故障检测阈值等参数，避免误切换或切换失败。六、总结PG 数据库主备切换是保障高可用性的核心手段，手动切换提供了灵活的维护能力，自动切换则实现了故障场景下的快速恢复。通过掌握切换原理、方法和实际操作，结合业务需求选择合适的切换策略，能够有效提升数据库服务的稳定性和可靠性。在实际应用中，建议定期进行切换演练，及时发现并解决潜在问题，确保关键时刻切换万无一失。以上详细阐述了 PG 数据库主备切换的各个方面。如果你对某个部分想深入了解，或是还有其他数据库相关问题，欢迎随时和我交流。

PG 数据库主备搭建全攻略在当今数据驱动的时代，数据库的高可用性至关重要。PostgreSQL（简称 PG）作为一款功能强大的开源关系型数据库，通过主备搭建，能有效保障数据的安全性和服务的连续性。当主数据库出现故障时，备数据库可迅速接管，减少业务中断时间。接下来，我将详细介绍 PG 数据库主备搭建的完整流程。一、环境准备在开始搭建前，需要在两台服务器上完成以下操作：安装 PostgreSQL：通过官方 yum 源安装 PostgreSQL 13。执行以下命令：sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm sudo yum install -y postgresql13-server sudo /usr/pgsql-13/bin/postgresql-13-setup initdb sudo systemctl enable postgresql-13 sudo systemctl start postgresql-13 关闭防火墙或配置规则：为了让主备服务器之间能够正常通信，需要关闭防火墙，或者配置允许 PostgreSQL 默认端口 5432 的通信规则。关闭防火墙命令如下：sudo systemctl stop firewalld sudo systemctl disable firewalld若选择配置防火墙规则，可执行：sudo firewall-cmd --permanent --add-port=5432/tcp sudo firewall-cmd --reload配置主机名解析：在两台服务器的/etc/hosts文件中，添加对方的 IP 地址和主机名映射。例如：二、主库配置在主服务器上进行以下配置：修改postgresql.conf文件：该文件位于/var/lib/pgsql/13/data/目录下，主要修改以下参数：# 允许来自备库的连接 listen_addresses = '*' # 启用流复制 wal_level = replica # 归档模式 archive_mode = on archive_command = 'test ! -f /var/lib/pgsql/13/archive/%f && cp %p /var/lib/pgsql/13/archive/%f' # 最大连接数，根据实际情况调整 max_wal_senders = 10 wal_keep_segments = 64 修改pg_hba.conf文件：同样在/var/lib/pgsql/13/data/目录下，添加允许备库连接的规则：这里创建了一个名为replicator的复制用户，后续会在备库使用该用户进行连接。创建复制用户：登录到 PostgreSQL 数据库，执行以下命令创建复制用户：CREATE USER replicator WITH REPLICATION PASSWORD 'your_password'; 重启主库服务：使配置生效：sudo systemctl restart postgresql-13 备份主库数据：使用pg_basebackup命令备份主库数据，并将备份文件传输到备库。在主库执行：sudo -u postgres pg_basebackup -h -U replicator -P -Xs -D /var/lib/pgsql/13/backup将备份文件目录打包并传输到备库：sudo tar -czf backup.tar.gz /var/lib/pgsql/13/backup scp backup.tar.gz postgres@192.168.1.101:/tmp三、备库配置在备服务器上进行操作：停止备库服务：sudo systemctl stop postgresql-13 删除原有数据目录内容：sudo rm -rf /var/lib/pgsql/13/data/*解压主库备份文件到备库数据目录：sudo tar -xzf /tmp/backup.tar.gz -C /var/lib/pgsql/13/ sudo mv /var/lib/pgsql/13/backup/* /var/lib/pgsql/13/data/ sudo chown -R postgres:postgres /var/lib/pgsql/13/data创建recovery.conf文件：在/var/lib/pgsql/13/data/目录下创建该文件，并添加以下内容：standby_mode = 'on' primary_conninfo = 'host= port=5432 user=replicator password=your_password sslmode=prefer' trigger_file = '/var/lib/pgsql/13/data/standby.signal' 修改postgresql.conf文件：主要修改以下参数：listen_addresses = '*' max_wal_senders = 0 wal_level = replica启动备库服务：sudo systemctl start postgresql-13 验证备库状态：登录到备库的 PostgreSQL 数据库，执行以下命令查看复制状态：SELECT * FROM pg_stat_replication; 如果显示主库相关信息，说明主备复制已成功建立。四、故障切换当主库出现故障时，需要将备库切换为主库。在备库上执行以下操作：停止备库复制：sudo touch /var/lib/pgsql/13/data/standby.signal sudo systemctl stop postgresql-13 修改recovery.conf文件：将其重命名或删除，确保备库不再以备用模式启动。启动备库作为新的主库：sudo systemctl start postgresql-13 配置新的备库（如果需要）：如果还需要新的备库，可以按照上述步骤，以新的主库为基础进行搭建。通过以上步骤，我们成功完成了 PG 数据库的主备搭建，为数据库的高可用性提供了保障。在实际生产环境中，还需要结合监控和自动化脚本，进一步提升系统的稳定性和运维效率。希望这篇文章能对你有所帮助，如果你在搭建过程中遇到问题，欢迎在评论区留言讨论。上述内容涵盖了 PG 数据库主备搭建全流程。若你对某个步骤想了解更多细节，或有其他特定需求，欢迎随时和我说。

PostgreSQL（简称 PG）是一款功能强大的开源关系型数据库，以下是 PG 数据库的入门语法和操作指南，适合初学者快速上手。一、基本操作1. 连接数据库bashpsql -U username -d dbname -h host -p port # 示例：连接本地数据库 postgres，用户名为 postgres psql -U postgres -d postgres2. 查看帮助sql\? -- 查看 psql 命令帮助 \h -- 查看 SQL 命令语法帮助 \h SELECT -- 查看 SELECT 命令详细帮助 二、数据库管理1. 创建数据库sqlCREATE DATABASE dbname; -- 示例：创建名为 mydb 的数据库 CREATE DATABASE mydb; 2. 删除数据库sqlDROP DATABASE dbname; -- 示例：删除 mydb 数据库 DROP DATABASE mydb; 3. 查看所有数据库sql\l -- 列出所有数据库 4. 切换数据库sql\c dbname; -- 示例：切换到 mydb 数据库 \c mydb; 三、表操作1. 创建表sqlCREATE TABLE table_name ( column1 datatype constraint, column2 datatype constraint, ... ); -- 示例：创建用户表 CREATE TABLE users ( id SERIAL PRIMARY KEY, -- 自增主键 name VARCHAR(50) NOT NULL, -- 非空字符串 age INT CHECK (age >= 0), -- 检查约束 email VARCHAR(100) UNIQUE, -- 唯一约束 created_at TIMESTAMP DEFAULT NOW() -- 默认值 ); 2. 查看表结构sql\d table_name; -- 示例：查看 users 表结构 \d users; 3. 修改表sql-- 添加列 ALTER TABLE users ADD COLUMN address VARCHAR(200); -- 修改列类型 ALTER TABLE users ALTER COLUMN age TYPE SMALLINT; -- 删除列 ALTER TABLE users DROP COLUMN address; 4. 删除表sqlDROP TABLE table_name; -- 示例：删除 users 表 DROP TABLE users; 四、数据操作1. 插入数据sqlINSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...); -- 示例：插入用户数据 INSERT INTO users (name, age, email) VALUES ('Alice', 30, 'alice@example.com'); 2. 查询数据sqlSELECT column1, column2, ... FROM table_name WHERE condition; -- 示例：查询所有用户 SELECT * FROM users; -- 示例：查询年龄大于 25 的用户 SELECT name, age FROM users WHERE age > 25; 3. 更新数据sqlUPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition; -- 示例：更新用户年龄 UPDATE users SET age = 31 WHERE name = 'Alice'; 4. 删除数据sqlDELETE FROM table_name WHERE condition; -- 示例：删除名为 Bob 的用户 DELETE FROM users WHERE name = 'Bob'; 五、高级查询1. 排序sqlSELECT * FROM users ORDER BY age DESC; -- 按年龄降序排列 2. 限制结果集sqlSELECT * FROM users LIMIT 10; -- 返回前 10 条记录 3. 聚合函数sqlSELECT COUNT(*) FROM users; -- 统计总数 SELECT AVG(age) FROM users; -- 计算平均年龄 SELECT MAX(age) FROM users; -- 最大年龄 4. 分组查询sqlSELECT age, COUNT(*) FROM users GROUP BY age; 5. 多表连接sql-- 创建订单表 CREATE TABLE orders ( order_id SERIAL PRIMARY KEY, user_id INT REFERENCES users(id), -- 外键关联 product VARCHAR(50), amount NUMERIC(10,2) ); -- 查询用户及其订单 SELECT users.name, orders.product FROM users JOIN orders ON users.id = orders.user_id; 六、索引与约束1. 创建索引sqlCREATE INDEX idx_users_email ON users (email); 2. 添加约束sqlALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email); 七、事务处理sqlBEGIN; -- 开始事务 INSERT INTO users (name, email) VALUES ('Charlie', 'charlie@example.com'); UPDATE users SET age = 28 WHERE name = 'Charlie'; COMMIT; -- 提交事务 -- 或回滚 ROLLBACK; 八、用户与权限1. 创建用户sqlCREATE USER username WITH PASSWORD 'password'; 2. 授予权限sqlGRANT SELECT, INSERT ON users TO username; GRANT ALL PRIVILEGES ON DATABASE mydb TO username; 3. 撤销权限sqlREVOKE INSERT ON users FROM username; 九、备份与恢复1. 备份数据库bashpg_dump -U username -d dbname -f backup.sql2. 恢复数据库bashpsql -U username -d dbname -f backup.sql十、常用命令速查表操作命令查看当前数据库SELECT current_database();查看当前用户SELECT current_user;查看所有表\dt查看表详细信息\d+ table_name退出 psql\q

PostgreSQL主备同步机制深度解析在分布式数据库系统中，确保数据的一致性和可靠性是一项至关重要的任务。PostgreSQL作为一个高度可扩展且功能丰富的开源关系型数据库管理系统，提供了多种数据同步机制来保证数据的高可用性和一致性。本文将深入探讨PostgreSQL的主备同步机制，特别是其流复制技术，并包含相关的配置代码示例。一、PostgreSQL主备同步概述PostgreSQL的主备同步机制基于日志的复制机制（Write-Ahead Logging, WAL）进行。这种机制使得数据在主数据库（Primary）和从数据库（Standby）之间保持同步，从而确保高可用性和数据冗余。主数据库（Primary）：负责处理所有写操作（插入、更新、删除）以及读操作。从数据库（Standby）：负责接收从主数据库传递来的WAL日志，并应用这些日志以保持数据的一致性。可以配置为只读以分担查询负载。二、WAL日志机制WAL日志文件记录了数据库的每一个修改操作。主数据库将所有变更首先写入WAL日志文件，然后将这些日志发送给从数据库。发送WAL数据：主数据库通过WAL Sender进程（wal_sender）将WAL日志发送给从数据库。接收和写入WAL数据：从数据库通过WAL Receiver进程（wal_receiver）接收来自主数据库的WAL日志，并将其写入本地存储。应用WAL数据：从数据库将接收到的WAL日志应用到自身的数据库实例中，从而使数据与主数据库保持一致。WAL日志文件通常被以16MB为一个文件块存储，称为WAL段。主数据库的WAL Sender进程将最新的WAL日志记录发送给从数据库。三、PostgreSQL主备同步配置要实现PostgreSQL的主备同步，需要对主库和备库进行一系列配置。以下是一个详细的配置步骤。1. 配置主库（1）docker-compose.yml配置文件version: '3' services: postgres: image: postgres:11-alpine container_name: postgres-master ports: - "5432:5432" volumes: - "./pgdata:/var/lib/postgresql/data" environment: POSTGRES_PASSWORD: password POSTGRES_USER: dbuser POSTGRES_DB: example TZ: Asia/Shanghai（2）pg_hba.conf配置host replication repuser 172.27.1.11/32 md5 host replication repuser 172.27.1.12/32 md5（3）postgresql.conf配置listen_addresses = '*' wal_level = replica archive_mode = on archive_command = '/bin/date' max_wal_senders = 10 wal_keep_segments = 64 wal_sender_timeout = 60s hot_standby = on max_connections = 1000（4）创建数据库复制用户docker exec -it postgres-master /bin/bash psql -U dbuser -d example CREATE ROLE repuser LOGIN REPLICATION ENCRYPTED PASSWORD 'repuser'; 2. 配置备库（1）使用pg_basebackup拉起备库./pg_basebackup -h localhost -U repuser -p 5432 -P -v -R -X stream -D ../data_b -l backup_label（2）修改postgresql.confecho "port = 5433" >> ../data_b/postgresql.conf echo "hot_standby = on" >> ../data_b/postgresql.conf（3）启动备库./pg_ctl start -D ../data_b -l logfile_b3. 设置同步复制参数在主库上设置synchronous_commit和synchronous_standby_names参数，使备库变为同步状态。ALTER SYSTEM SET synchronous_commit = 'on'; ALTER SYSTEM SET synchronous_standby_names = 'walreceiver'; SELECT pg_reload_conf(); 四、同步复制的工作机制在同步复制模式下，当主库执行写操作并等待确认时，备库接收并写入XLOG。随后，主库进程被唤醒，确保数据一致性。主库插入数据，刷XLOG：主库插入数据后，将变更写入WAL日志文件，并调用SyncRepWaitForLSN函数等待。备库接收并写入XLOG：备库的WAL Receiver进程接收来自主库的WAL日志，并将其写入本地存储。主库唤醒并确认提交：备库将XLOG刷入磁盘后，通知主库的WAL Sender进程。主库收到通知后，使用SyncRepWakeQueue唤醒所有等待队列中的进程，并确认事务提交。五、数据一致性保证在主库崩溃的情况下，通过recovery流程可以保证数据不丢失。由于主库先写了XLOG并且已落盘，如果主库崩溃而备库尚未收到这条XLOG，但在主库恢复时，会通过recovery流程恢复这条数据，从而保证数据一致性。六、性能与可靠性权衡synchronous_commit参数：off：性能优先，但存在数据丢失风险。local：本地数据安全，适度性能与安全平衡。on：最高级别的数据持久性保障，但会增加事务提交延迟。remote_write：适度数据安全与性能平衡，依赖于远程数据传输。remote_apply：高数据安全性，但事务提交延迟较大。synchronous_standby_names参数：定义参与同步复制过程中的备用服务器，确保数据的高可用性和一致性。七、应用场景银行交易、财务系统：对数据安全性和事务持久性有极高标准要求。医疗系统：需要确保数据的每一次更改都能被可靠保存。政府服务：要求数据的每一次变更都具备高度的可靠性和持久性。八、总结PostgreSQL的主备同步机制基于WAL日志实现，通过流复制技术确保数据在主库和备库之间保持同步。通过合理配置synchronous_commit和synchronous_standby_names参数，可以在性能与可靠性之间找到平衡点。在实际应用中，根据具体场景选择合适的配置参数，可以确保数据的高可用性和一致性。

在关系型数据库领域，PostgreSQL（简称PG）始终以"世界上最先进的开源数据库"自居。这个诞生于1995年的老牌数据库，经过28年的持续迭代，已发展成为支持JSON、GIS、时序数据等复杂场景的全能型选手。本文将通过架构解析与代码实践，揭示其六大核心优势，并探讨这些特性如何赋能现代应用。一、架构优势之一：极致的扩展性设计PostgreSQL通过多层次的扩展机制，实现了从单机到分布式集群的平滑过渡。其架构设计遵循"分而治之"的哲学，将扩展性分解为存储层、计算层和逻辑层三个维度。1.1 分区表技术-- 创建按时间分区的测量数据表 CREATE TABLE measurements ( device_id INT NOT NULL, logdate DATE NOT NULL, temperature FLOAT ) PARTITION BY RANGE (logdate); -- 创建子分区 CREATE TABLE measurements_y2023 PARTITION OF measurements FOR VALUES FROM ('2023-01-01') TO ('2024-01-01'); 通过声明式分区语法，开发者无需编写触发器即可实现自动路由。结合pg_partman扩展，可自动创建/删除历史分区，特别适合时序数据存储。1.2 逻辑复制与FDW-- 创建到远程集群的FDW连接 CREATE EXTENSION postgres_fdw; CREATE SERVER remote_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '192.168.1.100', dbname 'sales_db'); -- 创建映射用户 CREATE USER MAPPING FOR local_user SERVER remote_server OPTIONS (user 'remote_user', password 'secret'); -- 导入远程表 IMPORT FOREIGN SCHEMA public FROM SERVER remote_server INTO local_schema; 通过Foreign Data Wrapper（FDW），可跨数据库访问Oracle、MySQL甚至CSV文件，实现异构数据联邦查询。二、架构优势之二：多版本并发控制（MVCC）PostgreSQL的MVCC实现堪称教科书级别，通过事务ID和可见性映射表，在读写冲突时无需锁表即可保证ACID特性。2.1 事务隔离演示-- 会话1：开启事务并修改数据 BEGIN; UPDATE accounts SET balance = balance - 100 WHERE user_id = 1; -- 不提交事务 -- 会话2：尝试读取未提交数据 SELECT * FROM accounts WHERE user_id = 1; -- 返回旧版本数据（REPEATABLE READ隔离级别） 即使在高并发场景下，MVCC也能保证读操作始终访问一致性快照，避免了传统数据库的锁竞争问题。2.2 可见性判断机制每个元组头部都包含xmin（插入事务ID）、xmax（删除事务ID）、cmin/cmax（命令序号）等元数据。通过HeapTupleSatisfiesVacuum函数，系统可以快速判断元组可见性，配合VACUUM进程实现空间回收。三、架构优势之三：查询优化器的黑科技PostgreSQL的优化器采用基于成本的动态规划算法，结合统计信息和执行计划缓存，能生成接近最优的查询方案。3.1 执行计划分析EXPLAIN ANALYZE SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31' ORDER BY total_amount DESC LIMIT 10; 输出示例：Limit (cost=1234.56..1234.59 rows=10 width=120) -> Sort (cost=1234.56..1345.67 rows=4321 width=120) Sort Key: total_amount DESC -> Index Scan using idx_order_date on orders (cost=0.43..1123.45 rows=4321 width=120) Index Cond: ((order_date >= '2023-01-01'::date) AND (order_date <= '2023-12-31'::date)) 优化器能智能选择索引扫描+排序的组合，而非全表扫描后排序。3.2 自定义统计信息-- 创建多列统计信息 CREATE STATISTICS ord_stats (ndistinct, dependencies) ON order_date, total_amount FROM orders; -- 收集统计信息 ANALYZE orders; 通过扩展统计信息，优化器能更精准地估算多列联合查询的选择性，提升复杂查询性能。四、架构优势之四：丰富的数据类型支持PostgreSQL原生支持JSONB、几何类型、网络地址等100+数据类型，避免了应用层类型转换的开销。4.1 JSONB索引优化-- 创建GIN索引 CREATE INDEX idx_jsonb_data ON user_profiles USING GIN (profile_data jsonb_path_ops); -- 高性能查询 SELECT * FROM user_profiles WHERE profile_data @> '{"interests": ["tech", "music"]}'; GIN索引能高效处理JSONB的包含查询，比全表扫描快100倍以上。4.2 空间数据计算-- 计算地理围栏 SELECT * FROM delivery_points WHERE ST_Within( geom::geography, ST_Buffer( ST_MakePoint(-122.4194, 37.7749)::geography, 500 -- 500米半径 ) ); PostGIS扩展支持R-tree空间索引，可快速处理地理围栏查询、路径分析等场景。五、架构优势之五：ACID合规性的企业级实现PostgreSQL通过Write-Ahead Logging（WAL）和Checkpoint机制，在保证性能的同时实现严格的事务持久性。5.1 WAL段分析-- 查看当前WAL段 SELECT pg_current_wal_lsn(); -- 解析WAL内容 pg_waldump -p /usr/local/pgsql/data/pg_wal -s 0/3000000 每个事务修改都会先写入WAL段，确保崩溃后可通过重放恢复数据。5.2 两阶段提交（2PC）-- 开启分布式事务 BEGIN; PREPARE TRANSACTION 'txn1'; -- 在其他节点执行 COMMIT PREPARED 'txn1'; 通过PREPARE TRANSACTION，可实现跨节点的事务原子性，适合分布式系统。六、架构优势之六：高可用与扩展生态PostgreSQL提供流复制、逻辑复制、Patroni等多种高可用方案，结合社区扩展可构建云原生数据库。6.1 流复制配置# 主库postgresql.conf wal_level = replica max_wal_senders = 10 # 备库recovery.conf primary_conninfo = 'host=192.168.1.100 port=5432 user=repl_user' standby_mode = on异步流复制可实现毫秒级延迟，同步复制确保零数据丢失。6.2 使用Patroni构建集群# patroni.yml示例 scope: postgres name: node1 restapi: listen: 0.0.0.0:8008 connect_address: 192.168.1.100:8008 bootstrap: dcs: ttl: 30 loop_wait: 10 postgresql: use_pg_rewind: true Patroni配合etcd/Consul可自动处理故障转移，实现自愈集群。

 请看图：无缘无故报个这个问题，官网文档搜不到一点有用信息，搞什么？tpops 是 24.1.30版本