说我PG Freezing Boom 讲的一般的那个同学，专帖给你，看看这次可满意

SELECT
    relname AS table_name,
    reloptions AS table_options,
    pg_settings.setting AS autovacuum_global_setting,
    CASE
        WHEN reloptions::text LIKE '%autovacuum_enabled=off%' THEN 'Disabled by table option'
        WHEN pg_settings.setting = 'off' THEN 'Disabled globally'
        ELSE 'Enabled'
    END AS autovacuum_status
FROM
    pg_class
JOIN
    pg_namespace ON pg_namespace.oid = pg_class.relnamespace
LEFT JOIN
    pg_settings ON pg_settings.name = 'autovacuum'
WHERE
    relkind = 'r' -- Only tables
    AND pg_namespace.nspname NOT IN ('pg_catalog', 'information_schema') -- Exclude system schemas
    AND (
        reloptions::text LIKE '%autovacuum_enabled=off%' OR pg_settings.setting = 'off'
    )
ORDER BY
    table_name;

SELECT
    blocked_activity.pid AS blocked_pid,
    blocked_activity.application_name AS blocked_application,
    blocked_activity.query AS blocked_query,
    blocking_activity.pid AS blocking_pid,
    blocking_activity.application_name AS blocking_application,
    blocking_activity.query AS blocking_query,
    blocking_activity.wait_event_type AS blocking_wait_event_type,
    blocking_activity.wait_event AS blocking_wait_event,
    pg_locks.mode AS blocked_lock_mode,
    pg_locks.relation::regclass AS blocked_table
FROM
    pg_catalog.pg_locks AS blocked_locks
JOIN
    pg_catalog.pg_stat_activity AS blocked_activity
    ON blocked_activity.pid = blocked_locks.pid
JOIN
    pg_catalog.pg_locks AS blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocked_locks.pid != blocking_locks.pid
JOIN
    pg_catalog.pg_stat_activity AS blocking_activity
    ON blocking_activity.pid = blocking_locks.pid
WHERE
    NOT blocked_locks.granted
    AND (blocked_activity.backend_type = 'autovacuum worker' OR blocked_activity.application_name = 'autovacuum launcher')
ORDER BY
    blocked_table, blocked_pid;

SELECT
    pid,
    datname,
    usename,
    application_name,
    client_addr,
    backend_start,
    xact_start,
    now() - xact_start AS transaction_duration,
    state,
    state_change,
    wait_event_type,
    wait_event,
    query_start,
    query
FROM
    pg_stat_activity
WHERE
    state IN ('idle in transaction', 'idle in transaction (error)')
    AND (now() - xact_start) > interval '10 minutes' -- Adjust time as needed
ORDER BY
    transaction_duration DESC;

SELECT
    n.nspname AS schema_name,
    c.relname AS table_name,
    age(c.relfrozenxid) AS xid_age_since_freeze,
    pg_size_pretty(pg_relation_size(c.oid)) AS table_size,
    pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum,
    pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze
FROM
    pg_class c
JOIN
    pg_namespace n ON n.oid = c.relnamespace
WHERE
    c.relkind = 'r'
    AND n.nspname NOT IN ('pg_catalog', 'information_schema') -- 排除系统模式
ORDER BY
    xid_age_since_freeze DESC
LIMIT 1;

const fs = require('fs');
const readline = require('readline');
const path = require('path');

/**
 * PostgreSQL 日志分析器，专注于 VACUUM 信息。
 *
 * @param {string} logFilePath - PostgreSQL 日志文件的完整路径。
 * @returns {Promise<Array<Object>>} - 解析出的 VACUUM 事件数组。
 */
async function analyzePgVacuumLogs(logFilePath) {
    // 确保日志文件存在
    if (!fs.existsSync(logFilePath)) {
        console.error(`错误：日志文件不存在：${logFilePath}`);
        return [];
    }

    const vacuumEvents = [];
    let lineNumber = 0;

    // 创建 readline 接口，用于逐行读取文件
    const fileStream = fs.createReadStream(logFilePath, { encoding: 'utf8' });
    const rl = readline.createInterface({
        input: fileStream,
        crlfDelay: Infinity // 处理 Windows 和 Unix 风格的换行符
    });

    console.log(`开始分析日志文件: ${logFilePath}`);

    // 正则表达式来匹配 PostgreSQL 的日志行和 VACUUM 相关信息
    // 这是一个简化版本，具体正则表达式可能需要根据你的 PostgreSQL 日志格式调整
    const logLineRegex = /^(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}\.\d{3} [A-Z]{3}) \[(.+?)\] (.+?):(\s+autovacuum:)?\s*(VACUUM|ANALYZE)\s*(?:\((freeze)\))?\s*of table\s*"(.+?)"(?::\s*(.+?))?$/;
    const durationRegex = /duration:\s*(\d+\.\d+)\s*ms/; // 用于捕获 duration 信息

    for await (const line of rl) {
        lineNumber++;

        // 检查是否是 VACUUM 或 autovacuum 相关的日志行
        if (line.includes('VACUUM') || line.includes('autovacuum')) {
            const match = line.match(logLineRegex);
            const durationMatch = line.match(durationRegex);

            if (match) {
                const timestamp = match[1];
                const logType = match[3].trim(); // LOG, DETAIL, etc.
                const isAutovacuum = !!match[4]; // 是否是 autovacuum
                const operationType = match[5]; // VACUUM 或 ANALYZE
                const isFreeze = !!match[6]; // 是否包含 freeze
                const tableNameWithSchema = match[7]; // 表名，可能包含模式
                const details = match[8] ? match[8].trim() : ''; // 后续的详细信息

                let durationMs = null;
                if (durationMatch) {
                    durationMs = parseFloat(durationMatch[1]);
                }

                // 从 "schema.table" 中分离模式和表名
                const parts = tableNameWithSchema.split('.');
                const schemaName = parts.length > 1 ? parts[0] : 'public';
                const tableName = parts.length > 1 ? parts.slice(1).join('.') : parts[0];

                const event = {
                    lineNumber,
                    timestamp,
                    logType,
                    isAutovacuum,
                    operationType,
                    isFreeze,
                    schemaName: schemaName.replace(/"/g, ''), // 移除双引号
                    tableName: tableName.replace(/"/g, ''),
                    durationMs,
                    details: details,
                    rawLog: line
                };
                vacuumEvents.push(event);
            } else {
                // 如果没有匹配到预期的 VACUUM 格式，但包含关键字，仍然记录原始日志
                vacuumEvents.push({
                    lineNumber,
                    rawLog: line,
                    parsed: false
                });
            }
        }
    }

    console.log(`日志分析完成。发现 ${vacuumEvents.length} 条 VACUUM 相关事件。`);
    return vacuumEvents;
}

// --- 使用命令行参数 ---
(async () => {
    // 获取命令行参数: node pg-log-analyzer.js <日志路径> <日志文件名>
    const args = process.argv.slice(2); // 移除 'node' 和 'pg-log-analyzer.js'

    if (args.length < 2) {
        console.error('用法: node pg-log-analyzer.js <日志文件目录> <日志文件名>');
        console.error('例如: node pg-log-analyzer.js /var/lib/postgresql/data/pg_log postgresql-2025-07-25_000000.log');
        console.error('或者在 Windows 上: node pg-log-analyzer.js "C:\\Program Files\\PostgreSQL\\16\\data\\log" postgresql-2025-07-25_000000.log');
        process.exit(1); // 退出并返回错误码
    }

    const logDirectory = args[0];
    const logFileName = args[1];
    const fullLogPath = path.join(logDirectory, logFileName); // 拼接完整路径

    const events = await analyzePgVacuumLogs(fullLogPath);

    if (events.length > 0) {
        console.log('\n--- 识别到的 VACUUM 事件摘要 ---');
        // 打印前 10 条事件，或所有事件如果数量不多
        const eventsToDisplay = events.slice(0, Math.min(events.length, 10));

        eventsToDisplay.forEach((event, index) => {
            console.log(`\n事件 ${index + 1}:`);
            console.log(`  行号: ${event.lineNumber}`);
            console.log(`  时间: ${event.timestamp}`);
            console.log(`  操作: ${event.isAutovacuum ? '自动' : '手动'}${event.operationType}${event.isFreeze ? ' (冻结)' : ''}`);
            console.log(`  表名: ${event.schemaName}.${event.tableName}`);
            if (event.durationMs !== null) {
                console.log(`  耗时: ${event.durationMs.toFixed(3)} ms`);
            }
            if (event.details) {
                console.log(`  详情: ${event.details}`);
            }
            if (!event.parsed) {
                console.log(`  (未完全解析，原始日志): ${event.rawLog}`);
            }
        });

        // 你可以将所有结果保存到 JSON 文件
        const outputFileName = `vacuum_analysis_${path.basename(logFileName, path.extname(logFileName))}.json`;
        fs.writeFileSync(outputFileName, JSON.stringify(events, null, 2), 'utf8');
        console(`\n完整分析结果已保存到：${outputFileName}`);

        // 进一步分析：例如，找出最耗时的 VACUUM 操作
        const longestVacuum = events.filter(e => e.operationType === 'VACUUM' && e.durationMs !== null)
                                    .sort((a, b) => b.durationMs - a.durationMs)[0];
        if (longestVacuum) {
            console(`\n最耗时的 VACUUM 操作:`);
            console(`  表名: ${longestVacuum.schemaName}.${longestVacuum.tableName}`);
            console(`  耗时: ${longestVacuum.durationMs.toFixed(3)} ms`);
            console(`  时间: ${longestVacuum.timestamp}`);
            console(`  行号: ${longestVacuum.lineNumber}`);
        }

    } else {
        console('\n未找到 VACUUM 相关事件。请检查日志路径、文件名以及 `log_autovacuum_min_duration` 配置。');
    }
})();

node pg-log-analyzer.js /var/lib/postgresql/data/pg_log postgresql-2025-07-25_000000.log