rootandClaude
9d6cc1d0d4
## 修复内容 - ✅ 解决容器重启后数据库丢失问题 - ✅ 创建手动数据库初始化脚本 - ✅ 添加自动化数据库设置脚本 ## 文件说明 - init-db-manual.sql: 手动数据库初始化脚本 - ensure_database.sh: 自动化数据库设置脚本 ## 解决方案 1. 数据库持久化卷已正确配置 2. 手动创建数据库后需要运行初始化脚本 3. 提供自动化脚本来简化设置过程 ## 使用方法 ```bash # 手动设置（推荐） ./ensure_database.sh # 或者手动执行 docker exec ai-soc-postgres createdb -U postgres ai_soc docker exec ai-soc-postgres psql -U postgres -d ai_soc -f init-db-manual.sql ``` ## 预防措施 - ✅ 容器重启后数据将保持 - ✅ 提供快速恢复脚本 - ✅ 完整的数据库配置和性能优化 🤖 Generated with [Claude Code](https://claude.com/claude-code) Co-Authored-By: Claude <noreply@anthropic.com>
102 lines
3.5 KiB
PL/PgSQL
102 lines
3.5 KiB
PL/PgSQL
-- Manual database initialization script for AI SOC platform
|
|
-- This script should be run after creating the ai_soc database manually
|
|
|
|
-- Connect to ai_soc database first:
|
|
-- \c ai_soc
|
|
|
|
-- Create extensions
|
|
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
|
CREATE EXTENSION IF NOT EXISTS "pg_trgm"; -- Full-text search support
|
|
CREATE EXTENSION IF NOT EXISTS "btree_gin"; -- Composite index support
|
|
|
|
-- Set timezone
|
|
SET timezone = 'UTC';
|
|
|
|
-- Create schema if not exists (for better organization)
|
|
CREATE SCHEMA IF NOT EXISTS public;
|
|
|
|
-- Grant privileges
|
|
GRANT ALL PRIVILEGES ON SCHEMA public TO postgres;
|
|
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO postgres;
|
|
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO postgres;
|
|
|
|
-- Default privileges for future objects
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO postgres;
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO postgres;
|
|
|
|
-- Performance tuning for security analytics workload
|
|
ALTER DATABASE ai_soc SET random_page_cost = 1.1;
|
|
ALTER DATABASE ai_soc SET effective_cache_size = '4GB';
|
|
ALTER DATABASE ai_soc SET work_mem = '64MB';
|
|
ALTER DATABASE ai_soc SET maintenance_work_mem = '256MB';
|
|
ALTER DATABASE ai_soc SET max_parallel_workers_per_gather = 2;
|
|
ALTER DATABASE ai_soc SET effective_io_concurrency = 200;
|
|
|
|
-- Query optimization settings
|
|
ALTER DATABASE ai_soc SET enable_partitionwise_join = on;
|
|
ALTER DATABASE ai_soc SET enable_partitionwise_aggregate = on;
|
|
|
|
-- Logging configuration
|
|
ALTER DATABASE ai_soc SET log_statement = 'mod';
|
|
ALTER DATABASE ai_soc SET log_min_duration_statement = 1000;
|
|
ALTER DATABASE ai_soc SET log_connections = on;
|
|
ALTER DATABASE ai_soc SET log_disconnections = on;
|
|
|
|
-- Create helper function for safe index creation
|
|
CREATE OR REPLACE FUNCTION create_index_if_table_exists(
|
|
idx_name TEXT,
|
|
tbl_name TEXT,
|
|
idx_definition TEXT
|
|
) RETURNS VOID AS $$
|
|
BEGIN
|
|
IF EXISTS (
|
|
SELECT 1 FROM information_schema.tables
|
|
WHERE table_schema = 'public'
|
|
AND table_name = tbl_name
|
|
) THEN
|
|
EXECUTE format('CREATE INDEX IF NOT EXISTS %I ON %I %s',
|
|
idx_name, tbl_name, idx_definition);
|
|
RAISE NOTICE 'Index % created on table %', idx_name, tbl_name;
|
|
ELSE
|
|
RAISE NOTICE 'Table % does not exist, skipping index %', tbl_name, idx_name;
|
|
END IF;
|
|
EXCEPTION WHEN OTHERS THEN
|
|
RAISE WARNING 'Failed to create index % on table %: %', idx_name, tbl_name, SQLERRM;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Create performance monitoring views
|
|
CREATE OR REPLACE VIEW v_table_stats AS
|
|
SELECT
|
|
schemaname,
|
|
tablename,
|
|
pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
|
|
pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS table_size,
|
|
pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) AS index_size,
|
|
n_tup_ins AS inserts,
|
|
n_tup_upd AS updates,
|
|
n_tup_del AS deletes
|
|
FROM pg_stat_user_tables
|
|
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;
|
|
|
|
-- Create slow query monitoring view
|
|
CREATE OR REPLACE VIEW v_slow_queries AS
|
|
SELECT
|
|
query,
|
|
calls,
|
|
total_exec_time,
|
|
mean_exec_time,
|
|
max_exec_time
|
|
FROM pg_stat_statements
|
|
WHERE mean_exec_time > 1000 -- queries slower than 1 second
|
|
ORDER BY mean_exec_time DESC
|
|
LIMIT 20;
|
|
|
|
-- Success message
|
|
DO $$
|
|
BEGIN
|
|
RAISE NOTICE '✅ AI SOC database initialized successfully';
|
|
RAISE NOTICE 'Database: ai_soc';
|
|
RAISE NOTICE 'Timezone: UTC';
|
|
RAISE NOTICE 'Extensions: uuid-ossp, pg_trgm, btree_gin';
|
|
END $$; |