PostgreSQL 运维要点：安装配置、备份与连接排障

sudo apt install postgresql-16        # 版本号跟随发行版仓库
sudo -u postgres psql -c "ALTER USER postgres PASSWORD :'pw';"  # 管理员先设口令
systemctl status postgresql@16-main   # 确认实例服务状态

# /etc/postgresql/16/main/pg_hba.conf（地址段为文档示例，按实际网段替换）
# 本机管理：peer
local   all   postgres              peer
# 应用网段：强制 scram + 要求 TLS
hostssl all   blog_app  10.0.0.0/8   scram-sha-256
# 运维跳板网段：scram
hostssl all   ops_rw    10.0.0.0/8   scram-sha-256
# 拒绝其余一切（显式兜底，防止上方规则被误删后裸奔）
host    all   all       0.0.0.0/0    reject

-- 观察排序/哈希有没有溢出到磁盘：溢出说明 work_mem 不够（但先改查询再改内存）
SELECT query, calls, temp_blks_written
FROM pg_stat_statements
ORDER BY temp_blks_written DESC LIMIT 5;

-- 只读角色：报表、运维查询、误操作护栏
CREATE ROLE blog_ro WITH LOGIN PASSWORD :'pw';
GRANT CONNECT ON DATABASE blog TO blog_ro;
GRANT USAGE ON SCHEMA public TO blog_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO blog_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO blog_ro;      -- 未来新建的表自动可读

-- 应用角色：仅业务 schema，收回危险默认权限
CREATE ROLE blog_app WITH LOGIN PASSWORD :'pw';
REVOKE CREATE ON SCHEMA public FROM PUBLIC;  -- 阻止任意角色建对象污染 schema
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO blog_app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO blog_app;  -- 序列要单独授

-- 列出角色在某库能做的敏感操作面
SELECT grantee, privilege_type, table_schema, table_name
FROM information_schema.role_table_grants
WHERE grantee IN ('blog_app', 'blog_ro')
ORDER BY grantee, table_name;

# 单库备份：自定义格式 + 并行恢复的基础
pg_dump -Fc -d blog -f /backup/blog_$(date +%F).dump

# 大库用目录格式：备份阶段就并行（前提是并行会话数，-j 数值）
pg_dump -Fd -j 4 -d blog -f /backup/blog_$(date +%F).dir

# 表级恢复：从自定义格式里只捞一张表（这就是选 -Fc 的理由）
pg_restore -d blog_restore --table=articles blog_2026-10-22.dump

# 库级全量恢复：-C 顺带建库，-j 并行导入
pg_restore -d postgres -C -j 4 /backup/blog_2026-10-22.dump

-- 前置参数（改后重启）：至少 replica 级别才记录完整 WAL
ALTER SYSTEM SET wal_level = 'replica';
ALTER SYSTEM SET archive_mode = 'on';
ALTER SYSTEM SET archive_command =
  'test ! -f /archive/%f && cp %p /archive/%f';   -- 归档命令，失败必须返回非零

# 基础备份：物理拷贝数据目录 + 记录 WAL 位点
pg_basebackup -D /backup/base/$(date +%F) -X stream -P \
  --max-rate=32768 -h db.internal -U backup_user

# 恢复到目标时刻：数据目录摆好后，配置恢复目标再启动
# （示意，参数在 recovery 配置 / restore_command 中给出）
# restore_command = 'cp /archive/%f %p'
# recovery_target_time = '2026-10-22 10:23:45+08'
# recovery_target_action = 'promote'

-- 死元组与表膨胀情况的快速体检
SELECT relname, n_dead_tup, n_live_tup,
       round(n_dead_tup*100.0/NULLIF(n_live_tup+n_dead_tup,0)) AS dead_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 10;

-- 最老的事务：膨胀排查第一眼
SELECT pid, state, now() - xact_start AS xact_age,
       now() - state_change AS state_age, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start LIMIT 5;

-- 表级覆盖示例：高频更新的表更积极地回收（按表决策，不要全局调小）
ALTER TABLE sessions SET (autovacuum_vacuum_scale_factor = 0.02,
                          autovacuum_vacuum_threshold = 1000);

SELECT usename, state, count(*) AS conns
FROM pg_stat_activity
GROUP BY 1, 2 ORDER BY conns DESC;

-- 找出空闲事务并处理（kill 的是数据库侧会话，进程还活着只是断连）
SELECT pid, usename, now()-state_change AS idle_for, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND state_change < now() - interval '15 minutes';

-- 给未来兜底：空闲事务 15 分钟自动断开（对应用是连接错误，配合应用重试与事务纪律）
ALTER SYSTEM SET idle_in_transaction_session_timeout = '15min';
SELECT pg_reload_conf();

-- 按 application_name 分组看连接年龄分布：正常的池化连接应该年轻且稳定
SELECT application_name, client_addr, count(*) AS conns,
       min(backend_start) AS oldest_conn
FROM pg_stat_activity WHERE pid <> pg_backend_pid()
GROUP BY 1, 2 ORDER BY conns DESC;

-- 一次性开启：预载入库 + 建扩展（需要重启一次才生效 preload）
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
-- 重启后：
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 巡检三连：最耗时 / 最耗 IO / 临时溢出
SELECT round(total_exec_time::numeric,1) AS ms, calls, left(query, 80) AS q
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;

SELECT calls, round((shared_blks_read*8)/1024.0) AS read_mb, left(query, 80) AS q
FROM pg_stat_statements ORDER BY shared_blks_read DESC LIMIT 5;

SELECT round(temp_blks_written*8/1024.0) AS spill_mb, left(query, 80) AS q
FROM pg_stat_statements WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC LIMIT 5;

# pg_upgrade 的干跑：先做 --check，能提前暴露所有阻断项
/usr/lib/postgresql/16/bin/pg_upgrade \
  --old-datadir /var/lib/postgresql/14/main \
  --new-datadir /var/lib/postgresql/16/main \
  --old-bindir /usr/lib/postgresql/14/bin \
  --new-bindir /usr/lib/postgresql/16/bin --check