272 lines
7.8 KiB
Bash
Executable File
272 lines
7.8 KiB
Bash
Executable File
#!/bin/bash
|
||
|
||
# PostgreSQL 数据库部署脚本
|
||
# 使用方法：bash deploy-database.sh
|
||
|
||
set -e # 遇到错误立即退出
|
||
|
||
echo "🚀 开始部署 PostgreSQL 数据库..."
|
||
echo ""
|
||
|
||
# 服务器信息
|
||
SERVER="root@45.249.208.118"
|
||
SERVER_PASSWORD="lxvhNYFZ4160" # 从聊天记录中获取
|
||
REMOTE_DIR="/opt/flnxi-website"
|
||
DB_NAME="flnxi_db"
|
||
DB_USER="flnxi_user"
|
||
DB_PASSWORD="Flnxi@2025!SecurePassword" # 生成的强密码
|
||
|
||
# 颜色定义
|
||
GREEN='\033[0;32m'
|
||
BLUE='\033[0;34m'
|
||
YELLOW='\033[1;33m'
|
||
RED='\033[0;31m'
|
||
NC='\033[0m' # No Color
|
||
|
||
# 检查是否安装 sshpass
|
||
if ! command -v sshpass &> /dev/null; then
|
||
echo -e "${YELLOW}⚠️ sshpass 未安装，正在安装...${NC}"
|
||
if [[ "$OSTYPE" == "darwin"* ]]; then
|
||
# macOS
|
||
brew install hudochenkov/sshpass/sshpass 2>/dev/null || {
|
||
echo -e "${RED}❌ 请先安装 Homebrew，然后运行：${NC}"
|
||
echo "brew install hudochenkov/sshpass/sshpass"
|
||
exit 1
|
||
}
|
||
else
|
||
# Linux
|
||
sudo apt-get install -y sshpass || sudo yum install -y sshpass
|
||
fi
|
||
echo -e "${GREEN}✅ sshpass 已安装${NC}"
|
||
fi
|
||
|
||
# SSH 执行函数
|
||
ssh_exec() {
|
||
sshpass -p "$SERVER_PASSWORD" ssh -o StrictHostKeyChecking=no $SERVER "$1"
|
||
}
|
||
|
||
# 步骤1：检查系统类型
|
||
echo -e "${BLUE}步骤 1/10: 检查服务器系统...${NC}"
|
||
OS_INFO=$(ssh_exec "cat /etc/os-release 2>/dev/null || cat /etc/redhat-release 2>/dev/null || echo 'Unknown'")
|
||
echo "$OS_INFO" | head -3
|
||
echo ""
|
||
|
||
# 步骤2：安装 Docker Compose PostgreSQL 方案（推荐）
|
||
echo -e "${BLUE}步骤 2/10: 使用 Docker 部署 PostgreSQL...${NC}"
|
||
ssh_exec "cd $REMOTE_DIR && cat > docker-compose-db.yml << 'EOFDB'
|
||
version: '3.8'
|
||
|
||
services:
|
||
postgres:
|
||
image: postgres:15-alpine
|
||
container_name: flnxi-postgres
|
||
environment:
|
||
POSTGRES_DB: ${DB_NAME}
|
||
POSTGRES_USER: ${DB_USER}
|
||
POSTGRES_PASSWORD: ${DB_PASSWORD}
|
||
POSTGRES_INITDB_ARGS: '--encoding=UTF-8 --lc-collate=C --lc-ctype=C'
|
||
volumes:
|
||
- postgres_data:/var/lib/postgresql/data
|
||
ports:
|
||
- '127.0.0.1:5432:5432'
|
||
restart: unless-stopped
|
||
healthcheck:
|
||
test: ['CMD-SHELL', 'pg_isready -U ${DB_USER}']
|
||
interval: 10s
|
||
timeout: 5s
|
||
retries: 5
|
||
|
||
volumes:
|
||
postgres_data:
|
||
driver: local
|
||
EOFDB
|
||
"
|
||
echo -e "${GREEN}✅ Docker Compose 配置已创建${NC}"
|
||
echo ""
|
||
|
||
# 步骤3：启动 PostgreSQL
|
||
echo -e "${BLUE}步骤 3/10: 启动 PostgreSQL 数据库...${NC}"
|
||
ssh_exec "cd $REMOTE_DIR && docker-compose -f docker-compose-db.yml up -d"
|
||
echo -e "${GREEN}✅ PostgreSQL 已启动${NC}"
|
||
echo ""
|
||
|
||
# 等待数据库启动
|
||
echo -e "${BLUE}等待数据库启动... (10秒)${NC}"
|
||
sleep 10
|
||
|
||
# 步骤4：验证数据库连接
|
||
echo -e "${BLUE}步骤 4/10: 验证数据库连接...${NC}"
|
||
ssh_exec "docker exec flnxi-postgres psql -U $DB_USER -d $DB_NAME -c 'SELECT version();'" || {
|
||
echo -e "${RED}❌ 数据库连接失败${NC}"
|
||
exit 1
|
||
}
|
||
echo -e "${GREEN}✅ 数据库连接成功${NC}"
|
||
echo ""
|
||
|
||
# 步骤5：更新环境变量
|
||
echo -e "${BLUE}步骤 5/10: 配置数据库连接字符串...${NC}"
|
||
DATABASE_URL="postgresql://${DB_USER}:${DB_PASSWORD}@localhost:5432/${DB_NAME}?schema=public"
|
||
ssh_exec "cd $REMOTE_DIR && cat >> .env.local << EOFENV
|
||
|
||
# PostgreSQL 数据库配置
|
||
DATABASE_URL=\"${DATABASE_URL}\"
|
||
EOFENV
|
||
"
|
||
echo -e "${GREEN}✅ 环境变量已配置${NC}"
|
||
echo ""
|
||
|
||
# 步骤6：创建用户表 SQL
|
||
echo -e "${BLUE}步骤 6/10: 创建用户表...${NC}"
|
||
ssh_exec "docker exec -i flnxi-postgres psql -U $DB_USER -d $DB_NAME << '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 '✅ 用户表创建成功' as message;
|
||
EOFSQL
|
||
"
|
||
echo -e "${GREEN}✅ 用户表已创建${NC}"
|
||
echo ""
|
||
|
||
# 步骤7：显示数据库信息
|
||
echo -e "${BLUE}步骤 7/10: 数据库信息汇总...${NC}"
|
||
echo -e "${GREEN}━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━${NC}"
|
||
echo -e "${GREEN}📊 数据库连接信息${NC}"
|
||
echo -e "${GREEN}━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━${NC}"
|
||
echo ""
|
||
echo "容器名称: flnxi-postgres"
|
||
echo "数据库名: $DB_NAME"
|
||
echo "用户名: $DB_USER"
|
||
echo "密码: $DB_PASSWORD"
|
||
echo "端口: 5432 (仅本地访问)"
|
||
echo "连接字符串: $DATABASE_URL"
|
||
echo ""
|
||
echo -e "${YELLOW}⚠️ 请妥善保管数据库密码！${NC}"
|
||
echo ""
|
||
|
||
# 步骤8：保存数据库信息到文件
|
||
echo -e "${BLUE}步骤 8/10: 保存配置信息...${NC}"
|
||
cat > DATABASE_CONFIG.md << EOFCONFIG
|
||
# PostgreSQL 数据库配置信息
|
||
|
||
## 连接信息
|
||
|
||
- **容器名称**: \`flnxi-postgres\`
|
||
- **数据库名**: \`$DB_NAME\`
|
||
- **用户名**: \`$DB_USER\`
|
||
- **密码**: \`$DB_PASSWORD\`
|
||
- **端口**: \`5432\` (仅本地访问)
|
||
- **连接字符串**:
|
||
\`\`\`
|
||
$DATABASE_URL
|
||
\`\`\`
|
||
|
||
## 管理命令
|
||
|
||
### 连接数据库
|
||
\`\`\`bash
|
||
# 在服务器上执行
|
||
docker exec -it flnxi-postgres psql -U $DB_USER -d $DB_NAME
|
||
\`\`\`
|
||
|
||
### 查看表
|
||
\`\`\`sql
|
||
\dt
|
||
\`\`\`
|
||
|
||
### 查看用户数据
|
||
\`\`\`sql
|
||
SELECT id, name, email, email_verified, created_at FROM users;
|
||
\`\`\`
|
||
|
||
### 备份数据库
|
||
\`\`\`bash
|
||
docker exec flnxi-postgres pg_dump -U $DB_USER $DB_NAME > backup_\$(date +%Y%m%d).sql
|
||
\`\`\`
|
||
|
||
### 恢复数据库
|
||
\`\`\`bash
|
||
docker exec -i flnxi-postgres psql -U $DB_USER $DB_NAME < backup_20250101.sql
|
||
\`\`\`
|
||
|
||
## 表结构
|
||
|
||
### users 表
|
||
| 字段 | 类型 | 说明 |
|
||
|------|------|------|
|
||
| id | SERIAL | 主键 |
|
||
| name | VARCHAR(255) | 用户名 |
|
||
| email | VARCHAR(255) | 邮箱（唯一） |
|
||
| password | VARCHAR(255) | 密码（bcrypt加密） |
|
||
| email_verified | BOOLEAN | 邮箱是否验证 |
|
||
| created_at | TIMESTAMP | 创建时间 |
|
||
| updated_at | TIMESTAMP | 更新时间 |
|
||
|
||
## 安全建议
|
||
|
||
1. ✅ 数据库仅监听 localhost，外部无法访问
|
||
2. ✅ 使用强密码
|
||
3. ⚠️ 定期备份数据
|
||
4. ⚠️ 定期更新密码
|
||
5. ⚠️ 监控数据库日志
|
||
|
||
## 下一步
|
||
|
||
1. 安装 Prisma ORM
|
||
2. 更新注册 API 使用数据库
|
||
3. 更新登录 API 使用数据库
|
||
4. 添加密码加密（bcrypt）
|
||
|
||
---
|
||
生成时间: $(date)
|
||
EOFCONFIG
|
||
|
||
echo -e "${GREEN}✅ 配置已保存到 DATABASE_CONFIG.md${NC}"
|
||
echo ""
|
||
|
||
# 步骤9：测试数据库
|
||
echo -e "${BLUE}步骤 9/10: 测试数据库功能...${NC}"
|
||
ssh_exec "docker exec -i flnxi-postgres psql -U $DB_USER -d $DB_NAME << 'EOFSQL'
|
||
-- 插入测试数据
|
||
INSERT INTO users (name, email, password, email_verified)
|
||
VALUES ('测试管理员', 'admin@flnxi.com', 'temp_password_will_be_hashed', true)
|
||
ON CONFLICT (email) DO NOTHING;
|
||
|
||
-- 查询测试
|
||
SELECT id, name, email, email_verified, created_at FROM users;
|
||
EOFSQL
|
||
"
|
||
echo -e "${GREEN}✅ 数据库功能正常${NC}"
|
||
echo ""
|
||
|
||
# 步骤10：完成
|
||
echo -e "${GREEN}━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━${NC}"
|
||
echo -e "${GREEN}🎉 PostgreSQL 数据库部署完成！${NC}"
|
||
echo -e "${GREEN}━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━${NC}"
|
||
echo ""
|
||
echo -e "${YELLOW}📝 下一步操作：${NC}"
|
||
echo "1. 查看数据库配置：cat DATABASE_CONFIG.md"
|
||
echo "2. 安装依赖：bash deploy-prisma-setup.sh"
|
||
echo "3. 更新代码使用数据库"
|
||
echo "4. 重新部署应用"
|
||
echo ""
|
||
echo -e "${YELLOW}🔍 快速测试：${NC}"
|
||
echo "ssh $SERVER 'docker exec flnxi-postgres psql -U $DB_USER -d $DB_NAME -c \"SELECT * FROM users;\"'"
|
||
echo ""
|
||
|