398 lines
7.7 KiB
Markdown
398 lines
7.7 KiB
Markdown
# PostgreSQL 数据库部署指南
|
||
|
||
## 🎯 概述
|
||
|
||
本指南提供了为 FLNXI 认证系统配置 PostgreSQL 数据库的完整方案。
|
||
|
||
---
|
||
|
||
## 📋 方案选择
|
||
|
||
### 方案A：使用 Docker 部署 PostgreSQL（推荐）
|
||
- ✅ 最简单，一键部署
|
||
- ✅ 隔离性好，不影响系统
|
||
- ✅ 易于备份和迁移
|
||
|
||
### 方案B：使用系统现有 PostgreSQL
|
||
- ✅ 节省资源
|
||
- ⚠️ 需要手动配置
|
||
|
||
---
|
||
|
||
## 🚀 方案A：Docker 部署（推荐）
|
||
|
||
### 步骤1：SSH连接服务器
|
||
|
||
```bash
|
||
ssh root@45.249.208.118
|
||
# 密码：lxvhNYFZ4160
|
||
```
|
||
|
||
### 步骤2：创建 Docker Compose 配置
|
||
|
||
```bash
|
||
cd /opt/flnxi-website
|
||
|
||
cat > docker-compose-db.yml << 'EOF'
|
||
version: '3.8'
|
||
|
||
services:
|
||
postgres:
|
||
image: postgres:15-alpine
|
||
container_name: flnxi-postgres
|
||
environment:
|
||
POSTGRES_DB: flnxi_db
|
||
POSTGRES_USER: flnxi_user
|
||
POSTGRES_PASSWORD: Flnxi@2025!SecurePassword
|
||
volumes:
|
||
- postgres_data:/var/lib/postgresql/data
|
||
ports:
|
||
- '127.0.0.1:5433:5432' # 使用5433避免与系统PostgreSQL冲突
|
||
restart: unless-stopped
|
||
healthcheck:
|
||
test: ['CMD-SHELL', 'pg_isready -U flnxi_user']
|
||
interval: 10s
|
||
timeout: 5s
|
||
retries: 5
|
||
|
||
volumes:
|
||
postgres_data:
|
||
driver: local
|
||
EOF
|
||
```
|
||
|
||
### 步骤3：启动PostgreSQL
|
||
|
||
```bash
|
||
docker-compose -f docker-compose-db.yml up -d
|
||
```
|
||
|
||
### 步骤4：等待数据库启动
|
||
|
||
```bash
|
||
# 等待15秒
|
||
sleep 15
|
||
|
||
# 检查状态
|
||
docker ps | grep flnxi-postgres
|
||
|
||
# 应该看到 "Up" 状态
|
||
```
|
||
|
||
### 步骤5：创建用户表
|
||
|
||
```bash
|
||
docker exec -i flnxi-postgres psql -U flnxi_user -d flnxi_db << 'EOFSQL'
|
||
-- 创建用户表
|
||
CREATE TABLE IF NOT EXISTS users (
|
||
id SERIAL PRIMARY KEY,
|
||
name VARCHAR(255) NOT NULL,
|
||
email VARCHAR(255) UNIQUE NOT NULL,
|
||
password VARCHAR(255) NOT NULL,
|
||
email_verified BOOLEAN DEFAULT FALSE,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- 创建索引
|
||
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
|
||
CREATE INDEX IF NOT EXISTS idx_users_created_at ON users(created_at);
|
||
|
||
-- 查看表结构
|
||
\d users
|
||
|
||
-- 测试
|
||
SELECT 'Database setup complete!' as status;
|
||
EOFSQL
|
||
```
|
||
|
||
### 步骤6：配置环境变量
|
||
|
||
```bash
|
||
cd /opt/flnxi-website
|
||
|
||
# 添加数据库连接字符串到环境变量
|
||
cat >> .env.local << 'EOF'
|
||
|
||
# PostgreSQL 数据库配置
|
||
DATABASE_URL="postgresql://flnxi_user:Flnxi@2025!SecurePassword@localhost:5433/flnxi_db?schema=public"
|
||
EOF
|
||
|
||
# 查看配置
|
||
cat .env.local
|
||
```
|
||
|
||
### 步骤7：验证连接
|
||
|
||
```bash
|
||
# 测试数据库连接
|
||
docker exec flnxi-postgres psql -U flnxi_user -d flnxi_db -c "SELECT current_database(), current_user;"
|
||
|
||
# 应该看到：
|
||
# current_database | current_user
|
||
# ------------------+-------------
|
||
# flnxi_db | flnxi_user
|
||
```
|
||
|
||
---
|
||
|
||
## 🔧 方案B：使用系统PostgreSQL
|
||
|
||
服务器上已经有PostgreSQL运行（端口5432），可以直接使用。
|
||
|
||
### 步骤1：找到PostgreSQL路径
|
||
|
||
```bash
|
||
ssh root@45.249.208.118
|
||
|
||
# 查找PostgreSQL安装路径
|
||
ps aux | grep postgres | head -1
|
||
|
||
# 通常在：
|
||
# /usr/local/pgsql/bin/
|
||
# /usr/bin/
|
||
# /usr/pgsql-*/bin/
|
||
```
|
||
|
||
### 步骤2：配置数据库（需要根据实际PostgreSQL安装调整命令）
|
||
|
||
```bash
|
||
# 假设使用 postgres 用户
|
||
sudo su - postgres
|
||
|
||
# 创建用户
|
||
createuser -P flnxi_user
|
||
# 输入密码：Flnxi@2025!SecurePassword
|
||
|
||
# 创建数据库
|
||
createdb -O flnxi_user flnxi_db
|
||
|
||
# 连接数据库
|
||
psql -d flnxi_db
|
||
```
|
||
|
||
### 步骤3：创建表（在 psql 中执行）
|
||
|
||
```sql
|
||
CREATE TABLE IF NOT EXISTS users (
|
||
id SERIAL PRIMARY KEY,
|
||
name VARCHAR(255) NOT NULL,
|
||
email VARCHAR(255) UNIQUE NOT NULL,
|
||
password VARCHAR(255) NOT NULL,
|
||
email_verified BOOLEAN DEFAULT FALSE,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
CREATE INDEX idx_users_email ON users(email);
|
||
CREATE INDEX idx_users_created_at ON users(created_at);
|
||
|
||
-- 授权
|
||
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO flnxi_user;
|
||
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO flnxi_user;
|
||
|
||
\q -- 退出
|
||
```
|
||
|
||
### 步骤4：配置环境变量
|
||
|
||
```bash
|
||
cd /opt/flnxi-website
|
||
|
||
cat >> .env.local << 'EOF'
|
||
|
||
# PostgreSQL 数据库配置（使用系统PostgreSQL，端口5432）
|
||
DATABASE_URL="postgresql://flnxi_user:Flnxi@2025!SecurePassword@localhost:5432/flnxi_db?schema=public"
|
||
EOF
|
||
```
|
||
|
||
---
|
||
|
||
## 📊 数据库信息
|
||
|
||
配置完成后，记录以下信息：
|
||
|
||
| 项目 | 方案A（Docker） | 方案B（系统PG） |
|
||
|------|-----------------|-----------------|
|
||
| 容器名 | flnxi-postgres | N/A |
|
||
| 数据库名 | flnxi_db | flnxi_db |
|
||
| 用户名 | flnxi_user | flnxi_user |
|
||
| 密码 | Flnxi@2025!SecurePassword | Flnxi@2025!SecurePassword |
|
||
| 端口 | 5433 | 5432 |
|
||
| 连接字符串 | postgresql://flnxi_user:...@localhost:5433/flnxi_db | postgresql://flnxi_user:...@localhost:5432/flnxi_db |
|
||
|
||
---
|
||
|
||
## 🧪 测试数据库
|
||
|
||
### 方案A（Docker）
|
||
|
||
```bash
|
||
# 连接数据库
|
||
docker exec -it flnxi-postgres psql -U flnxi_user -d flnxi_db
|
||
|
||
# 查看表
|
||
\dt
|
||
|
||
# 查看表结构
|
||
\d users
|
||
|
||
# 插入测试数据
|
||
INSERT INTO users (name, email, password, email_verified)
|
||
VALUES ('测试用户', 'test@example.com', 'temp_password', false);
|
||
|
||
# 查询数据
|
||
SELECT * FROM users;
|
||
|
||
# 退出
|
||
\q
|
||
```
|
||
|
||
### 方案B（系统）
|
||
|
||
```bash
|
||
# 需要使用系统的psql路径
|
||
psql -h localhost -U flnxi_user -d flnxi_db -W
|
||
# 输入密码
|
||
|
||
# 后续命令同上...
|
||
```
|
||
|
||
---
|
||
|
||
## 📦 管理命令
|
||
|
||
### 查看容器状态
|
||
```bash
|
||
docker ps | grep flnxi-postgres
|
||
```
|
||
|
||
### 查看日志
|
||
```bash
|
||
docker logs flnxi-postgres
|
||
```
|
||
|
||
### 停止数据库
|
||
```bash
|
||
docker-compose -f docker-compose-db.yml stop
|
||
```
|
||
|
||
### 启动数据库
|
||
```bash
|
||
docker-compose -f docker-compose-db.yml start
|
||
```
|
||
|
||
### 重启数据库
|
||
```bash
|
||
docker-compose -f docker-compose-db.yml restart
|
||
```
|
||
|
||
### 备份数据库
|
||
```bash
|
||
# 方案A
|
||
docker exec flnxi-postgres pg_dump -U flnxi_user flnxi_db > backup_$(date +%Y%m%d).sql
|
||
|
||
# 方案B
|
||
pg_dump -h localhost -U flnxi_user flnxi_db > backup_$(date +%Y%m%d).sql
|
||
```
|
||
|
||
### 恢复数据库
|
||
```bash
|
||
# 方案A
|
||
cat backup_20250101.sql | docker exec -i flnxi-postgres psql -U flnxi_user -d flnxi_db
|
||
|
||
# 方案B
|
||
psql -h localhost -U flnxi_user -d flnxi_db < backup_20250101.sql
|
||
```
|
||
|
||
---
|
||
|
||
## 🔐 安全建议
|
||
|
||
1. ✅ 数据库仅监听 localhost，外部无法直接访问
|
||
2. ✅ 使用强密码
|
||
3. ⚠️ 定期备份数据（建议每天）
|
||
4. ⚠️ 定期更新密码
|
||
5. ⚠️ 监控数据库日志
|
||
6. ⚠️ 限制数据库用户权限
|
||
|
||
---
|
||
|
||
## 🐛 故障排查
|
||
|
||
### 问题1：端口已被占用
|
||
```
|
||
Error: port 5432 already in use
|
||
```
|
||
|
||
**解决方案**：
|
||
- 方案A：使用不同端口（如5433）
|
||
- 或者：停止现有PostgreSQL
|
||
```bash
|
||
docker stop $(docker ps -q --filter name=postgres)
|
||
```
|
||
|
||
### 问题2：无法连接数据库
|
||
```
|
||
Connection refused
|
||
```
|
||
|
||
**检查**：
|
||
```bash
|
||
# 检查数据库是否运行
|
||
docker ps | grep postgres
|
||
|
||
# 检查日志
|
||
docker logs flnxi-postgres
|
||
|
||
# 测试端口
|
||
telnet localhost 5433
|
||
```
|
||
|
||
### 问题3：权限问题
|
||
```
|
||
permission denied for table users
|
||
```
|
||
|
||
**解决方案**：
|
||
```sql
|
||
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO flnxi_user;
|
||
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO flnxi_user;
|
||
```
|
||
|
||
---
|
||
|
||
## ✅ 验证清单
|
||
|
||
配置完成后，验证以下项目：
|
||
|
||
- [ ] PostgreSQL 容器/服务正在运行
|
||
- [ ] 可以连接到数据库
|
||
- [ ] users 表已创建
|
||
- [ ] 索引已创建
|
||
- [ ] 环境变量已配置
|
||
- [ ] 可以插入测试数据
|
||
- [ ] 可以查询数据
|
||
- [ ] 备份脚本可用
|
||
|
||
---
|
||
|
||
## 📝 下一步
|
||
|
||
数据库配置完成后：
|
||
|
||
1. ✅ 安装项目依赖（Prisma、bcrypt）
|
||
2. ✅ 更新注册 API 使用数据库
|
||
3. ✅ 更新登录 API 使用数据库和密码验证
|
||
4. ✅ 重新部署应用
|
||
5. ✅ 测试注册和登录功能
|
||
|
||
---
|
||
|
||
**配置时间**: 约 10-15 分钟
|
||
**难度**: 简单
|
||
**推荐方案**: 方案A（Docker）
|
||
|
||
🎉 **祝您配置顺利！**
|
||
|