PostgreSQL慢查询日志配置与日志管理完全指南

# postgresql.conf
logging_collector = on
log_destination = 'stderr,csvlog'
log_directory = 'pg_log'

log_filename = 'postgresql-%y-%m-%d_%h%m%s.log'
log_rotation_age = 1d
log_rotation_size = 100mb
log_truncate_on_rotation = off

pgdata/pg_log/
├── postgresql-2026-08-21_000000.log
├── postgresql-2026-08-21_120000.log
└── postgresql-2026-08-22_000000.log

log_connections = on
log_disconnections = on

log:  connection received: host=192.168.1.100 port=54321
log:  connection authorized: user=app_user database=app_db
log:  disconnection: session time: 0:00:05.123 user=app_user database=app_db host=192.168.1.100 port=54321

log_autovacuum_min_duration = 1000  # 记录超过1秒的autovacuum操作

log_checkpoints = on

log_lock_waits = on
deadlock_timeout = 1s

log_temp_files = 0  # 记录所有临时文件

log_min_duration_statement = 500  # 记录超过500ms的查询

log_duration = on

log_duration = on
log_min_duration_statement = 500

log_statement = 'ddl'  # 默认值

log_min_duration_sample = 100
log_statement_sample_rate = 0.5

log_min_duration_statement = 500
log_min_duration_sample = 100
log_statement_sample_rate = 0.1

log_line_prefix = '%m [%p] %q%u@%d/%a '

log_min_messages = 'warning'

client_min_messages = 'notice'

-- 创建扩展
create extension file_fdw;

-- 创建外部服务器
create server file_server foreign data wrapper file_fdw;

-- 创建外部表（以csv格式日志为例）
create foreign table pg_log (
    log_time timestamp(3) with time zone,
    user_name text,
    database_name text,
    process_id integer,
    connection_from text,
    session_id text,
    session_line_num bigint,
    command_tag text,
    session_start_time timestamp with time zone,
    virtual_transaction_id text,
    transaction_id bigint,
    error_severity text,
    sql_state_code text,
    message text,
    detail text,
    hint text,
    internal_query text,
    internal_query_pos integer,
    context text,
    query text,
    query_pos integer,
    location text,
    application_name text
) server file_server
options (filename '/path/to/pg_log/postgresql.csv', format 'csv');

select log_time, user_name, database_name, query
from pg_log
where error_severity = 'error'
  and log_time > current_timestamp - interval '1 hour'
order by log_time desc;

logging_collector = on
log_destination = 'stderr,csvlog'
log_directory = 'pg_log'
log_filename = 'postgresql-%y-%m-%d_%h%m%s.log'
log_rotation_age = 1d
log_rotation_size = 100mb
log_min_duration_statement = 500
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0
log_line_prefix = '%m [%p] %q%u@%d/%a '

sudo systemctl restart postgresql
# 或
pg_ctl restart -d /path/to/data

npm init -y
npm install pg
node demo.js

// demo.js
const { client } = require('pg');
const pg_config = {
    host: 'localhost',
    port: 5432,
    database: 'testdb',
    user: 'testuser',
    password: 'testpass'
};
// 创建带慢查询的测试数据
async function setuptestdata(client) {
    await client.query(`
        create table if not exists test_logging (
            id serial primary key,
            data text,
            created_at timestamp default now()
        )
    `);
    // 插入 10000 条测试数据
    await client.query(`
        insert into test_logging (data)
        select md5(random()::text)
        from generate_series(1, 10000)
    `);
    console.log('[setup] 测试数据已创建');
}
// 执行一个慢查询（模拟）
async function runslowquery(client) {
    const start = date.now();
    await client.query(`
        select count(*), data
        from test_logging
        group by data
        having count(*) > 1
        order by count(*) desc
    `);
    const duration = date.now() - start;
    console.log(`[query] 慢查询执行完成，耗时: ${duration}ms`);
}
// 使用 file_fdw 查询日志（需提前配置）
async function querylogswithfilefdw(client) {
    // 创建 file_fdw 扩展
    await client.query('create extension if not exists file_fdw');
    // 创建外部服务器
    await client.query(`
        create server if not exists log_server
        foreign data wrapper file_fdw
    `);
    // 创建外部表（csv格式）
    await client.query(`
        create foreign table if not exists pg_log_csv (
            log_time timestamp(3) with time zone,
            user_name text,
            database_name text,
            process_id integer,
            connection_from text,
            session_id text,
            session_line_num bigint,
            command_tag text,
            session_start_time timestamp with time zone,
            virtual_transaction_id text,
            transaction_id bigint,
            error_severity text,
            sql_state_code text,
            message text,
            detail text,
            hint text,
            internal_query text,
            internal_query_pos integer,
            context text,
            query text,
            query_pos integer,
            location text,
            application_name text
        ) server log_server
        options (filename '/path/to/pg_data/pg_log/postgresql.csv', format 'csv')
    `);
    // 查询最近1小时的慢查询
    const result = await client.query(`
        select
            log_time,
            user_name,
            database_name,
            substring(query, 1, 100) as query_preview,
            message
        from pg_log_csv
        where error_severity = 'log'
          and message like '%duration%'
          and log_time > now() - interval '1 hour'
        order by log_time desc
        limit 10
    `);
    console.log('[logs] 最近慢查询:');
    result.rows.foreach(row => {
        console.log(`  ${row.log_time} | ${row.user_name} | ${row.query_preview}...`);
    });
}
async function main() {
    const client = new client(pg_config);
    try {
        await client.connect();
        console.log('[demo] 已连接到 postgresql');
        await setuptestdata(client);
        // 执行多次慢查询以便产生日志
        for (let i = 0; i < 3; i++) {
            await runslowquery(client);
        }
        // 查询日志（需将路径替换为实际日志路径）
        // await querylogswithfilefdw(client);
        // 查看当前慢查询配置
        const config = await client.query(`
            select name, setting
            from pg_settings
            where name in (
                'log_min_duration_statement',
                'log_duration',
                'log_statement',
                'logging_collector'
            )
        `);
        console.log('[config] 当前日志配置:');
        config.rows.foreach(row => {
            console.log(`  ${row.name} = ${row.setting}`);
        });
    } catch (err) {
        console.error('[error]', err);
    } finally {
        await client.end();
    }
}
main();

-- 查看当前慢查询阈值
show log_min_duration_statement;

-- 动态修改（当前会话）
set log_min_duration_statement = 1000;

-- 动态修改（全局，下次连接生效）
alter system set log_min_duration_statement = '1000';
select pg_reload_conf();

-- 查看日志相关所有参数
select name, setting, unit, context
from pg_settings
where name like 'log%'
order by name;

go mod init demo
go get github.com/lib/pq

go run demo.go

// demo.go
package main
import (
    "database/sql"
    "fmt"
    "log"
    "time"
    _ "github.com/lib/pq"
)
const (
    host     = "localhost"
    port     = 5432
    user     = "testuser"
    password = "testpass"
    dbname   = "testdb"
)
func main() {
    connstr := fmt.sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable",
        host, port, user, password, dbname)
    db, err := sql.open("postgres", connstr)
    if err != nil {
        log.fatal("连接失败:", err)
    }
    defer db.close()
    if err := db.ping(); err != nil {
        log.fatal("ping失败:", err)
    }
    fmt.println("[demo] 已连接到 postgresql")
    // 创建测试表
    _, err = db.exec(`
        create table if not exists test_logging (
            id serial primary key,
            data text,
            created_at timestamp default now()
        )
    `)
    if err != nil {
        log.fatal("建表失败:", err)
    }
    // 插入测试数据
    _, err = db.exec(`
        insert into test_logging (data)
        select md5(random()::text)
        from generate_series(1, 10000)
    `)
    if err != nil {
        log.fatal("插入数据失败:", err)
    }
    fmt.println("[setup] 测试数据已创建")
    // 执行慢查询（重复3次）
    for i := 0; i < 3; i++ {
        start := time.now()
        rows, err := db.query(`
            select count(*), data
            from test_logging
            group by data
            having count(*) > 1
            order by count(*) desc
        `)
        if err != nil {
            log.fatal("查询失败:", err)
        }
        rows.close()
        duration := time.since(start)
        fmt.printf("[query] 慢查询执行完成，耗时: %v\n", duration)
    }
    // 查看当前慢查询配置
    var name, setting string
    rowscfg, err := db.query(`
        select name, setting
        from pg_settings
        where name in ('log_min_duration_statement', 'log_duration', 'log_statement', 'logging_collector')
    `)
    if err != nil {
        log.fatal("查询配置失败:", err)
    }
    defer rowscfg.close()
    fmt.println("[config] 当前日志配置:")
    for rowscfg.next() {
        rowscfg.scan(&name, &setting)
        fmt.printf("  %s = %s\n", name, setting)
    }
}

pip install psycopg2-binary

python demo.py

# demo.py
import psycopg2
import time
pg_config = {
    'host': 'localhost',
    'port': 5432,
    'database': 'testdb',
    'user': 'testuser',
    'password': 'testpass'
}
def main():
    conn = psycopg2.connect(**pg_config)
    conn.autocommit = true
    cur = conn.cursor()
    print("[demo] 已连接到 postgresql")
    # 创建测试表
    cur.execute("""
        create table if not exists test_logging (
            id serial primary key,
            data text,
            created_at timestamp default now()
        )
    """)
    # 插入测试数据
    cur.execute("""
        insert into test_logging (data)
        select md5(random()::text)
        from generate_series(1, 10000)
    """)
    print("[setup] 测试数据已创建")
    # 执行慢查询（重复3次）
    for i in range(3):
        start = time.time()
        cur.execute("""
            select count(*), data
            from test_logging
            group by data
            having count(*) > 1
            order by count(*) desc
        """)
        rows = cur.fetchall()
        duration = (time.time() - start) * 1000
        print(f"[query] 慢查询执行完成，耗时: {duration:.2f}ms")
    # 查看当前慢查询配置
    cur.execute("""
        select name, setting
        from pg_settings
        where name in ('log_min_duration_statement', 'log_duration', 'log_statement', 'logging_collector')
    """)
    print("[config] 当前日志配置:")
    for name, setting in cur.fetchall():
        print(f"  {name} = {setting}")
    cur.close()
    conn.close()
if __name__ == "__main__":
    main()

<dependency>
    <groupid>org.postgresql</groupid>
    <artifactid>postgresql</artifactid>
    <version>42.7.3</version>
</dependency>

mvn compile
mvn exec:java -dexec.mainclass="demo"

// demo.java
import java.sql.*;
import java.util.properties;
public class demo {
    private static final string url = "jdbc:postgresql://localhost:5432/testdb";
    private static final string user = "testuser";
    private static final string password = "testpass";
    public static void main(string[] args) {
        properties props = new properties();
        props.setproperty("user", user);
        props.setproperty("password", password);
        try (connection conn = drivermanager.getconnection(url, props)) {
            system.out.println("[demo] 已连接到 postgresql");
            // 创建测试表
            try (statement stmt = conn.createstatement()) {
                stmt.execute("""
                    create table if not exists test_logging (
                        id serial primary key,
                        data text,
                        created_at timestamp default now()
                    )
                """);
                // 插入测试数据
                stmt.execute("""
                    insert into test_logging (data)
                    select md5(random()::text)
                    from generate_series(1, 10000)
                """);
                system.out.println("[setup] 测试数据已创建");
            }
            // 执行慢查询（重复3次）
            for (int i = 0; i < 3; i++) {
                long start = system.currenttimemillis();
                try (statement stmt = conn.createstatement();
                     resultset rs = stmt.executequery("""
                         select count(*), data
                         from test_logging
                         group by data
                         having count(*) > 1
                         order by count(*) desc
                     """)) {
                    while (rs.next()) {
                        // 消费结果集
                    }
                }
                long duration = system.currenttimemillis() - start;
                system.out.printf("[query] 慢查询执行完成，耗时: %dms%n", duration);
            }
            // 查看当前慢查询配置
            try (statement stmt = conn.createstatement();
                 resultset rs = stmt.executequery("""
                     select name, setting
                     from pg_settings
                     where name in ('log_min_duration_statement', 'log_duration', 'log_statement', 'logging_collector')
                 """)) {
                system.out.println("[config] 当前日志配置:");
                while (rs.next()) {
                    system.out.printf("  %s = %s%n", rs.getstring("name"), rs.getstring("setting"));
                }
            }
        } catch (sqlexception e) {
            e.printstacktrace();
        }
    }
}