294 lines
5.9 KiB
Markdown
294 lines
5.9 KiB
Markdown
# PostgreSQL Docker 部署指南
|
||
|
||
## 📋 目录结构
|
||
|
||
```
|
||
deploy/
|
||
├── docker-compose.postgres.yml # Docker Compose 配置
|
||
├── postgres/
|
||
│ ├── init.sql # 数据库初始化脚本
|
||
│ └── postgresql.conf # PostgreSQL 配置文件
|
||
└── README.postgres.md # 本文档
|
||
```
|
||
|
||
## 🚀 快速启动
|
||
|
||
### 1. 启动数据库
|
||
|
||
```bash
|
||
# 在 deploy 目录下执行
|
||
cd deploy
|
||
docker-compose -f docker-compose.postgres.yml up -d
|
||
```
|
||
|
||
### 2. 查看日志
|
||
|
||
```bash
|
||
# 查看 PostgreSQL 日志
|
||
docker-compose -f docker-compose.postgres.yml logs -f postgres
|
||
|
||
# 查看所有服务日志
|
||
docker-compose -f docker-compose.postgres.yml logs -f
|
||
```
|
||
|
||
### 3. 停止服务
|
||
|
||
```bash
|
||
# 停止服务（保留数据）
|
||
docker-compose -f docker-compose.postgres.yml stop
|
||
|
||
# 停止并删除容器（保留数据卷）
|
||
docker-compose -f docker-compose.postgres.yml down
|
||
|
||
# 停止并删除所有内容（包括数据）
|
||
docker-compose -f docker-compose.postgres.yml down -v
|
||
```
|
||
|
||
## 🔧 配置说明
|
||
|
||
### 数据库连接信息
|
||
|
||
| 项目 | 值 |
|
||
|------|-----|
|
||
| Host | localhost |
|
||
| Port | 5432 |
|
||
| Database | st_dev |
|
||
| Username | st_user |
|
||
| Password | st_password |
|
||
|
||
### pgAdmin 管理界面
|
||
|
||
- **访问地址**: http://localhost:5050
|
||
- **登录邮箱**: admin@st.local
|
||
- **登录密码**: admin123
|
||
|
||
### 已安装的扩展
|
||
|
||
- ✅ **pgvector** - 向量相似度搜索（用于 AI 记忆功能）
|
||
- ✅ **uuid-ossp** - UUID 生成
|
||
- ✅ **pg_trgm** - 模糊搜索和全文搜索
|
||
- ✅ **btree_gin** - GIN 索引优化（加速 JSONB 查询）
|
||
|
||
## 📝 更新 Go 配置
|
||
|
||
修改 `server/config.yaml` 中的数据库配置：
|
||
|
||
```yaml
|
||
# system/config.yaml
|
||
system:
|
||
db-type: postgres
|
||
|
||
pgsql:
|
||
path: 127.0.0.1
|
||
port: "5432"
|
||
config: sslmode=disable TimeZone=Asia/Shanghai
|
||
db-name: st_dev
|
||
username: st_user
|
||
password: st_password
|
||
max-idle-conns: 10
|
||
max-open-conns: 100
|
||
log-mode: info
|
||
log-zap: false
|
||
```
|
||
|
||
## 🔍 数据库管理
|
||
|
||
### 使用 Docker 命令连接
|
||
|
||
```bash
|
||
# 进入 PostgreSQL 容器
|
||
docker exec -it st-postgres bash
|
||
|
||
# 连接数据库
|
||
psql -U st_user -d st_dev
|
||
|
||
# 或者直接执行
|
||
docker exec -it st-postgres psql -U st_user -d st_dev
|
||
```
|
||
|
||
### 常用 SQL 命令
|
||
|
||
```sql
|
||
-- 查看已安装的扩展
|
||
\dx
|
||
|
||
-- 查看所有表
|
||
\dt
|
||
|
||
-- 查看表结构
|
||
\d+ table_name
|
||
|
||
-- 查看数据库大小
|
||
SELECT pg_size_pretty(pg_database_size('st_dev'));
|
||
|
||
-- 查看表大小
|
||
SELECT
|
||
schemaname,
|
||
tablename,
|
||
pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
|
||
FROM pg_tables
|
||
WHERE schemaname = 'public'
|
||
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;
|
||
|
||
-- 测试 pgvector 扩展
|
||
SELECT vector_dims(ARRAY[1,2,3]::vector);
|
||
|
||
-- 查看当前连接数
|
||
SELECT count(*) FROM pg_stat_activity;
|
||
```
|
||
|
||
## 🔄 数据备份与恢复
|
||
|
||
### 备份数据库
|
||
|
||
```bash
|
||
# 备份整个数据库
|
||
docker exec st-postgres pg_dump -U st_user st_dev > backup_$(date +%Y%m%d_%H%M%S).sql
|
||
|
||
# 备份为自定义格式（推荐，支持并行恢复）
|
||
docker exec st-postgres pg_dump -U st_user -Fc st_dev > backup_$(date +%Y%m%d_%H%M%S).dump
|
||
```
|
||
|
||
### 恢复数据库
|
||
|
||
```bash
|
||
# 从 SQL 文件恢复
|
||
docker exec -i st-postgres psql -U st_user -d st_dev < backup.sql
|
||
|
||
# 从自定义格式恢复
|
||
docker exec -i st-postgres pg_restore -U st_user -d st_dev -c backup.dump
|
||
```
|
||
|
||
## 🛠️ 性能优化建议
|
||
|
||
### 开发环境（当前配置）
|
||
|
||
- `shared_buffers`: 256MB
|
||
- `effective_cache_size`: 1GB
|
||
- `work_mem`: 5MB
|
||
|
||
### 生产环境（8GB 内存服务器）
|
||
|
||
修改 `postgresql.conf`:
|
||
|
||
```conf
|
||
shared_buffers = 2GB # 系统内存的 25%
|
||
effective_cache_size = 6GB # 系统内存的 75%
|
||
maintenance_work_mem = 512MB
|
||
work_mem = 16MB
|
||
```
|
||
|
||
## 🐛 常见问题
|
||
|
||
### 1. 端口被占用
|
||
|
||
如果 5432 端口被占用，修改 `docker-compose.postgres.yml`:
|
||
|
||
```yaml
|
||
ports:
|
||
- "15432:5432" # 映射到本地 15432 端口
|
||
```
|
||
|
||
### 2. 权限问题
|
||
|
||
```bash
|
||
# Linux/Mac 系统可能需要修改数据目录权限
|
||
sudo chown -R 999:999 /path/to/postgres_data
|
||
```
|
||
|
||
### 3. 连接失败
|
||
|
||
检查防火墙和 Docker 网络：
|
||
|
||
```bash
|
||
# 查看容器状态
|
||
docker ps
|
||
|
||
# 查看容器日志
|
||
docker logs st-postgres
|
||
|
||
# 测试连接
|
||
docker exec st-postgres pg_isready -U st_user
|
||
```
|
||
|
||
### 4. 性能调优
|
||
|
||
```bash
|
||
# 查看当前配置
|
||
docker exec st-postgres psql -U st_user -d st_dev -c "SHOW ALL;"
|
||
|
||
# 分析慢查询
|
||
docker exec st-postgres psql -U st_user -d st_dev -c "
|
||
SELECT query, calls, total_time, mean_time
|
||
FROM pg_stat_statements
|
||
ORDER BY mean_time DESC
|
||
LIMIT 10;
|
||
"
|
||
```
|
||
|
||
## 📊 监控
|
||
|
||
### 使用 pgAdmin
|
||
|
||
1. 访问 http://localhost:5050
|
||
2. 登录后，添加新服务器：
|
||
- Name: ST Development
|
||
- Host: postgres (容器名称)
|
||
- Port: 5432
|
||
- Username: st_user
|
||
- Password: st_password
|
||
|
||
### 使用命令行监控
|
||
|
||
```bash
|
||
# 查看活动连接
|
||
docker exec st-postgres psql -U st_user -d st_dev -c "
|
||
SELECT pid, usename, application_name, client_addr, state, query
|
||
FROM pg_stat_activity
|
||
WHERE datname = 'st_dev';
|
||
"
|
||
|
||
# 查看表统计信息
|
||
docker exec st-postgres psql -U st_user -d st_dev -c "
|
||
SELECT schemaname, tablename, n_live_tup, n_dead_tup
|
||
FROM pg_stat_user_tables
|
||
ORDER BY n_live_tup DESC;
|
||
"
|
||
```
|
||
|
||
## 🔐 安全建议
|
||
|
||
### 生产环境部署
|
||
|
||
1. **修改默认密码**：
|
||
```yaml
|
||
environment:
|
||
POSTGRES_PASSWORD: 使用强密码
|
||
```
|
||
|
||
2. **限制网络访问**：
|
||
```yaml
|
||
ports:
|
||
- "127.0.0.1:5432:5432" # 只允许本地访问
|
||
```
|
||
|
||
3. **启用 SSL**：
|
||
```conf
|
||
ssl = on
|
||
ssl_cert_file = '/path/to/cert.pem'
|
||
ssl_key_file = '/path/to/key.pem'
|
||
```
|
||
|
||
4. **定期备份**：
|
||
```bash
|
||
# 添加到 crontab
|
||
0 2 * * * docker exec st-postgres pg_dump -U st_user -Fc st_dev > /backups/st_$(date +\%Y\%m\%d).dump
|
||
```
|
||
|
||
## 📚 参考资料
|
||
|
||
- [PostgreSQL 官方文档](https://www.postgresql.org/docs/18/)
|
||
- [pgvector 扩展文档](https://github.com/pgvector/pgvector)
|
||
- [Docker Hub - PostgreSQL](https://hub.docker.com/_/postgres)
|
||
- [pgAdmin 文档](https://www.pgadmin.org/docs/)
|