akmon/doc_eldercare/eldercare_postgresql_deployment_guide.md

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. 触发器和函数

• ✅ 自动更新时间戳触发器
• ✅ 自动生成老人编号
• ✅ 健康预警自动生成
• ✅ 入住率自动计算

部署步骤

第一步：环境准备

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

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

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

第二步：执行SQL脚本

-- 在PostgreSQL中执行
\i eldercare_system_database_postgresql.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_%';

第四步：配置用户认证

-- 方法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;

第五步：插入测试数据

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

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

性能优化配置

1. PostgreSQL配置优化

-- 内存配置
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. 索引监控

-- 检查索引使用情况
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. 查询性能分析

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

数据维护

1. 定期清理

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

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

2. 数据完整性检查

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

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

3. 统计信息更新

-- 更新表统计信息
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. 逻辑备份

# 备份整个数据库
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. 物理备份

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

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

监控和告警

1. 系统监控

-- 监控连接数
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. 业务监控

-- 创建监控视图
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. 用户权限管理

-- 创建只读用户
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配置

-- 在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. 审计日志

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

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

故障排除

1. 常见问题

问题： RLS策略阻止数据访问

-- 临时禁用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;

问题： 触发器执行失败

-- 检查触发器状态
SELECT 
    trigger_name,
    event_object_table,
    trigger_schema,
    action_statement
FROM information_schema.triggers
WHERE trigger_schema = 'public'
AND event_object_table LIKE 'ec_%';

问题： 性能问题

-- 检查慢查询
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. 日志分析

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

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

升级和维护

1. 版本升级

# 使用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. 架构变更

-- 添加新列示例
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. 功能测试

-- 测试老人数据插入
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. 性能测试

-- 测试查询性能
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环境中成功部署和运行养老管理系统。