# ✅ 正确: 参数化查询
query = """
    MATCH (p:Person {name: $name})
    RETURN p.age, p.city
"""
params = {'name': 'Alice'}
result = execute_cypher(query, params)

# ❌ 错误: 字符串拼接（有注入风险）
query = f"""
    MATCH (p:Person {{name: '{user_input}'}})
    RETURN p.age, p.city
"""

-- 查看索引使用情况
SELECT
    indexrelname AS index_name,
    idx_scan AS scans,
    idx_tup_read AS tuples_read,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

-- 查找未使用的索引（可能需要删除）
SELECT
    indexrelname,
    idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid)) AS wasted_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%pkey%'
ORDER BY pg_relation_size(indexrelid) DESC;

-- 创建只读用户
CREATE USER readonly_user WITH PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE agens TO readonly_user;
GRANT USAGE ON SCHEMA social_network TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA social_network TO readonly_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA social_network
  GRANT SELECT ON TABLES TO readonly_user;

-- 创建应用用户（最小权限原则）
CREATE USER app_user WITH PASSWORD 'app_secure_password';
GRANT CONNECT ON DATABASE agens TO app_user;
GRANT USAGE ON SCHEMA social_network TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA social_network TO app_user;

-- 创建管理员用户
CREATE USER admin_user WITH PASSWORD 'admin_secure_password' SUPERUSER;

# agens.conf
ssl = on
ssl_cert_file = '/etc/ssl/server.crt'
ssl_key_file = '/etc/ssl/server.key'
ssl_ca_file = '/etc/ssl/ca.crt'
ssl_crl_file = '/etc/ssl/root.crl'
ssl_min_protocol_version = 'TLSv1.2'
ssl_ciphers = 'HIGH:!aNULL:!MD5'

# 生成自签名证书（测试环境）
openssl req -new -x509 -days 365 -nodes \
  -text -out server.crt \
  -keyout server.key \
  -subj "/CN=agensgraph.local"

# 设置权限
chmod 600 server.key
chown agens:agens server.*

#!/bin/bash
# verify_backup.sh - 备份验证脚本

BACKUP_FILE=$1
VERIFY_PORT=5433

echo "=== Backup Verification ==="

# 1. 恢复到临时实例
docker run -d --name ag_verify \
  -p ${VERIFY_PORT}:5432 \
  -e AG_PASSWORD=verify123 \
  bitnine/agensgraph:v2.13

sleep 10

# 2. 恢复备份
pg_restore -h localhost -p ${VERIFY_PORT} -U agens -d agens ${BACKUP_FILE}

# 3. 数据完整性检查
TABLE_COUNT=$(psql -h localhost -p ${VERIFY_PORT} -U agens -t -c "
  SELECT count(*) FROM information_schema.tables
  WHERE table_schema = 'public';
")

TOTAL_ROWS=$(psql -h localhost -p ${VERIFY_PORT} -U agens -t -c "
  SELECT sum(n_live_tup) FROM pg_stat_user_tables;
")

echo "Tables: ${TABLE_COUNT}"
echo "Total rows: ${TOTAL_ROWS}"

# 4. 基本查询测试
psql -h localhost -p ${VERIFY_PORT} -U agens -c "
  SET graph_path = social_network;
  MATCH (p:Person) RETURN count(p);
"

# 5. 清理
docker stop ag_verify && docker rm ag_verify

echo "=== Verification Complete ==="

# prometheus/alert_rules.yml
groups:
  - name: agensgraph_alerts
    rules:
      - alert: AgensGraphDown
        expr: pg_up == 0
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: "AgensGraph instance is down"

      - alert: HighDiskUsage
        expr: (node_filesystem_avail_bytes{mountpoint="/"} / node_filesystem_size_bytes{mountpoint="/"}) < 0.1
        for: 5m
        labels:
          severity: critical
        annotations:
          summary: "Disk usage above 90%"

      - alert: LowBufferHitRatio
        expr: pg_stat_database_blks_hit_ratio < 0.95
        for: 10m
        labels:
          severity: warning
        annotations:
          summary: "Buffer hit ratio below 95%"

      - alert: HighReplicationLag
        expr: pg_stat_replication_lag > 30
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "Replication lag above 30 seconds"

      - alert: TooManyConnections
        expr: pg_stat_activity_count > (pg_settings_max_connections * 0.8)
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "Connection count above 80% of max"

# docker-compose.dev.yml
version: '3.8'

services:
  agensgraph:
    image: bitnine/agensgraph:v2.13
    ports:
      - "5432:5432"
    environment:
      AG_PASSWORD: dev123
    volumes:
      - ./data:/home/agens/AgensGraph/data
      - ./migrations:/docker-entrypoint-initdb.d
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U agens"]
      interval: 5s
      timeout: 3s
      retries: 5

  # 每个开发者使用独立端口
  # developer1: 5432
  # developer2: 5433
  # developer3: 5434

# 图模型文档模板

## 1. 概述
- 图名称: social_network
- 用途: 社交网络关系管理
- 负责人: 张三
- 创建时间: 2024-01-01

## 2. 顶点定义
| 标签 | 属性 | 约束 | 说明 |
|------|------|------|------|
| Person | id, name, email, age | id UNIQUE | 用户 |
| Company | id, name, industry | id UNIQUE | 公司 |

## 3. 边定义
| 类型 | 起点 | 终点 | 属性 | 说明 |
|------|------|------|------|------|
| KNOWS | Person | Person | since, context | 好友关系 |
| WORKS_AT | Person | Company | position, since | 工作关系 |

## 4. 索引
| 名称 | 类型 | 说明 |
|------|------|------|
| person_name_idx | B-Tree | Person.name 查询 |
| person_email_idx | B-Tree | Person.email 查询 |

## 5. 变更记录
| 日期 | 版本 | 变更内容 | 操作人 |
|------|------|---------|--------|
| 2024-01-01 | v1.0 | 初始版本 | 张三 |
| 2024-02-15 | v1.1 | 添加 Company 标签 | 李四 |

# 故障处理流程

## P0 故障（服务不可用）
1. 确认故障范围和影响
2. 启动应急预案
3. 通知相关方
4. 执行故障恢复
5. 验证服务恢复
6. 故障复盘

## P1 故障（性能下降）
1. 检查监控指标
2. 定位瓶颈（CPU/内存/IO/锁）
3. 应急优化（杀慢查询/扩连接）
4. 根因分析
5. 制定长期优化方案

## P2 故障（功能异常）
1. 收集错误信息
2. 定位问题代码/配置
3. 修复并测试
4. 部署上线
5. 验证恢复