数据库配置
本文档介绍如何配置PostgreSQL数据库以支持学习追踪系统。
📋 系统要求
- PostgreSQL: 12.0+
- 内存: 2GB RAM (推荐4GB+)
- 存储: 10GB+ 可用空间
🚀 安装PostgreSQL
Ubuntu/Debian
bash
sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql
sudo systemctl enable postgresqlCentOS/RHEL
bash
sudo yum install postgresql-server postgresql-contrib
sudo postgresql-setup initdb
sudo systemctl start postgresql
sudo systemctl enable postgresqlmacOS
bash
brew install postgresql
brew services start postgresql👤 创建数据库用户
bash
sudo -u postgres psql
CREATE USER study_tracker WITH PASSWORD 'your_password';
CREATE DATABASE study_tracker_dev OWNER study_tracker;
CREATE DATABASE study_tracker_prod OWNER study_tracker;
GRANT ALL PRIVILEGES ON DATABASE study_tracker_dev TO study_tracker;
GRANT ALL PRIVILEGES ON DATABASE study_tracker_prod TO study_tracker;
\q⚙️ 数据库配置
postgresql.conf 主要设置
conf
listen_addresses = '*'
port = 5432
max_connections = 200
shared_buffers = 256MB
effective_cache_size = 1GB
work_mem = 4MB
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'pg_hba.conf 访问控制
conf
local all postgres peer
local all all md5
host all all your-server-ip/32 md5
host all all ::1/128 md5🔧 项目环境变量
创建 .env 文件：
bash
# 数据库配置
DB_HOST=your-server-ip
DB_PORT=5432
DB_USER=study_tracker
DB_PASSWORD=your_password
DB_NAME=study_tracker_dev
# 连接池配置
DB_POOL_MIN=2
DB_POOL_MAX=10
DB_ACQUIRE_TIMEOUT=30000
DB_CREATE_TIMEOUT=30000
DB_IDLE_TIMEOUT=30000📊 数据库迁移
bash
# 安装依赖
npm install
# 运行迁移
npm run db:migrate
# 运行种子数据
npm run db:seed🔍 性能优化
重要索引
sql
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_study_projects_user_id ON study_projects(user_id);
CREATE INDEX idx_study_sessions_user_id ON study_sessions(user_id);
CREATE INDEX idx_study_sessions_study_date ON study_sessions(study_date);
CREATE INDEX idx_user_achievements_user_id ON user_achievements(user_id);性能监控
sql
-- 查看慢查询
SELECT query, mean_time, calls
FROM pg_stat_statements
ORDER BY mean_time DESC LIMIT 10;
-- 查看表大小
SELECT tablename, pg_size_pretty(pg_total_relation_size(tablename))
FROM pg_tables WHERE schemaname = 'public';💾 备份和恢复
自动备份脚本
bash
#!/bin/bash
BACKUP_DIR="/var/backups/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
DB_NAME="study_tracker_prod"
mkdir -p $BACKUP_DIR
pg_dump -h your-server-ip -U postgres -d $DB_NAME > $BACKUP_DIR/${DB_NAME}_${DATE}.sql
gzip $BACKUP_DIR/${DB_NAME}_${DATE}.sql
find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete恢复数据
bash
gunzip -c backup_file.sql.gz | psql -h your-server-ip -U postgres -d study_tracker_prod🔐 安全配置
用户权限
sql
-- 创建只读用户
CREATE USER readonly_user WITH PASSWORD 'readonly_password';
GRANT CONNECT ON DATABASE study_tracker_prod TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
-- 应用用户权限
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO study_tracker;🚨 故障排除
常见问题
连接被拒绝
bash
sudo systemctl status postgresql
netstat -tlnp | grep 5432权限错误
sql
\du
GRANT ALL PRIVILEGES ON DATABASE study_tracker_dev TO study_tracker;性能问题
sql
EXPLAIN ANALYZE SELECT * FROM study_sessions WHERE user_id = 1;
VACUUM ANALYZE;