PostgreSQL 运维实战：配置调优、连接池、慢查询与高可用

SELECT name, setting, unit, context
FROM pg_settings
WHERE context IN ('postmaster', 'sighup')
ORDER BY context, name;
-- context = postmaster 需要重启
-- context = sighup 热加载即可

[databases]
; 格式：逻辑库名 = host=... port=... dbname=...
myapp = host=127.0.0.1 port=5432 dbname=myapp

; 也可以使用通配符，让应用连同名数据库
* = host=127.0.0.1 port=5432

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432

; 认证方式（推荐 scram-sha-256，老版本用 md5）
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

; 池化模式
pool_mode = transaction

; 数据库侧最大连接数（所有 pool 共享）
max_client_conn = 1000
default_pool_size = 20

; 等待队列上限
max_db_connections = 100

; 空闲连接保活/超时
server_idle_timeout = 600
client_idle_timeout = 0

; 连接释放后清理 session 状态
server_reset_query = DISCARD ALL

; 健康检查
server_check_query = SELECT 1
server_check_delay = 30

; 日志
log_connections = 0
log_disconnections = 0
log_pooler_errors = 1

; 管理接口
admin_users = pgbouncer
stats_users = monitoring

# 从 PostgreSQL 导出用户密码哈希
psql -c "SELECT concat('\"', usename, '\" \"', passwd, '\"') FROM pg_shadow WHERE usename NOT LIKE 'pg_%';" -t

psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer

-- 查看连接池状态
SHOW POOLS;

-- 查看所有数据库连接统计
SHOW DATABASES;

-- 查看当前活跃客户端连接
SHOW CLIENTS;

-- 查看当前服务端连接
SHOW SERVERS;

-- 查看统计汇总
SHOW STATS;

-- 在线重载配置
RELOAD;

-- 优雅暂停（维护时使用）
PAUSE myapp;
RESUME myapp;

-- 在 postgresql.conf 中加载
shared_preload_libraries = 'pg_stat_statements'

-- 然后在目标数据库创建扩展
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT
    round(total_exec_time::numeric, 2) AS total_ms,
    calls,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct,
    left(query, 120) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

SELECT
    calls,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    round(stddev_exec_time::numeric, 2) AS stddev_ms,
    left(query, 120) AS query
FROM pg_stat_statements
WHERE calls > 100
ORDER BY mean_exec_time DESC
LIMIT 20;

SELECT
    calls,
    shared_blks_hit,
    shared_blks_read,
    round(100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0), 2) AS hit_rate,
    left(query, 120) AS query
FROM pg_stat_statements
WHERE shared_blks_hit + shared_blks_read > 0
ORDER BY shared_blks_read DESC
LIMIT 20;

SELECT pg_stat_statements_reset();

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.id, u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id, u.name
ORDER BY order_count DESC
LIMIT 100;

-- 查找高 seq scan 的表（可能需要索引）
SELECT schemaname, tablename, seq_scan, seq_tup_read,
       idx_scan, idx_tup_fetch,
       n_live_tup
FROM pg_stat_user_tables
WHERE seq_scan > 1000
ORDER BY seq_tup_read DESC
LIMIT 20;

SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexname NOT LIKE 'pg_%'
  AND indexname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

-- 加 CONCURRENTLY 不阻塞写入，但耗时更长
CREATE INDEX CONCURRENTLY idx_orders_user_created
ON orders (user_id, created_at DESC)
WHERE status != 'cancelled';  -- 部分索引，减少索引大小

# 备份单个数据库（自定义格式，支持并行恢复）
pg_dump \
  -h localhost \
  -U postgres \
  -Fc \                    # 自定义压缩格式
  -j 4 \                   # 4 并发 worker
  -f /backup/myapp_$(date +%Y%m%d_%H%M%S).dump \
  myapp

# 备份所有数据库（含角色和全局对象）
pg_dumpall -h localhost -U postgres > /backup/globals.sql

# 恢复
pg_restore \
  -h localhost \
  -U postgres \
  -d myapp \
  -j 4 \                   # 并行恢复
  --clean \                # 恢复前先删除已存在对象
  --if-exists \
  /backup/myapp_20240318_101500.dump

# 创建物理基础备份
pg_basebackup \
  -h localhost \
  -U replicator \
  -D /backup/basebackup_$(date +%Y%m%d) \
  -Ft \                    # tar 格式
  -z \                     # gzip 压缩
  -Xs \                    # 包含 WAL（streaming 模式）
  -P \                     # 显示进度
  --wal-method=stream

# 恢复时解压到 PostgreSQL data 目录
tar -xzf /backup/basebackup_20240318/base.tar.gz -C /var/lib/postgresql/data/
tar -xzf /backup/basebackup_20240318/pg_wal.tar.gz -C /var/lib/postgresql/data/pg_wal/

-- 确认已退出恢复模式
SELECT pg_is_in_recovery();  -- 应返回 false

-- 确认数据状态
SELECT now(), count(*) FROM orders WHERE created_at > '2024-03-18 09:00:00';

-- 创建专用复制用户
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'strong_password';

# 在备库机器上执行基础备份
pg_basebackup \
  -h 10.0.1.10 \
  -U replicator \
  -D /var/lib/postgresql/data \
  -Xs \
  -R \                         # 自动生成 standby.signal 和复制配置
  -P

SELECT
    client_addr,
    state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    (sent_lsn - replay_lsn) AS replication_lag_bytes,
    write_lag,
    flush_lag,
    replay_lag,
    sync_state
FROM pg_stat_replication;

-- 确认备库状态
SELECT pg_is_in_recovery();         -- true 表示仍在 standby 模式
SELECT pg_last_wal_receive_lsn();   -- 已接收的 WAL 位置
SELECT pg_last_wal_replay_lsn();    -- 已应用的 WAL 位置
SELECT now() - pg_last_xact_replay_timestamp() AS replication_delay;

# Prometheus AlertRule
- alert: PostgresReplicationLagHigh
  expr: |
    pg_replication_lag > 30
  for: 5m
  labels:
    severity: warning
  annotations:
    summary: "PostgreSQL 复制延迟超过 30 秒"
    description: "实例 {{ $labels.instance }} 复制延迟 {{ $value }}s"

# 在备库执行提升操作（PG 12+）
pg_ctl promote -D /var/lib/postgresql/data

# 或通过触发文件（老版本）
touch /var/lib/postgresql/data/failover.signal

-- 查看当前连接数按状态分布
SELECT state, count(*) FROM pg_stat_activity GROUP BY state ORDER BY count DESC;

-- 查看连接数按应用/用户分布
SELECT usename, application_name, client_addr, state, count(*)
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
GROUP BY 1,2,3,4
ORDER BY count DESC
LIMIT 30;

-- 查看长时间 idle 的连接（可能是连接泄漏）
SELECT pid, usename, application_name, client_addr, state,
       now() - state_change AS idle_duration,
       query
FROM pg_stat_activity
WHERE state = 'idle'
  AND now() - state_change > interval '10 minutes'
ORDER BY idle_duration DESC;

-- 终止特定连接（非 superuser 可用）
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
  AND now() - state_change > interval '30 minutes'
  AND usename = 'myapp_user';

# 从日志中查找死锁事件
grep -i "deadlock detected" /var/log/postgresql/postgresql-*.log | tail -20

-- 查看当前等待锁的查询
SELECT
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query,
    now() - blocked.query_start AS blocked_duration
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
    ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
ORDER BY blocked_duration DESC;

-- 查看死元组比例较高的表
SELECT
    schemaname,
    tablename,
    n_live_tup,
    n_dead_tup,
    round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
    last_autovacuum,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY dead_pct DESC
LIMIT 20;

-- 普通 VACUUM（回收死元组，不缩小文件）
VACUUM myapp.orders;

-- VACUUM ANALYZE（同时更新统计信息）
VACUUM ANALYZE myapp.orders;

-- VACUUM FULL（压缩文件，需要锁表，谨慎使用）
VACUUM FULL myapp.orders;  -- 生产环境用 pg_repack 替代

ALTER TABLE large_table SET (
    autovacuum_vacuum_scale_factor = 0.005,
    autovacuum_vacuum_cost_delay = 2
);

# 安装
apt install postgresql-14-repack

# 重建单表（不锁表）
pg_repack -h localhost -U postgres -d myapp -t large_table

# 重建所有表和索引
pg_repack -h localhost -U postgres -d myapp

# docker-compose.yml 示例
services:
  postgres_exporter:
    image: prometheuscommunity/postgres-exporter:v0.15.0
    environment:
      DATA_SOURCE_NAME: "postgresql://monitoring:password@postgres:5432/postgres?sslmode=disable"
    ports:
      - "9187:9187"
    command:
      - "--extend.query-path=/etc/postgres_exporter/queries.yaml"

# Prometheus recording rule
- record: pg:database:cache_hit_ratio
  expr: |
    rate(pg_stat_database_blks_hit[5m])
    /
    (rate(pg_stat_database_blks_hit[5m]) + rate(pg_stat_database_blks_read[5m]))

# /etc/postgres_exporter/queries.yaml
pg_long_running_queries:
  query: |
    SELECT
      count(*) AS count,
      max(extract(epoch FROM (now() - query_start))) AS max_duration_seconds
    FROM pg_stat_activity
    WHERE state = 'active'
      AND query_start < now() - interval '1 minute'
      AND query NOT LIKE '%pg_stat_activity%'
  metrics:
    - count:
        usage: "GAUGE"
        description: "长时间运行的查询数量"
    - max_duration_seconds:
        usage: "GAUGE"
        description: "最长运行查询的持续时间（秒）"

# 查看 PostgreSQL 版本和运行状态
psql -c "SELECT version();"
pg_lsclusters  # Debian/Ubuntu 系统

# 查看所有数据库大小
psql -c "\l+"

# 查看表大小（含索引）
psql -d myapp -c "\dt+"

# 查看当前锁等待链
psql -d myapp -c "SELECT pid, wait_event_type, wait_event, state, left(query,80) FROM pg_stat_activity WHERE wait_event IS NOT NULL;"

# 立即终止某个 pid
psql -c "SELECT pg_terminate_backend(12345);"

# 查看慢查询日志（实时）
tail -f /var/log/postgresql/postgresql-*.log | grep -E "duration:|ERROR:|FATAL:"

# 重载配置
psql -c "SELECT pg_reload_conf();"

# 查看参数值
psql -c "SHOW shared_buffers;"
psql -c "SHOW ALL;" | grep work_mem