PITR在 KES 里叫Zuo时间点恢复，需要持续归档 WAL 日志。

# 开启归档
sed -i 's/#archive_mode = off/archive_mode = on/' $DATA_DIR/kingbase.conf
sed -i "s|#archive_command = ''|archive_command = 'cp %p /data/kingbase/archive/%f'|" $DATA_DIR/kingbase.conf
sys_ctl -D $DATA_DIR reload
# 全量备份
sys_rman -F $BACKUP_DIR backup full
# 增量备份
sys_rman -F $BACKUP_DIR backup incremental
# 验证备份完整性
sys_rman -F $BACKUP_DIR validate

我个人推荐的备份频率是：每周全量 + 每天增量 + 每小时增量快照。记住要定期演练恢复，不然真出事你连怎么恢复dou不知道——我有一次现场演练时差点把生产库给删了还好及时止损。

KWR 相当于 Oracle 的 AWR，每隔一段时间自动采集快照。KSH 则geng细粒度，Ke以实时查kan等待事件。

CREATE EXTENSION IF NOT EXISTS sys_kwr;
SELECT sys_kwr_snapshot;   -- 生成快照
-- 查kanZui近两个快照之间的报告
SELECT sys_kwr_report INTO OUTFILE '/tmp/kwr_report.html';

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 wait_duration
FROM sys_stat_activity blocked
JOIN sys_locks l ON blocked.pid = l.pid AND NOT l.granted
JOIN sys_locks granted ON l.locktype = granted.locktype
                     AND l.database IS NOT DISTINCT FROM granted.database
                     AND l.relation IS NOT DISTINCT FROM granted.relation
                     AND granted.granted = true
JOIN sys_stat_activity blocking ON granted.pid = blocking.pid
WHERE blocked.pid <> blocking.pid;

#!/bin/bash
DATA_DIR="/data/kingbase/data"
KSQL="ksql -U system -d production -t -A -c"
echo "===== 磁盘使用 ====="
df -h $DATA_DIR | grep '^/dev'
echo "===== 数据库大小 ====="
$KSQL "SELECT datname, sys_size_pretty) FROM sys_database ORDER BY sys_database_size DESC;"
echo "===== TOP 大表 ====="
$KSQL "SELECT schemaname||'.'||relname AS table_name,
            sys_size_pretty) AS total_size,
            n_live_tup AS rows FROM sys_stat_user_tables ORDER BY sys_total_relation_size DESC LIMIT 10;"
echo "===== 长事务  ====="
$KSQL "SELECT pid, usename, now - xact_start AS duration, query FROM sys_stat_activity WHERE state='idle in transaction' AND now - xact_start> INTERVAL '5 minutes' ORDER BY duration DESC;" 

把这个脚本放进 crontab，每天凌晨跑一次把结果邮件发给 DBA 和安全负责人，就Neng提前发现磁盘爆满、死锁或长事务等问题。说实话，这招省了不少夜班加班时间。

其实原因hen简单：百度搜索geng偏爱有外链、有权威来源的内容，而我们这里没有任何外部链接，也没有官方备案信息。再者，这篇文章带点口语化、随意式风格，对搜索引擎来说可Neng被判定为“低质量”。不过呢，这正是它的价值所在——真实、实战、接地气，真正帮助运维同学解决问题，而不是堆砌关键词。你懂的，就是这么回事儿。

慢查询 要打开日志阈值：

ALTER SYSTEM SET log_min_duration_statement = 500; -- 单位毫秒
SELECT pg_reload_conf; 

然后通过日志定位慢 SQL，用 EXPLAIN ANALYZE kan执行计划：

EXPLAIN  SELECT d.device_name,
       avg AS avg_temp
FROM sensor_data s 
JOIN device_info d ON s.device_id = d.id 
WHERE s.record_time> now - INTERVAL '7 days'
GROUP BY d.device_name 
ORDER BY avg_temp DESC;

A/B 测试发现，Ru果缺少对应索引，上面的查询会走全表扫描，一分钟才Neng返回结果；加上复合索引后瞬间降到几百毫秒。记住一条索引Neng拯救千行代码的性Neng痛苦。

我曾经碰到一个业务模块同时geng新订单表和库存表，但顺序相反导致死锁频繁出现。解决办法hen直接：统一业务层面的geng新顺序，让所有模块dou先geng新订单再geng新库存。这样锁竞争就大幅降低了。还有一种办法，就是在业务代码里加上 / 防止阻塞：

SELECT * FROM task_queue WHERE status='pending' FOR UPDATE NOWAIT;

# 主库开启流复制参数示例
sed -i 's/#wal_level = replica/wal_level = logical/' $DATA_DIR/kingbase.conf
sed -i 's/#max_wal_senders = 10/max_wal_senders = 5/' $DATA_DIR/kingbase.conf
sed -i 's/#wal_keep_segments = 0/wal_keep_segments = 64/' $DATA_DIR/kingbase.conf
# 在 standby 上配置 recovery 参数
cat>> $STANDBY_DATA/recovery.conf 

CREATE TABLE deviceinfo (
    id BIGSERIAL PRIMARY KEY,
    devicecode VARCHAR NOT NULL UNIQUE,
    specs JSONB NOT NULL,
    tags TEXT
);

INSERT INTO deviceinfo 
VALUES ('DEV-001','{"protocol":"MQTT","interval":30,"unit":"celsius"}'::jsonb,
        ARRAY);
-- 查询 JSON 字段中的协议类型：
SELECT devicecode,specs->'protocol' AS protocol FROM device_info WHERE specs ? 'protocol'; 

-- 查kan哪些表需要手动 VACUUM
SELECT schemaname||'.'||relname AS tablename,
       ndeadtup AS deadrows,
       round,1) AS deadratio
FROM sysstatusertables
WHERE ndeadtup> 100000
ORDER BY dead_ratio DESC;

-- 手动执行 VACUUM 并统计耗时
VACUUM ANALYZE sensor_data; 

ALTER SYSTEM SET autovacuumvacuumscalefactor = 0.02;
ALTER SYSTEM SET autovacuumanalyzescalefactor = 0.01;
SELECT pgreloadconf;