PostgreSQL入门

发表于 2019-12-12 更新于 2026-11-05 分类于 Python Python教程 Python基础本文字数： 55k 阅读时长 ≈ 1:40

这篇讲什么

这篇把 PostgreSQL 当成「MySQL 之外的第二个关系库」来讲：装在哪里、连得上吗、类型和约束怎么写、索引与执行计划怎么读、事务与并发到底锁了什么，再到备份恢复、流复制、逻辑复制，以及 Python（psycopg 3）和 Django 怎么接上去。
版本口径是 PostgreSQL 18.6，装在 WSL2 的 Ubuntu 26.04.1 上，端口 5600；文中每条标了「实测」的输出都是这台机器跑出来的原始结果，MySQL 与 PostgreSQL 的行为差别也逐个验证过才写。
想快速上手的顺序：安装连接 → 类型与约束 → 查询 → Python 操作，后面几节（复制、VACUUM、权限模型）等真要用了再看。

PostgreSQL 是什么

PostgreSQL（简称 PG，社区里也叫 Postgres）是一个开源的对象-关系型数据库：SQL 是它的接口，但它骨子里更像「带事务和 SQL 前端的通用数据存储平台」。相比 MySQL，它常被提起的几点是：
- 类型系统丰富：numeric、数组、jsonb、uuid、范围、几何、复合类型都是原生一等公民，还能自己 create type 造枚举、范围类型。
- 标准与一致性：DDL 在事务里（create table 能回滚）、约束能 deferrable、大小写折叠规则严格照 SQL 标准来。
- MVCC 实现不同：PG 用「行多版本 + 死元组 + VACUUM」，MySQL InnoDB 用「undo log 回滚段」，所以 PG 需要关心表膨胀，MySQL 需要关心 undo。
- 扩展机制：create extension 打开官方 contrib（pg_stat_statements、hstore、pgcrypto……），第三方向量库、PostGIS 都靠它。
代价也要说清：PG 没有 MySQL 那种「一个进程内部线程池」的连接模型，每条连接 fork 一个 backend 进程，连接数是最先要花钱的资源（下面有实测上限）。

本机的进程模型（实测 ps，父进程是 384 那个 postmaster）：

499   384  3176 postgres: 18/main: io worker 2
506   384 21640 postgres: 18/main: checkpointer
507   384  5108 postgres: 18/main: background writer
535   384  7536 postgres: 18/main: walwriter
536   384  5120 postgres: 18/main: autovacuum launcher
537   384  4500 postgres: 18/main: logical replication launcher

每个客户端连接就是一个 postgres: 18/main: <用户> <库> <地址> 的子进程（实测 pg_backend_pid() 返回的 pid 就是其中一个），不是线程——所以「连接数」在 PG 上是实打实的进程数。
io worker 这几行是 PG18 的新默认（io_method = worker，max_worker_processes = 8，实测），老版本这里是 archiver/stats collector 之类。

版本口径与官方安装路线

PG 没有 LTS 这条线，别按 MySQL/MongoDB 的思路找「长期维护版」。官方 Versioning Policy 的口径是：大约每年发一个大版本，每个大版本自发布起支持 5 年；小版本至少每季度一次，里面既有 bug 修复也含安全修复。现役大版本（2026-11 抄自那张表）：

大版本	当前小版本	首发	最终小版本日期
18	18.6	2025-09-25	2030-11-14
17	17.11	2024-09-26	2029-11-08
16	16.15	2023-09-14	2028-11-09
15	15.19	2022-10-13	2027-11-11
14	14.24	2021-09-30	2026-11-12

13 及更早已经停止支持，网上大量教程还在用 9.x、10 的写法，抄之前先对一下这张表。
大版本升级要 pg_dump/pg_restore 或 pg_upgrade（数据目录格式不向后兼容）；小版本只需停服、换二进制、再起。

官方推荐的两条安装路线（Ubuntu 下载页）：

发行版自带源：sudo apt install postgresql——官方原话是 “Ubuntu includes PostgreSQL by default”，但它是随 Ubuntu 发布冻结的一个快照，只跟到该发行版的生命周期。
PGDG 官方 apt 源（apt.postgresql.org）：想要任意受支持版本、并且跟着它的支持期一直拿更新，就用这条。官方给的自动化写法只有两行：
1
2
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

手工配置则是把 key 放到 /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc，再写 /etc/apt/sources.list.d/pgdg.sources，Suites: 填 resolute-pgdg（resolute 是 Ubuntu 26.04 的代号），最后 sudo apt install postgresql-18。

本机走的是第 1 条（发行版自带源），第 2 条未在本机实测；apt-cache policy 能看到版本从哪来：

postgresql-18:
  Installed: 18.6-0ubuntu0.26.04.1
  Candidate: 18.6-0ubuntu0.26.04.1
  Version table:
 *** 18.6-0ubuntu0.26.04.1 500
        500 http://archive.ubuntu.com/ubuntu resolute-updates/main amd64 Packages
        500 http://security.ubuntu.com/ubuntu resolute-security/main amd64 Packages
    18.3-1 500
        500 http://archive.ubuntu.com/ubuntu resolute/main amd64 Packages

读法：发行版 GA 时冻结的是 18.3，小版本 18.6 是通过 resolute-updates 和 resolute-security 推上来的——这也是「为什么不用 PGDG 也能拿到最新小版本」的答案，但拿不到「更新的 major」。

装完的包与二进制（实测）：

libpq5:amd64 18.6-0ubuntu0.26.04.1
postgresql 18+290ubuntu1
postgresql-18 18.6-0ubuntu0.26.04.1
postgresql-18-jit 18.6-0ubuntu0.26.04.1
postgresql-client-18 18.6-0ubuntu0.26.04.1
postgresql-client-common 290ubuntu1
postgresql-common 290ubuntu1
psql (PostgreSQL) 18.6 (Ubuntu 18.6-0ubuntu0.26.04.1)

注意 postgresql-common（Ubuntu 打的包，版本 290ubuntu1）才是 pg_ctlcluster、pg_lsclusters、pg_createcluster 这组命令的来源；Debian/Ubuntu 的集群布局不是「数据目录里放 postgresql.conf」，而是配置在 /etc/postgresql/<版本>/<集群名>/、数据在 /var/lib/postgresql/<版本>/<集群名>/。

本机第一次连上它

服务与监听（实测）：

1
2
3

systemctl is-active postgresql        # active
systemctl is-enabled postgresql       # enabled
ss -ltnp | grep 5600

1 2	LISTEN 0 200 0.0.0.0:5600 users:(("postgres",pid=267,fd=6)) LISTEN 0 200 [::]:5600 users:(("postgres",pid=267,fd=7))

端口为什么是 5600 而不是 5432：这台 Windows 机器的 TCP 动态保留区间把 5432 吃掉了（netsh interface ipv4 show excludedportrange protocol=tcp 实测片段）：
1
2
3
4
开始端口结束端口
5284 5383
5384 5483
5484 5583
- 5432 落在 5384-5483 里，WSL 里起服务、Windows 侧连过去时容易被 Hyper-V/WinNAT 抢占，出现「监听正常但连不上」。本机把 PG 挪到 5600，顺带避开了 5484-5583。
- 改端口的位置是 /etc/postgresql/18/main/postgresql.conf 的 port，或者 Debian 系用 pg_ctlcluster 18 main 时的集群配置；改完要重启集群。

关键参数（实测 show 一圈）：

server_version           18.6 (Ubuntu 18.6-0ubuntu0.26.04.1)
port                     5600
data_directory           /var/lib/postgresql/18/main
hba_file                 /etc/postgresql/18/main/pg_hba.conf
unix_socket_directories  /var/run/postgresql
max_connections          100
shared_buffers           128MB
wal_level                replica

Ubuntu 装完默认连不上的原因：postgres 角色初始没有密码，而 pg_hba.conf 给本地 socket 的是 peer 认证（实测生效规则）：

local   all             postgres                                peer
local   all             all                                     peer
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    all             all             0.0.0.0/0               scram-sha-256

local 行是 Unix socket，peer 表示「操作系统用户名的角色名 == 数据库角色名」就放行，所以最顺的第一次登录是：
1
sudo -u postgres psql # 不用密码

要走 TCP（比如从 Windows 连、或者用 psycopg）就得给角色设密码，scram-sha-256 行才会放行：

-- 二选一：\password 是 psql 的交互命令，不会把明文留在历史里
\password postgres
-- 或者直接写 SQL（注意：这么写，明文会进语句、日志与 pg_stat_statements，见后文）
ALTER ROLE postgres PASSWORD 'password';

最后那条 host all all 0.0.0.0/0 scram-sha-256 是本机为了从 Windows 侧连 WSL 加的；生产上不要留 0.0.0.0/0，要按网段收窄。

库与角色（实测 \l 与 pg_roles）：

   datname
-------------
 django_note
 postgres
 template0
 template1

 rolname                       | rolsuper | rolcanlogin
-------------------------------+----------+------------
 django                         | f        | t
 pg_checkpoint                  | f        | f
 pg_create_subscription         | f        | f
 pg_read_server_files           | f        | f
 pg_monitor                     | f        | f

pg_ 开头那批是不能登录的角色组（rolcanlogin=f），权限是靠「把角色 grant 给它们」来实现的，别去给它们设密码。
template0/template1 是建库时的模板；create database 默认抄 template1，要一个干净的空库得 template template0。

psql 的常用姿势

命令行式与交互式两用的几个开关（实测都在用）：

psql -h 127.0.0.1 -p 5600 -U postgres -d postgres          # 进交互
psql -h 127.0.0.1 -p 5600 -U postgres -d pgnote_probe \
      -P pager=off -c "select version();"                  # 跑一句就走
psql ... -tA -c "select current_database();"               # -t 去表头 -A 无边框：脚本里取值

1 2	-tA 的输出：pgnote_probe -c 的输出：一行带表头的表格；超过一屏会自动进 pager（-P pager=off 关掉，别在脚本里卡住）

元命令（反斜杠开头，是 psql 自己的，不是 SQL）：

命令	干什么	实测片段
`\l`	列库	输出里有 `Collate = C.UTF-8`、`Locale Provider = libc`
`\dt`	列表	`List of tables`，带 Schema/Name/Type/Owner 四列
`\d+ 表名`	看表结构+存储细节	多出 `Storage`、`Compression`、`Stats target` 三列
`\x`	竖排显示宽行	`-[ RECORD 1 ]` 那种一行一字段
`\copy`	客户端读写本地文件	普通账号也能用（服务端 `COPY` 不行，见权限节）
`\pset`	调输出格式	`\pset border 2` 换框线
`\timing`	每条语句计时	交互式排查询有用

中文/带空格的别名会炸，这是本机第一次踩到的语法坑（实测报错原文）：

1	select count(*) as 默认路径下能直接看到 orders 吗 from shop.orders;

1
2
3

ERROR:  syntax error at or near "orders"
LINE 1: select count(*) as 默认路径下能直接看到 orders 吗 from shop....
                                                ^

标识符里有空格就得加双引号：as "默认路径下能直接看到 orders 吗"。中文本身不用引号，PG 允许非 ASCII 标识符。

从脚本里批量喂 SQL 用 heredoc 比 -c 串十几条好维护，也避免 shell 把 $、反引号吃掉：

psql -h 127.0.0.1 -p 5600 -U postgres -d pgnote_probe <<'SQL'
begin;
insert into t_cat(name) values ('服饰');
select count(*) from t_cat;
rollback;
SQL

从 MySQL 过来最容易搞错的六件事

1. 标识符默认折成小写。不带引号的名字全部变小写：CREATE TABLE MyTable (ID int) 建出来的是 mytable(id)；想保留大小写必须一路带双引号，而且带错就找不到表（实测）：
1
2
select * from mytable; -- 有结果
select * from "MyTable"; -- 报错
1
2
3
ERROR: relation "MyTable" does not exist
LINE 1: select * from "MyTable";
^
- 结论（也是官方风格建议）：库里的表名列名一律小写下划线，别用引号名。这条和 Python 侧 PEP 8 的命名要求正好一致。反引号 ` 在 PG 里不是引用标识符的字符，那是 MySQL 的写法。

2. DDL 能进事务。create table / drop table 都在事务里，可以回滚（实测）：

begin;
create table t_rollback(id int);
select count(*) as 事务里可见 from t_rollback;   -- 1（建表语句后立刻能用）
rollback;
select to_regclass('public.t_rollback') as 回滚后还在吗;

1
2
3

 回滚后还在吗
--------------
(空)

MySQL 会隐式提交 DDL，所以「迁移失败自动回滚」这种事在 PG 上是真的成立的；Django 的 migration 在 PG 上能整段回滚，就是靠这个。

3. 整除返回整数，1/3 = 0 不是 bug（实测）：

1	select 1/3 as int_div, 1.0/3 as num_div, 1.0::float8/3 as f8_div;

1
2
3

 int_div |          num_div           |       f8_div
---------+----------------------------+---------------------
       0 | 0.33333333333333333333     | 0.3333333333333333

要小数就显式写 1.0 或 ::numeric；numeric 是精确的任意精度，float8 只有 15 位左右有效数字。

4. 分页写法不同：PG 支持标准 SQL 的 fetch first n rows only，也支持 limit n offset m，还支持 limit all（实测三种都能跑，limit 3 与 fetch first 3 rows only 返回同一批 1,2,3）。MySQL 的 limit 3 offset 2 PG 也认，但 select ... limit 3, 2 这种逗号写法 PG 直接语法错。
5. 没有 group by 也要能跑。PG 严格照 SQL 标准：select city, item, count(*) ... group by city 会报 column "t_sale.item" must appear in the GROUP BY clause。MySQL 老默认（ONLY_FULL_GROUP_BY 关闭）放过这种写法，迁过来时最容易集中报错。
6. 布尔是真的布尔列。boolean 存 t/f，默认值写 'draft'::item_state 这类带类型的字面量很常见；MySQL 里 tinyint(1) 那种「0/1 当布尔」的写法在 PG 里要显式 true/false（实测 \d+ 与查询输出里布尔列就是 t/f）。

数据类型

下面这张表是本机 create table 之后用 \d+ t_item 的真实输出整理的，覆盖入门最常用的类型：

列	类型	实测要点
`id`	`integer` + `serial`	默认值显示为 `nextval('t_item_id_seq'::regclass)`，序列是独立对象
`sku`	`text not null`	PG 里 `text` 没有性能劣势，默认推荐 `text` 而不是 `varchar(n)`
`price`	`numeric(10,2)`	精确小数，`Storage: main`（不 TOAST）
`qty`	`integer not null`	配 `check (qty >= 0)`
`state`	`item_state`（自建 enum）	默认 `'draft'::item_state`
`tags`	`text[]`	默认 `'{}'::text[]`，数组是原生类型
`meta`	`jsonb`	默认 `'{}'::jsonb`，二进制存储、可建 GIN 索引
`ext_id`	`uuid`	默认 `gen_random_uuid()`，PG13 起是内置函数
`created`	`timestamp with time zone`	默认 `now()`，输出带 `+08`
`upper_sku`	`text generated always as (upper(sku)) stored`	生成列，`attgenerated = 's'`

\d+ 尾部还有两行值得注意：Access method: heap，以及 PG 把每个 not null 也当成一条命名约束列出来（Not-null constraints: 段落，形如 "t_item_id_not_null" NOT NULL "id"）。

numeric 与 float8：钱只能用 numeric（实测）：
1
2
3
select 0.1::float8 + 0.2::float8 = 0.3::float8 as float8相等,
0.1::numeric + 0.2::numeric = 0.3::numeric as numeric相等,
0.1::float8 + 0.2::float8 - 0.3::float8 as 差值;
1
2
3
float8相等 | numeric相等 | 差值
------------+-------------+----------------------
f | t | 5.551115123125783e-17
- 那个差值就是 IEEE-754 双精度的固有误差；0.1::float8 + 0.2::float8 实际等于 0.30000000000000004（实测 = 0.30000000000000004::float8 为 t）。
- 显示上看不出问题是因为 extra_float_digits = 1（实测默认值）会用最短往返格式打印。
- 判等要修约：round(0.1::numeric + 0.2::numeric, 1) = 0.3::numeric 实测 t。

numeric(p,s) 的两种越界（实测，差别很关键）：

--- 只超标度（小数位）：静默四舍五入，不报错
insert into t_item(sku, qty, price) values('d-400', 1, 12345678.901);
 price
------------
12345678.90

--- 超精度（整数位放不下）：直接报错
ERROR:  numeric field overflow
DETAIL:  A field with precision 10, scale 2 must round to an absolute value less than 10^8.

也就是说「小数位多了会被自动四舍五入」这件事必须自己心里有数，只有整数值装不下才会拦你。

类型转换失败的报错原文（写卡点表用得上，都是实测）：

ERROR:  invalid input syntax for type integer: "abc"
ERROR:  value too long for type character varying(3)
ERROR:  value too long for type character(5)
ERROR:  division by zero
ERROR:  null value in column "qty" of relation "t_item" violates not-null constraint
DETAIL:  Failing row contains (4, d-400, null, 12345678.90, null, draft, {}, {}, ...).

注意 division by zero：整数、numeric、float8 除零在 PG 里全是报错（实测 1/0、1.0/0、0.0::float8/0 三条都报错），不会返回 Infinity。浮点的 infinity 值本身是存在的（'infinity'::float8 可用），只是除零不走它。
那条 DETAIL: Failing row contains (...) 很实用——它把整行原样吐出来，排错时直接看是哪个字段为空。

字符类型的空格问题（实测，和 MySQL 的 CHAR 一样会补空格，但取出来的行为要说清）：

create table t_ch3(a char(5), b varchar(5), c text);
insert into t_ch3 values ('ab', 'ab', 'ab');
select length(a) as 长度函数, octet_length(a) as 字节数,
       octet_length(a::text) as 转text后字节, a::text = 'ab   ' as 转text还带空格,
       a = 'ab  ' as 比较忽略尾空格, char_length(a) as 字符数,
       encode(convert_to(a::text, 'UTF8'), 'hex') as 存储字节
from t_ch3;

1
2
3

 长度函数 | 字节数 | 转text后字节 | 转text还带空格 | 比较忽略尾空格 | 字符数 | 存储字节
----------+--------+--------------+----------------+----------------+--------+----------
        2 |      5 |            2 | f              | t              |      2 | 6162

读法：char(5) 这一列宽度是 5（octet_length 数出 5），但 length()/char_length() 忽略尾随空格所以是 2；转成 text 之后尾随空格被去掉（6162 就是 ab）。这和官方文档 Character Types 的两句话完全对应： “Trailing spaces are removed when converting a character value to one of the other string types.”，以及比较时 “trailing spaces are treated as semantically insignificant”。
因此 'ab'::char(5) = 'ab'::char(3) 实测是 t——char(n) 比较不看宽度。结论还是别用 char(n)，用 text 加 check (length(x) between 1 and 50)，或 varchar(n) 只在你真的要限长时用。
name 是标识符专用的内部类型，实测超长会被截到 63 字节（select length('…80 个 a…'::name) → 63）。

数组、jsonb、uuid 的实际行为（实测）：

1 2	select sku, tags, tags @> array['外设'] as has_外设, '促销' = any(tags) as is_促销 from t_item order by id;

  sku  |    tags     | has_外设 | is_促销
-------+-------------+----------+---------
 a-100 | {编程,教材} | f        | f
 b-200 | {外设}      | t        | f
 c-300 | {外设,促销} | t        | t

select '{"b":1,"a":2}'::json  as json原样,
       '{"b":1,"a":2}'::jsonb as jsonb重排,
       '{"a":1,"a":2}'::jsonb as jsonb去重,
       '{"a":{"b":[1,2]}}'::jsonb -> 'a' -> 'b' -> 1 as 取元素,
       '{"a":1}'::jsonb || '{"b":2}'::jsonb as 合并;

1
2
3

     json原样    |    jsonb重排    | jsonb去重 | 取元素 |       合并
-----------------+-----------------+-----------+--------+------------------
 {"b":1,"a":2}   | {"a": 2, "b": 1} | {"a": 2}  | 2      | {"a": 1, "b": 2}

json 是「原文存着、每次解析」，jsonb 是「解析后存、键序重排 + 同名键去重 + 能建 GIN 索引」。99% 的情况选 jsonb。
gen_random_uuid() 实测不需要任何扩展就能用；uuid-ossp 的 uuid_generate_v4() 才需要 create extension "uuid-ossp"，而这台机器上 pg_available_extensions 里 uuid-ossp 是 1.1、installed_version 为空（没装）。老教程里 create extension "uuid-ossp" 那一步现在基本可以删掉（本站另有一篇讲宝塔环境装 uuid-ossp 的老笔记，看的时候按这条订正）。

自增的三种写法要分清（实测对比）：

写法	元数据证据	语义
`id serial`	默认值 `nextval('t_item_id_seq'::regclass)`，`pg_sequences` 里有 `t_item_id_seq`（`increment_by=1`、`cache_size=10`）	只是「建序列 + 设默认值」的语法糖，序列和列没关系，删列不会删序列
`id int generated always as identity`	`pg_attribute.attidentity = 'a'`	标准 SQL，列拥有序列，`drop column` 会带走它
`生成列`	`pg_attribute.attgenerated = 's'`	`generated always as (upper(sku)) stored`，值由表达式算，不能手写

给 generated always as identity 列显式塞值会被拦（实测）：

1
2
3

ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

导数据时就得用这个 HINT 给的写法（实测可用）：insert into t_gen(id, v) overriding system value values (99, '乙') → 插入成功，且生成列 c1 自动变成 198。
序列的常用操作（实测）：nextval 拿到 5 → setval('t_item_id_seq', 1000) → 下一次取到 1001。pg_get_serial_sequence('t_gen','id') 对 identity 列同样能返回序列名（实测非空），所以 setval 统一走它最稳。
Django 的 AutoField/BigAutoField 在 PG 上生成的是 serial/bigserial 风格的默认值，不是 identity；这点在后面的 Django 一节有实测的建表 SQL。

日期时间（实测）：

1
2
3

select '2026-11-05'::date - '2026-10-01'::date as day_diff,
       '2026-11-05 10:00+08'::timestamptz at time zone 'UTC' as to_utc,
       '1 day 2 hours'::interval + '3 hours'::interval as iv;

1
2
3

 day_diff |       to_utc        |       iv
----------+---------------------+-----------------
        35 | 2026-11-05 02:00:00 | 1 day 05:00:00

select date_trunc('hour', timestamp '2026-11-05 17:46:12') as 截到小时,
       extract(epoch from now())::bigint as unix秒,
       extract(dow from date '2026-11-05') as 星期几,
       extract(isoyear from date '2026-01-01') as iso年;

1
2
3

     截到小时       |   unix秒   | 星期几 | iso年
---------------------+------------+--------+-------
 2026-11-05 17:00:00 | 1793871759 |      4 |  2026

select make_date(2026, 11, 5) as 造日期,
       make_interval(days => 3, hours => 2) as 造间隔,
       to_char(now(), 'YYYY"年"MM"月"') as 格式化,
       to_date('2026-11-05', 'YYYY/MM/DD') as 解析;

1
2
3

     造日期   |     造间隔      |   格式化   |    解析
------------+-----------------+------------+------------
 2026-11-05 | 3 days 02:00:00 | 2026年11月 | 2026-11-05

to_char 里的中文要用双引号包住（'YYYY"年"MM"月"'），裸写中文会被当成未知格式字符。
to_date('2026-11-05', 'YYYY/MM/DD') 实测也能解析——分隔符不匹配不影响，但别依赖这一点。
截到月粒度是 date_trunc('month', now())，实测返回 2026-11-01 00:00:00+08（带时区，因为 now() 是 timestamptz）。
会话时区实测 Asia/Shanghai，show timezone 与系统时区一致；timestamptz 存的是 UTC 瞬间，显示时按会话时区渲染（所以 now() 打印带 +08）。
now()::timestamp 会把时区信息直接抹掉而不是换算（实测得到 2026-11-05 17:59:28.612916，和 now() at time zone 'Asia/Shanghai' 数字一样，但后者返回的是不带时区的 timestamp，语义上更该用它）。
加「1 个月」要用 interval '1 month'（实测 2026-01-01 + 1 month = 2026-02-01），别用 + 30。

排序规则（collation）在本机是 C.UTF-8（实测）：
1
2
3
select 'Ä' = 'A' as 库默认, 'Ä' = 'A' collate "C" as C规则, 'ä' = 'A' collate "C" as C小写;
select name from t_coll order by name;
select name from t_coll order by name collate "C";
1
2
3
4
5
6
7
8
9
10
11
库默认 | c规则 | c小写
--------+-------+-------
f | f | f

name
------
B
C
a
b
Ä
- 建库时 datcollate=C.UTF-8、datlocprovider=c（实测 pg_database，且 ICU 提供者的库数为 0）。C.UTF-8 的排序基本就是按码位，所以大写在前、a 在后，和 MySQL 里 utf8mb4_general_ci 那种「大小写不分、重音折叠」完全不同。
- 要做「搜 a 也能命中 A」的检索，不要指望 collation，用 lower(col) 建表达式索引，或者 citext 扩展，或者 ilike。
- 语法注意：collation "C" 是报错写法（实测 syntax error at or near ""C""），修饰一个表达式要写 collate "C"。

约束、默认值与 UPSERT

外键报错长这样（实测）：
1
2
ERROR: insert or update on table "t_book" violates foreign key constraint "t_book_cat_id_fkey"
DETAIL: Key (cat_id)=(9999) is not present in table "t_cat".
- 约束没写名字时 PG 自动起名 表名_列名_fkey，_key/_check 后缀同理，排错时靠这个后缀就能反推约束类型。

唯一约束撞了（实测）：

1 2	ERROR: duplicate key value violates unique constraint "t_cat_name_key" DETAIL: Key (name)=(图书) already exists.

deferrable 不能用在建表时的列级 check 上，这是本机实测出来、网上教程经常抄错的一条：

--- 列级写法直接报语法错
create table t_defer(id int primary key, v int check (v > 0) deferrable, ...);
ERROR:  misplaced DEFERRABLE clause

--- 命名约束写法能过语法，但 CHECK 压根不允许 deferrable
create table t_def2(id int primary key, v int, constraint v_pos check (v > 0) deferrable);
ERROR:  CHECK constraints cannot be marked DEFERRABLE

能 deferrable 的是 unique、排他约束和外键。实测用命名唯一约束做到「事务里先插两条重复、提交前改掉」（begin; ... commit; 一个会话跑完）：

create table t_def2(id int primary key, code text,
                    constraint code_uniq unique (code) deferrable initially deferred);
begin;
insert into t_def2 values (1, 'x'), (2, 'x');      -- 此刻两条重复值都在表里
select '事务内还没校验' as 阶段, count(*) from t_def2;
update t_def2 set code = 'y' where id = 2;         -- 提交前把它改掉
commit;                                            -- 校验通过

       阶段      | count
----------------+-------
 事务内还没校验 |     2

 id | code
----+------
  1 | x
  2 | y

反过来，如果提交时还改不平，报错发生在 commit 那一刻而不是 insert 那一刻（实测：同一张延迟约束的表再 begin; insert into t_def2 values (3,'y'),(4,'y'); commit;，吐出 ERROR: duplicate key value violates unique constraint "code_uniq"，整条事务全丢）。普通的（非延迟）唯一约束则是在 insert 当场就报同一个形状的错误，前面贴过的 t_cat_name_key 就是这种。

unique 默认允许多个 NULL（和 MySQL 一样），但 PG15 起能显式要求 NULL 也参与唯一性（实测）：

create table t_u(id int generated always as identity primary key, code text unique);
insert into t_u(code) values (null), (null);   -- 可以，两行 NULL 并存
select count(*) as null_rows from t_u;         -- 2

-- 事后想改成 nulls not distinct，前提是库里已经没有重复 NULL
alter table t_u drop constraint t_u_code_key;
alter table t_u add constraint t_u_code_key unique nulls not distinct (code);

1 2	ERROR: could not create unique index "t_u_code_key" DETAIL: Key (code)=(null) is duplicated.

也就是说 unique nulls not distinct 是建表时就该定好的事，脏数据后补会被自己已有的两行 NULL 顶回来。

not null 的违反会带整行 DETAIL（前面数据类型一节贴过原文），check 违反的报错同理带约束名。

UPSERT（on conflict）与 returning（实测三条语句连着跑）：

insert into t_cat(name) values('数码')
  on conflict (name) do update set name = excluded.name
  returning id, name, xmax = 0 as is_new;
update t_item set qty = qty + 5 where sku = 'b-200' returning id, sku, qty;
delete from t_item where sku = 'c-300' returning id, sku;

 id | name | is_new
----+------+--------
  2 | 数码 | f

 id |  sku  | qty
----+-------+-----
  2 | b-200 |   5

 id |  sku
----+-------
  3 | c-300

xmax = 0 是判断「这行是本次真插入」的常用土办法：insert 走冲突分支（没真的写入新元组）时实测返回 f。
do update 里可以用 excluded 引用「本来要插入的那行」，do nothing 则静默跳过。
on conflict (col) 必须指定冲突列或约束名（on conflict on constraint 名字）；裸写 on conflict do nothing 对所有唯一约束生效。批量插入时这条和 psycopg 的 executemany 有关：实测第一次插入 3 行 rowcount = 3，第二次同样的语句因为全部冲突被跳过，rowcount = 0——别把 rowcount = 0 当成「没执行」。

索引与执行计划

先用一张 20 万行的表把场景搭起来（实测建表与灌数）：

create table t_big(id int primary key, cat int not null, name text not null,
                  ts timestamptz not null default now());
insert into t_big(id, cat, name, ts)
select g, g % 500, 'n' || g, timestamp '2026-01-01' + (g || ' seconds')::interval
from generate_series(1, 200000) g;
analyze t_big;

generate_series 是 PG 侧造测试数据最省事的办法，MySQL 里没有对应物（要靠递归 CTE 或数字表）。
analyze 必须跑：规划器靠统计信息决定要不要用索引，刚建完的索引没有统计常被无视。

explain 的正确开关名是 costs（带 s），实测 explain (analyze, buffers, cost off) 会报 ERROR: unrecognized EXPLAIN option "cost"。日常组合是 explain (analyze, buffers, costs off)：analyze 真跑一次并给耗时，buffers 看命中了多少个 8 kB 页。

btree + Index Only Scan（实测）：

1 2	create index idx_big_name on t_big(name); explain (analyze, buffers, costs off) select count(*) from t_big where name = 'n123456';

Aggregate (actual time=0.134..0.134 rows=1.00 loops=1)
  Buffers: shared hit=1 read=3
  ->  Index Only Scan using idx_big_name on t_big (actual time=0.129..0.130 rows=1.00 loops=1)
        Index Cond: (name = 'n123456'::text)
        Heap Fetches: 0
        Index Searches: 1

Heap Fetches: 0 是这条计划的关键：答案全在索引里拿到，一个堆页都没回表。
它成立的前提是可见性映射（visibility map）知道那些页对所有事务都可见——所以刚大量写入过、还没 vacuum 的表会看到 Heap Fetches 不为 0，这是「为什么 index-only scan 依赖 autovacuum」的实测解释。

覆盖索引 include（PG11+，实测）：

1
2
3

create index idx_big_cov on t_big(cat) include (name);
vacuum analyze t_big;
explain (analyze, buffers, costs off) select name from t_big where cat = 3;

Index Only Scan using idx_big_cov on t_big (actual time=0.119..0.152 rows=400.00 loops=1)
  Index Cond: (cat = 3)
  Heap Fetches: 0
  Buffers: shared hit=1 read=5

include (name) 让 name 只作为「载荷」存在索引叶子里，不参与排序，所以它比 index on (cat, name) 小、也不能用来按 name 过滤；但 select name where cat = ? 就能免回表。

部分索引（partial）：只索引满足条件的行（实测建了 where cat = 0 的索引）：
1
2
3
create index idx_big_cat0 on t_big(cat) where cat = 0;
explain (costs off) select id from t_big where cat = 0;
explain (costs off) select id from t_big where cat = 1;
1
2
3
4
5
6
7
Bitmap Heap Scan on t_big
Recheck Cond: (cat = 0)
-> Bitmap Index Scan on idx_big_cat0

Bitmap Heap Scan on t_big
Recheck Cond: (cat = 1)
-> Bitmap Index Scan on idx_big_cov
- 第二个计划说明了两件事：where cat = 1 用不上部分索引（条件不包含在索引谓词里），规划器转而去用覆盖索引 idx_big_cov。
- 查询必须写成 where cat = 0 [and ...] 这种「能推出索引谓词」的形式才会命中部分索引，光有 cat = 0 是不够的，还得让 PG 证明查询是索引的子集。

表达式索引（实测，lower(name) 大小写不敏感检索的标准做法）：

1 2	create index idx_big_lower on t_big(lower(name)); explain (costs off) select id from t_big where lower(name) = 'n123456';

1 2	Index Scan using idx_big_lower on t_big Index Cond: (lower(name) = 'n123456'::text)

查询里的表达式必须和索引定义逐字一致（lower(name)），写成 lower(name::text) 之类就不认了。

BRIN（大块区间索引，适合时间/自增这类物理有序的大表，实测）：

1
2
3

create index idx_big_brin on t_big using brin(ts);
explain (analyze, costs off) select count(*) from t_big
  where ts >= '2026-01-02' and ts < '2026-01-03';

Aggregate (actual time=8.532..8.534 rows=1.00 loops=1)
  Buffers: shared hit=649
  ->  Bitmap Heap Scan on t_big (actual time=0.319..5.877 rows=86400.00 loops=1)
        Recheck Cond: ((ts >= '2026-01-02 00:00:00+08') AND (ts < '2026-01-03 00:00:00+08'))
        Rows Removed by Index Recheck: 14080
        Heap Blocks: lossy=640

lossy 和 Rows Removed by Index Recheck 是 BRIN 的常态：它只记「这段物理块里最小/最大是多少」，命中块后要整块重扫。换来的是极小的体积（实测 24 kB，同一张表的 btree 是 4408 kB）。

GIN（jsonb/数组/全文检索）要注意「表太小不用索引」（实测）：三行的 t_item 上 create index if not exists idx_item_meta on t_item using gin (meta jsonb_path_ops); 之后，查询仍是 Seq Scan：
1
2
Seq Scan on t_item
Filter: (meta @> '{"lang": "zh"}'::jsonb)
- 把同类数据造到 20 万行再 analyze，计划立刻变成 Bitmap Heap Scan on t_doc ... Recheck Cond: (meta @> '{"k": 7}'::jsonb)，Buffers: shared hit=203。
- 体积：实测 t_doc 主键 btree 4408 kB，jsonb 的 GIN 索引 1560 kB（pg_relation_size）。

规划器开关只用来做实验，别写进生产配置（实测把两种扫描都关掉就会退化成顺序扫描）：

1 2	set enable_indexscan = off; set enable_bitmapscan = off; explain (costs off) select count(*) from t_big where name = 'n123456';

1
2
3

Aggregate
  ->  Seq Scan on t_big
        Filter: (name = 'n123456'::text)

参数名是 enable_indexscan，实测写成 enableindexscan 报 unrecognized configuration parameter。

索引体积与使用次数（实测，运维时先看这两个视图）：

1 2	select indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) as size from pg_stat_user_indexes where relname = 't_big' order by 1;

 indexrelname  | idx_scan |  size
---------------+----------+---------
 idx_big_brin  |        0 | 24 kB
 idx_big_cat0  |        0 | 16 kB
 idx_big_cov   |        0 | 6184 kB
 idx_big_lower |        0 | 4408 kB
 idx_big_name  |        0 | 4408 kB
 t_big_pkey    |        0 | 4408 kB

 total | heap_only
-------+-----------
 29 MB | 10192 kB

这张表 10 MB 的堆，索引却把总体积顶到 29 MB——索引是拿磁盘和写入吞吐换读速度的。idx_scan 长期为 0 的索引就是纯负担（注意统计是从上次 pg_stat_reset() 起算的）。

生产上加索引不能锁表：用 concurrently，但它不能放在事务里（实测）：
1
begin; create index concurrently idx_big_cat_cc on t_big(cat); commit;
1
ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block
- 单独跑 create index concurrently 实测成功，pg_indexes/pg_stat_user_indexes 里也能看到它；失败时会留下一个 invalid 索引（要 drop index 重来），这是它唯一的代价。
- 重建同样有非阻塞版本：reindex index concurrently idx_big_name 实测可用。
- psycopg 里执行 CREATE INDEX CONCURRENTLY 记得 conn.autocommit = True，否则默认事务模式会撞上上面那条报错。

查询里值得记住的写法

递归 CTE 与物化（实测，一条五层链，不依赖任何表）：

with recursive tree(n, path) as (
  select 1, '1'::text
  union all
  select n + 1, path || '>' || (n + 1)::text from tree where n < 5
)
select count(*) as 递归行数, max(path) as 最长路径 from tree;

1
2
3

 递归行数 | 最长路径
----------+-----------
         5 | 1>2>3>4>5

递归 CTE 必须写 with recursive，哪怕递归部分是空集也一样；实测把 union all 换成 union 也能跑（with recursive r(n) as (select 1 union select n+1 from r where n<5) 实测输出 1…5），区别是 union 会去重、每轮多做一次排序，所以默认用 union all + 自己保证收敛。

非递归的 with 在 PG12 起默认可能被内联进主查询，要强制「只算一次」就写 materialized（实测）：

1 2	with c as materialized (select generate_series(1,3) as g) select count(*) from c, c as c2; -- 3×3 = 9

窗口函数（实测，row_number / 累计 sum / lag 一次看全）：

select sku, price,
       row_number() over (order by price desc) as rn,
       sum(price) over (order by id) as 累计,
       lag(price)  over (order by id) as 上一行价格
from t_item order by id;

  sku  | price  | rn |  累计  | 上一行价格
-------+--------+----+--------+------------
 a-100 |  59.00 |  2 |  59.00 |
 b-200 | 199.50 |  1 | 258.50 |      59.00

第一行的 lag 是 NULL（没有上一行），属正常语义；rn 是按 price desc 排的名次，和 order by id 的输出顺序无关——窗口的 over (...) 与查询的 order by 是两回事。

PG 特有的三个查询糖（三条都是实测原句）：

-- 1) DISTINCT ON：每个城市金额最高的一条
select distinct on (city) city, item, amount from t_sale order by city, amount desc;
-- 2) FILTER：条件聚合，一条语句出三个指标
select city, count(*) as 单数, sum(amount) filter (where amount >= 50) as 大额合计,
       round(avg(amount)::numeric, 2) as 均值,
       string_agg(item, ',' order by item) as 明细,
       array_agg(distinct item) as 去重数组
from t_sale group by city order by city;
-- 3) LATERAL：右边的子查询能引用左边的行
select c.name, x.sku
from t_cat c
join lateral (select sku from t_item i where i.tags && array['编程'] limit 1) x on true
order by c.id;

 city | item | amount
------+------+--------
 上海 | i3   |     99
 北京 | i3   |     99
 广州 | i3   |     99

 city | 单数 | 大额合计 | 均值  | 明细(节选)      | 去重数组
------+------+----------+-------+-----------------+--------------
 上海 |  100 |     3725 | 49.50 | i0,i0,...,i3   | {i0,i1,i2,i3}

 name |  sku
------+-------
 图书 | a-100
 数码 | a-100

distinct on 必须配 order by，且排序的前导列要和 on (...) 一致，否则「取哪一条」不确定；这是 MySQL 用户最陌生的一条 PG 写法。
lateral ... on true 相当于「对左边每一行跑一次右边的子查询」，limit 1 放在里面才有意义。

聚合到数组/JSON（实测）：
1
2
select jsonb_agg(t) as jsonb_数组
from (select city, count(*) as n from t_sale group by city) t;
1
[{"n": 100, "city": "广州"}, {"n": 100, "city": "北京"}, {"n": 100, "city": "上海"}]
- 同类还有 array_agg(distinct item) 实测得到 {i0,i1,i2,i3}，string_agg(item, ',' order by item) 拼字符串。给前端返回结构时这些比在 Python 里循环拼装省一轮往返。

字符串与匹配（实测）：

select 'PG' ilike 'pg' as ilike,                -- t：大小写不敏感 like
       'PG' ~~ 'P%' as like用波浪号,             -- t：~~ 就是 LIKE 的运算符形式
       'abc123' ~ '[0-9]+' as 正则匹配,           -- t
       'ABC' ~ 'abc' as 大小写敏感,               -- f
       'ABC' ~* 'abc' as 忽略大小写;              -- t

select left('abcdefg', 3) as 左三, right('abcdefg', 3) as 右三,
       position('c' in 'abcdefg') as 位置, replace('aaa','a','b') as 替换,
       split_part('a-b-c','-','2') as 切分, md5('x') as md5,
       length('中文') as 字符数, octet_length('中文') as 字节数;

1 2	左三 \| 右三 \| 位置 \| 替换 \| 切分 \| md5 \| 字符数 \| 字节数 abc \| efg \| 3 \| bbb \| b \| 9dd4e461268c8034f5c8564e155c67a6 \| 2 \| 6

split_part 的第三参数在 SQL 里写字符串 '2' 也能用（实测），但那是老写法，建议写数字 2。
length() 数字符、octet_length() 数字节（中文在 UTF-8 里 3 字节/字）。
正则家族：regexp_matches('a1b2', '[ab]([0-9])', 'g') 实测返回两行 {1}、{2}，去掉 'g' 就只返回第一行 {1}（实测）——「正则只匹配到一条」的标准答案。另有 regexp_split_to_array('a-1-b-2', '-') → {a,1,b,2}、regexp_replace('2026-11-05','-','','g') → 20261105。

NULL 的几条行为（实测，都是面试常客）：

select 'a' || null as 字符串拼接遇null;      -- 空（整个表达式变 NULL）
select concat('a', null) as concat_版本;     -- a（concat 忽略 NULL）
select count(*) as count_忽略null from (values (1),(null),(2)) v(x);   -- 2
select 1 in (null) as in_null, not exists (select null) as not_exists_结果;
select '  空白  ' = '空白' as 空格敏感;       -- f

 in_null | not_exists_结果
---------+-----------------
  (空)   | f

 空格敏感
----------
   f

1 in (null) 返回的是 NULL 而不是 false，所以 where 1 in (null) 一行都查不出来；同理 where col <> 1 不会返回 col is null 的行——要写 col <> 1 or col is null。
count(*) 数行、count(col) 只数非 NULL（实测 count(null::int) 得 0，同一条语句里 count(*) 得 1）。
nullif(1, 1) 得 NULL（相等就变 NULL，做除法防零很有用）；greatest(3, null, 5) 得 5——greatest/least 会忽略 NULL，但 coalesce 是「取第一个非 NULL」。
order by 时 NULL 默认在最后（ASC 时），实测 (values (1),(null),(2)) 排序得 1, 2, (空)；显式 order by x nulls first 得 (空), 1, 2。

JSONB 的路径查询（实测）：
1
select sku from t_item where jsonb_path_exists(meta, '$.stock.wh1 ? (@ > 1)');
1
2
3
sku
-------
a-100
- SQL/JSON 路径表达式（jsonb_path_exists、jsonb_path_query、jsonb_query_array 一族）是 PG 里最像 MongoDB 查询语法的部分；日常更常用的是 meta @> '{"lang":"zh"}'::jsonb（实测 count(*) filter 统计到 2 条）和 meta->>'lang'（取文本，注意 -> 返回 jsonb、->> 返回 text，实测分别是 "zh" 和 zh），嵌套取值用 meta #>> '{stock,wh1}'（实测得到 2）。
视图与物化视图（实测）：create view v_item as select id, sku, price from t_item where price > 50 之后 pg_class.relkind 是 v，create materialized view mv_cat as select cat_id, count(*) ... 是 m；refresh materialized view mv_cat 实测可用。物化视图本质是一张表，视图只是存着的查询文本。

事务、并发与 VACUUM

隔离级别：PG 只实现 READ UNCOMMITTED / READ COMMITTED / REPEATABLE READ / SERIALIZABLE 四种（SET TRANSACTION ISOLATION LEVEL 四种语法都接受，但 READ UNCOMMITTED 实际按 READ COMMITTED 执行——官方文档写明，本机也实测过：两会话下 read uncommitted 事务在对方提交后读到的是新值 20，和 read committed 读到 30 的行为一模一样，PG 从不读未提交数据）。默认是 READ COMMITTED。

快照隔离是真的（实测，两个会话同一张 t_iso，级别 repeatable read）：

[A] begin; select v from t_iso;      -> a1=10
    （会话 B 把 v 改成 20 并 commit）
[A] select v from t_iso;            -> a2=10     还是快照里的 10
[A] update t_iso set v = 30;        -> ERROR: could not serialize access due to concurrent update
最终库里的 v=20

也就是说 REPEATABLE READ 下写冲突不是「覆盖」而是直接报错（SQLSTATE 40001），事务整个回滚，重试逻辑要写在应用层。
同一个场景在默认的 READ COMMITTED 下则是：第一次读 b1=10，别人提交后再读 b2=99（实测）——这叫不可重复读，PG 默认允许它发生。

select ... for update skip locked：PG 上做「数据库当队列」的标准写法（实测两个会话并发取任务）：
1
2
3
-- 会话 1 与会话 2 各自执行：
begin;
select id from t_job where not done order by id for update skip locked limit 1;
1
2
会话1取到 id=1
会话2取到 id=2
- 同一会话里连续执行两次，实测都是 1、1：skip locked 只跳过别的后端锁住的行，不跳自己锁的。
- 这也是 Celery/RQ 之外最常见的「用 PG 做任务队列」方案；配合 update ... set done = true where id = (select ... for update skip locked limit 1) 就是原子领取。

死锁（实测两个会话交叉更新两行）：

ERROR:  deadlock detected
DETAIL:  Process 50438 waits for ShareLock on transaction 946; blocked by process 50440.
         Process 50440 waits for ShareLock on transaction 945; blocked by process 50438.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,2) in relation "t_dl"

ShareLock on transaction 是 PG 死锁报告的特征形状：A 在等 B 那个未提交事务结束。避免办法就是「所有会话按同一顺序访问同一批行」+ 事务尽量短。

三个超时（实测默认值全为 0，即不限制；打开后的报错原文）：

参数	作用	实测报错
`statement_timeout`	单条语句最长执行时间	`ERROR: canceling statement due to statement timeout`
`lock_timeout`	单次等锁最长	`ERROR: canceling statement due to lock timeout` + `CONTEXT: while updating tuple (0,1) in relation "t_lock"`
`idle_in_transaction_session_timeout`	事务里发呆多久踢掉	实测默认 `0`（不踢）
`transaction_timeout`	整个事务最长（PG17 新增）	实测默认 `0`；`pg_dump` 生成的脚本里会先 `SET transaction_timeout = 0;`

生产上建议给 Web 应用账号设 ALTER ROLE myapp SET statement_timeout = '5s'，比在应用层等更可靠。

MVCC 的代价就是死元组（实测，50k 行全部更新一遍）：

1 2	update t_vac set v = v \|\| '!'; -- 50000 行 select n_live_tup, n_dead_tup from pg_stat_user_tables where relname = 't_vac';

 n_live_tup | n_dead_tup
------------+------------
       50000 |      50000

 更新后堆大小
--------------
  3544 kB

PG 的 update 是「插入新行版本 + 老行标死」，所以更新全表等于把表暂时翻倍。
vacuum analyze t_vac; 实测把 n_dead_tup 清成 0，但堆大小仍是 3544 kB——普通 vacuum 只是把空间标为可复用，不还给操作系统。
后续实测：删掉一半数据再 vacuum，堆变成 1784 kB（有可回收的整页时才真降）；vacuum full 才变成 8192 bytes——它会重写表并拿排他锁，业务时间别跑。

vacuum 的选项要写成括号形式，full 也当选项放进括号里（实测对比）：

vacuum full (index_cleanup off) t_ru;      -- ERROR:  syntax error at or near "("
vacuum (full, index_cleanup off) t_ru;     -- VACUUM
vacuum (analyze, buffer_usage_limit 1024) t_ru;   -- VACUUM
vacuum (verbose) t_ru;
  INFO:  finished vacuuming "...t_ru": index scans: 0
  INFO:  pages: 0 removed, 1 remain, 1 scanned (100.00% of total), 0 eagerly scanned
  INFO:  tuples: 0 removed, 1 remain, 0 are dead but not yet removable

index_cleanup off 用来让这次 vacuum 别去碰索引（大批量删数据后能省一遍索引扫描）；pages removed / tuples removed 就是「这次到底还回去多少」的直接答案。

autovacuum 的默认参数（实测 pg_settings）：

            name               | setting | unit
-------------------------------+---------+-----
 autovacuum                     | on      |
 autovacuum_analyze_scale_factor | 0.1     |
 autovacuum_max_workers         | 3       |
 autovacuum_naptime             | 60      | s
 autovacuum_vacuum_scale_factor | 0.2     |
 maintenance_work_mem           | 65536   | kB
 checkpoint_timeout             | 300     | s

判据是「死元组超过 0.2 × 表行数 + 20MB 才自动 vacuum」，小表因此可能长期不回收。

autovacuum_enabled 不是 GUC，是表级 reloption（实测坑）：

select current_setting('autovacuum_enabled');
ERROR:  unrecognized configuration parameter "autovacuum_enabled"

select reloptions from pg_class where relname = 't_vac';
 {autovacuum_enabled=false,fillfactor=90}

要关某张表的自动清理得写 alter table t_vac set (autovacuum_enabled = false)，然后从 pg_class.reloptions 里看，别去 pg_settings 里找。

TOAST：长值不在主表里（实测）：

1
2
3

insert into t_toast(id, body) values (1, repeat('a', 100000));
select id, length(body) as 字符数, length(compress(body)) as 存储字节,
       pg_column_size(t_toast.*) as 整行字节 from t_toast;

 id | 字符数 | 存储字节 | 整行字节
----+--------+----------+----------
  1 | 100000 |     1156 |     1176
  2 |      1 |        4 |       24
  3 |   5000 |       69 |       89

10 万个字符最后只占 1156 字节：先压缩、再切成 chunk 存进 TOAST 表，主表那个列只放指针。
TOAST 表的名字是 pg_toast.pg_toast_<主表oid>（实测 pg_toast.pg_toast_16760），所以看体积要用总大小：
1
2
3
主表 | 含toast
------------+---------
8192 bytes | 32 kB
这也是「pg_relation_size 看起来很小但表其实很大」的原因；日常用 pg_total_relation_size。
顺带一条：select count(*) from t_toast 这类全表扫描不会去碰 TOAST 列，但 select body 会，宽表按需取列能省很多 IO

schema、函数、触发器与分区

一个 database 里有多个 schema，这是 PG 和 MySQL 结构上最大的不同：MySQL 里 database 和 schema 是同义词，PG 里 database 之间不能跨库查询，schema 之间可以。日常做法是「一个项目一个库，多个模块多个 schema」：

create schema if not exists shop;
create table if not exists shop.orders(id int generated always as identity primary key,
                                       total numeric(10,2));
insert into shop.orders(total) values(12.5),(80) returning id, total;
set search_path = shop, public;
select current_schema(), count(*) as 当前模式下订单数 from orders;
reset search_path;

 id | total
----+-------
  1 |  12.50
  2 |  80.00

 current_schema | 当前模式下订单数
----------------+------------------
 shop           |                2

search_path 就是「不带 schema 前缀时按什么顺序找对象」，实测默认值是 "$user", public（show search_path; 在 noteops_app 角色下也是同一个值，因为这个角色名对应的 schema 不存在，就只剩 public）。set search_path = shop, public; 之后裸写 orders 就命中 shop.orders；reset search_path 回到默认，此时必须写 shop.orders（实测 from shop.orders 计数为 2）。
实测可见的非系统 schema：information_schema、public、shop。
把表挪走：alter table t_csv set schema shop; 实测生效（pg_tables 里变成 shop|t_csv）。

两种自定义函数（实测）：language sql 适合一行表达式，language plpgsql 适合带流程的：

create or replace function add_num(a int, b int) returns int
  language sql immutable as
$$ select a + b $$;
select add_num(2, 3) as 函数结果;

1
2
3

 函数结果
----------
       5

create or replace function sum_range(n int) returns table(i bigint, s bigint)
language plpgsql as $$
begin
  return query select g, sum(g) over () from generate_series(1, n) g;
end $$;

$$ ... $$ 是美元引用（dollar quoting），函数体里就不用 escape 单引号。
immutable 是给优化器的承诺（同输入必同输出），标错了会导致表达式索引/生成列不被刷新，标之前想清楚。

返回类型对不上会直接报错（实测原文）：

ERROR:  structure of query does not match function result type
DETAIL:  Returned type integer does not match expected type bigint in column "i" (position 1).
CONTEXT:  SQL statement "select g, sum(g) over () from generate_series(1, n) g"
PL/pgSQL function sum_range(integer) line 3 at RETURN QUERY

generate_series 给的是 integer，而 returns table(i bigint, ...) 要 bigint——return query 不做隐式放宽，得自己 g::bigint。

函数还能当默认值（实测 default upper(substr('abc',1,3)) 建表后 insert ... default values 得到 ABC）。

触发器（实测的 BEFORE UPDATE 自动填时间戳）：

create or replace function touch_row() returns trigger language plpgsql as $$
begin
  new.updated_at := now();
  return new;
end $$;

create trigger trg_touch before update on t_trig
  for each row execute function touch_row();

1
2
3

 id | v | 触发器填了时间
----+---+----------------
  1 | b | t

现代写法是 execute function（execute procedure 是老写法，还能用）；new 是行变量的名字，before 触发器改 new 就能改要写进去的值。
Django 的 auto_now 是在应用层 SET 里塞 now()，PG 触发器则是数据库层保证——两边都做也不会冲突，但要清楚谁生效。

声明式分区（range）与分区裁剪（实测按月分区，t_log_part 上插了 5001 行）：

create table t_log_part(id int, ts timestamptz not null) partition by range (ts);
create table t_log_part_202611 partition of t_log_part for values from ('2026-11-01') to ('2026-12-01');
create table t_log_part_202612 partition of t_log_part for values from ('2026-12-01') to ('2027-01-01');
create table t_log_part_default partition of t_log_part default;

 总行数
--------
   5001

 Aggregate
   ->  Append
         ->  Seq Scan on t_log_part_202612 t_log_part_1
               Filter: (ts >= '2026-12-01 00:00:00+08'::timestamp with time zone)

 t_log_part|0 bytes
 t_log_part_202611|32 kB
 t_log_part_202612|40 kB
 t_log_part_default|192 kB

查询条件里有 ts >= '2026-12-01' 时，计划里只出现 t_log_part_202612 一个分区，这就是分区裁剪；父表本身 0 bytes，数据全在子表。
default 分区实测吃了 192 kB——因为造数据时用了「5000 小时」这种跨到 2027 年的范围，落在声明之外的行全进了默认分区。默认分区是会掩盖「忘记建分区」这个故障的，生产上要么不建默认分区让它报错，要么盯紧它的体积。

权限、角色与认证

PG 只有「角色（role）」这一个概念，user 只是「能登录的角色」的别名；权限分三层（实测）：
- 库级：create database <库> owner <角色>；
- schema 级：grant usage on schema public to pgnote;（没有 usage 连表名都解析不了）；
- 对象级：grant select, insert, update, delete on all tables in schema public to pgnote;，序列要单独 grant usage, select on all sequences ...。

最小权限账号实测的三个拒绝现场：

pgnote 插入：ERROR:  permission denied for table t_item
pgnote 读表：正常（只读了 2 行）
pgnote 删表：DETAIL:  view v_item depends on table t_item
             HINT:  Use DROP ... CASCADE to drop the dependent objects too.

最后一条说明删表被依赖挡住，这是好事（MySQL 会直接删掉并让视图失效）。

COPY 读写服务器本地文件需要专门的权限，\copy 不需要（实测原文）：
1
2
3
ERROR: ...
DETAIL: Only roles with privileges of the "pg_read_server_files" role may COPY from a file.
HINT: Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.
- \copy t_csv from '/tmp/a.csv' with (format csv) 实测在普通账号下成功导入 2 行；服务端 copy ... from '/tmp/a.csv' 用非超级用户直接被拒。
- copy ... to stdout 实测可用（输出 1,甲 / 2,乙），这是脚本里导数据最省事的口子。

行级安全（RLS）最小样例（实测：postgres 数全表 2 行，换成被授权的 pgnote 只看到自己那 1 行）：

create table t_note(id int generated always as identity primary key,
                    owner_name text not null, body text);
alter table t_note enable row level security;
create policy note_self on t_note for select using (current_user = owner_name);
grant select, insert on t_note to pgnote;
grant usage, select on sequence t_note_id_seq to pgnote;
insert into t_note(owner_name, body) values ('pgnote','我的'), ('postgres','别人的');

1 2	postgres 数全表： 2 pgnote 数全表： 1 -- RLS 把别人的那行藏起来了

RLS 是「同一张表按用户过滤」的数据库层实现，比在每个查询里记得加 where user_id = ... 更难漏。owner 与超级用户默认绕过策略（官方文档口径），要让他们也受约束就 alter table t_note force row level security;，实测 pg_class 里 relrowsecurity|relforcerowsecurity 变成 t|t。

认证方式与密码哈希（实测）：password_encryption = scram-sha-256，pg_authid.rolpassword 的前缀就是 SCRAM-SHA-256$：
1
2
postgres|SCRAM-SHA-256$
pgnote |SCRAM-SHA-256$
- PG18 官方发布说明里已经把 MD5 口令认证标为 deprecated（Deprecate MD5 password authentication），老教程里 set password_encryption = md5 的写法别再抄了。
- 改 pg_hba.conf 或 postgresql.conf 后不必重启：select pg_reload_conf(); 实测返回 t（重载配置文件；改 port、shared_buffers 这类仍需重启）。
密码不要写进语句里：ALTER ROLE ... PASSWORD 'xxx' 的明文会留在 psql 历史、服务端日志，还会被 pg_stat_statements 原样记下来（实测抓到的就是完整语句，见下一节的例子）。要么用 psql 的 \password 角色名（它发的是哈希后的语句），要么从环境变量拼。
角色级默认参数很好用（实测）：
1
2
create role noteops_app login password 'password';
alter role noteops_app set statement_timeout = '5s';
1
2
-- 用该角色登录后
show statement_timeout; -> 5s
- 设置记录在 pg_db_role_setting（实测看到 0|17401|{statement_timeout=5s}，setdatabase=0 表示对所有库生效）；alter database <库> set statement_timeout = '1s'; 同样可行，实测两条能共存，角色级+库级还能组合（setrole 与 setdatabase 都非 0）。
- 撤销用 alter role ... reset statement_timeout / alter database ... reset ...。

监控与日常运维视图

看谁在连、在干什么（实测）：
1
2
3
4
select pid, now() - xact_start as 事务已开, left(query, 40)
from pg_stat_activity
where xact_start is not null and state <> 'idle'
order by xact_start limit 3;
- state 常见值：active（正在跑）、idle（连着但没事）、idle in transaction（事务开着发呆，最容易造成膨胀和锁堆积）。
- 批量取连接后实测：同一进程开 5 条连接时 pg_stat_activity 里就是 5 条，其中 idle 4 条（发起查询那条是 active）。

连接数是 PG 的第一资源（实测打满）：max_connections = 100、superuser_reserved_connections = 3，非超级用户最多 97 条：

第 98 条开始连不上：OperationalError - connection failed:
  connection to server at "127.0.0.1", port 5600 failed:
  FATAL:  remaining connection slots are reserved for roles with the SUPERUSER attribute
此时本用户后端数： 97

报错原文就是 FATAL: remaining connection slots are reserved for roles with the SUPERUSER attribute，Python 侧被包成 psycopg.OperationalError。
应用侧要么把每条连接的开销降下来（Django 的 CONN_MAX_AGE），要么上连接池/ PgBouncer（PgBouncer 与 Docker 部署本机未实测，只作为方向提出）。

锁等待与死锁留痕（实测）：
1
2
select count(*) as 当前锁记录 from pg_locks;
select pid, locktype, mode, granted from pg_locks order by pid limit 5;
- 本机测出的死锁详情只出现在那条语句的 ERROR ... DETAIL 与服务器日志里（前文贴过原文）。官方还提供了 log_lock_waits（默认 off，本机未复测）用来把「等锁超过 deadlock_timeout」也记进日志。

体积类快查（实测）：

1
2
3

select pg_size_pretty(pg_database_size(current_database()));   -- 43 MB
select relname, pg_size_pretty(pg_relation_size(relid)) as 分区大小
from pg_stat_user_tables order by pg_relation_size(relid) desc limit 5;

pg_relation_size（堆）与 pg_total_relation_size（含索引与 TOAST）差别很大，前文 t_big 的 10 MB 对 29 MB 就是例子。

慢查询日志：默认关着（实测 log_min_duration_statement = -1、log_statement = none），改成毫秒阈值即可：

log_checkpoints|on
log_line_prefix|%m [%p] %q%u@%d
log_min_duration_statement|-1
log_statement|none
--- 会话级打开
set log_min_duration_statement = '500ms'; show log_min_duration_statement;  -> 500ms

Debian/Ubuntu 的日志在 /var/log/postgresql/postgresql-18-main.log（pg_lsclusters 的 Log file 列直接给出路径）。

生产上更常用 pg_stat_statements，但它必须预加载后重启才能用，而扩展本身在没预加载时也能装上——这是一个很容易误判的组合（实测）：

select shared_preload_libraries;          -> 空
select count(*) from pg_stat_statements;
ERROR:  pg_stat_statements must be loaded via "shared_preload_libraries"
select extname from pg_extension where extname = 'pg_stat_statements';
 pg_stat_statements          -- 扩展其实在，只是查不动

在临时集群里把 shared_preload_libraries = 'pg_stat_statements' 配好并重启后，同一条 SQL 就能读到归一化后的语句：

 calls | rows | 总耗时ms |                   query
-------+------+----------+-------------------------------------------
     2 |    2 |    0.011 | select $1+$2 as 先跑一条查询
     1 |    1 |    0.006 | select $1||current_setting($2)

注意第二列那个反例：alter role postgres password '<明文>' 这类 DDL 被原样记录（字面量没被 $1 归一化），所以 pg_stat_statements 是会泄密码的地方。这也是本机复测过程中必须清一遍日志的原因。
参数化的语句里字面量会变成 $1、$2（读不到具体值），排错时要知道这个取舍。

备份与恢复

逻辑备份三件套：pg_dump（单库）、pg_dumpall（含角色与表空间）、pg_restore（恢复自定义格式）。实测：

1
2
3

pg_dump -h 127.0.0.1 -p 5600 -U postgres -d pgnote_probe -Fc -t t_cat -t t_item -f d.dump
ls -l d.dump                              # 5603 d.dump
pg_restore -h 127.0.0.1 -p 5600 -U postgres -l d.dump | head -12

; Archive created at 2026-11-05 17:28:56 CST
;     dbname: pgnote_probe
;     TOC Entries: 19
;     Compression: gzip
;     Dump Version: 1.16-0
;     Format: CUSTOM
;     Dumped from database version: 18.6 (Ubuntu 18.6-0ubuntu0.26.04.1)
;     Dumped by pg_dump version: 18.6 (Ubuntu 18.6-0ubuntu0.26.04.1)

-Fc（custom）是推荐格式：压缩、能选表、能用 pg_restore -j 并行恢复；-l 只看目录不恢复。

恢复到新库实测（先建空库，再灌）：

1
2

psql -h 127.0.0.1 -p 5600 -U postgres -d postgres          -q -c "drop database if exists pgnote_restore;" -c "create database pgnote_restore;"
pg_restore -h 127.0.0.1 -p 5600 -U postgres -d pgnote_restore d.dump

1 2	pg_restore: warning: errors ignored on restore: 8 恢复后 t_cat 行数=2

那 8 个 warning 来自扩展/权限类语句（沙箱库里缺对应角色），数据是完整的（实测恢复后 t_cat 仍是 2 行）。pg_restore 默认「报错也继续」，要一遇错就停可加 --exit-on-error（这条是文档项，本机未复测）。

纯 SQL 文本备份（pg_dump --no-owner --schema-only）实测开头长这样，有两点新东西：

\restrict qdGSLtBVOvRV4NgPTCn6z2cJfzDM39oJxZ6sYiINb64LJeLk3KLLHfZ3KQVWa5e
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET transaction_timeout = 0;
...
SELECT pg_catalog.set_config('search_path', '', false);

\restrict <随机串> 是 PG18 给 psql 加的新防护（限制脚本里混进来的反斜杠元命令），如果你自己写工具去解析 dump 文件，会看到它。
SET transaction_timeout = 0; 也是 PG17+ 才有的新行，老脚本里没有。
恢复纯 SQL 用 psql -f dump.sql（或 psql < dump.sql），不要用 psql -1（单事务）除非你确定要「要么全成要么全不上」。

物理备份（pg_basebackup）放在下一节，它是流复制的搭建步骤之一；只谈备份的话它等价于「在线取一个数据目录快照」，需要服务端开 wal_level >= replica 与复制槽。
别忘了角色本身：pg_dump 不含 create role，迁整机要加一句 pg_dumpall --globals-only（实测输出就是一串 CREATE ROLE ...; 加 ALTER ROLE ... PASSWORD 'SCRAM-SHA-256$4096:...'，口令是 SCRAM 哈希而不是明文，但这份文件依然要当机密管）

高可用：流复制与逻辑复制

两条路线的定位先说清：
- 流复制（physical / streaming replication）：把 WAL 日志原样搬到另一台机器重放，备库是主库的二进制副本。它是「读扩展 + 容灾」的手段，备库只读、延迟通常是亚秒级。
- 逻辑复制（logical replication）：解析 WAL 里的行变更，按「发布/订阅」把某些表搬到另一个库。它可以跨大版本、可以单向多路、目标端还能写，代价是开销更大、要求表有主键之类的标识。
本机为了这两条各起了临时集群（pg_createcluster 出来的 5611/5612、5631/5632），测完就 pg_dropcluster 清掉了，主集群 5600 全程没动。下面按实测步骤写。

流复制：从主库拉一个只读备库

主库侧要满足三个前提：wal_level >= replica（实测本机默认就是 replica）、有复制账号、pg_hba.conf 放行 replication：
1
2
create role repl with replication login password 'password';
select pg_create_physical_replication_slot('slot_pgnote2');
- pg_hba.conf 里那两行 host replication all 127.0.0.1/32 scram-sha-256 就是为它准备的（前面安装一节贴过）。
- 复制槽（slot）的作用是「主库别把备库还没收的 WAL 删掉」；不用槽也能跑，但备库长时间断开会造成主库 WAL 堆积。

拉基线（实测进度输出）：

# 先用 pg_createcluster 把备库骨架建出来（配置在 /etc 下），再覆盖数据目录
pg_createcluster 18 pgnote2 -p 5612 --start-conf=manual
pg_basebackup -h 127.0.0.1 -p 5611 -U repl -D /var/lib/postgresql/18/pgnote2 \
              -Fp -Xs -P -R -S slot_pgnote2

waiting for checkpoint
   64/23709 kB (0%), 0/1 tablespace
23720/23720 kB (100%), 0/1 tablespace
23720/23720 kB (100%), 1/1 tablespace

-R（--write-recovery-conf）会自动生成 standby.signal 并把连接信息写进 postgresql.auto.conf，省掉手工配置；-Fp 是纯文件（不打包 tar）、-Xs 边拉边流 WAL、-P 打印进度。
-S 指定的槽必须先建好，否则 pg_basebackup 直接报错（本机踩过一次）。

备库侧生成物（实测，口令打码）：

1
2
3

/var/lib/postgresql/18/pgnote2/standby.signal
primary_conninfo = 'user=repl password=*** channel_binding=prefer host=127.0.0.1 port=5611 sslmode=prefer ... dbname=postgres'
primary_slot_name = 'slot_pgnote2'

Debian/Ubuntu 上的坑：pg_basebackup 直接把数据写进 /var/lib/postgresql/18/pgnote2 之前，得先用 pg_createcluster 18 pgnote2 -p 5612 建出集群骨架，因为这套打包方式的服务配置在 /etc/postgresql/18/pgnote2/，数据目录里没有 postgresql.conf 会启动失败（实测报错原文：postgres: could not access the server configuration file ".../pgnote2/postgresql.conf": No such file or directory）。

起备库后的日志（实测）：

1
2
3

LOG:  consistent recovery state reached at 0/2000120
LOG:  database system is ready to accept read-only connections
LOG:  started streaming WAL from primary at 0/3000000 on timeline 1

主库侧看复制状态（实测）：

1
2
3

select application_name, state, sync_state, client_addr,
       sent_lsn, write_lsn, flush_lsn, replay_lsn
from pg_stat_replication;

1
2
3

 application_name |   state   | sync_state | client_addr | sent_lsn  | write_lsn | flush_lsn | replay_lsn
------------------+-----------+------------+-------------+-----------+-----------+-----------+------------
 18/pgnote2       | streaming | async      | 127.0.0.1   | 0/3000060 | 0/3000060 | 0/3000060 | 0/3000060

pg_stat_replication 没有 received_lsn 这一列（实测报 column "received_lsn" does not exist），四个 LSN 是主库视角的发送/写盘/刷盘/回放进度，本机全等说明零延迟。
sync_state = async 是默认（异步复制）：主库提交不等备库确认。要「备库没落盘就别提交」得配 synchronous_standby_names。

备库上只读（实测）：
1
2
3
select pg_is_in_recovery(); -> t
insert into t_sr values (3,'也来一条');
ERROR: cannot execute INSERT in a read-only transaction
- 主库插入的两行（主库写的、也来一条）在备库查得到；主库停一次再起来，实测复制自动接上：pg_stat_replication 里 state 回到 streaming、备库行数 3、槽 active = t。
提升备库（实测）：
1
2
select pg_promote(); -- t
select pg_is_in_recovery(); -- f
- pg_promote() 之后这台就能写了；promote 不可逆，旧主库要么清掉重建要么用 pg_rewind 拉回来（pg_rewind 本机未实测）。

逻辑复制：发布表，订阅到另一个库

发布端必须是 wal_level = logical（实测在 replica 的库上建发布会警告）：

1 2	WARNING: "wal_level" is insufficient to publish logical changes HINT: Set "wal_level" to "logical" before creating subscriptions.

两端命令（实测，发布端 5631 → 订阅端 5632）：

-- 发布端
create publication pub_l for all tables;
-- 订阅端
create subscription sub_l connection 'host=127.0.0.1 port=5631 user=postgres password=password dbname=pgpub'
    publication pub_l;

NOTICE:  created replication slot "sub_l" on publisher
 subname | subenabled | 有连接串
---------+------------+---------
 sub_l   | t          | t

初始化后订阅端行数=3        -- copy_data 默认 true，会把已有数据搬过来

增量三种操作都实测同步：insert 后订阅端 4 行（甲乙丙丁）、update 后看到 1 | 甲改、delete 后订阅端回到 3 行。

没有主键的表默认删不动（实测原文与修复）：

HINT:  To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.
 relname | relreplident
---------+--------------
 t_l     | d            -- d = default：只用主键定位行
 t_nopk  | d

alter table t_nopk replica identity full;   -- 之后 relreplident 变成 f，delete 才同步

replica identity full 会让 WAL 记录整行旧值，体积和开销都涨；更好的做法是给表加主键。

管理命令实测可用：alter subscription sub_l disable;（subenabled 变 f）、drop publication pub_l;（发布端 pg_publication 剩 0 行）。订阅端还会在发布端留下一个同名槽，drop subscription 时才一起清掉。
在流复制的备库上做逻辑复制是不行的（实测）：create subscription 报 cannot execute CREATE SUBSCRIPTION in a read-only transaction——备库能作为「发布的读副本」，但不能当订阅端。

Python 操作 PostgreSQL（psycopg 3）

装哪个包：驱动用 psycopg 3。pip install "psycopg[binary]" 实测装上 psycopg 3.3.8 + psycopg-binary 3.3.8（还有 tzdata），不需要编译器也不需要系统 libpq；只写 pip install psycopg 装的是源码版（impl = python，要自己找到 libpq）。要连接池还得再加一句 pip install psycopg_pool——上面那次安装里没有它，说明它不是必装依赖。
本机版本（实测）：psycopg 3.3.8，实现是 binary（psycopg.pq.__impl__，自带 libpq，免编译）；psycopg_pool 3.3.3；Python 3.14.10。

第一条连接与参数化查询（实测）：

import psycopg

with psycopg.connect(host="127.0.0.1", port=5600, dbname="pgnote_probe",
                     user="pgnote") as conn:
    cur = conn.execute("select id, sku, price from t_item where price > %s order by id", (50,))
    print(cur.fetchall())

1 2	连接耗时 0.059 s 参数化查询：[(1, 'a-100', Decimal('59.00')), (2, 'b-200', Decimal('199.50'))]

numeric(10,2) 到 Python 里是 decimal.Decimal，不是 float，钱能直接算。
占位符是 %s（psycopg 自己的风格，不是 MySQL 的 ?，也不是 SQLite 的 ?），而且永远不要拼字符串；要拼表名/列名用 psycopg.sql：
1
2
3
from psycopg import sql

q = sql.SQL("select count(*) from {}").format(sql.Identifier("t_big"))
实测这条拿到 200000 行。sql.Literal 也能拼但等价于放弃参数化，除非知道自己在做什么，只用 Identifier。

executemany 与 COPY（实测）：
1
2
3
cur.executemany("insert into t_cat(name) values (%s) on conflict do nothing",
[("窗口%d" % i,) for i in range(3)])
print(cur.rowcount)
- 第一次跑 rowcount = 3；数据已存在时（全部被 on conflict do nothing 跳过）rowcount = 0。
- 参数序列的每一项必须是元组/列表，写成 [(x) for x in ...] 会在执行时报 TypeError: query parameters should be a sequence or a mapping, got str（本机踩过，正确写法是 ("窗口%d" % i,) 带逗号）。
- 大批量灌数用 copy。这里有个真实坑：cursor.copy() 的 write_row 用的是 text 格式（制表符分隔），你在 SQL 里写 with (format csv) 它也不会改成 CSV，结果服务端按 CSV 解析就报：
  1
  2
  psycopg.errors.BadCopyFileFormat: missing data for column "item"
  CONTEXT: COPY t_sale, line 1: "北京\tcopy测\t7"
  - 正确做法二选一：要么 copy("copy t_sale(city, item, amount) from stdin") 配 write_row（实测写入 2 行）；要么声明 CSV 后自己拼字节 cp.write('深圳,"含,逗号",9\n'.encode())（实测带逗号的字段被正确引起来）。

事务语义（实测，和 sqlite3/mysql-connector 都不一样）：

with psycopg.connect(...) as conn:          # 退出时提交并**关闭**连接
    with conn.transaction():                # 这一块单独回滚
        cur.execute("insert into t_cat(name) values ('要回滚的')")
        raise RuntimeError("故意炸")

实测：with conn.transaction() 里抛异常只回滚那段（要回滚的 没进去），块外的 executemany/copy 仍在同一事务里、with conn 正常退出时一起提交。查回来 1 行（只有 窗口0）。
另一个坑：with psycopg.connect(...) 退出时把连接关掉，出块再执行会报 psycopg.OperationalError: the connection is closed（实测）。想复用就别用 with 包住 connect。

异步模式在 Windows 上必须换事件循环（实测原文）：

1
2
3

psycopg.InterfaceError: Psycopg cannot use the 'ProactorEventLoop' to run in async mode.
Please use a compatible event loop, for instance by running
'asyncio.run(..., loop_factory=asyncio.SelectorEventLoop(selectors.SelectSelector()))'

import asyncio, selectors

asyncio.run(amain(),
            loop_factory=lambda: asyncio.SelectorEventLoop(selectors.SelectSelector()))

实测加上 loop_factory 之后 AsyncConnection 正常返回 [{'id': 1, 'sku': 'a-100'}, {'id': 2, 'sku': 'b-200'}]（配 row_factory=psycopg.rows.dict_row）。
另外异步游标的 execute 本身是协程：cur = await aconn.execute(...)，忘了 await 会得到 AttributeError: 'coroutine' object has no attribute 'fetchall'（实测踩到）。Linux 上不需要 loop_factory，这条只影响 Windows。

连接池（实测）：ConnectionPool(conninfo=..., kwargs={"password": pw})，with pool.connection() as conn: 取一条，实测 select 1 返回 (1,)。psycopg_pool 不在 psycopg 的必装依赖里，要单独 pip install psycopg_pool。
connect_timeout 一定要设（实测，这是 WSL/远程环境最容易出事的一条）：
1
2
带 connect_timeout=3： 3.01 s 后失败 -> connection timeout expired
不带 connect_timeout：130.01 s 后失败 -> connection timeout expired
- 连一个没人监听的端口时，WSL 的端口转发层不会立刻回 RST，所以 Python 侧表现为「干等 130 秒」而不是「立刻拒绝」。生产上没设超时，一个挂掉的库能把整池请求拖成雪崩。
- 写法：psycopg.connect(..., connect_timeout=5)，Django 则是 DATABASES["default"]["OPTIONS"] = {"connect_timeout": 5}。

localhost 与 127.0.0.1：本机实测两者都能连（localhost 0.079 s、127.0.0.1 0.064 s），因为 PG 同时监听了 [::]:5600 和 0.0.0.0:5600。但 psycopg 解析 localhost 时会依次试 ::1 和 127.0.0.1，实测失败时的报错把两次尝试都列出来：

1
2
3

Multiple connection attempts failed. All failures were:
- host: 'localhost', port: 5600, hostaddr: '::1': connection timeout expired
- host: 'localhost', port: 5600, hostaddr: '127.0.0.1': connection timeout expired

结论：配置里写 127.0.0.1，出问题时报错形状更简单，耗时也不会翻倍（connect_timeout=5 时两次尝试各等 5 秒）。

TLS 默认就在用（实测）：psycopg 默认 sslmode=prefer，服务端 show ssl = on，所以本机这条连接实测是 TLSv1.3；建连耗时 0.063 s，而 sslmode=disable 是 0.025 s。看是否加密要 join pg_stat_ssl：
1
2
3
select s.ssl, s.version
from pg_stat_activity a join pg_stat_ssl s on s.pid = a.pid
where a.usename = 'pgnote' limit 5;
- pg_stat_activity 没有 ssl 列（实测报 column "ssl" does not exist），要这样连表。
空闲连接：实测「建连后空闲 300 秒再复用」两种情况都还能用（默认和不默认 keepalives 都一样），因为服务端 tcp_keepalives_idle = 7200 s。真要让服务端主动踢空闲会话，用 PG14+ 的 idle_session_timeout（实测可设，默认 0 即不踢）：
1
set idle_session_timeout = '15s';

顺带一条与 Celery 类似的坑（本机复现过一次，非稳定规律，列在这里当线索）：连续两次长超时连接尝试之后，另一条已经建好的连接在跑 DDL 时炸出

1
2
3

psycopg.OperationalError: consuming input failed: could not receive data from server:
Software caused connection abort (0x00002745/10053)
SSL SYSCALL error: Software caused connection abort (0x00002745/10053)

后续 300 秒空闲复测没能复现，所以它更像 WSL 端口转发层的一次偶发中断，不是 PG 的行为；遇到时先重连，别当成数据库故障去排查。

Django 里用 PostgreSQL

官方口径：Django 5.x 的 django.db.backends.postgresql 后端要求 psycopg 3。老写法 django.db.backends.postgresql_psycopg2 现在只是一个别名——实测 load_backend() 两个名字返回的是同一个模块文件（.../backends/postgresql/base.py），所以它不会报错，但 Django 文档已经不再推荐写它。本机版本组合是 Django 5.2.19 + psycopg 3.3.8。

一个真实工程跑通（startproject → migrate → 建模型 → 读写，全部实测）：

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "NAME": "pgnote_dj",
        "USER": "django_pg",
        "PASSWORD": "password",
        "HOST": "127.0.0.1",
        "PORT": "5600",
        "OPTIONS": {"connect_timeout": 5},
        "CONN_MAX_AGE": 60,
    }
}

python manage.py check --deploy 能跑（报的是 HSTS/SSL 那几条安全告警，和数据库无关）。
python manage.py migrate --noinput 实测成功建出 auth、admin、contenttypes、sessions 共 10 张表（直接问库的 information_schema.tables 数出来的）。
python manage.py createsuperuser --noinput --username admin_pg --email admin@example.com（配合 DJANGO_SUPERUSER_PASSWORD 环境变量）实测成功，auth_user 里 admin_pg|t。

Django 替 PG 生成的 DDL（sqlmigrate 实测输出，信息量很大）：

CREATE TABLE "shop_book" (
    "id" bigint NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    "title" varchar(80) NOT NULL,
    "price" numeric(8, 2) NOT NULL,
    "tags" varchar(20)[] NOT NULL,
    "meta" jsonb NOT NULL,
    "created_at" timestamp with time zone NOT NULL
);

BigAutoField（Django 的 default_auto_field）在 PG 上是 identity 列而不是 serial：\d shop_book 里 id 的 Default 是 generated by default as identity，pg_attribute.attidentity = 'd'。
d 是 GENERATED BY DEFAULT，允许显式插值；前面手工建的表用的是 a（GENERATED ALWAYS），插值会被拒。
序列仍然存在：pg_get_serial_sequence('shop_book','id') → public.shop_book_id_seq。
pg_sequences 里 Django 这 10 张表的序列 cache_size 全是 1（手工建的 t_item_id_seq 是 10），意味着 Django 场景下取值更「老实」，不会因为缓存跳号。
字段映射：DecimalField(max_digits=8, decimal_places=2) → numeric(8, 2)；ArrayField(CharField(20)) → varchar(20)[]；JSONField → jsonb；DateTimeField → timestamp with time zone。
主键索引实测形状：CREATE UNIQUE INDEX shop_book_pkey ON public.shop_book USING btree (id)。

django.contrib.postgres 才有 PG 专属字段（ArrayField、JSONField(旧)、RangeField 等），要用数组/区间就得把它加进 INSTALLED_APPS。
旧写法 from django.contrib.postgres.fields import JSONField 在 Django 5.2 已经不能用了（实测报错原文）：
1
2
3
shop.Book.meta: (fields.E904) django.contrib.postgres.fields.JSONField is removed
except for support in historical migrations.
HINT: Use django.db.models.JSONField instead.
- 正确写法是 models.JSONField（django.db.models），它在任何后端上都能用；contrib.postgres 那个版本只是为了历史上的 migration 保留。老笔记里 ArrayField 还要 import，JSONField 不要——这是最容易抄错的一处。

ORM 读写实测（同一个模型）：

b = Book.objects.create(title="Django 与 PG", price="12.34",
                        tags=["编程", "数据库"], meta={"lang": "zh", "pages": 320})
q = Book.objects.get(pk=b.pk)
print(q.title, q.price, q.tags, q.meta)
print(Book.objects.filter(tags__contains=["编程"]).values_list("title", flat=True))
print(Book.objects.filter(meta__lang="zh").values_list("title", flat=True))

读回: Django 与 PG 12.34 ['编程', '数据库'] {'lang': 'zh', 'pages': 320}
price 的 Python 类型: <class 'decimal.Decimal'> | tags: <class 'list'> | meta: <class 'dict'>
数组过滤 tags__contains: ['Django 与 PG']
json 键查: ['Django 与 PG']
连接 vendor: postgresql | 引用标识符: "shop_book"

ArrayField 和 JSONField 的 lookup（tags__contains、meta__lang）是 contrib.postgres 提供的，MySQL 后端没有对应能力。
直接问库看到的列类型（实测 information_schema.columns）：('tags', 'ARRAY', '_varchar')、('meta', 'jsonb', 'jsonb')、('created_at', 'timestamp with time zone', 'timestamptz')。
manage.py flush --noinput 实测能在 PG 上把测试数据清干净（重建表内容）。

其他和后端无关但值得记的：python manage.py migrate 在 PG 上是整段事务（DDL 可回滚），所以一次迁移中途失败会全部退回；MySQL 上则可能留下半套表结构。

新手卡点速查

现象	原因	处理
`FATAL: password authentication failed for user "postgres"`	Ubuntu 装的 `postgres` 角色默认没有密码，只能 socket + `peer`	`sudo -u postgres psql`，或 `\password postgres` 设密码后再走 TCP
`could not access the server configuration file ".../postgresql.conf"`	Debian 系配置在 `/etc/postgresql/<ver>/<cluster>/`，数据目录里没有	用 `pg_createcluster` 建集群，别只 `initdb`
`FATAL: remaining connection slots are reserved ...`	非超级用户把 `max_connections - superuser_reserved_connections` 用满	收 `CONN_MAX_AGE`/连接池，或调 `max_connections`
连接一个挂掉的端口要等 130 秒	没设 `connect_timeout`，WSL/NAT 层不回 RST	`OPTIONS: {"connect_timeout": 5}`
`relation "MyTable" does not exist`	标识符不带引号会折成小写	全用小写下划线；需要大小写时才加双引号并一路保持
`ERROR: LIMIT #,# syntax is not supported`	抄了 MySQL 的 `limit 3, 2`	写 `limit 3 offset 2` 或 `fetch first 3 rows only`
`column "t_sale.item" must appear in the GROUP BY clause`	PG 严格按 SQL 标准做分组校验	把非聚合列全进 `group by`，或用窗口函数
`ERROR: CHECK constraints cannot be marked DEFERRABLE`	`deferrable` 只支持唯一/排他/外键	换成命名唯一约束，或改成应用层校验
`1/3` 结果是 0	整数除法	写 `1.0/3` 或 `::numeric`
`0.1 + 0.2 = 0.3` 为 `f`	`float8` 的固有误差	钱用 `numeric`，别用 `float8`
`psycopg.errors.InterfaceError: ... 'ProactorEventLoop'`	psycopg3 异步在 Windows 上不支持默认事件循环	`asyncio.run(..., loop_factory=lambda: asyncio.SelectorEventLoop(selectors.SelectSelector()))`
`psycopg.errors.BadCopyFileFormat: missing data for column ...`	`copy` 的 `write_row` 只写 text 格式	用 text 的 `COPY`，或自己拼 CSV 字节
`pg_stat_statements must be loaded via "shared_preload_libraries"`	扩展装上了但没预加载	配 `shared_preload_libraries` 后重启集群
`unrecognized configuration parameter "autovacuum_enabled"`	它是表级 reloption，不是 GUC	`alter table ... set (autovacuum_enabled = false)`，查 `pg_class.reloptions`
`vacuum full (index_cleanup off) t` 报语法错	选项必须全写在括号里	`vacuum (full, index_cleanup off) t`
`cannot insert a non-DEFAULT value into column "id"`	`generated always as identity` 不给显式值	导入数据时加 `overriding system value`
`ERROR: numeric field overflow`	整数位超出 `numeric(p,s)` 的精度	放大 `p`，注意超「标度」是静默四舍五入而不是报错
`HINT: To enable deleting from the table, set REPLICA IDENTITY`	逻辑复制的表没主键	加主键，或 `alter table ... replica identity full`
定时任务/索引/表体积看着不对	统计视图的值是从上次 `pg_stat_reset()` 起算	先确认统计基线，再看 `idx_scan`
`django.contrib.postgres.fields.JSONField is removed`	Django 5.x 删掉了这个导入路径	用 `django.db.models.JSONField`

参考

PostgreSQL 18 Documentation — Downloads for Ubuntu：发行版自带源与 PGDG apt 源两条官方路线的原文。
PostgreSQL Versioning Policy：年度大版本、5 年支持期、小版本内容，以及「没有 LTS 这个说法」。
Documentation: 8.3. Character Types：character(n) 补空格与「转成其它字符串类型时去掉尾随空格」的官方表述。
Documentation: 15.1. psql：\copy、\password、\d 系列元命令。
Documentation: 14.5. High-Availability, Load-Balancing, Backups：流复制/逻辑复制/连接池的定位。
Documentation: 30.2. Streaming Replication and Backups（pg_basebackup）与 Logical Replication：-R、复制槽、REPLICA IDENTITY。
Documentation: F.33. pg_stat_statements：预加载要求与语句归一化的边界。
psycopg 3 documentation：connect() 的事务语义、sql 模块、copy、异步用法。
Django 5.2: DATABASES 设置与 django.contrib.postgres：PostgreSQL 后端的驱动要求与专属字段。
PostgreSQL 18 Release Notes：MD5 口令认证被标为 deprecated、initdb 默认开 checksum、时区缩写处理变化等迁移注意点。