PostgreSQL笔记5: 学习资源与社区生态全景指南

《postgresql指南--内幕探索》第二章 进程与内存架构 本文参考书籍《postgresql指南–内幕探索》的 Chapter 2:Process and Memory Architecture 该书的网络内容可参考:The Internals of PostgreSQL 进程架构 Postgresql 是一个C/S架构的关系型数据库,由多个后台进程管理数据库,下面分别介绍一些这些进程: postgres server process :是所有进程的父进程 backend process:每一个客户端的连接都有一个后端进程存在 backgroud process 阅读详情

概述

PostgreSQL 作为全球最先进的开源关系型数据库,其成功在很大程度上归功于活跃且庞大的全球社区生态。对于数据库管理员、应用开发者乃至内核爱好者而言,掌握一套高效的学习路径和资源获取渠道,是精通 PostgreSQL 的关键前提。本文将系统梳理中国及全球 PostgreSQL 社区的核心组织、优质学习资源、技术进阶路径以及实用的开发工具链。

纲要

  • PostgreSQL 中国社区组织
    • 中国开源软件推进联盟PostgreSQL分会(PG分会)
      • 年度技术大会 PostgresConf.CN / PGConf.Asia
      • 认证培训体系: PGCA / PGCE / PGCM
      • 荣誉体系: PostgreSQL ACE 计划
    • PostgreSQL中文社区
      • 中文文档翻译与维护
      • 社区荣誉体系: MVP
  • 学习资源
    • 官方文档与 Wiki
    • 推荐书籍
      • 入门与实战: 《PostgreSQL 小工端专家》、《PostgreSQL 实战》
      • 原理与内核: 《PostgreSQL 指南:内幕探索》、《PostgreSQL 技术内幕:查询优化深度探索》、《PostgreSQL 技术内幕:事务处理深度探索》、《PostgreSQL 内参》
    • 技术社区与平台
      • 问答社区: PGFans
      • 资讯与博客: 数据库内核月报、PGConf 演讲资料
    • 开发者资源
      • CommitFest 与 Mailing List
      • 源代码与 Git
  • API 速览
    • psycopg2 (Python) 连接与操作
    • pg (Node.js) 连接与操作
  • Demo 简单示例
    • 环境准备与数据库连接
    • 建表与批量数据插入
    • 事务与并发控制演示

中国 PostgreSQL 社区生态

PostgreSQL 在国内拥有完善且活跃的本土化组织与生态,为开发者提供了从入门到专家级的全方位支持。

中国开源软件推进联盟PostgreSQL分会(PG分会)

中国开源软件推进联盟 PostgreSQL 分会(简称“中国 PG 分会”)是在工业和信息化部赛迪研究院(中国电子信息产业发展研究院)指导下,依托中国开源软件推进联盟成立的核心组织[reference:1][reference:2]。其宗旨在于推动国内 PostgreSQL 产业的生态建设,促进产学研用协同发展[reference:3]。

  • 年度技术大会:分会自2017年起,连续举办 PGOpen.CN、PostgresConf.CN、PGConf.Asia 等年度性大会[reference:4][reference:5]。这些会议通常会邀请全球 PostgreSQL 核心贡献者、行业技术专家及高校学者,围绕数据库内核优化、AI 融合、云原生架构等前沿议题展开深度交流
  • 培训认证体系:分会建立了国内权威的 PostgreSQL 技术等级认证体系,分为三个层级:
    • PGCA (PostgreSQL Certified Associate):初级认证专员,面向初级管理员与开发人员。
    • PGCE (PostgreSQL Certified Expert):中级认证专家,面向数据库管理工程师与开发工程师。
    • PGCM (PostgreSQL Certified Master):高级认证大师,面向数据库高级工程师与架构师。
  • 荣誉体系 (PostgreSQL ACE 计划):该计划于 2020 年由分会创立,旨在承认和表彰对 PG 技术推广与生态发展有卓越贡献的人士。计划共分三级:“PostgreSQL ACE 伙伴”、“PostgreSQL ACE”、“PostgreSQL ACE 总监”。热心于布道的社区成员可通过贡献积分或邀请制加入。

PostgreSQL中文社区

PostgreSQL 中文社区(官网:postgres.cn)是一个由志愿者组成的非营利性公益组织。社区致力于 PostgreSQL 开源数据库技术在国内的宣传与推广。

  • 中文文档:社区的核心贡献之一是由志愿者团队维护的 PostgreSQL 中文手册。该项目在社区第一代功勋 laser 翻译的 8.2.3 中文手册基础上,持续跟进新版本的翻译工作。
  • 社区活动:中文社区同样组织年度技术大会及不定期的线下沙龙。
  • 荣誉体系 (MVP):社区设有 MVP (Most Valuable Professional) 计划,用以认可和表彰为技术传播与生态建设做出贡献的成员。

高质量学习资源

官方文档与Wiki

  • 官方文档:PostgreSQL 官方文档(www.postgresql.org/docs)是学习的第一手资料,内容详尽且权威。对于英文阅读有困难的开发者,可参考 PostgreSQL 中文社区翻译的中文手册(www.postgres.cn/docs)。
  • PostgreSQL Wiki:wiki.postgresql.org 托管了项目的 FAQ、TODO 列表以及大量社区贡献的技术主题。

推荐书籍

根据学习阶段的不同,可以参考以下书籍资源:

阶段推荐书籍核心内容
入门与基础《PostgreSQL 小工端专家》、《PostgreSQL 实战》全面系统的入门知识、实战案例与运维技巧[reference:28]。
原理与调优《PostgreSQL 指南:内幕探索》(铃木启修 著)系统解析内核架构,涵盖查询处理、并发控制、WAL、MVCC 等核心模块[reference:29][reference:30]。
查询优化《PostgreSQL 技术内幕:查询优化深度探索》(张树杰 著)深入剖析子查询提升、外连接消除、谓词下推、代价估算等优化器实现细节[reference:31][reference:32]。
事务处理《PostgreSQL 技术内幕:事务处理深度探索》(张树杰 著)详解事务、两阶段锁、MVCC、故障恢复及物理/逻辑复制机制[reference:33][reference:34]。
内核高级《PostgreSQL 内参》(《The Internals of PostgreSQL》中译版)侧重内核原理与源代码导读[reference:35]。

技术社区与平台

  • PGFans 问答社区:www.pgfans.cn 是活跃的 PostgreSQL 中文技术问答社区。开发者可以在上面发布问题,由资深爱好者和专业人士解答。社区还运营多个区域交流群(如北京、上海、广东等)。
  • 数据库内核月报:由阿里云数据库内核团队维护,每月输出关于数据库内核(包含 MySQL、PostgreSQL 等)的深度技术文章。内容涉及 WAL 检查点调度、并行查询、Vacuum 机制等底层原理。
  • PGConf 演讲资料:PGConf 系列会议(含中国区大会及国际 PGConf)的演讲 PDF 和视频资料是获取最佳实践与内核新特性解析的重要渠道。

开发者资源

对于有志于参与内核开发或深入理解代码的开发者,以下资源不可或缺:

  • CommitFest:PostgreSQL 每年有 5 个 CommitFest(7月、9月、11月、1月、3月),用于审查和合并补丁。通过 commitfest.postgresql.org 可以跟踪最新的功能开发、Bug 修复和特性合并进展。
  • Mailing List:邮件列表是社区沟通的核心渠道,开发者可以在此提问、分享经验或与核心开发者交流。
  • 源代码:PostgreSQL 源代码托管在 Git 仓库中,通过阅读源码是理解数据库运行原理的最高阶方式。

API 速览

本部分梳理与 PostgreSQL 交互时常用的核心 API,主要以 Python 的 psycopg2 和 Node.js 的 pg 库为例。

psycopg2 (Python)

psycopg2 是 Python 中最流行的 PostgreSQL 数据库适配器。

安装

pip install psycopg2-binary

核心API

  1. 建立连接: psycopg2.connect(dsn=None, connection_factory=None, cursor_factory=None, **kwargs)
    • 参数: 可通过 DSN (数据源名称) 字符串或关键字参数(如 host, port, database, user, password)传递连接信息。
    • 返回值: 返回一个 connection 对象。
  2. 创建游标: connection.cursor(name=None, cursor_factory=None, scrollable=None, withhold=False)
    • 返回值: 返回一个 cursor 对象,用于执行数据库操作。
  3. 执行SQL: cursor.execute(query, vars=None)
    • 参数: query 为 SQL 语句字符串,vars 为可选的参数元组或字典,用于安全地传递变量值(防止 SQL 注入)。
  4. 提交事务: connection.commit()
    • 用于提交当前事务,使更改永久生效。
  5. 回滚事务: connection.rollback()
    • 用于回滚当前事务,撤销未提交的更改。

代码示例

import psycopg2

# 1. 建立连接
conn = psycopg2.connect(
    host="localhost",
    port="5432",
    database="postgres",
    user="postgres",
    password="your_password"
)

try:
    # 2. 创建游标
    cur = conn.cursor()

    # 3. 执行SQL (使用参数化查询防止注入)
    cur.execute("SELECT version();")
    version = cur.fetchone()
    print(f"PostgreSQL 版本: {version[0]}")

    # 4. 执行数据操作
    cur.execute("""
        CREATE TABLE IF NOT EXISTS users (
            id SERIAL PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            email VARCHAR(255) UNIQUE NOT NULL
        );
    """)

    cur.execute(
        "INSERT INTO users (name, email) VALUES (%s, %s) RETURNING id;",
        ("John Doe", "john.doe@example.com")
    )
    new_id = cur.fetchone()[0]
    print(f"插入的用户ID: {new_id}")

    # 5. 提交事务
    conn.commit()

except Exception as e:
    # 6. 发生异常时回滚
    conn.rollback()
    print(f"发生错误: {e}")
finally:
    # 7. 关闭游标和连接
    if cur:
        cur.close()
    if conn:
        conn.close()

pg (Node.js)

pg 是 Node.js 的非阻塞 PostgreSQL 客户端库。

安装

npm install pg

核心API

  1. 创建客户端: new pg.Client(config)
    • 参数: config 对象可包含 host, port, database, user, password 等属性。
    • 返回值: 返回一个 Client 实例。
  2. 连接: client.connect()
    • 建立与数据库服务器的连接,返回 Promise。
  3. 查询: client.query(text, values)
    • 参数: text 为 SQL 字符串,values 为参数数组。
    • 返回值: 返回 Promise,解析为包含 rows (结果行数组)、rowCount (影响行数) 等属性的对象。
  4. 事务: 通过执行 BEGIN、COMMIT、ROLLBACK 命令手动管理。
  5. 结束: client.end()
    • 关闭客户端连接。

代码示例

const { Client } = require('pg');

async function main() {
    // 1. 创建客户端
    const client = new Client({
        host: 'localhost',
        port: 5432,
        database: 'postgres',
        user: 'postgres',
        password: 'your_password',
    });

    try {
        // 2. 连接
        await client.connect();
        console.log('数据库连接成功');

        // 3. 执行查询
        const res = await client.query('SELECT version();');
        console.log(`PostgreSQL 版本: ${res.rows[0].version}`);

        // 4. 创建表和插入数据 (使用参数化查询)
        await client.query(`
            CREATE TABLE IF NOT EXISTS users (
                id SERIAL PRIMARY KEY,
                name VARCHAR(100) NOT NULL,
                email VARCHAR(255) UNIQUE NOT NULL
            );
        `);

        const insertRes = await client.query(
            'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id;',
            ['Jane Doe', 'jane.doe@example.com']
        );
        console.log(`插入的用户ID: ${insertRes.rows[0].id}`);

    } catch (err) {
        console.error('发生错误:', err);
    } finally {
        // 5. 关闭连接
        await client.end();
    }
}

main();

Demo 简单示例

本示例基于 Node.js 和 pg 库,演示一个完整的用户数据管理流程,包含连接、建表、批量插入和事务处理。

运行说明

  1. 环境准备:
    • 确保已安装 Node.js (v14+) 和 npm。
    • 确保本地或远程有一个可用的 PostgreSQL 实例 (v12+)。
    • 创建一个测试数据库,例如 testdb。
  2. 项目初始化:
    mkdir pg-demo
    cd pg-demo
    npm init -y
    npm install pg
    
  3. 配置数据库连接:
    创建 .env 文件 (或直接修改代码中的连接配置):
    PG_HOST=localhost
    PG_PORT=5432
    PG_DATABASE=testdb
    PG_USER=postgres
    PG_PASSWORD=your_password
    
  4. 运行脚本:
    node index.js
    

代码说明 (index.js)

const { Client } = require('pg');

// 数据库连接配置
const config = {
    host: process.env.PG_HOST || 'localhost',
    port: parseInt(process.env.PG_PORT || '5432'),
    database: process.env.PG_DATABASE || 'testdb',
    user: process.env.PG_USER || 'postgres',
    password: process.env.PG_PASSWORD || 'your_password',
};

// 模拟的用户数据
const users = [
    { name: 'Alice', email: 'alice@example.com' },
    { name: 'Bob', email: 'bob@example.com' },
    { name: 'Charlie', email: 'charlie@example.com' },
];

async function runDemo() {
    const client = new Client(config);

    try {
        await client.connect();
        console.log('✅ 数据库连接成功');

        // --- 1. 建表 ---
        console.log('📦 创建 users 表...');
        await client.query(`
            DROP TABLE IF EXISTS users CASCADE;
            CREATE TABLE users (
                id SERIAL PRIMARY KEY,
                name VARCHAR(100) NOT NULL,
                email VARCHAR(255) UNIQUE NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            );
        `);
        console.log('✅ users 表创建成功');

        // --- 2. 批量插入 (事务演示) ---
        console.log('📝 开始批量插入用户数据 (事务)...');
        await client.query('BEGIN'); // 开启事务

        const insertPromises = users.map(user =>
            client.query(
                'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id;',
                [user.name, user.email]
            )
        );

        const results = await Promise.all(insertPromises);
        const insertedIds = results.map(r => r.rows[0].id);

        await client.query('COMMIT'); // 提交事务
        console.log(`✅ 批量插入成功,插入的ID: [${insertedIds.join(', ')}]`);

        // --- 3. 查询与验证 ---
        console.log('🔍 查询所有用户...');
        const queryRes = await client.query('SELECT id, name, email, created_at FROM users ORDER BY id;');
        console.table(queryRes.rows);

        // --- 4. 模拟错误与回滚 ---
        console.log('🧪 模拟错误场景 (插入重复邮箱,应触发回滚)...');
        try {
            await client.query('BEGIN');
            await client.query(
                'INSERT INTO users (name, email) VALUES ($1, $2);',
                ['Duplicate', 'alice@example.com'] // 故意重复
            );
            await client.query('COMMIT');
        } catch (err) {
            await client.query('ROLLBACK');
            console.log('⛔ 事务回滚成功,错误信息:', err.message);
        }

        // --- 5. 最终数据状态 ---
        console.log('📊 最终数据状态:');
        const finalRes = await client.query('SELECT COUNT(*) FROM users;');
        console.log(`当前 users 表共有 ${finalRes.rows[0].count} 条记录`);

    } catch (err) {
        console.error('❌ 发生严重错误:', err);
    } finally {
        await client.end();
        console.log('🔌 数据库连接已关闭');
    }
}

runDemo();

技术点总结

  • 连接管理: 使用 pg.Client 管理数据库连接生命周期。
  • 参数化查询: 使用 $1, $2 占位符防止 SQL 注入。
  • 事务控制: 通过 BEGIN, COMMIT, ROLLBACK 命令管理事务,确保数据一致性。
  • 批量操作: 使用 Promise.all 并行执行插入,提升效率(生产环境需注意连接数和资源控制)。
  • 错误处理: 使用 try...catch 捕获异常并执行回滚,保证数据完整性。

多语言数据库驱动实践指南 (Go/Python/Java)

在掌握了 PostgreSQL 的核心概念与学习资源后,将理论知识转化为实际编码能力是技术进阶的关键一步。本文基于前一章的 Node.js 示例,分别使用 Go、Python 和 Java 三种主流后端语言,实现相同的用户数据管理流程。通过对比不同语言驱动 API 的设计哲学和事务处理方式,开发者可以快速将 PostgreSQL 集成到各自的技术栈中。

Go: pgx

pgx 是 Go 语言中性能最高的 PostgreSQL 驱动,支持原生 context 取消、COPY 协议和 LISTEN/NOTIFY。推荐使用 pgx/v5 及其连接池 pgxpool。

安装

go get github.com/jackc/pgx/v5
go get github.com/jackc/pgx/v5/pgxpool

核心API

  1. 创建连接池: pgxpool.New(ctx, connString)
    • 参数: context.Context,连接字符串 (格式: postgres://user:pass@host:port/db?sslmode=disable)。
    • 返回值: 返回 *pgxpool.Pool,该池实现了 Begin, Query, Exec 等方法。
  2. 执行查询 (单行): pool.QueryRow(ctx, sql, args...)
    • 用于返回单行结果,调用 Scan() 将列值映射到变量。
  3. 执行查询 (多行): pool.Query(ctx, sql, args...)
    • 返回 pgx.Rows,需遍历并调用 Scan,最后关闭 rows。
  4. 执行命令 (无结果集): pool.Exec(ctx, sql, args...)
    • 返回 pgconn.CommandTag,包含 RowsAffected()。
  5. 事务: pool.Begin(ctx)
    • 返回 pgx.Tx,可调用 Commit() 或 Rollback()。

Python: psycopg2

psycopg2 是 Python 的事实标准驱动,支持 COPY、LISTEN/NOTIFY 和异步通知。

安装

pip install psycopg2-binary

核心API (详见前一章,这里简要重申)

  • psycopg2.connect(dsn) → connection
  • conn.cursor() → cursor
  • cur.execute(sql, vars) → 无返回值或 None
  • cur.fetchone() / cur.fetchall() → 获取结果
  • conn.commit() / conn.rollback() → 事务控制

Java: JDBC (官方驱动)

Java 标准 JDBC 驱动由 PostgreSQL 官方提供 (org.postgresql:postgresql)。推荐使用 DataSource 配合连接池(如 HikariCP)。

安装 (Maven)

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.7.3</version>
</dependency>

核心API

  1. 获取连接: DataSource.getConnection() 或 DriverManager.getConnection(url, user, password)
    • url 格式: jdbc:postgresql://host:port/database
  2. 创建预编译语句: Connection.prepareStatement(sql)
    • 返回 PreparedStatement,支持参数占位符 ?。
  3. 执行查询: PreparedStatement.executeQuery()
    • 返回 ResultSet,通过 rs.next() 和 rs.getXXX() 遍历。
  4. 执行更新: PreparedStatement.executeUpdate()
    • 返回 int 表示受影响行数。
  5. 事务: 通过 Connection.setAutoCommit(false) 开启,手动 commit() 或 rollback()。

Demo 完整示例

以下为三种语言独立完整的可运行示例,均实现:

  1. 连接本地 PostgreSQL 数据库。
  2. 创建 users 表(若存在则删除重建)。
  3. 批量插入三条用户记录(使用事务)。
  4. 查询并输出所有用户。
  5. 模拟插入重复邮箱触发错误并回滚。
  6. 最终输出表记录总数。

语言: Go (使用 pgx)

运行说明
  1. 创建项目目录 pg-go-demo,初始化 go module:
    mkdir pg-go-demo && cd pg-go-demo
    go mod init pg-go-demo
    go get github.com/jackc/pgx/v5/pgxpool
    
  2. 将以下代码保存为 main.go。
  3. 修改连接字符串中的密码和数据库名(默认 postgres://postgres:your_password@localhost:5432/testdb?sslmode=disable)。
  4. 运行 go run main.go。
完整代码 (main.go)
package main

import (
    "context"
    "fmt"
    "log"

    "github.com/jackc/pgx/v5"
    "github.com/jackc/pgx/v5/pgxpool"
)

type User struct {
    ID    int
    Name  string
    Email string
}

func main() {
    ctx := context.Background()
    connString := "postgres://postgres:your_password@localhost:5432/testdb?sslmode=disable"

    // 1. 创建连接池
    pool, err := pgxpool.New(ctx, connString)
    if err != nil {
        log.Fatalf("无法连接数据库: %v", err)
    }
    defer pool.Close()

    fmt.Println("✅ 数据库连接成功")

    // 2. 建表 (删除并重建)
    _, err = pool.Exec(ctx, `DROP TABLE IF EXISTS users CASCADE`)
    if err != nil {
        log.Fatalf("删除表失败: %v", err)
    }
    _, err = pool.Exec(ctx, `
        CREATE TABLE users (
            id SERIAL PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            email VARCHAR(255) UNIQUE NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    `)
    if err != nil {
        log.Fatalf("创建表失败: %v", err)
    }
    fmt.Println("📦 users 表创建成功")

    // 3. 批量插入 (使用事务)
    users := []User{
        {Name: "Alice", Email: "alice@example.com"},
        {Name: "Bob", Email: "bob@example.com"},
        {Name: "Charlie", Email: "charlie@example.com"},
    }

    // 开启事务
    tx, err := pool.Begin(ctx)
    if err != nil {
        log.Fatalf("开启事务失败: %v", err)
    }
    // 确保事务最终结束
    defer tx.Rollback(ctx)

    var insertedIDs []int
    for _, u := range users {
        var id int
        err := tx.QueryRow(ctx,
            "INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id",
            u.Name, u.Email,
        ).Scan(&id)
        if err != nil {
            log.Fatalf("插入失败: %v", err)
        }
        insertedIDs = append(insertedIDs, id)
    }
    // 提交事务
    err = tx.Commit(ctx)
    if err != nil {
        log.Fatalf("提交事务失败: %v", err)
    }
    fmt.Printf("✅ 批量插入成功,插入的ID: %v\n", insertedIDs)

    // 4. 查询所有用户
    rows, err := pool.Query(ctx, "SELECT id, name, email, created_at FROM users ORDER BY id")
    if err != nil {
        log.Fatalf("查询失败: %v", err)
    }
    defer rows.Close()

    fmt.Println("🔍 查询所有用户:")
    for rows.Next() {
        var u User
        var createdAt string
        err := rows.Scan(&u.ID, &u.Name, &u.Email, &createdAt)
        if err != nil {
            log.Fatalf("扫描行失败: %v", err)
        }
        fmt.Printf("  ID: %d, Name: %s, Email: %s, CreatedAt: %s\n", u.ID, u.Name, u.Email, createdAt)
    }
    if rows.Err() != nil {
        log.Fatalf("遍历行出错: %v", rows.Err())
    }

    // 5. 模拟错误与回滚
    fmt.Println("🧪 模拟错误场景 (插入重复邮箱,应触发回滚)...")
    tx2, err := pool.Begin(ctx)
    if err != nil {
        log.Fatalf("开启事务失败: %v", err)
    }
    _, err = tx2.Exec(ctx, "INSERT INTO users (name, email) VALUES ($1, $2)", "Duplicate", "alice@example.com")
    if err != nil {
        // 回滚
        rbErr := tx2.Rollback(ctx)
        if rbErr != nil {
            log.Printf("回滚失败: %v", rbErr)
        }
        fmt.Printf("⛔ 事务回滚成功,错误信息: %v\n", err)
    } else {
        // 正常情况下不会执行到,但为了安全
        _ = tx2.Commit(ctx)
    }

    // 6. 最终统计
    var count int
    err = pool.QueryRow(ctx, "SELECT COUNT(*) FROM users").Scan(&count)
    if err != nil {
        log.Fatalf("统计失败: %v", err)
    }
    fmt.Printf("📊 当前 users 表共有 %d 条记录\n", count)

    fmt.Println("🔌 数据库连接已关闭")
}
技术点总结
  • 连接池: 使用 pgxpool 管理连接,提升并发性能。
  • 上下文: 所有数据库操作都接收 context.Context,支持超时和取消。
  • 事务: 使用 Begin 获取 Tx,通过 defer tx.Rollback(ctx) 保证错误时回滚,并在成功时 Commit。
  • 参数占位符: 使用 $1, $2 风格,与 PostgreSQL 原生一致。
  • 错误处理: 显式检查每个返回的错误,保证健壮性。

语言: Python (使用 psycopg2)

运行说明
  1. 安装依赖: pip install psycopg2-binary。
  2. 将代码保存为 main.py,修改连接参数(host, port, database, user, password)。
  3. 运行 python main.py。
完整代码 (main.py)
import psycopg2
from psycopg2 import sql, errors

# 连接配置
config = {
    'host': 'localhost',
    'port': 5432,
    'database': 'testdb',
    'user': 'postgres',
    'password': 'your_password'
}

def main():
    conn = None
    cur = None
    try:
        # 1. 建立连接
        conn = psycopg2.connect(**config)
        conn.autocommit = False  # 手动事务控制
        cur = conn.cursor()
        print("✅ 数据库连接成功")

        # 2. 建表
        cur.execute("DROP TABLE IF EXISTS users CASCADE")
        cur.execute("""
            CREATE TABLE users (
                id SERIAL PRIMARY KEY,
                name VARCHAR(100) NOT NULL,
                email VARCHAR(255) UNIQUE NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)
        conn.commit()
        print("📦 users 表创建成功")

        # 3. 批量插入 (事务)
        users = [
            ('Alice', 'alice@example.com'),
            ('Bob', 'bob@example.com'),
            ('Charlie', 'charlie@example.com'),
        ]
        inserted_ids = []
        cur.execute("BEGIN")  # 显式开启事务
        for name, email in users:
            cur.execute(
                "INSERT INTO users (name, email) VALUES (%s, %s) RETURNING id",
                (name, email)
            )
            inserted_ids.append(cur.fetchone()[0])
        conn.commit()
        print(f"✅ 批量插入成功,插入的ID: {inserted_ids}")

        # 4. 查询所有用户
        cur.execute("SELECT id, name, email, created_at FROM users ORDER BY id")
        rows = cur.fetchall()
        print("🔍 查询所有用户:")
        for row in rows:
            print(f"  ID: {row[0]}, Name: {row[1]}, Email: {row[2]}, CreatedAt: {row[3]}")

        # 5. 模拟错误与回滚
        print("🧪 模拟错误场景 (插入重复邮箱,应触发回滚)...")
        try:
            cur.execute("BEGIN")
            cur.execute("INSERT INTO users (name, email) VALUES (%s, %s)", ("Duplicate", "alice@example.com"))
            conn.commit()
        except errors.UniqueViolation as e:
            conn.rollback()
            print(f"⛔ 事务回滚成功,错误信息: {e.pgerror}")

        # 6. 最终统计
        cur.execute("SELECT COUNT(*) FROM users")
        count = cur.fetchone()[0]
        print(f"📊 当前 users 表共有 {count} 条记录")

    except Exception as e:
        print(f"❌ 发生严重错误: {e}")
        if conn:
            conn.rollback()
    finally:
        if cur:
            cur.close()
        if conn:
            conn.close()
            print("🔌 数据库连接已关闭")

if __name__ == "__main__":
    main()
技术点总结
  • 游标管理: 使用 cursor 对象执行 SQL,支持 %s 占位符(注意不要使用字符串格式化拼接)。
  • 自动提交关闭: 设置 autocommit=False 以手动管理事务。
  • 异常捕获: 捕获 psycopg2.errors.UniqueViolation 处理唯一约束冲突。
  • 事务控制: 通过 BEGIN 和 COMMIT/ROLLBACK 显式控制。
  • 资源释放: finally 中关闭游标和连接。

语言: Java (使用 JDBC)

运行说明
  1. 使用 Maven 或 Gradle 添加依赖(见上文)。
  2. 将以下代码保存为 Main.java(注意包名和类名)。
  3. 修改 url, user, password 常量。
  4. 编译并运行:
    javac Main.java
    java Main
    ```(或者直接在 IDE 中运行)
    
    
完整代码 (Main.java)
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class Main {
    private static final String URL = "jdbc:postgresql://localhost:5432/testdb";
    private static final String USER = "postgres";
    private static final String PASSWORD = "your_password";

    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            // 1. 加载驱动 (JDBC 4+ 自动加载)
            Class.forName("org.postgresql.Driver");
            // 2. 建立连接
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
            conn.setAutoCommit(false); // 手动事务
            System.out.println("✅ 数据库连接成功");

            // 3. 建表
            stmt = conn.createStatement();
            stmt.execute("DROP TABLE IF EXISTS users CASCADE");
            stmt.execute("""
                CREATE TABLE users (
                    id SERIAL PRIMARY KEY,
                    name VARCHAR(100) NOT NULL,
                    email VARCHAR(255) UNIQUE NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            """);
            conn.commit();
            System.out.println("📦 users 表创建成功");

            // 4. 批量插入 (事务)
            String[][] users = {
                {"Alice", "alice@example.com"},
                {"Bob", "bob@example.com"},
                {"Charlie", "charlie@example.com"}
            };
            List<Integer> insertedIds = new ArrayList<>();
            // 预编译插入语句
            String insertSql = "INSERT INTO users (name, email) VALUES (?, ?) RETURNING id";
            pstmt = conn.prepareStatement(insertSql);
            for (String[] u : users) {
                pstmt.setString(1, u[0]);
                pstmt.setString(2, u[1]);
                rs = pstmt.executeQuery();
                if (rs.next()) {
                    insertedIds.add(rs.getInt(1));
                }
                rs.close(); // 每次关闭结果集
            }
            conn.commit();
            System.out.println("✅ 批量插入成功,插入的ID: " + insertedIds);

            // 5. 查询所有用户
            String querySql = "SELECT id, name, email, created_at FROM users ORDER BY id";
            pstmt = conn.prepareStatement(querySql);
            rs = pstmt.executeQuery();
            System.out.println("🔍 查询所有用户:");
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String email = rs.getString("email");
                Timestamp createdAt = rs.getTimestamp("created_at");
                System.out.printf("  ID: %d, Name: %s, Email: %s, CreatedAt: %s%n",
                        id, name, email, createdAt);
            }
            rs.close();

            // 6. 模拟错误与回滚
            System.out.println("🧪 模拟错误场景 (插入重复邮箱,应触发回滚)...");
            try {
                pstmt = conn.prepareStatement("INSERT INTO users (name, email) VALUES (?, ?)");
                pstmt.setString(1, "Duplicate");
                pstmt.setString(2, "alice@example.com");
                pstmt.executeUpdate();
                conn.commit();
            } catch (SQLException e) {
                conn.rollback();
                System.out.println("⛔ 事务回滚成功,错误信息: " + e.getMessage());
            }

            // 7. 最终统计
            pstmt = conn.prepareStatement("SELECT COUNT(*) FROM users");
            rs = pstmt.executeQuery();
            if (rs.next()) {
                int count = rs.getInt(1);
                System.out.println("📊 当前 users 表共有 " + count + " 条记录");
            }

        } catch (ClassNotFoundException e) {
            System.err.println("❌ 未找到 JDBC 驱动: " + e.getMessage());
        } catch (SQLException e) {
            System.err.println("❌ 数据库错误: " + e.getMessage());
            if (conn != null) {
                try { conn.rollback(); } catch (SQLException ex) { ex.printStackTrace(); }
            }
        } finally {
            // 关闭资源 (逆序)
            try { if (rs != null) rs.close(); } catch (SQLException e) { e.printStackTrace(); }
            try { if (pstmt != null) pstmt.close(); } catch (SQLException e) { e.printStackTrace(); }
            try { if (stmt != null) stmt.close(); } catch (SQLException e) { e.printStackTrace(); }
            try { if (conn != null) conn.close(); System.out.println("🔌 数据库连接已关闭"); } catch (SQLException e) { e.printStackTrace(); }
        }
    }
}
技术点总结
  • JDBC 标准: 使用 DriverManager 获取连接,PreparedStatement 防止注入。
  • 显式事务: setAutoCommit(false) 后手动 commit/rollback。
  • 资源管理: 使用 try-catch-finally 确保 ResultSet、Statement 和 Connection 被关闭。
  • 异常处理: 捕获 SQLException,可根据 SQLState 或错误码细分处理。
  • 返回ID: 使用 RETURNING id 子句,通过 executeQuery 获取自增主键。

官方文档

参考链接

阶段性总结

本文分别使用 Go (pgx)、Python (psycopg2) 和 Java (JDBC) 三种语言实现了 PostgreSQL 数据库的完整 CRUD 与事务管理流程。各语言驱动虽然 API 风格各异,但核心逻辑高度一致:通过连接字符串建立连接,使用参数化查询执行 SQL,通过 BEGIN/COMMIT/ROLLBACK 控制事务,并妥善处理资源释放和异常。

开发者可以根据项目技术选型选择合适的驱动,并参考本文的示例快速构建数据库访问层。在实际生产环境中,建议使用连接池(如 pgxpool、HikariCP)和结构化日志来增强应用的可靠性和可观测性。

官方文档

参考链接

总结

PostgreSQL 的成功不仅源于其技术先进性,更得益于其背后庞大且活跃的开源社区。在中国,以“中国开源软件推进联盟 PostgreSQL 分会”和“PostgreSQL 中文社区”为代表的两大组织,构建了从线上问答(PGFans)、线下沙龙、年度技术大会到官方认证(PGCA/PGCE/PGCM)和荣誉体系(ACE/MVP)的完整生态。

对于学习者而言,官方文档是基石,而《PostgreSQL 指南:内幕探索》等经典书籍则是深入理解内核原理的阶梯。此外,CommitFest、邮件列表和数据库内核月报等开发者资源,为跟踪技术前沿和参与社区贡献提供了窗口。

本文梳理的 API 与 Demo 示例,旨在为开发者提供一个快速上手的实践起点。通过“资源 + 社区 + 成长路径”的结合,开发者可以高效地完成从入门到专家的进阶。

Flarum 1.8.17 与 Discourse 3.3.0 深度对比:5项核心指标与社区生态分析 本文深度对比了Flarum 1.8.17与Discourse 3.3.0两大开源社区论坛软件,从性能表现、功能完备性、插件生态、部署复杂度、社区活跃度五个核心指标进行分析。Flarum基于PHP架构,资源占用低且部署简单,适合预算有限的项目;Discourse功能全面但资源需求较高,适合大型社区。帮助开发者根据项目需求做出明智的技术选型。 阅读详情

相关推荐

德哥的Postgresql数据库优化的培训视频(网盘链接)

德哥的Postgresql数据库优化的培训视频,从Postgtesql的安装,配置,到数据库配置文件postgresql.conf的讲解,执行计划的查看,非常详细。

荐书:《PostgreSQL指南:内幕探索》| 留言送书

墨墨导读:最近电子工业出版社博文视点出版了《PostgreSQL指南:内幕探索》,该书为《The Internals of PostgreSQL for databas...

Enmotech的博客 1461

PostgreSQL培训

别的网友整理的,我发布到csdn上来。初级的资料,高手绕开。

《postgresql指南--内幕探索》第三章查询处理

《postgresql指南--内幕探索》第三章查询处理

渔夫数据库笔记 949

Internals Of PostgreSQL Wal.pdf

postgresql大神 代码级讲述WAll文件的构成、产生、使用 是学习postgresql不可缺少的环节 WAL就是预先写入日志,用于数据保全的 跟Oracle的REDO文件是一种意思

PostgreSQL技术内幕:事务处理深度探索.docx

PostgreSQL技术内幕:事务处理深度探索.docx

《PostgreSQL 指南:内幕探索》之基础备份与时间点恢复(下)

墨墨导读:最近电子工业出版社博文视点出版了《PostgreSQL指南:内幕探索》,日前「数据和云」公众号推荐了这本书并赠送了五本,百多位用户参与,几十条留言未能放出,为了...

Enmotech的博客 803

智能体在车联网中的应用:第9天 核心工具链与仿真世界:SUMO交通仿真入门——从安装到构建你的第一个虚拟十字路口

摘要 SUMO是一款开源微观交通仿真工具,为车联网和自动驾驶算法开发提供高效安全的测试环境。文章详细介绍了SUMO的核心概念、安装方法及实战应用。在Ubuntu或Windows系统安装SUMO后,可通过netedit工具构建十字路口路网,包括创建节点、连接边、设置交通信号灯等步骤。随后通过编写.rou.xml文件定义车辆类型、路线和车流,实现动态交通场景模拟。SUMO支持与外部程序交互,能复现真实场景、进行极端测试和算法验证,是车联网研究的理想数字沙盘。

IT深耕十余载,大道之简 1455

数据库集簇、数据库和数据表

文章目录数据库集簇的逻辑结构数据库集簇的物理结构数据库集簇的布局数据库布局表和索引相关文件的布局表空间的布局堆表文件的内部布局读写元组的方式写入堆元组读取堆元组 数据库集簇的逻辑结构 数据库集簇(database cluster)是一组数据库(database)的集合,由一个PostgreSQL服务器管理。数据库集簇与高可用数据库集群不同,并非意味着“一组数据库服务器”,一个PostgreSQL服务器只会在单机上运行并管理单个数据库集簇。 数据库集簇的物理结构 数据库集簇本质上就是一个文件目录,即基础目录

LoongTu 3287

【Linux系统】Kdenlive软件多机位同音频视频同步化处理

Kdenlive简介 官网链接:https://kdenlive.org/zh/ 命令行安装: apt-get install kdenlive Kdenlive能够进行多轨道视频编辑,可以使用和调整多条音频、视频轨道。每条轨道可以按需进行单独锁定、静音。 Kdenlive多机位同音频视频同步化处理步骤 1. 新建Kdenlive文件 选择<文件>-<新建>,建立新的kdenlive文件,初始设置为<视频轨:2>,<音频轨:2>。 视.

qq_31550269的博客 1887

【论文翻译】Enhancing Channel Estimation in High Mobility OTFS Systems: A Novel Pilot-Based Method...

本文介绍了一种用于正交时频空间(OTFS)调制系统的新型基于导频的信道估计方法。我们提出的方法是在ZP-OTFS系统的零填充区域插入一行导频符号,具有减少开销和降低峰均功率比(PAPR)的显著优势。特别是,通过沿多普勒轴分布导频符号,我们的方法在高移动性场景下依然能够提供鲁棒且精确的信道估计,这是像TransPod这种时速超过1000 km/h的运输系统的一个典型特征。这一点至关重要,因为在此类场景下,多普勒频移可能会导致系统性能的严重退化。

Cuby的博客 1215

PG实战笔记 | 05 PostgreSQL社区介绍与资源获取

本文介绍了PostgreSQL开源社区的核心构成与资源获取路径。重点解析了无实体的PGDG贡献者共同体、核心邮件列表(如pgsql-hackers)的使用规范,以及PGConf.dev等全球会议参与机会。同时推荐官方文档、Wiki、Planet PostgreSQL等学习资源,并详述通过PGXN管理扩展的方法。最后提供从订阅邮件列表、参与commitfest审查到高效提问的实践指南,帮助开发者逐步融入社区,实现从使用者到贡献者的转变。

源码获取 | 运行部署 | 定制二开修改,如果回复不及时请耐心等待,请见谅。 482

【GameFramework框架内置模块】17、声音(Sound)

声音(Sound)模块是管理音频的模块,比如播放、停止、修改音量等,支持淡入淡出、AudioMixer等高级设置。声音(Sound)的配置面板如下所示:所有的声音统一按照组来分,可以根据需要添加或者溢出组,声音组可以配置Agent的数量和音量等。

优快云博客专家,Unity3D高级软件工程师,《Unity 3D从入门到实战》作者。 1917

PostgreSQL 学习笔记整理 20250624

建议根据当前需求选择主题深入阅读,并搭配实践验证(如搭建流复制集群、使用pg_waldump解析日志)。:所有实验在 Docker 容器中完成,核心命令保存为 SQL 脚本。以下是根据您提供的 PostgreSQL 知识体系整理的。以下基于您提供的 PostgreSQL 知识体系,整合成。

喝醉酒的小白 1389

华为OD统一考试 - 模拟消息队列(Java & Python& JS & C++ & C )

让我们来模拟一个消息队列的运作,有一个发布者和若干消费者,发布者会在给定的时刻向消息队列发送消息,

算法大师! 3519

静态代码审计插件 snyk 使用教程

静态代码审计插件 snyk 使用教程

SHELLCODE_8BIT的博客 2006

PostgreSQL插件生态全景解析:赋能数据库的无限可能

PostgreSQL插件生态全景解析:赋能数据库的无限可能

huanhuanzhou blog 1457

【法如faro】三维激光软件Scene2023数据处理(自动配准并转换坐标)流程

法如faro Scene2023数据处理(自动配准并转换坐标)的主要流程为:新建项目、导入数据、处理、自动注册、坐标系转换、模型导出立和面模型导出等。

智慧空间实验室 2387

django-multitenant社区与生态:插件、工具与资源大全

构建可扩展的SaaS应用从未如此简单!django-multitenant作为Python/Django生态中领先的多租户数据库解决方案,拥有丰富完整的社区生态和工具链。无论您是刚开始接触多租户架构,还是正在寻找优化现有SaaS应用的方案,这篇文章将为您提供完整的django-multitenant插件、工具与资源大全,帮助您快速构建高性能的分布式多租户应用。✨ ## 🚀 django-mul

gitblog_00385的博客 536

lwip-2.0.3

lwip是瑞典计算机科学院(SICS)的Adam Dunkels 开发的一个小型开源的TCP/IP协议栈。实现的重点是在保持TCP协议主要功能的基础上减少对RAM 的占用。

精雕软件(JDpaint) 5.5 绿色版

精雕软件(JDpaint) 5.5 绿色版精雕软件(JDpaint) 5.5 绿色版精雕软件(JDpaint) 5.5 绿色版

PostgreSQL生态数据库选型与学习指南

关系型数据库作为企业级应用的核心组件,其技术选型直接影响系统性能和可维护性。PostgreSQL生态凭借其开源特性和强大扩展性,衍生出多个针对不同场景优化的分支产品。从技术原理看,这些数据库在存储引擎、连接管理和查询优化等方面各有创新,如openGauss的NUMA架构优化和行列混合存储,Vastbase的金融级安全特性等。在实际工程应用中,开发者需要根据OLTP业务复杂度、国产化适配需求或金融安全规范等具体场景进行技术选型。通过系统化学习SQL基础、性能调优和差异化特性,可以快速掌握PostgreSQL生

weixin_32715349的博客 274

文档:亲测可用解决vnc灰屏,黑屏,鼠标大黑叉情况

主要解决vnc-viewer灰屏,黑屏,鼠标大黑叉等现象,详细见文档。

2023中国PostgreSQL数据库生态大会-核心PPT资料下载

大会以“极速进化·融合新生”为主题,探讨了PostgreSQL数据库生态的发展趋势和未来方向。在大会主论坛上,专家们就PostgreSQL数据库的技术创新、应用实践和生态发展进行了深入交流。同时,大会还设置了技术创新&云原生论坛、运维技能实践论坛、行业应用探索论坛和内核与特性解读论坛,从不同角度探讨数据库技术的热点问题和发展趋势。此次大会是一次集结了众多专家学者、企业代表和生态伙伴的盛会,旨在推动中国PostgreSQL数据库生态的繁荣和发展。

百家峰会官方 1241

LinePhone3.2.7(android端最新版本) sip客户端

SIP客户端For android:LinePhone3.2.7(最新版本),官网地址:http://www.linphone.org/

Python asyncio 生态全景导航:awesome-asyncio 精选框架、库与学习资源梳理

> 本文以 [awesome-asyncio](https://link.gitcode.com/i/69f3e8601435486b02f2087058a4321d) 这份精选清单(curated list)为主体,系统梳理 Python asyncio 生态中经社区挑选的 Web 框架、消息队列、数据库驱动、网络库、GraphQL、测试工具、事件循环替代方案以及写作与演讲资源,帮助读者按应用场

gitblog_00060的博客 171

数据库开源生态与社区实践

MySQL社区贡献机制:Oracle主导但接受外部贡献的CLA签署流程:核心开发者评审流程与代码质量要求:典型贡献路径示例:年度MySQL贡献者峰会的协同机制:PostgreSQL社区治理模式:基于邮件列表的分布式决策体系:年度commitfest活动运作细节:核心团队选举制度:企业赞助体系实施细则:技术架构:扩展API深度集成: 空间索引优化: 地理库集成组件:开发规范要求:版本兼容性策略:测试体系: 文档标准: 社区协作机制:OSGeo技术协同:商业化路径:年度报

Made In SQL 940

BankAPI社区与支持:开源金融消息系统的生态系统建设与发展

BankAPI是一个专注于金融机构间安全分布式消息传递的开源项目,为金融行业提供了可靠、安全的通信解决方案。本文将深入探讨BankAPI社区的建设、支持资源以及生态系统的发展,帮助新手和普通用户更好地了解和使用这一强大的金融消息系统。 ## 1. BankAPI项目概述:金融通信的安全保障 BankAPI项目的核心功能是实现金融机构之间的安全消息传递,其设计理念基于"Secure Distri

gitblog_00427的博客 742

Clojure.java.jdbc社区生态:相关工具与扩展库完整指南

Clojure.java.jdbc作为Clojure生态系统中经典的JDBC数据库访问库,已经发展出一个丰富而成熟的工具生态系统。这个完整的指南将为您详细介绍围绕Clojure.java.jdbc构建的各种相关工具和扩展库,帮助您更好地利用这个强大的数据库访问解决方案。🎯 ## Clojure.java.jdbc简介与核心功能 Clojure.java.jdbc是一个低级的Clojure包装

gitblog_00930的博客 378
上一篇: PostgreSQL笔记4: 市场地位、生态全景与行业应用实践
下一篇: PostgreSQL笔记6:三种主流安装方式详解(RPM / YUM / 源码编译)
Wang's Blog
博客等级 码龄12年 7440粉丝 2427原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Wang's Blog

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值