【万字完整版】PostgreSQL 零基础到精通 · 命令行笔记

（本地 Docker 练习专用 + 生产通用 + 你未来统一 PG 必备）

你这个思路非常稳、非常专业：
本地练习 → 测试环境验证 → 再动生产
绝对是运维/自建服务最安全的路径。

我给你写一份真正能当手册用的 PostgreSQL 笔记，
全部是你未来一定会用到的命令，从 0 开始，不跳步、不废话。

---

toggle

Happy Markdowning!!

1. Use <code>{ctrl}</code>+<code>{c}</code> to copy text.
2. Use <code>{ctrl}</code>+<code>{v}</code> to paste text.
3. Open task manager: <code>{ctrl}</code>+<code>{alt}</code>+<code>{del}</code>

一、先在本地搭练习环境（1 分钟）

1. 拉取你未来要用的镜像（pgvector:pg16）

docker pull pgvector/pgvector:pg16

2. 本地启动 PostgreSQL 容器（永久用）

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 命令行（核心入口）

docker exec -it pg-study psql -U postgres

进入后提示符长这样：

postgres=#

---

二、最基础：查看命令

# 查看所有数据库
\l

# 查看所有用户（角色）
\du

# 查看当前库所有表
\dt

# 查看表结构
\d 表名

# 退出 psql
\q

---

三、用户管理（你未来最常用）

1. 创建用户（必须指定密码）

CREATE USER testuser WITH PASSWORD 'mypassword';

2. 创建超级管理员（生产慎用）

CREATE USER admin WITH SUPERUSER PASSWORD 'admin123';

3. 修改用户密码

ALTER USER postgres PASSWORD 'newpassword';

4. 给用户赋予数据库全部权限

GRANT ALL PRIVILEGES ON DATABASE appflowy TO testuser;

5. 回收权限

REVOKE ALL PRIVILEGES ON DATABASE appflowy FROM testuser;

6. 删除用户

DROP USER testuser;

---

四、数据库管理（AppFlowy / Affine 必备）

1. 创建数据库

CREATE DATABASE appflowy;
CREATE DATABASE affine;

2. 创建数据库并指定所有者

CREATE DATABASE appflowy OWNER testuser;

3. 切换数据库

# 进入时直接指定
psql -U postgres -d appflowy

# 或在 psql 内
\c appflowy

4. 修改数据库名称

ALTER DATABASE oldname RENAME TO newname;

5. 删除数据库（危险！）

DROP DATABASE appflowy;

---

五、导入、导出（备份/迁移核心）

【导出】整个数据库（最常用）

docker exec -it pg-study pg_dump -U postgres appflowy > appflowy_backup.sql

【导出】带结构+数据+权限

docker exec -it pg-study pg_dump -U postgres -d appflowy -C -c -O > appflowy_full.sql

【导入】数据库（最常用）

cat appflowy_backup.sql | docker exec -i pg-study psql -U postgres -d appflowy

【导出所有数据库】整 PG 实例备份

docker exec -it pg-study pg_dumpall -U postgres > all_db.sql

【导入所有数据库】

cat all_db.sql | docker exec -i pg-study psql -U postgres

---

六、Docker PostgreSQL 标准运维命令

重启

docker restart pg-study

停止

docker stop pg-study

查看日志

docker logs pg-study

查看连接信息

docker inspect pg-study | grep IPAddress

---

七、权限体系（你统一 PG 必须懂）

给用户可以登录的权限

ALTER ROLE testuser LOGIN;

禁止登录

ALTER ROLE testuser NOLOGIN;

创建可以创建库的用户

CREATE USER testuser WITH CREATEDB PASSWORD '123456';

赋予用户在 public 模式下的所有表权限

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO testuser;

让用户未来新建的表也自动有权限

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO testuser;

---

八、模式（Schema）管理（AppFlowy 专用）

AppFlowy 用 <code>search_path=auth</code>，就是 schema。

查看所有 schema

\dn

创建 schema

CREATE SCHEMA auth;

设置用户默认搜索 schema

ALTER ROLE appflowy SET search_path TO auth, public;

删除 schema

DROP SCHEMA auth CASCADE;

---

九、pgvector 向量插件（你统一 PG 必须装）

安装向量扩展

CREATE EXTENSION IF NOT EXISTS vector;

查看已安装扩展

\dx

卸载扩展

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. 查看当前连接

SELECT * FROM pg_stat_activity;

2. 杀掉慢查询/卡住的连接

SELECT pg_terminate_backend(pid);

3. 数据库无法连接

常见原因：

• 端口没映射
• 防火墙
• PostgreSQL 配置 <code>listen_addresses</code> 限制
• 密码错误
• 用户没登录权限

4. 密码正确但无法远程连接

修改 <code>pg_hba.conf</code>

host all all 0.0.0.0/0 md5

Docker 容器默认已经允许所有连接。

5. 数据库占空间太大

SELECT pg_size_pretty(pg_database_size('appflowy'));

6. 清理日志、释放空间

VACUUM FULL;

7. 修复数据库损坏（最后手段）

docker exec -it pg-study pg_resetwal -U postgres

---

十二、跨版本迁移（你统一 PG 最关键）

高版本 → 低版本 必须用 SQL 导出导入

错误：直接复制 data 目录（一定会崩溃）

正确：

# 导出
pg_dump -U postgres db > db.sql

# 导入
psql -U postgres db < db.sql

---

十三、生产最佳实践（你未来服务器统一 PG 用）

1. 不要用 postgres 超级用户跑业务

创建专用用户：

CREATE USER appflowy WITH PASSWORD 'xxx';
CREATE DATABASE appflowy OWNER appflowy;

2. 定期自动备份

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

cd /opt/1panel/apps/postgresql/postgresql
docker compose down

2. 修改镜像

image: pgvector/pgvector:pg16

3. 启动

docker compose up -d

4. 安装 vector

docker exec -it postgresql psql -U postgres -c "CREATE EXTENSION vector;"

5. 创建 AppFlowy 库

CREATE DATABASE appflowy;
CREATE DATABASE affine;

6. 导入旧数据

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. 自动备份脚本 + 异地同步脚本

你只要说一声，我全部给你写好。