# PostgreSQL 养老系统数据库部署指南

## 概述
本文档提供了PostgreSQL兼容版本的养老管理系统数据库的部署和配置指南。

## PostgreSQL版本要求
- **最低版本：** PostgreSQL 13+
- **推荐版本：** PostgreSQL 15+
- **必需扩展：** uuid-ossp, pgcrypto

## 主要改进

### 1. PostgreSQL特性兼容
- ✅ 使用 `CURRENT_TIMESTAMP` 替代 `now()`
- ✅ 使用 `CREATE TABLE IF NOT EXISTS` 避免重复创建
- ✅ 使用 `ON CONFLICT DO NOTHING` 处理重复插入
- ✅ 添加数据约束检查（CHECK constraints）
- ✅ 使用PostgreSQL原生UUID生成函数

### 2. RLS策略优化
- ✅ 创建 `get_current_user_id()` 函数替代 `auth.uid()`
- ✅ 支持session变量获取用户ID
- ✅ 添加错误处理和默认值

### 3. 数据完整性增强
- ✅ 添加CHECK约束验证数据范围
- ✅ 外键级联删除配置
- ✅ 唯一约束和索引优化
- ✅ 时间顺序验证约束

### 4. 触发器和函数
- ✅ 自动更新时间戳触发器
- ✅ 自动生成老人编号
- ✅ 健康预警自动生成
- ✅ 入住率自动计算

## 部署步骤

### 第一步：环境准备

```bash
# 1. 检查PostgreSQL版本
psql --version

# 2. 连接到数据库
psql -h localhost -U your_user -d your_database

# 3. 检查扩展可用性
\dx
```

### 第二步：执行SQL脚本

```sql
-- 在PostgreSQL中执行
\i eldercare_system_database_postgresql.sql
```

### 第三步：验证安装

```sql
-- 检查系统状态
SELECT * FROM eldercare_system_status();

-- 检查表创建
\dt ec_*

-- 检查索引
\di idx_ec_*

-- 检查触发器
\df *elder*

-- 检查RLS策略
SELECT schemaname, tablename, policyname, permissive, roles, cmd, qual
FROM pg_policies 
WHERE tablename LIKE 'ec_%';
```

### 第四步：配置用户认证

```sql
-- 方法1：使用session变量
-- 在应用连接时设置用户ID
SET app.current_user_id = 'your-user-uuid';

-- 方法2：修改get_current_user_id函数
-- 根据实际认证系统调整函数实现
CREATE OR REPLACE FUNCTION get_current_user_id()
RETURNS uuid AS $$
BEGIN
    -- 示例：从JWT token获取用户ID
    RETURN (current_setting('jwt.claims.user_id', true))::uuid;
    -- 或从其他认证源获取
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
```

### 第五步：插入测试数据

```sql
-- 插入示例数据
SELECT insert_sample_eldercare_data();

-- 验证数据
SELECT * FROM public.ec_facilities;
```

## 性能优化配置

### 1. PostgreSQL配置优化

```sql
-- 内存配置
shared_buffers = '256MB'                # 25% of RAM
effective_cache_size = '1GB'            # 75% of RAM
work_mem = '4MB'                        # Per connection
maintenance_work_mem = '64MB'

-- 连接配置
max_connections = 100
listen_addresses = '*'

-- 日志配置
log_statement = 'mod'
log_duration = on
log_min_duration_statement = 1000
```

### 2. 索引监控

```sql
-- 检查索引使用情况
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes 
WHERE schemaname = 'public' 
AND tablename LIKE 'ec_%'
ORDER BY idx_scan DESC;

-- 检查未使用的索引
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan
FROM pg_stat_user_indexes 
WHERE idx_scan = 0 
AND schemaname = 'public'
AND tablename LIKE 'ec_%';
```

### 3. 查询性能分析

```sql
-- 分析慢查询
SELECT 
    query,
    calls,
    total_time,
    mean_time,
    rows
FROM pg_stat_statements 
WHERE query LIKE '%ec_%'
ORDER BY mean_time DESC
LIMIT 10;
```

## 数据维护

### 1. 定期清理

```sql
-- 设置定期清理任务
SELECT cron.schedule('eldercare-cleanup', '0 2 * * *', 'SELECT cleanup_expired_data();');

-- 手动执行清理
SELECT cleanup_expired_data();
```

### 2. 数据完整性检查

```sql
-- 执行完整性检查
SELECT check_elder_data_integrity();

-- 查看约束违反
SELECT conname, conrelid::regclass
FROM pg_constraint
WHERE NOT convalidated;
```

### 3. 统计信息更新

```sql
-- 更新表统计信息
ANALYZE public.ec_elders;
ANALYZE public.ec_care_tasks;
ANALYZE public.ec_vital_signs;

-- 自动更新配置
ALTER TABLE public.ec_elders SET (autovacuum_analyze_scale_factor = 0.05);
```

## 备份和恢复

### 1. 逻辑备份

```bash
# 备份整个数据库
pg_dump -h localhost -U your_user -d your_database -f eldercare_backup.sql

# 仅备份养老系统表
pg_dump -h localhost -U your_user -d your_database -t 'ec_*' -f eldercare_tables_backup.sql

# 恢复数据库
psql -h localhost -U your_user -d your_database -f eldercare_backup.sql
```

### 2. 物理备份

```bash
# 使用pg_basebackup
pg_basebackup -h localhost -D /backup/postgresql -U replication_user -W

# 配置连续归档
archive_mode = on
archive_command = 'cp %p /backup/archive/%f'
```

## 监控和告警

### 1. 系统监控

```sql
-- 监控连接数
SELECT count(*) as active_connections
FROM pg_stat_activity
WHERE state = 'active';

-- 监控锁等待
SELECT 
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query AS blocked_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;
```

### 2. 业务监控

```sql
-- 创建监控视图
CREATE OR REPLACE VIEW public.vw_eldercare_monitoring AS
SELECT 
    'active_elders' as metric_name,
    COUNT(*) as metric_value,
    CURRENT_TIMESTAMP as measured_at
FROM public.ec_elders WHERE status = 'active'
UNION ALL
SELECT 
    'pending_tasks',
    COUNT(*),
    CURRENT_TIMESTAMP
FROM public.ec_care_tasks WHERE status = 'pending'
UNION ALL
SELECT 
    'active_alerts',
    COUNT(*),
    CURRENT_TIMESTAMP
FROM public.ec_health_alerts WHERE status = 'active';
```

## 安全配置

### 1. 用户权限管理

```sql
-- 创建只读用户
CREATE USER eldercare_readonly WITH PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE your_database TO eldercare_readonly;
GRANT USAGE ON SCHEMA public TO eldercare_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO eldercare_readonly;

-- 创建应用用户
CREATE USER eldercare_app WITH PASSWORD 'app_password';
GRANT CONNECT ON DATABASE your_database TO eldercare_app;
GRANT USAGE ON SCHEMA public TO eldercare_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO eldercare_app;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO eldercare_app;
```

### 2. SSL配置

```sql
-- 在postgresql.conf中启用SSL
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'ca.crt'

-- 在pg_hba.conf中要求SSL连接
hostssl all eldercare_app 0.0.0.0/0 md5
```

### 3. 审计日志

```sql
-- 启用审计扩展（如果可用）
CREATE EXTENSION IF NOT EXISTS pgaudit;

-- 配置审计
ALTER SYSTEM SET pgaudit.log = 'write,ddl';
SELECT pg_reload_conf();
```

## 故障排除

### 1. 常见问题

**问题：** RLS策略阻止数据访问
```sql
-- 临时禁用RLS（仅用于调试）
ALTER TABLE public.ec_elders DISABLE ROW LEVEL SECURITY;

-- 检查当前用户ID设置
SELECT get_current_user_id();

-- 重新启用RLS
ALTER TABLE public.ec_elders ENABLE ROW LEVEL SECURITY;
```

**问题：** 触发器执行失败
```sql
-- 检查触发器状态
SELECT 
    trigger_name,
    event_object_table,
    trigger_schema,
    action_statement
FROM information_schema.triggers
WHERE trigger_schema = 'public'
AND event_object_table LIKE 'ec_%';
```

**问题：** 性能问题
```sql
-- 检查慢查询
SELECT 
    query,
    calls,
    total_time/calls as avg_time_ms
FROM pg_stat_statements
WHERE query LIKE '%ec_%'
ORDER BY total_time DESC
LIMIT 5;
```

### 2. 日志分析

```bash
# 分析PostgreSQL日志
tail -f /var/log/postgresql/postgresql.log | grep "ec_"

# 使用pgbadger分析日志
pgbadger /var/log/postgresql/postgresql.log -o report.html
```

## 升级和维护

### 1. 版本升级

```bash
# 使用pg_upgrade升级
pg_upgrade -b /old/version/bin -B /new/version/bin -d /old/data -D /new/data

# 或使用pg_dump/restore方式
pg_dump old_database | psql new_database
```

### 2. 架构变更

```sql
-- 添加新列示例
ALTER TABLE public.ec_elders 
ADD COLUMN IF NOT EXISTS emergency_contact_phone VARCHAR(32);

-- 创建迁移脚本
CREATE OR REPLACE FUNCTION migrate_eldercare_schema_v2()
RETURNS VOID AS $$
BEGIN
    -- 添加新功能...
    RAISE NOTICE 'Schema migration completed';
END;
$$ LANGUAGE plpgsql;
```

## 测试验证

### 1. 功能测试

```sql
-- 测试老人数据插入
DO $$
DECLARE
    test_elder_id uuid;
BEGIN
    INSERT INTO public.ec_elders (name, gender, birthday, facility_id)
    VALUES ('测试老人', 'male', '1950-01-01', 
            (SELECT id FROM public.ec_facilities LIMIT 1))
    RETURNING id INTO test_elder_id;
    
    RAISE NOTICE '测试老人创建成功，ID: %', test_elder_id;
    
    -- 清理测试数据
    DELETE FROM public.ec_elders WHERE id = test_elder_id;
END $$;
```

### 2. 性能测试

```sql
-- 测试查询性能
EXPLAIN (ANALYZE, BUFFERS) 
SELECT e.*, f.name as facility_name
FROM public.ec_elders e
JOIN public.ec_facilities f ON e.facility_id = f.id
WHERE e.status = 'active';
```

## 结论

PostgreSQL版本的养老管理系统数据库提供了：
- ✅ 完整的PostgreSQL兼容性
- ✅ 增强的数据完整性约束
- ✅ 优化的性能和索引
- ✅ 完善的安全策略
- ✅ 自动化的维护功能

通过本指南的配置，您可以在PostgreSQL环境中成功部署和运行养老管理系统。