## （本地 Docker 练习专用 + 生产通用 + 你未来统一 PG 必备）
你这个思路**非常稳、非常专业**：
**本地练习 → 测试环境验证 → 再动生产**
绝对是运维/自建服务最安全的路径。
我给你写一份**真正能当手册用**的 PostgreSQL 笔记，
**全部是你未来一定会用到的命令**，从 0 开始，不跳步、不废话。
---
# 一、先在本地搭练习环境（1 分钟）
## 1. 拉取你未来要用的镜像（pgvector:pg16）
### 三 级标题
#### 四级标题
```bash
docker pull pgvector/pgvector:pg16
```
## 2. 本地启动 PostgreSQL 容器（永久用）
```bash
docker run -d \
--name pg-study \
-p 5432:5432 \
-e POSTGRES_USER=postgres \
-e POSTGRES_PASSWORD=123456 \
-e POSTGRES_DB=testdb \
-v pg-data:/var/lib/postgresql/data \
--restart always \
pgvector/pgvector:pg16
```
## 3. 进入 PostgreSQL 命令行（核心入口）
```bash
docker exec -it pg-study psql -U postgres
```
进入后提示符长这样：
```
postgres=#
```
---
# 二、最基础：查看命令
```sql
# 查看所有数据库
\l
# 查看所有用户（角色）
\du
# 查看当前库所有表
\dt
# 查看表结构
\d 表名
# 退出 psql
\q
```
---
# 三、用户管理（你未来最常用）
## 1. 创建用户（必须指定密码）
```sql
CREATE USER testuser WITH PASSWORD 'mypassword';
```
## 2. 创建超级管理员（生产慎用）
```sql
CREATE USER admin WITH SUPERUSER PASSWORD 'admin123';
```
## 3. 修改用户密码
```sql
ALTER USER postgres PASSWORD 'newpassword';
```
## 4. 给用户赋予数据库全部权限
```sql
GRANT ALL PRIVILEGES ON DATABASE appflowy TO testuser;
```
## 5. 回收权限
```sql
REVOKE ALL PRIVILEGES ON DATABASE appflowy FROM testuser;
```
## 6. 删除用户
```sql
DROP USER testuser;
```
---
# 四、数据库管理（AppFlowy / Affine 必备）
## 1. 创建数据库
```sql
CREATE DATABASE appflowy;
CREATE DATABASE affine;
```
## 2. 创建数据库并指定所有者
```sql
CREATE DATABASE appflowy OWNER testuser;
```
## 3. 切换数据库
```bash
# 进入时直接指定
psql -U postgres -d appflowy
# 或在 psql 内
\c appflowy
```
## 4. 修改数据库名称
```sql
ALTER DATABASE oldname RENAME TO newname;
```
## 5. 删除数据库（危险！）
```sql
DROP DATABASE appflowy;
```
---
# 五、导入、导出（备份/迁移核心）
## 【导出】整个数据库（最常用）
```bash
docker exec -it pg-study pg_dump -U postgres appflowy > appflowy_backup.sql
```
## 【导出】带结构+数据+权限
```bash
docker exec -it pg-study pg_dump -U postgres -d appflowy -C -c -O > appflowy_full.sql
```
## 【导入】数据库（最常用）
```bash
cat appflowy_backup.sql | docker exec -i pg-study psql -U postgres -d appflowy
```
## 【导出所有数据库】整 PG 实例备份
```bash
docker exec -it pg-study pg_dumpall -U postgres > all_db.sql
```
## 【导入所有数据库】
```bash
cat all_db.sql | docker exec -i pg-study psql -U postgres
```
---
# 六、Docker PostgreSQL 标准运维命令
## 重启
```bash
docker restart pg-study
```
## 停止
```bash
docker stop pg-study
```
## 查看日志
```bash
docker logs pg-study
```
## 查看连接信息
```bash
docker inspect pg-study | grep IPAddress
```
---
# 七、权限体系（你统一 PG 必须懂）
## 给用户可以登录的权限
```sql
ALTER ROLE testuser LOGIN;
```
## 禁止登录
```sql
ALTER ROLE testuser NOLOGIN;
```
## 创建可以创建库的用户
```sql
CREATE USER testuser WITH CREATEDB PASSWORD '123456';
```
## 赋予用户在 public 模式下的所有表权限
```sql
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO testuser;
```
## 让用户未来新建的表也自动有权限
```sql
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO testuser;
```
---
# 八、模式（Schema）管理（AppFlowy 专用）
AppFlowy 用 `search_path=auth`，就是 schema。
## 查看所有 schema
```sql
\dn
```
## 创建 schema
```sql
CREATE SCHEMA auth;
```
## 设置用户默认搜索 schema
```sql
ALTER ROLE appflowy SET search_path TO auth, public;
```
## 删除 schema
```sql
DROP SCHEMA auth CASCADE;
```
---
# 九、pgvector 向量插件（你统一 PG 必须装）
## 安装向量扩展
```sql
CREATE EXTENSION IF NOT EXISTS vector;
```
## 查看已安装扩展
```sql
\dx
```
## 卸载扩展
```sql
DROP EXTENSION vector;
```
---
# 十、连接字符串（你统一 PG 后所有应用都用这种格式）
## 标准格式
```
postgres://user:password@host:port/dbname
```
## 例子
```
postgres://postgres:123456@192.168.1.100:5432/appflowy
```
## 带 schema
```
postgres://postgres:123456@192.168.1.100:5432/appflowy?search_path=auth
```
---
# 十一、日常问题排查（你未来一定会遇到）
## 1. 查看当前连接
```sql
SELECT * FROM pg_stat_activity;
```
## 2. 杀掉慢查询/卡住的连接
```sql
SELECT pg_terminate_backend(pid);
```
## 3. 数据库无法连接
常见原因：
- 端口没映射
- 防火墙
- PostgreSQL 配置 `listen_addresses` 限制
- 密码错误
- 用户没登录权限
## 4. 密码正确但无法远程连接
修改 `pg_hba.conf`
```
host all all 0.0.0.0/0 md5
```
Docker 容器默认已经允许所有连接。
## 5. 数据库占空间太大
```sql
SELECT pg_size_pretty(pg_database_size('appflowy'));
```
## 6. 清理日志、释放空间
```sql
VACUUM FULL;
```
## 7. 修复数据库损坏（最后手段）
```bash
docker exec -it pg-study pg_resetwal -U postgres
```
---
# 十二、跨版本迁移（你统一 PG 最关键）
## 高版本 → 低版本 **必须用 SQL 导出导入**
错误：直接复制 data 目录（一定会崩溃）
正确：
```bash
# 导出
pg_dump -U postgres db > db.sql
# 导入
psql -U postgres db < db.sql
```
---
# 十三、生产最佳实践（你未来服务器统一 PG 用）
## 1. 不要用 postgres 超级用户跑业务
创建专用用户：
```sql
CREATE USER appflowy WITH PASSWORD 'xxx';
CREATE DATABASE appflowy OWNER appflowy;
```
## 2. 定期自动备份
```bash
0 3 * * * docker exec pg-study pg_dump -U postgres appflowy > /backup/appflowy_$(date +%Y%m%d).sql
```
## 3. 禁止公网直接暴露 5432
用内部网络、VPN、Docker 网络。
## 4. 一个 PG 实例可以跑几十个应用
AppFlowy、Affine、Nextcloud、Photoprism 全部可以共用。
---
# 十四、你未来统一 PG 会用到的完整流程（我提前给你写好）
## 1. 停止 1Panel PG
```bash
cd /opt/1panel/apps/postgresql/postgresql
docker compose down
```
## 2. 修改镜像
```yaml
image: pgvector/pgvector:pg16
```
## 3. 启动
```bash
docker compose up -d
```
## 4. 安装 vector
```bash
docker exec -it postgresql psql -U postgres -c "CREATE EXTENSION vector;"
```
## 5. 创建 AppFlowy 库
```sql
CREATE DATABASE appflowy;
CREATE DATABASE affine;
```
## 6. 导入旧数据
```bash
cat appflowy_old.sql | docker exec -i postgresql psql -U postgres -d appflowy
```
## 7. 修改应用连接
```
host: 宿主机IP
port: 5432
user: 1panel 用户
password: 1panel 密码
db: appflowy
```
---
# 十五、我给你的最终学习路线（最适合你）
1. 本地用 Docker 把上面命令全部敲一遍（1 小时）
2. 练习：创建用户 → 创建库 → 导入导出 → 删除 → 重建
3. 练习：pgvector 安装
4. 练习：用连接字符串连接测试
5. 服务器上：
- 替换 1Panel PG 镜像
- 新建测试应用（Affine / AppFlowy）
6. 确认没问题 → 迁移真实服务
你**完全不会有风险**。
---
## 以后继续写：
1. **PostgreSQL 1Panel 替换镜像完整图文步骤**
2. **统一 PG 后 Affine + AppFlowy 完整连接配置**
3. **自动备份脚本 + 异地同步脚本**