451 lines
9.6 KiB
Markdown
451 lines
9.6 KiB
Markdown
# 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环境中成功部署和运行养老管理系统。
|