tar -xzf KingbaseES_V9_xxx.tar.gz
cd KingbaseES_V9_xxx

# 看一下目錄結構
ls -l
# setup.sh 是安裝腳本
# license.dat 是授權文件，沒這個裝不了

# 創(chuàng)建專用用戶
useradd -m kingbase
passwd kingbase

# 設置目錄權限
chown -R kingbase:kingbase /opt/Kingbase

# 靜默安裝示例
./setup.sh -i console \
  -d /opt/Kingbase/ES/V9 \
  -p server \
  -U system \
  -W "YourPassword123" \
  --license /opt/license.dat

# 切到 kingbase 用戶
su - kingbase

# 初始化數據目錄
initdb -D /data/kingbase/data \
  -U system \
  -W \
  --encoding=UTF8 \
  --locale=zh_CN.UTF-8

# 啟動服務
sys_ctl -D /data/kingbase/data start

# 確認服務狀態(tài)
sys_ctl -D /data/kingbase/data status

-- 通過 ksql 連接
-- ksql -U system -d test -p 54321

-- 改掉默認密碼
ALTER USER system WITH PASSWORD 'NewStrongP@ss2026';

-- 看一下當前數據庫列表
SELECT datname, datowner, encoding FROM sys_database;

-- 這幾個參數改完需要重啟
shared_buffers = 4GB
work_mem = 64MB
maintenance_work_mem = 512MB
effective_cache_size = 12GB

-- 連接數根據實際并發(fā)來定
max_connections = 200

-- 日志相關
logging_collector = on
log_directory = 'sys_log'
log_filename = 'kingbase-%Y-%m-%d.log'
log_min_duration_statement = 1000
log_rotation_age = 1d

# kingbase.conf
listen_addresses = '*'
port = 54321

# sys_hba.conf
# 允許內網段訪問
host  all  all  192.168.1.0/24  scram-sha-256

sys_ctl -D /data/kingbase/data reload

CREATE TABLE device_info (
    id          BIGSERIAL PRIMARY KEY,
    device_code VARCHAR(64) NOT NULL UNIQUE,
    device_name VARCHAR(200) NOT NULL,
    category    SMALLINT DEFAULT 1,
    specs       JSONB,
    tags        TEXT[],
    location    POINT,
    status      SMALLINT DEFAULT 1,
    created_at  TIMESTAMP DEFAULT now(),
    updated_at  TIMESTAMP DEFAULT now()
);

-- JSONB 類型特別適合存半結構化的配置信息
INSERT INTO device_info (device_code, device_name, specs)
VALUES (
    'DEV-20260101-001',
    '溫濕度傳感器-A3',
    '{"protocol": "MQTT", "interval": 30, "unit": "celsius"}'::jsonb
);

-- 查詢 JSONB 里的字段
SELECT device_code,
       specs->>'protocol' AS protocol,
       (specs->>'interval')::int AS report_interval
FROM device_info
WHERE specs ? 'protocol';

-- 搶不到鎖就立即返回錯誤，不傻等
SELECT * FROM task_queue
WHERE status = 0
FOR UPDATE NOWAIT;

-- 跳過已被其他事務鎖定的行，處理下一批
SELECT * FROM task_queue
WHERE status = 0
FOR UPDATE SKIP LOCKED
LIMIT 10;

-- 從 CSV 文件批量導入，速度比 INSERT 快得多
COPY device_info (device_code, device_name, category)
FROM '/data/import/devices.csv'
WITH (FORMAT csv, HEADER true, DELIMITER ',');

-- 如果是程序里拼接批量 INSERT，至少用多值語法
INSERT INTO device_info (device_code, device_name, category)
VALUES
    ('DEV-001', '溫度傳感器', 1),
    ('DEV-002', '壓力傳感器', 2),
    ('DEV-003', '流量計', 3);

-- 分批刪除過期數據
DO $$
DECLARE
    batch_size INT := 5000;
    deleted INT;
BEGIN
    LOOP
        DELETE FROM sensor_data
        WHERE ctid IN (
            SELECT ctid FROM sensor_data
            WHERE record_time < '2024-01-01'
            LIMIT batch_size
        );
        GET DIAGNOSTICS deleted = ROW_COUNT;
        COMMIT;
        EXIT WHEN deleted < batch_size;
        -- 暫停一下，給其他事務讓路
        PERFORM pg_sleep(0.5);
    END LOOP;
END $$;

-- 啟用三權分立需要修改配置
-- kingbase.conf 中設置
-- enable_sec_admin = on
-- 改完重啟生效

-- 查看當前角色和權限分配
SELECT rolname, rolsuper, rolcreatedb, rolcreaterole, rolcanlogin
FROM sys_roles
ORDER BY rolname;

-- 創(chuàng)建只讀用戶
CREATE ROLE readonly_user WITH LOGIN PASSWORD 'Read@2026';
GRANT CONNECT ON DATABASE production TO readonly_user;
GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

-- 設置默認權限，以后新建的表也自動有 SELECT 權限
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonly_user;

-- 創(chuàng)建應用用戶，只給增刪改查權限
CREATE ROLE app_user WITH LOGIN PASSWORD 'App@2026';
GRANT CONNECT ON DATABASE production TO app_user;
GRANT USAGE, CREATE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

-- 創(chuàng)建加密表空間
CREATE TABLESPACE encrypted_ts
LOCATION '/data/kingbase/encrypted'
WITH (encrypt_type = 'SM4');

-- 在加密表空間上建表
CREATE TABLE user_identity (
    id          BIGSERIAL PRIMARY KEY,
    user_name   VARCHAR(100) NOT NULL,
    id_card     VARCHAR(18) NOT NULL,
    phone       VARCHAR(20),
    created_at  TIMESTAMP DEFAULT now()
) TABLESPACE encrypted_ts;

-- 通過安全管理員配置審計策略
-- 記錄所有 DDL 操作
SELECT audit_set_rule('audit_ddl', 'on');

-- 記錄對敏感表的訪問
SELECT audit_set_object('public.user_identity', 'select,insert,update,delete');

-- 查看審計日志
SELECT event_time, username, event_type, object_name, statement
FROM sys_audit_log
WHERE event_time > now() - INTERVAL '1 day'
ORDER BY event_time DESC
LIMIT 50;

# 全量備份
sys_rman -F /data/kingbase/backup backup full

# 增量備份（基于上次全量或增量）
sys_rman -F /data/kingbase/backup backup incremental

# 查看備份集信息
sys_rman -F /data/kingbase/backup show

# 清理過期備份（保留最近 7 天）
sys_rman -F /data/kingbase/backup delete --older-than 7d

# 驗證備份完整性
sys_rman -F /data/kingbase/backup validate

# 恢復步驟概要
# 1. 停庫
sys_ctl -D /data/kingbase/data stop

# 2. 恢復基礎備份
sys_rman -F /data/kingbase/backup restore --target-time "2026-06-04 15:30:00"

# 3. 配置恢復目標
# 在 kingbase.auto.conf 中設置
# recovery_target_time = '2026-06-04 15:30:00'
# recovery_target_action = 'promote'

# 4. 啟動恢復
sys_ctl -D /data/kingbase/data start

archive_mode = on
archive_command = 'cp %p /data/kingbase/archive/%f'

# 備庫搭建：基于主庫做基礎備份
sys_basebackup -h primary_host -p 54321 -U replication_user \
  -D /data/kingbase/standby \
  -Fp -Xs -P

# 備庫配置 recovery 參數
# kingbase.auto.conf
primary_conninfo = 'host=primary_host port=54321 user=replication_user password=xxx'
hot_standby = on

-- 查看復制狀態(tài)
SELECT pid, usename, application_name, client_addr,
       state, sync_state, sent_lsn, write_lsn, flush_lsn, replay_lsn
FROM sys_stat_replication;

-- 確認 KWR 擴展已安裝
CREATE EXTENSION IF NOT EXISTS sys_kwr;

-- 手動創(chuàng)建一個快照
SELECT sys_kwr_snapshot();

-- 生成兩個快照之間的性能報告
-- 先查看快照 ID
SELECT snap_id, snap_time FROM sys_kwr_snapshots ORDER BY snap_id DESC LIMIT 10;

-- 生成報告（輸出到文件）
\o /tmp/kwr_report.html
SELECT sys_kwr_report(101, 105, 'html');
\o

-- 啟用 KSH
CREATE EXTENSION IF NOT EXISTS sys_ksh;

-- 查看最近 5 分鐘最耗時的等待事件
SELECT wait_event_type, wait_event, count(*)
FROM sys_ksh
WHERE sample_time > now() - INTERVAL '5 minutes'
GROUP BY 1, 2
ORDER BY 3 DESC
LIMIT 10;

-- 查看某段時間內執(zhí)行最慢的 SQL
SELECT query_id, query, calls, total_time, mean_time
FROM sys_ksh_statements
WHERE sample_time > now() - INTERVAL '1 hour'
ORDER BY mean_time DESC
LIMIT 10;

-- 當前活躍會話
SELECT pid, usename, application_name, client_addr,
       state, wait_event_type, wait_event,
       now() - query_start AS duration, query
FROM sys_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

-- 鎖等待分析
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;

-- 長時間未提交的事務（超過 5 分鐘）
SELECT pid, usename, now() - xact_start AS xact_duration, query
FROM sys_stat_activity
WHERE state = 'idle in transaction'
  AND now() - xact_start > INTERVAL '5 minutes'
ORDER BY xact_duration DESC;

idle_in_transaction_session_timeout = 300000  -- 5 分鐘，單位毫秒

#!/bin/bash
# db_check.sh - 簡單巡檢腳本
DATA_DIR="/data/kingbase/data"
KSQL="ksql -U system -d production -t -A -c"

echo "===== 磁盤使用 ====="
df -h $DATA_DIR

echo ""
echo "===== 數據庫大小 ====="
$KSQL "SELECT datname, sys_size_pretty(sys_database_size(datname))
       FROM sys_database ORDER BY sys_database_size(datname) DESC;"

echo ""
echo "===== TOP 10 大表 ====="
$KSQL "SELECT schemaname||'.'||relname AS table_name,
       sys_size_pretty(sys_total_relation_size(relid)) AS total_size,
       n_live_tup AS row_count
       FROM sys_stat_user_tables
       ORDER BY sys_total_relation_size(relid) DESC LIMIT 10;"

echo ""
echo "===== 未使用的索引 ====="
$KSQL "SELECT schemaname||'.'||indexrelname AS index_name,
       sys_size_pretty(sys_relation_size(indexrelid)) AS size,
       idx_scan AS scan_count
       FROM sys_stat_user_indexes
       WHERE idx_scan = 0
       ORDER BY sys_relation_size(indexrelid) DESC LIMIT 10;"

echo ""
echo "===== 表膨脹估算 ====="
$KSQL "SELECT schemaname||'.'||relname AS table_name,
       n_dead_tup AS dead_tuples,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_ratio
       FROM sys_stat_user_tables
       WHERE n_dead_tup > 10000
       ORDER BY n_dead_tup DESC LIMIT 10;"

-- 看看到底是哪些連接占著
SELECT state, count(*) FROM sys_stat_activity GROUP BY state;
-- 看各個來源 IP 的連接數
SELECT client_addr, count(*) FROM sys_stat_activity
WHERE client_addr IS NOT NULL
GROUP BY client_addr ORDER BY 2 DESC;

-- 也可以直接從視圖里查
SELECT query, calls, total_time, mean_time, rows
FROM sys_stat_statements
WHERE mean_time > 500
ORDER BY mean_time DESC
LIMIT 20;

-- 看執(zhí)行計劃
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT d.device_name, avg(s.temperature) 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;

-- 查看哪些表需要 VACUUM
SELECT schemaname||'.'||relname AS table_name,
       n_dead_tup AS dead_tuples,
       n_live_tup AS live_tuples,
       last_vacuum, last_autovacuum
FROM sys_stat_user_tables
WHERE n_dead_tup > 10000
  AND n_dead_tup > n_live_tup * 0.1
ORDER BY n_dead_tup DESC;

-- 手動 VACUUM 某個表（帶分析）
VACUUM ANALYZE sensor_data;

-- 看 autovacuum 是否正常工作
SELECT pid, query, now() - query_start AS duration
FROM sys_stat_activity
WHERE query LIKE 'autovacuum%'
ORDER BY duration DESC;