跳转到内容
多练网

第 21 章 · PostgreSQL

一句话：开源的关系数据库，SQL 标准支持全，带事务、外键、JSON、全文检索，靠扩展还能做地理信息和向量检索。 用它还是用别的：新项目要一个通用的关系库，默认选它；老系统或托管环境已经定了 MySQL 就用 MySQL（第 22 章）；单机小工具、测试用的库用 SQLite（第 23 章）。 核对版本：PostgreSQL 17（随书 compose 镜像）、psql 16.2，2026-10

21.1 安装与版本

Section titled “21.1 安装与版本”

本书用 Docker 跑 PostgreSQL。在随书的 compose/ 目录下启动，Compose 的用法详见第 13 章：

终端窗口
docker compose --profile db up -d

这一条同时起 PostgreSQL、MySQL、MongoDB。PostgreSQL 的宿主机端口是 5432，超级用户 postgres，口令 dev，数据在命名卷 pgdata 里。

进容器里自带的命令行客户端 psql，出现 postgres=# 提示符就是连上了。容器里的本地连接不要口令，原因见 21.2 的 pg_hba.conf：

终端窗口
docker compose exec postgres psql -U postgres

本章 21.3 和 21.4 的 SQL 和以 \ 开头的元命令都在这个提示符下输入，\q 退出。宿主机上装了 psql（装法见第 20 章 20.1）的，也可以直接用连接串连，这时要输口令：

终端窗口
psql postgresql://postgres:dev@localhost:5432/postgres

查版本，前一条查本机客户端，后一条在 psql 里查服务端，返回以 PostgreSQL 17. 开头的一行：

终端窗口
psql --version
SELECT version();

不想用 Docker、要在本机安装服务的，见 21.7 的“下载”。

21.2 五分钟概念

Section titled “21.2 五分钟概念”
  • 集群（Cluster）与数据库：一个 PostgreSQL 服务进程管一个数据目录，这一整套叫集群，不是多台机器的意思。集群里有多个数据库，彼此隔离，一条 SQL 不能跨库联表；psql 连上时就选定了库，换库要重新连（\c）。
  • 角色（Role）：用户和用户组都叫角色，带 LOGIN 属性的才能登录。角色属于整个集群，不属于某个库，所以 pg_dump 导出一个库时不含角色，要用 pg_dumpall 另外导。
  • 模式（Schema）：库里的命名空间，表放在模式下。不写模式名时按 search_path 去找，默认先找与当前用户同名的模式，再找 public。PostgreSQL 15 起，public 模式只有库的所有者能建表，其他用户建表报权限错误，见 21.6。
  • 客户端认证 pg_hba.conf：每一行写“什么连接方式、连哪个库、哪个用户、从哪个地址来、用什么方式认证”，服务端从上往下找第一条匹配的行，找不到就拒绝。随书镜像里，容器内经 Unix 套接字的连接是 trust（不要口令），从宿主机经端口进来的连接要口令（scram-sha-256）。
  • 预写日志（WAL，Write-Ahead Log）：每次改动先顺序写进 pg_wal/ 目录里的日志，再慢慢写回数据文件。断电后重启按 WAL 重放，提交过的事务不丢；主从复制和按时间点恢复也靠它。
  • 扩展（Extension）：装进某个库的附加功能，CREATE EXTENSION 名字; 启用，\dx 查看。官方镜像自带 pg_stat_statements（统计每类查询的耗时）、pg_trgm（模糊搜索加速）等；PostGIS、pgvector 这类第三方扩展要换用带它们的镜像。

下图是这几样东西的关系：客户端先过 pg_hba.conf 这一关，再进到某一个库；角色在库外面，对哪个库、哪个模式有什么权限靠授权决定；所有库的改动都先写进同一份 WAL。

客户端经 pg_hba.conf 认证后进入集群里的某个数据库；数据库下分模式，模式下是表；角色属于整个集群，通过授权访问库里的对象；改动先写进 WAL

21.3 常用命令

Section titled “21.3 常用命令”

元命令以 \ 开头，不用分号，只在 psql 里有效；SQL 要以分号结尾。备份恢复一组是命令行程序，在容器里执行时前面加 docker compose exec postgres，如 docker compose exec postgres pg_dump -U postgres -Fc -f /tmp/app.dump app，文件也在容器里。示例库叫 app，表 orders，reader 是只读用户。

任务 命令 说明
psql 元命令
列出数据库 \l 含所有者、编码；\l+ 再加大小
换库 \c app 重新连到 app 库，提示符变成 app=#
列出表 \dt 只列 search_path 里的模式；指定模式写 \dt report.*
看表结构 \d orders 列、类型、默认值、索引、外键、触发器都列出
列出角色 \du 含超级用户、能否登录、能否建库
列出模式 \dn \dn+ 带上权限
竖着显示 \x 再输一次切回；\x auto 宽了才竖排，看长行好用
显示耗时 \timing 之后每条 SQL 后打印 Time: 12.345 ms
执行 SQL 文件 \i init.sql 路径相对于启动 psql 的目录，在容器里就是容器里的路径
导出 CSV 到客户端 \copy orders TO 'orders.csv' CSV HEADER 文件写在 psql 这一边；SQL 的 COPY 写在服务器那一边
从 CSV 导入 \copy orders FROM 'orders.csv' CSV HEADER 列顺序要和表一致，或在表名后写列名
退出 \q 也可以按 Ctrl+D
用户与权限
建能登录的用户 CREATE ROLE app LOGIN PASSWORD 'app_pw'; 不写 LOGIN 就只是个用户组
建库并指定所有者 CREATE DATABASE app OWNER app; 所有者能在库里的 public 模式建表
改口令 ALTER ROLE app PASSWORD 'new_pw'; 用 \password app 输两遍更安全，口令不会进命令历史
允许连接某个库 GRANT CONNECT ON DATABASE app TO reader; 默认所有角色都能连，收紧后要单独给
允许使用模式 GRANT USAGE ON SCHEMA public TO reader; 没有它连表名都看不到
给已有表只读 GRANT SELECT ON ALL TABLES IN SCHEMA public TO reader; 只管执行时已有的表
以后建的表也只读 ALTER DEFAULT PRIVILEGES FOR ROLE app IN SCHEMA public GRANT SELECT ON TABLES TO reader; 由 app 新建的表自动授权给 reader
允许在 public 建表 GRANT CREATE ON SCHEMA public TO app; 不是库所有者又要建表时用
备份恢复
导出一个库（归档格式） pg_dump -U postgres -Fc -f app.dump app 压缩的二进制文件，恢复时能挑表
导出成 SQL 文本 pg_dump -U postgres -f app.sql app 能直接打开看，只能用 psql 整个恢复
从归档恢复 pg_restore -U postgres -d app_copy app.dump 目标库要先建好；覆盖已有对象加 --clean --if-exists
只恢复一张表 pg_restore -U postgres -d app_copy -t orders app.dump 只有归档格式能这样挑
看归档里有什么 pg_restore -l app.dump 列出表、索引、数据各项，不连数据库
从 SQL 文本恢复 psql -U postgres -d app_copy < app.sql 出错默认继续执行，加 -v ON_ERROR_STOP=1 遇错即停
导出所有库和角色 pg_dumpall -U postgres -f all.sql SQL 文本；只要角色和口令加 -g
运维
看当前连接 SELECT pid, usename, datname, state, query FROM pg_stat_activity; state 为 idle in transaction 的是开着事务没提交
结束一个连接 SELECT pg_terminate_backend(12345); 参数是上一条查到的 pid；只取消正在跑的语句用 pg_cancel_backend
回收空间并更新统计 VACUUM ANALYZE orders; 平时有自动清理（autovacuum），大批量删改后手动跑一次
看表大小 SELECT pg_size_pretty(pg_total_relation_size('orders')); 含索引和 TOAST，如 86 MB
最大的十张表 SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10; 当前库里的
看库大小 SELECT pg_size_pretty(pg_database_size('app'));
看一个配置项 SHOW max_connections; SHOW config_file; 显示配置文件路径
启用扩展 CREATE EXTENSION IF NOT EXISTS pg_trgm; 只对当前库生效，\dx 查看

21.4 典型工作流

Section titled “21.4 典型工作流”

给应用建专用的库和用户

Section titled “给应用建专用的库和用户”

程序不该用超级用户 postgres 连库：它能删任何库、读任何表。给每个应用建一个用户和一个归它所有的库：

  1. 起数据库：docker compose --profile db up -d。
  2. 以超级用户进 psql：docker compose exec postgres psql -U postgres。
  3. 建能登录的用户，口令换成自己的：CREATE ROLE app LOGIN PASSWORD 'app_pw';。
  4. 建库，所有者设成这个用户：CREATE DATABASE app OWNER app;。
  5. 退出：\q。
  6. 以新用户连新库，容器里不要口令：docker compose exec postgres psql -U app -d app。
  7. 建一张表，PostgreSQL 15 起能成功正是因为 app 是库的所有者：CREATE TABLE notes (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, body text NOT NULL);。
  8. 看表的所有者是 app：\dt。

程序里的连接串写 postgresql://app:app_pw@localhost:5432/app，从宿主机连要口令，正好验证第 3 步的口令。要一个只读账号给报表或排查用：先 CREATE ROLE reader LOGIN PASSWORD 'reader_pw';，再在 app 库里执行 21.3 用户与权限一组给 reader 的三条 GRANT 和一条 ALTER DEFAULT PRIVILEGES。

备份一个库，恢复成另一个库

Section titled “备份一个库，恢复成另一个库”

docker compose exec 默认给命令分配一个终端，终端会改写输出里的换行符，二进制的归档文件就坏了。经管道或重定向进出容器时一律加 -T。下面的命令在随书 compose/ 目录下执行：

  1. 把 app 库导出到宿主机的 app.dump：docker compose exec -T postgres pg_dump -U postgres -Fc app > app.dump。
  2. 建一个空库作为恢复目标，所有者同样是 app：docker compose exec postgres createdb -U postgres -O app app_copy。
  3. 把宿主机上的归档送进容器恢复，这是 bash 的写法，PowerShell 见下面的块：docker compose exec -T postgres pg_restore -U postgres -d app_copy < app.dump。
  4. 核对行数和原库一致：docker compose exec postgres psql -U postgres -d app_copy -c "SELECT count(*) FROM notes"。
  5. 演练完删掉：docker compose exec postgres dropdb -U postgres app_copy。
终端窗口
Get-Content app.dump -AsByteStream -Raw | docker compose exec -T postgres pg_restore -U postgres -d app_copy

第 1 步的 > 和第 3 步的管道在 PowerShell 7.4 以上原样传字节；Windows 自带的 Windows PowerShell 5.1 会把输出改成 UTF-16 文本，归档文件就损坏了，要用 PowerShell 7。只在同一个集群里复制一个库，不需要文件：CREATE DATABASE app_copy TEMPLATE app;，前提是这时没有别的连接连着 app。定期备份、异地保存和恢复演练的做法见第 20 章 20.4。

找出慢查询并加索引

Section titled “找出慢查询并加索引”
  1. 进 app 库：docker compose exec postgres psql -U postgres -d app。
  2. 造一张一百万行的订单表练手：CREATE TABLE orders AS SELECT g AS id, g % 1000 AS user_id, round((random() * 100)::numeric, 2) AS amount FROM generate_series(1, 1000000) g;。
  3. 看现在有哪些语句跑得久，duration 最大的排最前：SELECT pid, now() - query_start AS duration, state, query FROM pg_stat_activity WHERE state <> 'idle' ORDER BY duration DESC;。
  4. 让超过 500 毫秒的语句写进日志，不用重启：ALTER SYSTEM SET log_min_duration_statement = '500ms';，再 SELECT pg_reload_conf();。
  5. 在另一个终端看日志里的 LOG: duration: … ms statement: … 行：docker compose logs -f postgres。
  6. 打开耗时显示，对慢语句看执行计划，出现 Seq Scan on orders（大表常是 Parallel Seq Scan）就是全表扫描：\timing，再 EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;。
  7. 建索引，CONCURRENTLY 让建索引期间表照样能写：CREATE INDEX CONCURRENTLY idx_orders_user ON orders (user_id);。
  8. 更新统计后再看一次，变成 Bitmap Index Scan on idx_orders_user，Execution Time 明显变小：ANALYZE orders;，再执行第 6 步的 EXPLAIN ANALYZE。

日志和 pg_stat_activity 只能看到单条语句。要按“同一类查询”汇总调用次数和平均耗时，用 pg_stat_statements 扩展：先 ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements'; 并 docker compose restart postgres，再 CREATE EXTENSION pg_stat_statements;，之后执行 SELECT query, calls, mean_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;。

21.5 配置要点

Section titled “21.5 配置要点”

配置文件的位置，pg_hba.conf 和 postgresql.conf 在同一个目录：

范围 路径
配置文件（Linux，Debian/Ubuntu 包） /etc/postgresql/<版本>/main/postgresql.conf
配置文件（Windows 安装包） 安装目录下的 data\postgresql.conf，如 C:\Program Files\PostgreSQL\17\data\
配置文件（Docker 镜像内） /var/lib/postgresql/data/postgresql.conf，随卷 pgdata 保存
运行中查路径 SHOW config_file;、SHOW hba_file;
日志（Docker） 写到标准错误，用 docker compose logs postgres 看

改法有两种。直接编辑文件；或在 psql 里执行 ALTER SYSTEM SET 配置项 = 值;，它写进同目录的 postgresql.auto.conf，优先于 postgresql.conf，Docker 里不用进容器改文件就用它。改完 SELECT pg_reload_conf(); 重新加载，标着“要重启”的项还得 docker compose restart postgres（本机安装用 sudo systemctl restart postgresql）。pg_hba.conf 改完重新加载即可。

值得改的项：

配置项 建议值 效果
listen_addresses '*' 监听所有网卡，别的机器才连得进来；默认 'localhost'，官方镜像已改为 '*'；要重启
max_connections 200 最大连接数，默认 100；每个连接是一个进程，别设到几千，连接多用连接池；要重启
shared_buffers 内存的 25%，如 2GB 数据页缓存，默认 128MB 偏小；要重启
log_min_duration_statement 500ms 超过 500 毫秒的语句写进日志；默认 -1 不记
timezone 'UTC' 会话的默认时区，影响 now() 和 timestamptz 的显示；官方镜像默认 Etc/UTC
idle_in_transaction_session_timeout '10min' 开着事务不动超过 10 分钟就断开，防止长事务拖住清理

postgresql.conf 的最小片段，# 后面是注释，可以写在行尾：

listen_addresses = '*'                       # 监听所有网卡，改了要重启
max_connections = 200                        # 最大连接数，改了要重启
shared_buffers = 2GB                         # 约为内存的 25%，改了要重启
log_min_duration_statement = 500ms           # 超过 500 毫秒的语句写进日志
timezone = 'UTC'                             # 会话默认时区
idle_in_transaction_session_timeout = 10min  # 事务空闲 10 分钟就断开

pg_hba.conf 的最小片段，从上往下匹配，第一条匹配的行生效：

# 类型  数据库  用户  地址          认证方式
local   all     all                 peer           # 本机 Unix 套接字：系统用户名和数据库用户名相同才放行
host    all     all   127.0.0.1/32  scram-sha-256  # 本机 TCP 连接，要口令
host    app     app   10.0.0.0/24   scram-sha-256  # 内网这一段只许 app 用户连 app 库，要口令

21.6 注意事项

Section titled “21.6 注意事项”
  • FATAL: password authentication failed for user "app"。 原因：口令错，或连到了本机另装的 PostgreSQL。处理：容器里 \password app 重设；netstat -ano | findstr :5432 查端口，冲突就把 compose 端口改为 127.0.0.1:5433:5432。
  • 改了 compose 的 POSTGRES_PASSWORD，新口令仍报 password authentication failed。 原因：镜像只在卷为空的首次启动时用它，口令早存在卷里。处理：psql 里 ALTER ROLE postgres PASSWORD '新口令';；不要数据才 down -v，MySQL、MongoDB 的卷也会删。
  • FATAL: database "app" does not exist，只是执行了 psql -U app。 原因：不写 -d 时 psql 连与用户同名的库。处理：指定一个存在的库，如 psql -U app -d postgres；或先建库（同 21.4 第 2 步）。
  • 建表报 ERROR: permission denied for schema public。 原因：PostgreSQL 15 起只有库的所有者能在 public 模式建表，旧教程的写法不再管用。处理：ALTER DATABASE app OWNER TO app;，或 GRANT CREATE ON SCHEMA public TO app;。
  • 别的机器连 5432 报 Connection refused（旧版是 could not connect to server）。 原因：本机安装时 listen_addresses 默认 localhost；随书 compose 端口只绑 127.0.0.1。处理：前者改 '*' 重启、pg_hba.conf 放行；后者改为 5432:5432（有暴露风险）。
  • pg_restore: error: unsupported version (1.16) in file header。 原因：宿主机的 pg_restore 16 读不了容器里 pg_dump 17 导出的新格式归档。处理：用容器里的同版本工具 docker compose exec -T postgres pg_restore …；或把本机客户端升到 17。
  • 想从备份里只恢复一张表，pg_restore 却报 input file appears to be a text format dump. Please use psql.。 原因：导出时没加 -Fc，得到的是 SQL 文本。处理：只能整体恢复到临时库 psql -d app_copy -f app.sql 再挑表；以后导出一律加 -Fc。
  • 表的行数没怎么变，pg_total_relation_size 却越来越大，查询变慢。 原因：长事务一直开着，之后删改的旧行版本清理不掉（表膨胀）。处理：pg_stat_activity 找 idle in transaction 的 pid 并 pg_terminate_backend；设 idle_in_transaction_session_timeout。

21.7 深入阅读

Section titled “21.7 深入阅读”
  • PostgreSQL 文档：官方手册，SQL 命令、数据类型、配置项、备份与复制都在这里，页面顶部可切换版本。
  • psql 参考：psql 的全部命令行参数和以反斜杠开头的元命令，含 \copy、\x、\timing。
  • pg_dump 参考：四种导出格式、只导某些表或模式的参数，以及跨版本导出的注意事项。
  • 客户端认证 pg_hba.conf：pg_hba.conf 每一列的写法、匹配顺序和 trust、peer、scram-sha-256 等认证方式。
  • 调优你的 PostgreSQL 服务器（PostgreSQL wiki）：shared_buffers、work_mem、max_connections 等关键配置项该怎么估算。
  • 下载：不想用 Docker 时，Windows 安装包、macOS、各 Linux 发行版的官方安装方法。