PostgreSQL笔记57: 常规巡检方法与开源工具实践指南

PostgreSQL DBA必会:五大高频诊断SQL全解析 在数据库运维中,系统视图是DBA的“仪表盘”,而SQL则是读取仪表盘的钥匙。PostgreSQL通过pg_stat_activity、pg_stat_user_tables、pg_locks等核心视图,将连接状态、空间膨胀、锁等待、复制延迟等关键指标暴露给管理者,其背后依赖MVCC机制和锁模型,理解原理才能快速定位故障。这些高频诊断SQL能帮助DBA高效完成日常巡检、性能调优和应急处理,覆盖连接耗尽、表膨胀、死锁、慢查询等常见生产问题。本文结合实际踩坑经验,整理了连接与会话管理、空间与膨胀诊断、锁与阻塞监控 阅读详情

纲要

  • PostgreSQL 常规巡检
    • 核心巡检项
      • 事务ID回卷 (Transaction ID Wraparound) 与冻结 (Freezing)
      • 表膨胀 (Table Bloat) 与死元组 (Dead Tuples)
      • 子事务 (Subtransaction) 的危害
      • 锁管理 (Lock Management) 与死锁 (Deadlock)
      • WAL日志堆积 (WAL Accumulation)
      • 复制槽 (Replication Slot) 与归档 (Archive)
      • 索引使用情况 (Index Usage)
    • 开源巡检工具
      • PG Collector (pg_collector)
      • pg_gather
      • Postgres Checkup (postgres-checkup)
      • PostgreSQL HowTo
  • API 速览
  • Demo 简单示例
  • 项目难点与解决方案
  • 官方文档
  • 参考链接
  • 总结

事务ID回卷与冻结机制

PostgreSQL 使用32位事务ID (XID) 来标识事务。在多版本并发控制 (MVCC) 机制下,元组头部存储了 xmin 和 xmax 以判断可见性。32位事务ID空间约为40亿,在高并发场景下可能耗尽。为防止事务ID回卷 (Transaction ID Wraparound),PostgreSQL 引入了冻结 (Freezing) 机制。

冻结操作将不再变更的旧元组标记为冻结状态,使其在所有快照中均可见,从而允许回收其事务ID。VACUUM 命令负责执行冻结操作。autovacuum_freeze_max_age 参数控制触发冻结的阈值,默认为2亿事务。当数据库年龄 (age(datfrozenxid)) 超过阈值时,将触发反回卷清理 (Anti-wraparound VACUUM)。

若事务ID回卷发生,数据库将拒绝所有连接并报错:

ERROR: database is not accepting commands to avoid wraparound data loss in database "mydb"
HINT: Stop the postmaster and vacuum that database in single-user mode

以下查询可监控数据库年龄:

-- 查询所有数据库的年龄
SELECT datname, age(datfrozenxid) AS xid_age 
FROM pg_database;

-- 查询指定表的年龄
SELECT relname, age(relfrozenxid) AS xid_age, pg_size_pretty(pg_table_size(oid)) AS table_size 
FROM pg_class 
WHERE relname = 'your_table_name';

导致冻结失效的常见因素包括:长事务 (Long-running Transaction)、复制槽 (Replication Slot) 阻塞 VACUUM、两阶段提交 (Two-phase Commit) 遗留的预备事务 (Prepared Transaction),以及子事务 (Subtransaction) 的过度使用。这些因素均可能使 datfrozenxid 无法推进,从而引发事务ID回卷风险。

表膨胀与死元组

在 PostgreSQL 中,UPDATE 和 DELETE 操作不会立即回收物理空间,而是将旧版本标记为死元组 (Dead Tuple)。autovacuum 后台进程负责清理死元组并回收空间。若 autovacuum 未能及时清理,表将持续膨胀 (Table Bloat),导致查询效率下降和存储空间浪费。

-- 查看表的死元组数量
SELECT 
    schemaname,
    tablename,
    n_dead_tup,
    n_live_tup,
    round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio,
    last_autovacuum,
    autovacuum_count
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

膨胀率可通过 pgstattuple 扩展精确测量:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_table_size(schemaname||'.'||tablename)) AS table_size,
    round(100.0 - (100.0 * pgstattuple(schemaname||'.'||tablename)).tuple_len / 
          pgstattuple(schemaname||'.'||tablename).table_len, 2) AS bloat_pct
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY bloat_pct DESC NULLS LAST
LIMIT 20;

子事务的代价

子事务 (Subtransaction) 允许在事务内部设置保存点 (SAVEPOINT),实现部分回滚。然而,子事务会加速事务ID消耗,因为每个子事务均需分配独立的事务ID。同时,子事务与 SELECT FOR UPDATE 结合使用时可能产生大量 subtransaction 锁,在备库上尤其显著,可能导致查询性能急剧下降。

锁管理与死锁

PostgreSQL 的锁资源有限。表锁超出限制时将触发 out of shared memory 错误。死锁 (Deadlock) 检测机制虽然存在,但频繁的死锁检测本身也消耗系统资源。

-- 查看当前锁等待情况
SELECT 
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid
JOIN pg_locks blocking_locks ON blocked_locks.locktype = blocking_locks.locktype
    AND blocked_locks.relation = blocking_locks.relation
    AND blocked_locks.pid != blocking_locks.pid
JOIN pg_stat_activity blocking ON blocking_locks.pid = blocking.pid
WHERE NOT blocked_locks.granted;

WAL日志堆积

预写日志 (WAL) 在检查点 (Checkpoint) 时回收。若复制槽 (Replication Slot) 对应的备库离线或逻辑复制消费者停止消费,WAL日志将无法被回收而持续堆积。归档 (Archive) 失败同样导致WAL堆积。

可通过以下参数控制WAL保留量:

  • max_slot_wal_keep_size:复制槽最大保留WAL大小
  • wal_keep_size:保留WAL段的最小数量
-- 查看复制槽状态及WAL保留量
SELECT 
    slot_name,
    slot_type,
    database,
    active,
    pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS retained_wal_bytes
FROM pg_replication_slots;

开源巡检工具

PG Collector

PG Collector 是AWS实验室开源的纯SQL巡检脚本,采用MIT许可证。它通过 psql 执行SQL脚本收集数据库信息并生成HTML报告。

主要特性:

  • 数据库大小、配置参数、已安装扩展
  • VACUUM 与统计信息
  • 未使用索引与无效索引
  • 表膨胀 (Bloat)
  • 事务ID (TXID) 与多事务ID (MXID) 回卷检查
  • 复制槽 (Replication Slot)
  • TOAST 表映射

运行方式:

# 下载与PostgreSQL主版本匹配的脚本
# https://github.com/awslabs/pg-collector/releases

psql -h localhost -p 5432 -d testdb -U username -f pg_collector.sql

报告生成于 /tmp 目录,命名格式为 pg_collector_[DB Name]-[timestamp].html。

版本策略:从PostgreSQL 13开始,每个主版本提供专用脚本。

pg_gather

pg_gather 是另一款纯SQL巡检脚本。与 PG Collector 相比,巡检项较少但包含"重要发现"(Findings)部分,突出展示需关注的问题。

运行方式:

git clone https://github.com/jobinau/pg_gather.git
cd pg_gather
psql -d target_db -U username -f gather.sql > out.tsv

Postgres Checkup

Postgres Checkup 由PostgresAI团队开发,是深度分析型巡检工具。它生成28份报告,分为7个组别:

  • A. 通用/基础设施:系统信息、版本信息、PostgreSQL设置、集群信息
  • D. 监控/故障排查:Linux工具、pg_stat_statements与pg_stat_kcache设置
  • F. Autovacuum与膨胀:当前设置、事务ID回卷检查、死元组、表和索引膨胀估算
  • G. 性能/连接/内存:内存设置、连接与当前活动、超时与锁
  • H. 索引分析:无效索引、未使用索引、冗余索引
  • K. SQL查询分析:全局查询指标、Top-50查询
  • L. Schema分析:表大小、主键容量风险

每个报告包含三个部分:

  1. Observations:观察发现
  2. Conclusions:结论分析
  3. Recommendations:优化建议

运行 postgres-checkup 需安装 psql、coreutils、jq、go 等依赖。支持通过直接PostgreSQL连接或SSH远程采集数据。

PostgreSQL HowTo

PostgreSQL HowTo 是PostgresAI维护的最佳实践集合,收录大量短小精悍的实战经验。可与 postgres-checkup 结合使用,根据报告建议查阅对应的最佳实践文档。

API速览

系统目录表与视图

视图/表用途适用版本
pg_database数据库级冻结事务ID (datfrozenxid)所有版本
pg_class表级冻结事务ID (relfrozenxid)所有版本
pg_stat_user_tables死元组统计 (n_dead_tup)、autovacuum 状态PostgreSQL 8.3+
pg_replication_slots复制槽状态与WAL保留量PostgreSQL 9.4+
pg_locks锁等待与持有信息所有版本
pg_stat_activity当前会话与查询状态所有版本

核心函数

函数说明示例
age(datfrozenxid)计算数据库年龄SELECT age(datfrozenxid) FROM pg_database;
age(relfrozenxid)计算表年龄SELECT age(relfrozenxid) FROM pg_class;
pg_size_pretty(pg_table_size())获取人类可读的表大小SELECT pg_size_pretty(pg_table_size('table_name'));
pgstattuple()获取表膨胀详情SELECT * FROM pgstattuple('table_name');
pg_wal_lsn_diff()计算WAL LSN差值SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn);

Demo简单示例

以下Node.js脚本演示了巡检核心指标的采集。

# 初始化项目
mkdir pg-checkup-demo && cd pg-checkup-demo
npm init -y
npm install pg
// checkup.js
const { Client } = require('pg');

const client = new Client({
    host: 'localhost',
    port: 5432,
    database: 'postgres',
    user: 'postgres',
    password: 'your_password',
});

async function runCheckup() {
    await client.connect();
    console.log('=== PostgreSQL 巡检报告 ===\n');

    // 1. 数据库年龄检查
    const dbAgeRes = await client.query(`
        SELECT datname, age(datfrozenxid) AS xid_age 
        FROM pg_database 
        ORDER BY xid_age DESC
    `);
    console.log('【数据库年龄】');
    console.table(dbAgeRes.rows);

    // 2. 死元组检查
    const deadTupRes = await client.query(`
        SELECT 
            schemaname, 
            tablename, 
            n_dead_tup,
            n_live_tup,
            round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio
        FROM pg_stat_user_tables
        WHERE n_dead_tup > 1000
        ORDER BY n_dead_tup DESC
        LIMIT 10
    `);
    console.log('【死元组 TOP 10】');
    console.table(deadTupRes.rows);

    // 3. 复制槽检查
    const slotRes = await client.query(`
        SELECT 
            slot_name,
            slot_type,
            database,
            active,
            pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS retained_bytes
        FROM pg_replication_slots
    `);
    console.log('【复制槽状态】');
    console.table(slotRes.rows);

    // 4. 锁等待检查
    const lockRes = await client.query(`
        SELECT 
            blocked.pid AS blocked_pid,
            blocked.query AS blocked_query,
            blocking.pid AS blocking_pid
        FROM pg_stat_activity blocked
        JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid
        JOIN pg_locks blocking_locks ON blocked_locks.locktype = blocking_locks.locktype
            AND blocked_locks.relation = blocking_locks.relation
            AND blocked_locks.pid != blocking_locks.pid
        JOIN pg_stat_activity blocking ON blocking_locks.pid = blocking.pid
        WHERE NOT blocked_locks.granted
    `);
    console.log('【锁等待】');
    console.table(lockRes.rows);

    await client.end();
}

runCheckup().catch(console.error);

运行说明:

node checkup.js

技术点总结:

  • 通过 pg 驱动连接PostgreSQL数据库
  • 查询 pg_database 监控事务ID年龄
  • 查询 pg_stat_user_tables 监控死元组
  • 查询 pg_replication_slots 监控复制状态
  • 查询 pg_locks 与 pg_stat_activity 监控锁等待

多语言示例

Go 示例

// checkup.go
package main

import (
    "database/sql"
    "fmt"
    "log"

    _ "github.com/lib/pq"
)

func main() {
    connStr := "host=localhost port=5432 dbname=postgres user=postgres password=your_password sslmode=disable"
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    if err := db.Ping(); err != nil {
        log.Fatal(err)
    }

    fmt.Println("=== PostgreSQL 巡检报告 ===\n")

    // 1. 数据库年龄检查
    rows, err := db.Query(`
        SELECT datname, age(datfrozenxid) AS xid_age
        FROM pg_database
        ORDER BY xid_age DESC
    `)
    if err != nil {
        log.Fatal(err)
    }
    fmt.Println("【数据库年龄】")
    for rows.Next() {
        var datname string
        var xidAge int64
        if err := rows.Scan(&datname, &xidAge); err != nil {
            log.Fatal(err)
        }
        fmt.Printf("  %s: %d\n", datname, xidAge)
    }
    rows.Close()

    // 2. 死元组检查
    rows, err = db.Query(`
        SELECT
            schemaname,
            tablename,
            n_dead_tup,
            n_live_tup,
            round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio
        FROM pg_stat_user_tables
        WHERE n_dead_tup > 1000
        ORDER BY n_dead_tup DESC
        LIMIT 10
    `)
    if err != nil {
        log.Fatal(err)
    }
    fmt.Println("\n【死元组 TOP 10】")
    for rows.Next() {
        var schemaname, tablename string
        var nDeadTup, nLiveTup int64
        var deadRatio sql.NullFloat64
        if err := rows.Scan(&schemaname, &tablename, &nDeadTup, &nLiveTup, &deadRatio); err != nil {
            log.Fatal(err)
        }
        ratio := 0.0
        if deadRatio.Valid {
            ratio = deadRatio.Float64
        }
        fmt.Printf("  %s.%s: dead=%d, live=%d, ratio=%.2f%%\n", schemaname, tablename, nDeadTup, nLiveTup, ratio)
    }
    rows.Close()

    // 3. 复制槽检查
    rows, err = db.Query(`
        SELECT
            slot_name,
            slot_type,
            database,
            active,
            pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS retained_bytes
        FROM pg_replication_slots
    `)
    if err != nil {
        log.Fatal(err)
    }
    fmt.Println("\n【复制槽状态】")
    for rows.Next() {
        var slotName, slotType string
        var database sql.NullString
        var active bool
        var retainedBytes sql.NullInt64
        if err := rows.Scan(&slotName, &slotType, &database, &active, &retainedBytes); err != nil {
            log.Fatal(err)
        }
        dbName := ""
        if database.Valid {
            dbName = database.String
        }
        retained := int64(0)
        if retainedBytes.Valid {
            retained = retainedBytes.Int64
        }
        fmt.Printf("  %s (type=%s, db=%s, active=%t, retained=%d bytes)\n", slotName, slotType, dbName, active, retained)
    }
    rows.Close()

    // 4. 锁等待检查
    rows, err = db.Query(`
        SELECT
            blocked.pid AS blocked_pid,
            blocked.query AS blocked_query,
            blocking.pid AS blocking_pid
        FROM pg_stat_activity blocked
        JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid
        JOIN pg_locks blocking_locks ON blocked_locks.locktype = blocking_locks.locktype
            AND blocked_locks.relation = blocking_locks.relation
            AND blocked_locks.pid != blocking_locks.pid
        JOIN pg_stat_activity blocking ON blocking_locks.pid = blocking.pid
        WHERE NOT blocked_locks.granted
    `)
    if err != nil {
        log.Fatal(err)
    }
    fmt.Println("\n【锁等待】")
    for rows.Next() {
        var blockedPid, blockingPid int
        var blockedQuery sql.NullString
        if err := rows.Scan(&blockedPid, &blockedQuery, &blockingPid); err != nil {
            log.Fatal(err)
        }
        query := ""
        if blockedQuery.Valid {
            query = blockedQuery.String
        }
        fmt.Printf("  blocked_pid=%d, blocking_pid=%d, query=%s\n", blockedPid, blockingPid, query)
    }
    rows.Close()
}

运行说明:

# 初始化模块
go mod init pg-checkup-demo
go get github.com/lib/pq

# 运行
go run checkup.go

代码说明:

  • 使用 database/sql 标准库和 lib/pq 驱动连接 PostgreSQL
  • 执行与 Node.js 版本相同的四个核心巡检查询
  • 处理 NULL 值使用 sql.NullString、sql.NullInt64、sql.NullFloat64
  • 结果以格式化文本输出到标准输出

技术点总结:

  • Go 语言数据库操作的标准模式:sql.Open、Ping、Query、Rows.Scan
  • 空值处理方式与类型安全
  • 显式关闭 Rows 以释放数据库连接资源

Python 示例

# checkup.py
import psycopg2
import psycopg2.extras

def run_checkup():
    conn = psycopg2.connect(
        host="localhost",
        port=5432,
        dbname="postgres",
        user="postgres",
        password="your_password"
    )
    cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)

    print("=== PostgreSQL 巡检报告 ===\n")

    # 1. 数据库年龄检查
    cur.execute("""
        SELECT datname, age(datfrozenxid) AS xid_age
        FROM pg_database
        ORDER BY xid_age DESC
    """)
    print("【数据库年龄】")
    for row in cur.fetchall():
        print(f"  {row['datname']}: {row['xid_age']}")

    # 2. 死元组检查
    cur.execute("""
        SELECT
            schemaname,
            tablename,
            n_dead_tup,
            n_live_tup,
            round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio
        FROM pg_stat_user_tables
        WHERE n_dead_tup > 1000
        ORDER BY n_dead_tup DESC
        LIMIT 10
    """)
    print("\n【死元组 TOP 10】")
    for row in cur.fetchall():
        print(f"  {row['schemaname']}.{row['tablename']}: dead={row['n_dead_tup']}, live={row['n_live_tup']}, ratio={row['dead_ratio']}%")

    # 3. 复制槽检查
    cur.execute("""
        SELECT
            slot_name,
            slot_type,
            database,
            active,
            pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS retained_bytes
        FROM pg_replication_slots
    """)
    print("\n【复制槽状态】")
    for row in cur.fetchall():
        db_name = row['database'] if row['database'] else ""
        retained = row['retained_bytes'] if row['retained_bytes'] is not None else 0
        print(f"  {row['slot_name']} (type={row['slot_type']}, db={db_name}, active={row['active']}, retained={retained} bytes)")

    # 4. 锁等待检查
    cur.execute("""
        SELECT
            blocked.pid AS blocked_pid,
            blocked.query AS blocked_query,
            blocking.pid AS blocking_pid
        FROM pg_stat_activity blocked
        JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid
        JOIN pg_locks blocking_locks ON blocked_locks.locktype = blocking_locks.locktype
            AND blocked_locks.relation = blocking_locks.relation
            AND blocked_locks.pid != blocking_locks.pid
        JOIN pg_stat_activity blocking ON blocking_locks.pid = blocking.pid
        WHERE NOT blocked_locks.granted
    """)
    print("\n【锁等待】")
    for row in cur.fetchall():
        query = row['blocked_query'] if row['blocked_query'] else ""
        print(f"  blocked_pid={row['blocked_pid']}, blocking_pid={row['blocking_pid']}, query={query}")

    cur.close()
    conn.close()

if __name__ == "__main__":
    run_checkup()

运行说明:

# 安装依赖
pip install psycopg2-binary

# 运行
python checkup.py

代码说明:

  • 使用 psycopg2 作为 PostgreSQL 驱动,开启 DictCursor 以通过列名访问字段
  • 每个查询独立执行并立即打印结果,避免长时间持有游标
  • 处理 None 值以适配可能为空的字段

技术点总结:

  • psycopg2 的连接与游标管理
  • 使用 DictCursor 简化结果集处理
  • 异常处理(未显式展示,但实际生产需添加 try/except)
  • 通过 fetchall() 一次性获取所有结果,适合小数据集巡检

Java 示例

// Checkup.java
import java.sql.*;
import java.util.Properties;

public class Checkup {
    public static void main(String[] args) {
        String url = "jdbc:postgresql://localhost:5432/postgres";
        Properties props = new Properties();
        props.setProperty("user", "postgres");
        props.setProperty("password", "your_password");

        try (Connection conn = DriverManager.getConnection(url, props)) {
            System.out.println("=== PostgreSQL 巡检报告 ===\n");

            // 1. 数据库年龄检查
            try (Statement stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery(
                     "SELECT datname, age(datfrozenxid) AS xid_age " +
                     "FROM pg_database ORDER BY xid_age DESC")) {
                System.out.println("【数据库年龄】");
                while (rs.next()) {
                    System.out.printf("  %s: %d%n", rs.getString("datname"), rs.getLong("xid_age"));
                }
            }

            // 2. 死元组检查
            try (Statement stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery(
                     "SELECT schemaname, tablename, n_dead_tup, n_live_tup, " +
                     "round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio " +
                     "FROM pg_stat_user_tables WHERE n_dead_tup > 1000 " +
                     "ORDER BY n_dead_tup DESC LIMIT 10")) {
                System.out.println("\n【死元组 TOP 10】");
                while (rs.next()) {
                    System.out.printf("  %s.%s: dead=%d, live=%d, ratio=%.2f%%%n",
                        rs.getString("schemaname"),
                        rs.getString("tablename"),
                        rs.getLong("n_dead_tup"),
                        rs.getLong("n_live_tup"),
                        rs.getDouble("dead_ratio"));
                }
            }

            // 3. 复制槽检查
            try (Statement stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery(
                     "SELECT slot_name, slot_type, database, active, " +
                     "pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS retained_bytes " +
                     "FROM pg_replication_slots")) {
                System.out.println("\n【复制槽状态】");
                while (rs.next()) {
                    String dbName = rs.getString("database");
                    if (rs.wasNull()) dbName = "";
                    long retained = rs.getLong("retained_bytes");
                    if (rs.wasNull()) retained = 0;
                    System.out.printf("  %s (type=%s, db=%s, active=%b, retained=%d bytes)%n",
                        rs.getString("slot_name"),
                        rs.getString("slot_type"),
                        dbName,
                        rs.getBoolean("active"),
                        retained);
                }
            }

            // 4. 锁等待检查
            try (Statement stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery(
                     "SELECT blocked.pid AS blocked_pid, blocked.query AS blocked_query, " +
                     "blocking.pid AS blocking_pid " +
                     "FROM pg_stat_activity blocked " +
                     "JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid " +
                     "JOIN pg_locks blocking_locks ON blocked_locks.locktype = blocking_locks.locktype " +
                     "AND blocked_locks.relation = blocking_locks.relation " +
                     "AND blocked_locks.pid != blocking_locks.pid " +
                     "JOIN pg_stat_activity blocking ON blocking_locks.pid = blocking.pid " +
                     "WHERE NOT blocked_locks.granted")) {
                System.out.println("\n【锁等待】");
                while (rs.next()) {
                    String query = rs.getString("blocked_query");
                    if (rs.wasNull()) query = "";
                    System.out.printf("  blocked_pid=%d, blocking_pid=%d, query=%s%n",
                        rs.getInt("blocked_pid"),
                        rs.getInt("blocking_pid"),
                        query);
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

运行说明:

# 下载 PostgreSQL JDBC 驱动(例如 postgresql-42.7.4.jar)
# 编译
javac -cp ".:postgresql-42.7.4.jar" Checkup.java

# 运行(Linux/macOS)
java -cp ".:postgresql-42.7.4.jar" Checkup

# 运行(Windows)
java -cp ".;postgresql-42.7.4.jar" Checkup

代码说明:

  • 使用 JDBC 标准接口,驱动为 org.postgresql.Driver
  • 使用 try-with-resources 自动关闭 Connection、Statement、ResultSet
  • 通过 wasNull() 检查 SQL NULL 值,避免基本类型拆箱异常
  • 查询语句与 Node.js 版本完全一致,确保巡检逻辑相同

技术点总结:

  • JDBC 连接配置与驱动加载
  • 资源自动管理(try-with-resources)
  • ResultSet 遍历与空值处理
  • 统一的异常捕获与打印

多语言对比

维度Node.js (pg)Go (lib/pq)Python (psycopg2)Java (JDBC)
驱动/库pg (npm)github.com/lib/pqpsycopg2-binaryPostgreSQL JDBC Driver
连接方式new Client({...})sql.Open("postgres", connStr)psycopg2.connect(...)DriverManager.getConnection(url, props)
查询执行client.query(sql)db.Query(sql)cur.execute(sql)stmt.executeQuery(sql)
结果遍历rows.foreachrows.Next() + Scanfor row in cur.fetchall()while(rs.next())
空值处理直接返回 nullsql.Null* 类型None 判断wasNull() 检查
异步/同步异步(async/await)同步同步同步
连接管理手动 connect() / end()手动 Open / Close手动 close()try-with-resources 自动关闭
错误处理try/catch显式 if err != nil可加 try/exceptcatch SQLException
编译/运行解释执行,需 Node.js编译为二进制解释执行,需 Python编译为字节码,需 JVM
适用场景快速脚本、Web后端高并发、微服务数据分析、自动化脚本企业级应用、长期运维平台

项目难点与解决方案

核心难点

事务ID回卷是PostgreSQL运维中最严重的故障之一。当数据库年龄达到约21亿时,数据库将强制进入只读或拒绝连接状态。冻结失效的根因排查涉及长事务、复制槽、两阶段提交等多个维度。

解决方案

  1. 分层监控:设置多级阈值告警(如2亿、5亿、10亿)
  2. 定期巡检:每周执行 postgres-checkup 或 PG Collector
  3. 参数调优:合理设置 autovacuum_freeze_max_age 和 vacuum_freeze_table_age
  4. 预防性冻结:在业务低峰期主动执行 VACUUM FREEZE

广度

覆盖事务ID管理、存储膨胀、锁管理、WAL日志、复制架构等PostgreSQL核心运维领域。

深度

深入事务ID回卷的底层原理、冻结机制的工作方式、死元组与膨胀的量化分析方法。

复杂度

涉及系统目录表、统计视图、WAL LSN、锁等待图等多维度数据的关联分析,需要综合运用SQL查询和系统知识。

官方文档

参考链接

总结

本文系统梳理了PostgreSQL常规巡检的核心关注点与主流开源工具。事务ID回卷、表膨胀、锁等待和WAL堆积是巡检的四大支柱。PG Collector 和 pg_gather 以纯SQL脚本形式提供轻量级巡检报告,适合快速部署和二次开发。postgres-checkup 提供深度分析和 actionable 建议,适合定期深度巡检。

PostgreSQL HowTo 作为最佳实践知识库,可与巡检工具配合形成完整的运维闭环。建议将 postgres-checkup 作为定期巡检工具(每周/每月),结合 PG Collector 或 pg_gather 进行日常快速检查。

Agentic RAG生产落地五大反模式与韧性架构设计 Agentic RAG并非RAG与Agent的简单叠加,而是一种面向复杂任务编排的新型软件范式。其核心在于结构化任务分解、工具调用契约化、有状态执行流、语义级可观测性及混沌验证能力。区别于传统RAG的无状态问答,Agentic RAG需应对PDF解析置信度不足、SQL工具空结果误判、网络分区导致状态丢失等真实工程挑战。技术价值体现在任务可追溯、失败可恢复、输出可审计;典型场景覆盖金融合规审查、制造业故障推理、多源合同智能比对等需要多步工具协同与人工介入的高可靠性业务。本文聚焦生产环境中90%项目失败的深层根 阅读详情

相关推荐

AI Agent四大核心能力:目标解析、工具调度、状态记忆与反馈闭环

AI Agent并非高级聊天机器人,而是具备目标理解、任务拆解与动态执行能力的智能决策系统。其本质在于将模糊自然语言转化为结构化任务图谱,通过工具调度层精准调用API,依托状态记忆层维护跨会话任务进度,并借由反馈闭环层实现错误自修复。这种‘理解-规划-执行-优化’的技术范式,显著区别于传统NLU驱动的问答模型,在金融风控、电商运营、智能制造等强流程场景中展现出高确定性与可解释性。本文聚焦Agent系统最基础、最通用的四大能力模块设计原理与工程落地要点。

weixin_30555515的博客 404

开源数据库智能巡检平台 RaccoonX(原 DBCheck)功能详解与实战体验

RaccoonX 是当前少有的**「巡检 + 实时监控 + AI 诊断 + SQL 审核 + 工作流编排」五位一体**的开源数据库运维平台:社区版 Apache 2.0 免费、支持 21 种主流与国产数据库、可纯本地离线部署(数据零外传)、报告自动脱敏,天然契合信创与高安全监管场景。无论你是单一 MySQL 小站还是 Oracle RAC 集群,一条 Docker 命令即可纳入统一巡检与监控体系,把 DBA 的重复劳动变成一键流程。

dk843108982的博客 286

高德地图赋能四足机器人:具身智能导航与ROS2集成实践

机器人自主导航是移动机器人领域的核心技术,其原理是通过SLAM(即时定位与地图构建)实现环境感知与自我定位,结合路径规划算法决定行动路线。这项技术的价值在于赋予机器人在未知或动态环境中自主移动的能力,是实现服务机器人、自动驾驶等应用的基础。在应用场景上,它广泛覆盖了室内配送、安防巡检、公共服务导览等领域。本文以高德地图与四足机器人融合的“动量机器狗途途”项目为具体案例,深入剖析了如何利用高德地图API提供的高精地图与路径规划服务,作为机器人的“云端大脑”,与基于ROS2和Nav2框架的本地“小脑”(实时感知

weixin_34302561的博客 436

工业巡检机器人软件:架构设计与闭环实现

随着工业自动化的深入,智能巡检机器人正替代人工在变电站、生产车间等场景完成设备状态检查。其软件并非单一程序,而是涵盖边缘端感知、通信传输与服务端任务编排的分布式系统。理解其核心价值在于构建从导航到位、多模态数据采集到识别告警的业务闭环。基于机器人操作系统(ROS2)提供底层控制,结合MQTT实现弱网可靠传输,通过YOLO与OCR等技术完成表计读数与温度异常检测。本文从问题定义到架构分层,再到最小可运行闭环的搭建,系统梳理了工业巡检机器人软件的关键实现路径,为工程落地提供参考。

weixin_33859231的博客 334

智慧电厂架构设计与落地实践:从边缘网关到数据中台的完整方案

工业物联网通过边缘计算与数据中台技术,实现设备数据的统一采集、协议转换与存储分析。其核心原理是在靠近数据源侧完成协议解析与边缘预处理,再经由消息队列上传至时序数据库,从而打破数据孤岛、保障毫秒级时间同步。这一架构在智慧电厂场景中价值显著,可支撑燃烧优化、设备预警与负荷调度等应用。方案涵盖边缘网关选型、OPC UA与MQTT协议适配、时序库与关系库分工,并给出Docker Compose最小化验证环境,帮助工程师快速打通从Modbus采集到InfluxDB存储的全链路。

weixin_29169899的博客 100

医学图像标注工具教程——从零开始使用3D Slicer进行医学图像手动分割

3D Slicer手动分割软件下载及安装数据导入导入CT序列文件夹导入单张超声基本交互操作窗位和窗宽的调整改变切片及寻找特定点对应的三维切片图像分割功能介绍PaintDrawEraseLevel tracingGrow from seedsFill between slicesThreshold & Margin & HollowSmoothingScissors & IslandsLogical operator分割结果的保存 软件下载及安装 下载地址:官网的4.11.0版本 数据

神仙院B栋4楼保安的博客 1万+

本地大模型驱动OpenClaw:数据库运维自动化实践

AI代理(Agent)正从对话助手走向真正的任务执行者,其核心原理是通过大语言模型的规划能力拆解目标,并调用外部工具完成操作。在企业场景中,数据隐私与API调用成本让本地部署大模型成为刚需。OpenClaw作为开源AI代理,可接入Ollama运行的qwen2.5系列模型,实现对PostgreSQL等数据库的自动巡检、慢查询分析与报告生成,有效减轻DBA的重复运维负担。围绕环境配置、模型选型与工具调用,梳理了本地大模型与OpenClaw的整合实践,并展示了真实数据库场景中的能力边界。

weixin_29053695的博客 291

手把手教你用差分放大电路搭建LC振荡器(实测频率误差分析)

本文通过一个差分放大电路搭建LC振荡器的实践案例,深入分析了实测频率与理论值产生显著误差的原因。核心在于共模电感的互感效应改变了谐振回路的等效电感,导致频率偏差。文章提供了从电路搭建、误差根源剖析到波形优化与稳定性提升的完整解决方案,帮助电子爱好者深入理解模拟电路的实际行为。

svm4gardener的博客 545

快薅羊毛,这款企业级数据库监控诊断平台终于免费啦

基于eBPF技术,在不侵入任何内核代码的情况下拿到细粒度的监控和审计数据,是DBdoctor的重要创新点,首推此种采集方式。更甚者,拥有性能洞察、锁分析、索引推荐、性能审核、根因诊断等高阶功能的专业版也可以在公众号免费自助申请,赶紧关注下方公众号立即薅羊毛吧。:如果公司已使用Prometheus搭建监控平台,可以直接对接Prometheus的监控数据,无缝扩展公司监控平台的审核、巡检等能力。快速搭建属于你们公司的企业级数据库监控平台,立即拥有事前的SQL审核与巡检,事中的监控告警,事后的问题回溯与审计。

DBdoctor_off的博客 880

Excel数字变成了小数点+E+17?这些方法帮你解决!

本文介绍了三种解决Excel中数字变成小数点+E+17问题的方法:通过下拉箭头或右键菜单设置单元格格式为文本、使用TEXT函数将科学计数法转换为文本。这些方法各有优缺点,适用于不同的场景和需求。在实际应用中,我们可以根据具体情况选择合适的方法来解决问题。通过以上方法,我们可以轻松地从繁琐的小数点+E+17问题中解脱出来,更便捷地处理Excel中的数字。希望本文能够帮助到您,让您在使用Excel时更加得心应手!

nntxthml的博客 1250

conda upgrade出现Solving environment:failed CondaValueError Malformed version string invalid chars解决方案

本文主要介绍了conda install出现Solving environment:failed、CondaValueError: Malformed version string invalid character(s),希望能对使用conda的同学们有所帮助。 文章目录 1. 问题描述 2. 解决方案 2.1 步骤一 2.2 步骤二

weixin_43178406的博客 8万+

静态代码分析工具选型与实践:从SonarQube到Semgrep的落地指南

静态代码分析是提升代码质量与安全性的关键手段,其核心原理是在不运行程序的情况下,通过语法树、数据流分析等技术发现潜在的缺陷与漏洞。在工程实践中,合理配置规则并嵌入CI流程,能显著降低告警噪音、提升团队修复效率。从多语言质量平台SonarQube,到轻量级语言专用工具ESLint、Pylint,再到面向深度安全扫描的Semgrep、CodeQL,不同工具各有其适用场景。本文基于真实项目经验,梳理了工具选型逻辑、规则配置策略、质量门禁设置以及团队落地方法,并介绍了AI辅助审查与SCA融合等新趋势,帮助团队构建一

weixin_33875564的博客 200

不会精准对比前人研究写“讨论与对比”段落?教你用ChatGPT辅助,直接让这部分提升一个档次(附提示词指令)

一个我觉得很有效的做法是,选取几篇具有代表性的文献,从研究方法、变量设置或结论方面进行对照分析,并指出本研究在哪些方面进行了补充或修正。除了简单说“提出了新的方法”或“首次应用某模型”,更需要说明这一做法在结果解读、研究设计或理论拓展上的具体意义,最好能够与现有研究进行比较,形成明确的差异化描述。段落应包含:研究结果解释、与前人研究对比、研究贡献、局限性反思,并在结尾提出未来研究的可能方向。讨论与对比部分一般位于文献综述、研究讨论或结果分析的后面,目的是对比已有研究,突出自己研究的价值与新意。

智能AI让学术科研更容易 172

TRON 2机器人地下管廊无人巡检实战:从SLAM到AI识别的工程化落地

机器人技术,特别是自主导航与环境感知,正成为工业自动化和智慧城市运维的核心驱动力。其核心原理在于通过SLAM(同步定位与地图构建)、多传感器融合等技术,赋予机器人在无GPS等复杂环境中的“眼睛”和“大脑”。这项技术的价值在于能够替代人工执行危险、重复或高精度的作业,显著提升效率与安全性。在智慧城市和工业运维领域,其典型应用场景包括对地下综合管廊、变电站、隧道等基础设施进行自动化巡检。本文以TRON 2机器人在苏州地下管廊的无人巡检实录为具体案例,深入剖析了如何跨越从“技术可行”到“工程可用”的鸿沟,系统讲解

weixin_30247307的博客 393

游戏战斗系统架构设计的演绎之路——从一坨意大利面条到一座精密钟表

摘要:游戏战斗系统架构设计的演进之路 本文系统梳理了游戏战斗系统从早期街机到现代3A大作的架构发展历程。70-80年代的街机游戏采用基础碰撞盒判定(如《街头霸王》);90年代实现动画融合与状态机架构(《最终幻想》《拳皇》);2000年后MMO引入服务器验证与同步技术(《魔兽世界》);3D时代融合物理引擎与智能AI(《GTA5》《只狼》);移动端则发展出轻量化架构与云端判定(《王者荣耀》)。现代战斗系统已实现模块化、数据驱动和热更新,未来趋势是AI裁判与云战斗系统。这一演进过程展现了技术与创意的完美结合,数百

本博客聚焦游戏开发技巧、创业实战心得及大学热门课程干货。这里既有技术深度,也有思维广度,无论你是开发者、创业者还是高校学子,都能在这里找到适合自己的成长之路! 804

【网络与爬虫 61】移动端App爬虫终极指南:Android-iOS应用数据提取黑科技全解析

本文全面解析移动端App爬虫技术,涵盖Android和iOS两大平台的数据提取方法。从基础的网络抓包到高级的代码注入,从自动化测试框架到反调试技术绕过,为开发者提供移动端数据采集的完整解决方案和实战经验。

熵数实验室 3029

PostgreSQL Oracle GoldenGate支持种类繁多的数据库的同步,功能非常强大 pgsql_admin_script ogg_oracle_replicate_to_postgres

PostgreSQL Oracle GoldenGate支持种类繁多的数据库的同步,功能非常强大 pgsql_admin_script ogg_oracle_replicate_to_postgresql.md pgsql_admin_script 常用管理脚本 generate_report.sh # 用来生成数据库巡检报告, 注意可以使用lock_timeout和statement_timeout来避免长时间等待。 pgq.md # 用来实现数据逻辑增量复制

秋̶᭄ꦿ攻城狮࿆ྂ 838

K8S搭建(centos)四、安装K8S

每个人都有惰性,但不断学习是好好生活的根本,共勉!

感谢阅读 2654

云数据库慢查询优化:DAS系统核心功能与实战选型指南

慢查询是云数据库性能劣化的典型表征,其本质是SQL执行计划失优、索引缺失、统计信息过期或隐式转换等底层原理问题。解决它不仅依赖日志分析,更需结合执行计划解读、上下文感知的根因定位与可验证的优化建议,从而实现从问题发现到闭环修复的工程化治理。在云原生环境下,DAS系统(数据库自治服务)已成为主流方案,其核心价值在于将DBA经验转化为自动化能力,覆盖MySQL、Doris等多引擎场景,并支撑无专职DBA团队的低门槛运维。本文聚焦云数据库、慢查询优化及DAS系统的基本功能有哪些三大技术锚点,提供可复用的评估框架与

weixin_34221332的博客 282

Probability and Statistics - 4th edition

Probability and Statistics - 4th edition

STM32F407中文手册

STM32f407中文手册 本参考手册面向应用开发人员,提供有关使用 STM32F405xx/07xx、STM32F415xx/17xx、 STM32F42xxx 和 STM32F43xxx 微控制器存储器与外设的完整信息。STM32F405xx/07xx、STM32F415xx/17xx、STM32F42xxx 和 STM32F43xxx 构成一个微 控制器系列,各产品具有不同的存储器大小、封装和外设。有关订购信息以及器件的机械与电气特性,请参见数据手册。有关 ARM Cortex™-M4F 内核的信息,请参见《Cortex™-M4F 技术参考手册》。

Immich 数据库查询实战:基于 Postgres 的常用 SQL 操作与底层表结构解析

本文围绕 Immich 官方文档中的数据库查询指南展开,介绍如何直接进入 Immich 的 Postgres 容器执行 SQL,覆盖资产(Assets)按文件名/路径/ID/校验值/元数据/类型查找、标签与用户统计、人物删除、系统配置与文件状态检查、Postgres 用户管理等全部核心查询场景。结合服务端 schema 源码,进一步解释每张表、每个关键字段的真实定义与约束,帮助你在排查数据、做重复

gitblog_00315的博客 457

汉书HE8,好用的汇编代码编写工具

汉书HE8,好用的汇编代码编写工具早期编写汇编代码所用功能强大

OpenClaw Memoria:1分钟上手的智能体语义记忆系统

语义记忆是智能体实现长期上下文理解与知识复用的核心能力,其本质是将非结构化交互数据转化为可检索、可演化、可推理的向量化知识表征。技术原理上依赖分层架构——词法匹配保障响应速度,语义嵌入支撑概念关联,持久固化模拟人类记忆巩固机制。该能力显著提升Agent在RAG增强、多轮对话一致性、跨任务知识迁移等场景中的工程可用性。Memoria作为OpenClaw生态内置的记忆模块,以零配置默认策略(SQLite+all-MiniLM-L6-v2)、CLI原生集成和Dreaming自动巩固机制,大幅降低语义记忆落地门槛,

weixin_33726318的博客 433

C# WinForm 基于DirectShow.dll编写的程序,可边预览边保存视频,音频和图片

基于微软的DirectShow.dll的二次开发1.自动识别连接的摄像头跟麦克风且可以对其进行选择(默认选中第一个)2.可录制wmv和avi两种格式的视频并且都带有音频,avi可以录制的分辨率和帧数进行选择,wmv默认进行了淸晰度优化如不喜欢可去除代码中有相关提示3.可在预览点开之后进行拍照,也可边录制视频边拍照4.添加了视频和音频的压缩功能(酌情使用)这次二次开发发现C#对DirectShow的相关信息太少,这次做完也不敢藏私共享出来,还望大家指正

AI Agent跨会话记忆系统实战:从持久化到语义遗忘

AI Agent的‘记忆’并非简单存储用户信息,而是支撑长期人机协作的核心能力。其本质是动态交互快照,需满足毫秒级读取、多维语义锚定与响应式更新等硬性约束。传统用户画像方案因时间粒度粗、关联维度僵化、更新机制滞后,无法适配Agent实时交互需求。真实生产中,跨会话记忆必须解决会话标识可追溯、延迟≤200ms、容量线性扩展三大技术约束,并防范记忆污染、膨胀与跨端不同步等隐形风险。本文基于日均4.2万次跨会话请求的线上系统,详解PostgreSQL主存+向量库辅助的混合架构、结构化/非结构化记忆分离策略,以及语

weixin_29038345的博客 233

dbx:基于Rust的轻量级多数据库工具,支持83种数据库与本地AI SQL

数据库客户端工具是开发者和DBA日常连接、查询、管理数据的核心载体。其本质是数据库协议适配器+SQL执行引擎+用户界面的组合体,需兼顾跨平台兼容性、低资源占用、方言支持广度与安全性。传统Java或Electron方案常因虚拟机开销、WebView内存泄漏、驱动封装僵化等问题导致启动慢、卡顿、信创适配难。dbx以Rust语言构建原生性能内核,采用Tauri桥接系统级UI,实现20MB单文件、0.8秒冷启、112MB常驻内存;通过FFI统一抽象83种数据库驱动(含达梦、人大金仓等国产库),并内置本地运行的Tin

weixin_34212762的博客 450

PostgreSQL idle in transaction五大隐藏危害与实战解决方案

数据库事务管理是保障数据一致性的核心机制,而PostgreSQL中的idle in transaction状态常被忽视,却暗藏锁泄漏、事务ID回卷、WAL堆积、连接池耗尽和主从延迟等风险。本文从MVCC原理出发,解析该状态为何会阻塞并发写入与清理进程,并结合真实运维场景,提供从监控查询、超时参数设置到代码修复的完整排查路径。通过配置idle_in_transaction_session_timeout和定期巡检pg_stat_activity,可有效避免生产事故。适合DBA与后端开发者深入理解并预防此类隐患

ciya3282的博客 295

Mac下MySQL的配置和修改密码以及忘记密码后的重置密码

1 ) Mac下MySQL的配置和修改密码 2 ) 忘记并重置密码 启动MySQL服务,通过 $ps aux | grep mysql 判断是否启动该服务,若未启动,则到 系统偏好设置/MySQL面板 启动服务 软件默认会安装到:/usr/local/mysql 目录,进入这个目录下的bin目录:$cd /usr/local/mysql/bin 执行 $./mysql -u root -p 通过初始密码登录MySQL 通...

Wang的专栏 6260
上一篇: PostgreSQL笔记56: 慢查询日志分析与诊断工具实践
下一篇: PostgreSQL笔记58: 性能监控工具全景——从内核指标到操作系统诊断
Wang's Blog
博客等级 码龄12年 7439粉丝 2424原创
评论
成就一亿技术人!
拼手气红包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、付费专栏及课程。

余额充值