1 盲人摸象：PostgreSQL 与 Pigsty 全局地图

会写 SQL，并不等于知道 SQL 落在了哪里。一个连接 URI 里同时出现主机、端口、数据库和角色；连接成功后又会遇到实例、模式、关系、后端进程、WAL、服务端点与集群等词。它们属于不同层次，却经常被笼统地叫作“数据库”。许多误操作、权限错误和接入故障，都始于这张地图没有画清楚。

本章不急着介绍 PostgreSQL 的所有功能。我们只完成一件事：建立一套此后能够反复使用的坐标系。读者将从一条真实连接出发，逐层确认连接落点、对象边界、查询路径与服务拓扑，最后创建全书贯穿案例 pg36_shop 的最小基线。

版本基线：本章按 PostgreSQL 18.6、Pigsty v4.5.0 和单节点 L1 沙箱编写。核心 PostgreSQL 概念适用于当前受支持的大版本；Pigsty 的端口、组件和配置入口以 v4.5.0 为准。所有命令都先查询实际运行版本，书中示例输出只展示需要判断的字段。

本章目标

回答“我连到了什么、数据对象在哪里、一次查询经过什么、Pigsty 又管理了什么”，并固化后续章节共同使用的实验基线。

读者前置

开始本章前，你应当已经：

• 掌握 Linux 终端、环境变量和基本文件操作；
• 会写常用 SQL，但不要求熟悉 PostgreSQL 的系统目录；
• 拥有一个可连接的 PostgreSQL 环境；推荐使用第 0 章准备的 Pigsty L1 沙箱；
• 知道实验管理员连接信息存放在哪里，但不会把密码写进书稿、脚本或 Git。

如果已经有其他 PostgreSQL 环境，也可以完成 1.1–1.3 与 1.6；1.4、1.5 和 1.7 中的服务拓扑与平台证据需要 Pigsty。

学习完成标准

完成本章后，你应当能够拿出证据完成以下任务，而不是凭名称猜测：

1. 从连接 URI 中指出主机、端口、数据库和登录角色，并用 SQL 确认服务器、数据库、会话角色、模式搜索路径与读写状态；
2. 解释实例、database cluster、数据库、模式和关系对象的包含关系，说明角色为什么不隶属于某一个数据库；
3. 画出“客户端 → 服务入口 → PostgreSQL 后端 → 共享内存／数据文件／WAL”的最小查询路径；
4. 区分 PostgreSQL 原生能力、生产平台必须承担的职责和 Pigsty 的具体实现；
5. 使用最少的一组 psql 命令探索对象、切换数据库、执行脚本、保存证据和安全中断；
6. 创建并验证 pg36_shop 数据库、shop 模式与最小角色，生成后续章节可复用的环境快照。

贯穿场景

假设应用团队交给你下面这样的连接入口：

postgresql://pg36_app@pg-meta:5433/pg36_shop

这串字符并没有告诉你所有事实。pg-meta 可能是主机名，也可能是随主节点漂移的集群域名；5433 在 Pigsty 中通常是读写服务，而不是 PostgreSQL 进程直接监听的 5432；pg36_shop 是数据库名，不是实例名；pg36_app 是数据库角色，也不是 Linux 用户。只有把连接参数与服务器返回的证据合在一起，才能确认操作落点。

本章沿着同一条连接向内、再向外展开：

flowchart LR
  C["客户端与连接 URI"] --> S["Pigsty 服务入口<br/>HAProxy / PgBouncer"]
  S --> B["PostgreSQL 后端进程<br/>一个连接对应一个会话"]
  B --> O["数据库中的对象<br/>模式、表、索引、函数"]
  B --> M["实例共享资源<br/>共享内存、数据文件、WAL"]
  P["Pigsty 配置与控制面"] --> S
  P --> B
  E["日志、系统目录与指标"] -.复核.-> S
  E -.复核.-> B
  E -.复核.-> O

这张图不是完整架构图，而是本章的读图顺序：先确认连接参数，再让服务器说明自己是谁，然后才讨论平台如何把实例组合成服务。

本章路线

1.1 从连接串识别操作落点

先把 URI 中的五个名字拆开，并通过一条上下文快照查询确认“我到底连到了哪里”。这一节还会第一次区分实例端点、读写服务端点和只读服务端点。

1.2 PostgreSQL 对象与术语坐标

建立实例、database cluster、数据库、模式与关系对象的层级图，特别处理 PostgreSQL 中 “cluster” 与 Pigsty 集群容易混淆的问题。

1.3 一条查询经过了什么

用一个会话和一条查询观察客户端、后端进程、共享内存、数据文件、WAL、系统目录与统计视图各自扮演的角色。

1.4 从数据库实例到数据库服务

从单个 postgres 进程向外扩展，说明复制组、稳定入口、控制面、计算、存储、网络与可观测性为什么属于“服务”问题。

1.5 Pigsty 的资源模型

把通用职责映射到 Pigsty 的节点、实例、集群和服务，以及 PostgreSQL、Patroni、PgBouncer 与 HAProxy 的分工。

1.6 最小 psql 生存卡

只学习完成后续实验所需的最小命令集。更系统的连接保护、变量、脚本与可复现工作流留到 ch02《psql 与可复现工作流》。

1.7 实战：建立 pg36_shop 地图与实验基线

创建最小对象，采集连接、对象、服务与版本证据，建立 verify:state 和三档复位边界，并从 SQL 与 Pigsty 两侧指认同一对象。

本章交付物

完成实验后，至少保留以下内容：

• 一份不含密码的连接上下文快照；
• 一份 pg36_shop 对象树；
• 一份 L1 节点、实例、集群、服务与端口映射；
• pg36_shop 数据库、shop 模式和三类最小角色；
• 一次通过的 verify:state 输出；
• 明确的 reset:sql、reset:cluster、reset:host 适用边界。

这些产物从 ch02 开始会被直接复用。不要为了得到“好看”的输出而手工修改证据；环境差异本身也是需要记录的事实。

复习与迁移问题

1. postgresql://alice@db.example:5433/shop 中，哪一部分由客户端决定，哪一部分必须由服务器返回才能确认？
2. 为什么同一个角色可能连接多个数据库，而同一个普通表不能跨数据库直接访问？
3. 直连实例与连接稳定服务端点，各自暴露了什么假设？
4. pg_is_in_recovery() 能证明什么，不能证明什么？
5. 如果配置清单写着某实例是主库，而 SQL 显示它正在恢复，你会把哪一项当作当前运行事实？为什么？
6. 在托管数据库或 Kubernetes Operator 中，Pigsty 的“节点、实例、服务、控制面”分别可能映射成什么职责？

下一章如何使用本章

ch02《psql 与可复现工作流》不再解释这些对象是什么，而会把本章的临时命令整理成安全、可审查、可重跑的工作流。届时会加入服务文件、环境保护、失败即停、变量、确定性数据与机器可读输出。

如果此刻你仍不能在不查看答案的情况下画出连接到对象的完整路径，请先重做 1.7 的验收；后面的每一章都会默认这张地图已经建立。

参考基线

• PostgreSQL 18：连接字符串
• PostgreSQL 18：体系结构基础
• PostgreSQL 18：数据库角色
• PostgreSQL 18：psql
• Pigsty v4.5：PGSQL 集群模型
• Pigsty v4.5：服务与接入

返回上卷导读 · 下一章：手到擒来：psql 与可复现工作流 · 查看全书目录 · 查看索引中心

1.1 从连接串识别操作落点

一条连接字符串表达的是客户端的连接意图，不是服务器的自我证明。主机名可能经过 DNS 或 VIP，端口可能属于代理，登录角色还可能在会话内切换。可靠的第一步不是看到提示符就开始执行，而是把“我打算连到哪里”与“服务器说我落在哪里”对上。

本节全部操作属于 R0·观察。请使用第 0 章提供的实验凭据，不要把密码写入命令历史、书稿或 Git。pg36_shop 尚未创建，因此先用 Pigsty L1 已有的管理数据库观察；将 <L1_HOST> 替换为实际域名或 IP：

export PG36_BOOTSTRAP_URL='postgresql://dbuser_dba@<L1_HOST>:5436/postgres?application_name=pg36-ch01'
psql -X "$PG36_BOOTSTRAP_URL"

-X 表示暂不读取个人 psqlrc，避免本地定制改变示例行为。安全保存凭据、服务文件和连接保护会在 ch02《psql 与可复现工作流》中展开。

1.1.1 主机、端口、服务、数据库与角色

全书最终要交给应用的是类似下面的 URI。此刻先把它当作待解释的目标，而不是可以立即连接的成品：

postgresql://pg36_app@pg-meta:5433/pg36_shop?application_name=pg36-ch01
             └──角色──┘ └主机─┘└端口┘└─数据库──┘ └────连接参数─────┘

URI 支持 postgresql:// 和 postgres:// 两种 scheme。用户名、密码或数据库名含有 @、:、/、?、# 等保留字符时必须进行百分号编码。更重要的是，不要为了省事把密码直接写入可被 shell 历史、进程列表或日志记录的 URI；本章让 psql 交互式询问密码。

“服务”在这里是平台语义，而不是 URI 中额外的一段。它通常由“可访问的主机或域名 + 端口 + 路由规则”共同构成。Pigsty 的 pg-meta:5433 是读写服务入口；同样的 pg-meta 配上 5432，通常变成对当前 VIP 所在节点的 PostgreSQL 直连。端口改变，路径与故障语义也随之改变。

连接成功后，先执行一份上下文快照：

SELECT
    version()                         AS server_version,
    current_database()               AS database_name,
    session_user                     AS session_user,
    current_user                     AS current_user,
    inet_server_addr()                AS server_addr,
    inet_server_port()                AS server_port,
    pg_backend_pid()                  AS backend_pid,
    pg_is_in_recovery()               AS in_recovery;

关键判断不是输出长什么样，而是每一列证明了什么：

• version() 来自服务端，可以揭示服务器版本与构建信息；它不等于本机 psql --version；
• inet_server_addr() 和 inet_server_port() 是 PostgreSQL 后端接受连接的地址与端口。经过 HAProxy、PgBouncer 后，它们通常显示最后一跳 PostgreSQL 的地址与 5432，而不是客户端最初访问的 5433；
• 通过 Unix socket 连接时，inet_server_addr() 和 inet_server_port() 会是 NULL，这不是故障；
• pg_backend_pid() 是当前 PostgreSQL 后端进程号，只在该实例当前生命周期内有意义；
• pg_is_in_recovery() 为 false 表示当前实例不在恢复状态，通常是可写主库；为 true 表示处于恢复或热备状态。它不单独证明整套高可用系统健康。

客户端意图与服务器证据必须同时保留。只记 URI，会丢失实际落点；只记 SQL 输出，又会丢失客户端究竟通过哪个入口到达。

1.1.2 current_database()、current_user 与 search_path

进入服务器以后，还要确认三个会直接改变 SQL 含义的上下文：当前数据库、当前角色与模式搜索路径。

SELECT
    current_database()       AS database_name,
    session_user             AS authenticated_as,
    current_user             AS effective_as,
    current_setting('search_path') AS configured_path,
    current_schemas(true)    AS effective_path;

current_database() 返回当前连接所在数据库。PostgreSQL 的一个普通会话一次只连接一个数据库；\c 看似在会话内“切库”，实际是 psql 断开后重新建立连接。数据库之间不是类似 MySQL database.table 那样可以随意跨库限定访问的命名空间。

session_user 是最初通过认证的角色，通常在连接期间保持不变；current_user 是当前权限检查使用的有效角色。执行 SET ROLE 或进入使用 SECURITY DEFINER 的函数时，两者可能不同：

SELECT session_user, current_user;
-- 只有在当前角色有权切换时才能执行：
SET ROLE pg36_owner;
SELECT session_user, current_user;
RESET ROLE;

因此，审计“谁连进来”时看 session_user，判断“当前 SQL 以谁的权限运行”时看 current_user。两者都不等于操作系统账号。

search_path 决定没有写模式限定符的对象名如何解析，也决定未显式指定模式时新对象创建在哪里。假设有效路径是：

pg_catalog, shop

那么系统对象优先从 pg_catalog 解析，业务对象再从 shop 查找。current_setting('search_path') 返回配置文本；current_schemas(true) 返回去除不存在或不可访问项后的有效路径，并按参数决定是否包含隐含的系统模式。

不要把 search_path 当成界面便利设置。若不可信用户可以在搜索路径靠前的模式中创建对象，未限定名称的函数或操作符可能解析到攻击者提供的对象。应用与迁移脚本应采用受控路径，安全敏感 SQL 则显式写出模式名，例如 pg_catalog.set_config(...) 或 shop.orders。

本书为运行角色约定：

ALTER ROLE pg36_app IN DATABASE pg36_shop
SET search_path = pg_catalog, shop;

这条语句是 R1·可逆变更，只对角色 pg36_app 连接数据库 pg36_shop 时生效。回退方法是：

ALTER ROLE pg36_app IN DATABASE pg36_shop RESET search_path;

执行位置、权限与对象创建将在 1.7 一并处理。

1.1.3 实例端点、服务端点与只读端点

端点可以指向固定实例，也可以表达一种稳定服务意图。两者都能建立连接，但承诺不同。

这些是 Pigsty 的默认配置，不是 PostgreSQL 标准端口；用户可以修改。5432 才是 PostgreSQL 常见默认端口，6432 是 PgBouncer 常见默认端口。

最容易犯的错误，是把“replica 服务”理解成数据库层面的强制只读。服务名首先表达路由策略，不等同于授权策略。在单节点 L1 中没有专用备库，replica 服务可能没有可用后端，或者按具体配置回退到主库；即使连接到了备库，未来故障切换也可能改变承载实例。应用是否有写权限，仍应由角色授权、事务只读属性与数据库策略共同约束。

每次需要判断读写能力时，至少采集：

SELECT
    pg_is_in_recovery()                    AS in_recovery,
    current_setting('transaction_read_only')::boolean AS transaction_read_only,
    has_database_privilege(
        current_user,
        current_database(),
        'CREATE'
    )                                     AS can_create_in_database;

三个结果分别回答“实例是否在恢复”“当前事务是否只读”“角色是否拥有数据库级 CREATE 权限”，它们不是同一个问题。has_database_privilege 也不能穷举写入能力：表级权限、行级安全策略、函数权限和对象所有权仍可能改变结果。

一个两端互证练习

分别通过实例直连与 primary 服务建立连接，运行相同快照，并对比客户端入口与后端证据：

export PG36_INSTANCE_URL='postgresql://dbuser_dba@<INSTANCE_HOST>:5432/postgres?application_name=pg36-ch01-instance'
export PG36_PRIMARY_URL='postgresql://dbuser_dba@<L1_HOST>:5433/postgres?application_name=pg36-ch01-primary'

psql -X "$PG36_INSTANCE_URL" -c \
  "SELECT inet_server_addr(), inet_server_port(), pg_backend_pid(), pg_is_in_recovery();"

psql -X "$PG36_PRIMARY_URL" -c \
  "SELECT inet_server_addr(), inet_server_port(), pg_backend_pid(), pg_is_in_recovery();"

在单节点环境中，两次查询可能落到同一个 PostgreSQL 实例，但路径仍不同；在高可用环境中，primary 服务应随主库角色变化，而固定实例端点不会。不要为了让示例输出与书中一致而忽略差异，把实际结果写进环境清单。

本节验收

关闭终端前，确认你能回答：

• URI 中哪个字段选择数据库角色，哪个字段选择数据库？
• 为什么访问 5433 后，inet_server_port() 常常仍返回 5432？
• current_user 在什么情况下会与 session_user 不同？
• replica 服务、恢复状态、事务只读和角色权限为什么是四个不同判断？

若任何一个答案仍依赖“端口名字看起来像……”，重新执行上下文快照，用查询结果作答。

参考资料

• PostgreSQL 18：libpq 连接字符串
• PostgreSQL 18：系统信息函数
• PostgreSQL 18：模式与搜索路径
• Pigsty v4.5：服务与接入

返回本章目录 · 下一节：PostgreSQL 对象与术语坐标 · 查看全书目录 · 查看索引中心

1.2 PostgreSQL 对象与术语坐标

PostgreSQL 的对象不是装在一只名叫“数据库”的大盒子里。数据库和角色处在同一套服务器范围内，模式与关系对象则处在某一个数据库内部；一条普通连接只能进入其中一个数据库。只要层级画错，权限、命名、备份和迁移的判断就会跟着错。

先记住这张最小坐标图：

flowchart TD
  I["一个 PostgreSQL 实例<br/>进程 + 配置 + 数据目录"] --> D1["数据库 postgres"]
  I --> D2["数据库 pg36_shop"]
  I --> D3["数据库 template1"]
  I --> R["共享角色集合"]
  D2 --> S1["模式 shop"]
  D2 --> S2["模式 public"]
  S1 --> O1["表 / 分区表 / 序列"]
  S1 --> O2["索引 / 视图 / 物化视图"]
  S1 --> O3["函数 / 类型 / 其他对象"]

箭头表达包含或作用域，不表达磁盘上的目录结构。系统目录才是验证这些关系的权威入口。

1.2.1 实例、数据库、模式与关系对象

PostgreSQL 文档更常使用“服务器”或“服务器进程”描述运行实体。工程语境中的一个 PostgreSQL 实例，通常指一套正在运行的服务器进程，以及它们共同使用的配置、共享内存和数据目录。实例是运行边界，不是可以用 CREATE INSTANCE 创建的 SQL 对象。

一个实例管理多个数据库。数据库是连接边界：客户端在启动连接时选定数据库，普通 SQL 名称不能直接写成 另一个数据库.模式.表 跨库访问。确有跨库需求时，需要应用发起另一条连接，或显式使用 postgres_fdw、dblink 等机制；那是后续章节的内容。

每个数据库内部有多个模式（schema）。模式是数据库内的命名空间，shop.orders 中 shop 是模式，orders 是关系名。不同模式可以有同名对象，例如 shop.orders 与 archive.orders。

“关系”（relation）是 PostgreSQL 中一个比“表”更宽的家族。普通表、分区表、索引、序列、视图和物化视图等，都在 pg_class 中占有记录。先用系统目录观察层级：

-- 这张共享目录列出实例管理的数据库
SELECT oid, datname, datallowconn
FROM pg_catalog.pg_database
ORDER BY datname;

-- 以下目录只描述当前数据库中的对象
SELECT oid, nspname
FROM pg_catalog.pg_namespace
WHERE nspname !~ '^pg_temp_'
ORDER BY nspname;

SELECT
    n.nspname AS schema_name,
    c.relname AS relation_name,
    c.relkind
FROM pg_catalog.pg_class AS c
JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace
WHERE n.nspname = 'shop'
ORDER BY c.relname;

oid 是 PostgreSQL 为许多对象分配的内部标识。把它用于取证和目录连接很方便，但不要把 OID 当成跨数据库、跨重建仍稳定的业务标识。应用数据应有自己的键。

可以用一个简单实验确认数据库边界：分别连接 postgres 和 pg36_shop，查询 pg_namespace。你会看到两个数据库各自拥有一套模式目录；而查询 pg_database 时，两边都能看到同一组数据库。

1.2.2 角色为何跨数据库存在

角色（role）属于整个 PostgreSQL database cluster，而不是某一个数据库。原因很直接：客户端必须先用角色通过实例级认证，服务器才能允许它进入目标数据库。若角色本身藏在目标数据库里，认证顺序就会形成循环。

使用普通可见的 pg_roles 视图观察角色：

SELECT
    rolname,
    rolcanlogin,
    rolsuper,
    rolcreatedb,
    rolcreaterole
FROM pg_catalog.pg_roles
WHERE rolname LIKE 'pg36_%'
ORDER BY rolname;

在 postgres 与 pg36_shop 中执行这条查询，会看到相同的角色集合。底层的共享系统目录是 pg_authid，其中包含敏感认证信息，普通用户不应直接依赖；pg_roles 会隐藏密码字段。

角色跨数据库存在，不代表权限也自动跨数据库生效。需要分开看三件事：

1. 角色身份与成员关系：在整个 database cluster 中存在；
2. 进入数据库的资格：由数据库的 CONNECT 权限和认证规则控制；
3. 使用数据库内对象的权限：由模式、表、序列、函数等各级授权控制。

例如，pg36_app 可以同时存在于所有数据库，却只被授予进入 pg36_shop 和使用 shop 模式的权限。角色名称与 Linux 用户也相互独立；本地 peer 认证可以建立两者的映射，但那是认证配置，不是二者天然相同。

用 PostgreSQL 自带的权限函数验证，不要只读 GRANT 脚本猜测最终结果：

SELECT
    has_database_privilege('pg36_app', 'pg36_shop', 'CONNECT')
        AS can_connect,
    has_schema_privilege('pg36_app', 'shop', 'USAGE')
        AS can_use_shop;

第二个函数必须在包含 shop 模式的数据库中执行。这个差异本身正好说明：角色是共享的，模式对象是数据库本地的。

1.2.3 表、索引、序列、视图、函数与扩展

psql 的 \d 不是“describe table”的缩写式替代，而是进入 PostgreSQL 对象体系的一扇门。不同对象在目录中的位置和生命周期不同：

使用下面的查询把 relkind 翻译成可读类型：

SELECT
    n.nspname,
    c.relname,
    CASE c.relkind
      WHEN 'r' THEN 'table'
      WHEN 'p' THEN 'partitioned table'
      WHEN 'i' THEN 'index'
      WHEN 'I' THEN 'partitioned index'
      WHEN 'S' THEN 'sequence'
      WHEN 'v' THEN 'view'
      WHEN 'm' THEN 'materialized view'
      WHEN 'f' THEN 'foreign table'
      ELSE c.relkind::text
    END AS object_type
FROM pg_catalog.pg_class AS c
JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace
WHERE n.nspname = 'shop'
ORDER BY object_type, c.relname;

扩展尤其容易被误解。CREATE EXTENSION 不是启动一个数据库外部插件进程，而是让 PostgreSQL 按扩展控制文件和 SQL 脚本创建并登记一组对象；某些扩展另外需要预加载共享库或外部服务，但不能据此概括所有扩展。查看当前数据库已安装扩展：

SELECT extname, extversion, extnamespace::regnamespace
FROM pg_catalog.pg_extension
ORDER BY extname;

扩展按数据库安装。软件包在操作系统上“可用”，不等于已经在每个数据库中执行了 CREATE EXTENSION。ch14《内核分支与扩展生态》会系统处理安装、启用、升级和退出成本。

1.2.4 PostgreSQL “database cluster”的特殊含义

PostgreSQL 官方文档中的 database cluster，是“由一套服务器实例管理、存放在共同数据区域中的数据库集合”。initdb 的工作就是初始化这样一个 database cluster。它不天然表示多节点、高可用或分布式。

Pigsty 文档中的 PGSQL 集群，则是一个平台级业务单元：由一个主实例和零个或多个复制实例组成，通过服务暴露能力。每个实例都有自己的 PostgreSQL 数据目录；物理备库的数据来自主库复制，但仍是独立运行的服务器实例。

下面两条观察命令能把概念落到当前实例：

SHOW data_directory;

SELECT oid, datname
FROM pg_catalog.pg_database
ORDER BY oid;

data_directory 是当前实例的数据区域；pg_database 列出该实例所管理的数据库集合。生产环境不应依靠直接浏览数据目录理解对象，更不能手工移动或删除其中的文件。数据目录路径属于运维信息，公开证据包时应按环境敏感度处理。

若需要识别物理复制成员是否来自同一 PostgreSQL database cluster，可由有权限的管理员查询控制文件信息：

SELECT system_identifier, pg_control_version, catalog_version_no
FROM pg_catalog.pg_control_system();

物理主备通常共享 system_identifier；逻辑复制目标不会因此自动相同。这个值是取证线索，不是业务主键，也不等于 Pigsty 的 pg_cluster 名称。

本节验收：不用“数据库”糊弄过去

请为下面每项写出完整限定描述：

• pg36_shop：一个数据库；
• shop：pg36_shop 数据库中的模式；
• pg36_app：当前 PostgreSQL database cluster 中的角色；
• pg-meta-1：Pigsty 管理的 PostgreSQL 实例名；
• pg-meta：Pigsty PGSQL 集群名，也可能被配置为集群服务域名。

随后在 postgres 与 pg36_shop 各执行一次 pg_roles、pg_database 和 pg_namespace 查询。验收标准是：你能够根据结果解释哪些目录共享、哪些目录随数据库改变，而不是只说“两个库看起来差不多”。

参考资料

• PostgreSQL 18：管理数据库概览
• PostgreSQL 18：数据库角色
• PostgreSQL 18：系统目录
• PostgreSQL 18：数据库物理存储
• Pigsty v4.5：PGSQL 集群模型

上一节：从连接串识别操作落点 · 返回本章目录 · 下一节：一条查询经过了什么 · 查看全书目录 · 查看索引中心

1.3 一条查询经过了什么

现在沿着已经确认的连接继续向内走。客户端发送的不是“直接操作磁盘”的命令，而是 PostgreSQL 前后端协议中的消息；服务器端后端进程在一个会话上下文中解析、规划并执行 SQL，再通过共享资源读写数据。

本节只建立全景路径。查询优化器、事务、锁和执行器的细节会在 ch05《查询、事务与锁的核心心智模型》和 ch07《执行计划与统计信息》中展开。

1.3.1 客户端、后端进程与会话

PostgreSQL 采用客户端／服务器模型。psql、应用驱动和 GUI 都是客户端；postgres 服务器进程接受连接，并为每个直接连接创建一个后端进程（backend）。这个后端在连接存续期间保存会话状态，例如当前角色、当前数据库、参数、临时对象和事务状态。

连接池会改变观察方式。直连 PostgreSQL 时，一个客户端连接稳定对应一个后端；经过 PgBouncer 的事务池时，客户端连接与 PostgreSQL 后端只在事务期间绑定，下一个事务可能换到另一个后端。不要把某次观察到的 PID 永久记作“这个应用的进程”。

在当前连接中查看自己的会话：

SELECT
    pid,
    backend_type,
    datname,
    usename,
    application_name,
    client_addr,
    backend_start,
    state,
    wait_event_type,
    wait_event
FROM pg_catalog.pg_stat_activity
WHERE pid = pg_backend_pid();

这里有三类事实：

• pid、backend_start 描述当前后端生命周期；
• datname、usename、application_name 描述会话上下文；
• state、wait_event_type、wait_event 描述采样时刻正在做什么。

state = 'active' 只表示该后端当时正在执行查询，不等于它“健康”或“很忙”；idle 也不等于可以随意终止，因为客户端可能正在两次请求之间等待。活动视图是现场快照，解释它必须结合时间、事务状态和应用协议。

一条普通查询的最小路径如下：

1. 客户端建立连接并完成认证；
2. 客户端发送 SQL 或带参数的协议消息；
3. 后端解析语法，解析对象名称与权限；
4. 重写器处理规则和视图，规划器选择执行方案；
5. 执行器读取或修改关系页，必要时等待锁、I/O 或其他资源；
6. 后端把行、命令状态或错误返回客户端。

并行查询可能临时使用并行工作进程，但会话仍由领导后端承接；后台还有 autovacuum、WAL writer 等进程。它们都出现在进程体系中，却不是“一位用户连接一个会话”的反例。

双会话观察

打开终端 A，使用直连端点运行：

SELECT pg_backend_pid(), pg_sleep(10);

立刻在终端 B 中查询：

SELECT
    pid,
    application_name,
    state,
    wait_event_type,
    wait_event,
    left(query, 60) AS query_sample
FROM pg_catalog.pg_stat_activity
WHERE datname = current_database()
  AND query LIKE '%pg_sleep%'
  AND pid <> pg_backend_pid();

预期能看到终端 A 的后端处于 active，并等待 Timeout/PgSleep。这是受控 L1 实验，不要在共享生产连接池中用 pg_sleep 制造占用。

1.3.2 共享内存、数据文件与 WAL

不同后端进程需要看到同一份数据库状态，因此 PostgreSQL 使用共享内存协调缓存、锁、WAL 缓冲区和其他全局结构。最常被提到的 shared_buffers 是 PostgreSQL 管理的数据页缓存，但它不是数据库使用的全部内存；后端私有内存、操作系统页缓存和外部组件都在同一台主机上争用资源。

SELECT name, setting, unit, context
FROM pg_catalog.pg_settings
WHERE name IN (
    'shared_buffers',
    'wal_buffers',
    'work_mem',
    'maintenance_work_mem'
)
ORDER BY name;

context 提示参数需要怎样生效，但不直接说明“最佳值”。参数机制与资源预算留到 ch27《参数调优与资源治理》。

表和索引最终以页面形式保存在数据文件中。系统目录知道对象对应的相对路径：

SELECT
    'pg_catalog.pg_class'::regclass AS relation,
    pg_relation_filepath('pg_catalog.pg_class') AS relative_path;

返回的是相对于数据目录的路径线索，不是让用户绕过 PostgreSQL 直接读取或修改文件的接口。数据文件可能分段、使用表空间，并受到版本、存储管理器和关系类型影响。手工编辑数据目录几乎从来不是合格的应用操作。

WAL（Write-Ahead Log，预写式日志）记录数据库变化所需的重做信息。“先写”指的是：相关 WAL 必须在脏数据页落盘之前持久化；事务提交通常要等待其提交记录达到所要求的持久性级别，而数据页可以稍后由后台进程写出。这个顺序使崩溃恢复能够从一致检查点继续重放。

观察当前实例的 WAL 位置：

SELECT
    CASE
      WHEN pg_is_in_recovery()
        THEN pg_last_wal_replay_lsn()
      ELSE pg_current_wal_lsn()
    END AS observed_lsn,
    pg_is_in_recovery() AS in_recovery;

LSN（Log Sequence Number）是 WAL 流中的位置，不是墙上时钟，也不能脱离时间线和实例角色直接比较。WAL 也不是“把每条 SQL 原文记下来”的审计日志，更不能替代经过验证的备份；这些边界会在 ch20、ch21 和 ch32 分别用于复制、高可用与恢复。

可以先形成一个高层心智模型：

sequenceDiagram
  participant C as 客户端
  participant B as 后端进程
  participant M as 共享缓冲区
  participant W as WAL
  participant D as 数据文件
  C->>B: INSERT / UPDATE / DELETE
  B->>M: 修改内存中的数据页
  B->>W: 生成 WAL 记录
  W-->>B: 按持久性要求刷新
  B-->>C: COMMIT 成功
  M-->>D: 脏页稍后写回

图中省略了锁、检查点、全页镜像、复制和存储栈等细节；它只用来记住 WAL 与数据页写出的先后约束。

1.3.3 系统目录与统计视图如何描述自身

PostgreSQL 很大一部分可观测性来自“用 SQL 描述自己”，但系统目录与统计视图回答的是两类不同问题。

系统目录保存数据库的结构事实，例如：

• pg_database：有哪些数据库；
• pg_namespace：当前数据库有哪些模式；
• pg_class：有哪些关系对象；
• pg_attribute：关系有哪些列；
• pg_proc：有哪些函数与过程。

目录变化参与事务。例如，在事务中创建表后，当前事务能立即从 pg_class 看到它；回滚后记录消失。应用不应直接修改系统目录，而应使用 CREATE、ALTER、DROP 和 GRANT 等 SQL 接口。

统计视图描述运行活动与累计现象，例如：

• pg_stat_activity：当前会话、状态与等待；
• pg_stat_database：按数据库累计的事务、读写与冲突计数；
• pg_stat_user_tables：用户表扫描、修改与维护计数；
• pg_stat_replication：主库看到的复制发送状态。

统计信息可能因采样时刻、权限、统计重置和事务快照而变化。0 表示在当前统计口径下没有观测到，不等于历史上从未发生。监控系统从这些视图采集并保存时间序列，正是为了补上“当前快照没有历史”的缺口。

下面的查询把结构事实、运行事实和配置事实放在一张快照里：

SELECT jsonb_pretty(
    jsonb_build_object(
        'database', current_database(),
        'database_oid', (
            SELECT oid
            FROM pg_catalog.pg_database
            WHERE datname = current_database()
        ),
        'user', current_user,
        'backend_pid', pg_backend_pid(),
        'in_recovery', pg_is_in_recovery(),
        'server_version_num', current_setting('server_version_num'),
        'search_path', current_setting('search_path')
    )
) AS connection_snapshot;

JSON 只是便于保存的输出格式，不改变证据强度。对每个字段仍要问：

• 它来自客户端标签、配置、系统目录还是运行统计？
• 它描述当前连接、当前数据库、当前实例还是整套平台？
• 它会不会在重连、故障切换、统计重置或升级后改变？

本节验收：复述一条查询的旅程

不看本页，画出以下路径并为每一段写出一个可观察证据：

客户端 →（可选代理）→ 后端进程 → 对象目录／执行器
                         ├→ 共享内存
                         ├→ WAL
                         └→ 数据文件

最低验收结果：

• 用 pg_backend_pid() 与 pg_stat_activity 从两个会话观察到同一个后端；
• 用 pg_settings 记录共享内存相关配置，而不是猜测；
• 用 pg_relation_filepath() 找到关系路径线索，但没有直接修改数据文件；
• 能解释系统目录的结构事实与统计视图的运行事实为什么不能混为一谈。

参考资料

• PostgreSQL 18：体系结构基础
• PostgreSQL 18：服务器进程
• PostgreSQL 18：系统目录
• PostgreSQL 18：累计统计系统
• PostgreSQL 18：WAL 可靠性

上一节：PostgreSQL 对象与术语坐标 · 返回本章目录 · 下一节：从数据库实例到数据库服务 · 查看全书目录 · 查看索引中心

1.4 从数据库实例到数据库服务

一个 PostgreSQL 实例可以接受连接、执行事务并持久化数据，却还不自动等于一项可交付的数据库服务。生产用户关心的是稳定入口、可用性、恢复目标、容量、安全与责任人，而不是某个进程今天恰好运行在哪台主机上。

从实例走向服务，不是贬低 PostgreSQL“功能不全”，而是把数据库内核与平台工程放在正确边界上。这个边界也是本书后半卷的总地图。

1.4.1 单实例、复制组、服务入口与控制面

先把四个层次分开：

1. 单实例：一套 PostgreSQL 服务器进程与数据目录。它可以是完整、可用的开发数据库，但主机或实例故障会直接中断服务；
2. 复制组：主实例产生 WAL，一个或多个备实例接收并重放。复制提供数据副本与追赶机制，不自动回答何时提升、如何避免双主、客户端去哪里重连；
3. 服务入口：用稳定的域名、VIP、代理端口或服务发现名称表达“读写”“只读”“管理直连”等访问意图，并把流量路由到当前合格实例；
4. 控制面：保存期望状态、观察实际状态，并协调初始化、配置、选主、切换、备份、扩缩和维护等动作。

可以把请求路径与控制路径分开看：

flowchart LR
  A["应用"] --> E["稳定服务入口"]
  E --> P["当前主实例"]
  P --> R["备实例"]
  P -- "WAL 流" --> R

  C["控制面"] -. "观察角色与健康" .-> P
  C -. "观察角色与健康" .-> R
  C -. "更新路由或期望状态" .-> E

实线是数据面：应用查询和复制数据真实流动的路径。虚线是控制面：决定谁有资格承载流量，以及系统应当处于什么状态。控制面发生故障，不一定让当前 PostgreSQL 事务立即停止；但它可能让后续切换、配置变更或成员管理失效。

PostgreSQL 原生提供物理复制、同步提交、恢复、时间线和角色状态等基础机制，却有意不规定唯一的高可用编排方案。你可以用 Patroni，也可以使用托管数据库控制面、Kubernetes Operator 或组织自建系统。ch20《高可用拓扑与容灾目标》会根据故障模型重新审视这些选择。

在单节点 L1 中，复制组退化为一个主实例，服务入口与控制面仍然存在。这很适合学习组件关系，却不能证明多节点高可用已经成立。不要从“我能访问 5433”推导出“这套环境能容忍主机故障”。

1.4.2 计算、存储、网络、配置与可观测职责

数据库服务需要同时管理五类资源。每一类都要有“期望状态、运行事实、变更入口和失败边界”。

表格中的“平台”不是某个特定产品，而是任何生产方案都绕不开的责任集合。托管数据库把很多责任交给云厂商；自建系统则必须明确由谁实现、谁值守，以及产品边界之外还剩什么。

一个常见错误是只记录配置文件中的目标值。例如清单写着 shared_buffers: 8GB，实际实例可能尚未重启，SHOW shared_buffers 仍是旧值；清单写着实例角色为 primary，运行中的 PostgreSQL 却可能已经因为切换成为备库。声明、落地和运行事实是三层证据，不能互相替代。

另一个错误是把监控面板当成事实源本身。面板是对指标和日志的解释界面；当图表异常时，应当能够追到采集查询、标签、时间范围和 PostgreSQL 原生证据。反过来，只查询当前系统目录也没有历史，因此不能取代时间序列监控。

1.4.3 PostgreSQL 原生能力与平台组合能力的边界

全书采用三层叙述，避免把某个平台的按钮写成数据库原理：

第一层：PostgreSQL 原生机制

包括 SQL 语义、事务与锁、MVCC、WAL、复制协议、备份与恢复接口、角色权限、配置参数、系统目录、统计视图和扩展机制。关键结论优先回到 PostgreSQL SQL、日志、配置或官方文档验证。

第二层：数据库平台通用职责

包括主机置备、软件分发、拓扑编排、故障检测、选主与防脑裂、稳定接入、连接池、备份调度、密钥管理、监控告警、变更审计和恢复演练。这些责任客观存在，但实现方式不唯一。

第三层：Pigsty 参考实现

Pigsty 使用声明式配置与自动化，把 PostgreSQL、Patroni、etcd、HAProxy、PgBouncer、pgBackRest 和可观测组件组合起来。它提供一套可以拆开验证的具体答案，而不是 PostgreSQL 唯一的运行方式。

面对任意平台操作，都按下面的证据链追问：

这套追问可以迁移到其他平台。即使界面、CLI 和组件名称完全不同，“服务目标—原生状态—控制规则—运行证据—回退路径”的结构仍然成立。

分类练习

把下面动作分别归入“PostgreSQL 原生机制”“平台通用职责”“Pigsty 具体实现”，允许一项跨两层，但要说明边界：

• CREATE ROLE；
• 为主库提供稳定域名；
• pg_basebackup 复制基础数据；
• 发现主实例故障后选择备实例提升；
• HAProxy 在 5433 暴露读写服务；
• pg_stat_activity 观察会话；
• 保存 30 天指标并在 SLO 违约时告警。

参考判断：CREATE ROLE 与 pg_stat_activity 是原生接口；稳定域名、故障切换和长期告警是通用职责；“HAProxy + 5433”是 Pigsty 的具体实现。pg_basebackup 是原生工具，但“何时运行、保存在哪里、如何校验”仍属于平台工作流。

本节验收

选取你当前连接的 pg36_shop 服务，写出一条完整证据链：

服务目标
→ 客户端入口
→ 路由组件
→ PostgreSQL 实例
→ 原生 SQL 证据
→ 平台状态证据
→ 不一致时的停止条件

合格答案必须包含至少一个 PostgreSQL 原生查询，且不能只引用面板颜色或配置文件。下一节会把这条抽象链具体映射到 Pigsty。

参考资料

• PostgreSQL 18：高可用、负载均衡与复制
• PostgreSQL 18：监控数据库活动
• Pigsty v4.5：PGSQL 架构

上一节：一条查询经过了什么 · 返回本章目录 · 下一节：Pigsty 的资源模型 · 查看全书目录 · 查看索引中心

1.5 Pigsty 的资源模型

上一节把数据库平台拆成一组通用职责。本节只做一件事：把这些职责映射到 Pigsty v4.5 的实体、组件和证据源。记住映射比背命令重要，因为端口、界面和组件版本会变化，而“谁负责数据、谁负责角色判断、谁负责路由”必须始终说得清。

1.5.1 节点、集群、实例与服务

Pigsty 的 PGSQL 模块使用四个核心实体组织 PostgreSQL：

Pigsty 默认采用节点与 PostgreSQL 实例 1:1 的独占部署模型，因此节点名经常借用实例名；这是 Pigsty 的部署约定，不是 PostgreSQL 限制。pg_cluster、pg_seq 和 pg_role 三个身份参数构成最小声明：

pg-meta:
  hosts:
    10.10.10.10: { pg_seq: 1, pg_role: primary }
  vars:
    pg_cluster: pg-meta

由此可以推导：

节点：10.10.10.10（默认命名可为 pg-meta-1）
实例：pg-meta-1
集群：pg-meta
服务：pg-meta-primary / pg-meta-replica / pg-meta-default / pg-meta-offline

配置中的 pg_role: primary 表示初始化或编排意图，不是永远不变的运行角色。多节点集群发生切换后，主库可以从 pg-meta-1 变成其他实例，而实例编号不变。服务名表达访问意图，也不应跟着当前主实例改名。

在 Pigsty 管理节点的安装目录中，用只读命令观察解析后的配置范围：

cd ~/pigsty

# 只显示分组与主机关系，不输出包含密码的完整变量
ansible-inventory --graph

# 只从源文件定位非敏感身份字段；动态清单环境应改查相应 CMDB
grep -nE 'pg-meta:|pg_cluster:|pg_seq:|pg_role:' pigsty.yml

不要把完整 ansible-inventory --list 直接贴进工单或书稿，它可能包含密码、令牌和内部地址。证据包只采集解决当前问题所需的字段。

单节点 L1 会让四种服务最终落到同一台节点甚至同一个 PostgreSQL 实例，但实体仍然不同。就像一位工程师可以兼任开发、值班和发布审批，职责名称相同不意味着角色边界消失。

1.5.2 PostgreSQL、Patroni、PgBouncer 与 HAProxy 的职责

一条默认生产读写连接的路径是：

客户端
  → HAProxy :5433
  → 当前主实例上的 PgBouncer :6432
  → PostgreSQL :5432

组件之间不是相互替代，而是逐层收窄职责：

在高可用集群中，Patroni 通常使用 etcd 之类的分布式配置存储（DCS）协调领导者信息。HAProxy 请求 Patroni 健康接口判断实例角色，再把 5433 流量送往主库的 PgBouncer。PgBouncer 最后通过本地连接进入 PostgreSQL。

Pigsty v4.5 的默认入口如下，均可配置：

“默认目标”必须结合配置阅读。例如 pg_default_service_dest 可以让 primary/replica 服务绕过 PgBouncer。不要仅凭端口号推断实际路径。

在管理节点上查看控制面状态：

# R0：列出集群成员、角色和复制状态
pg list pg-meta

在数据库中从另一侧复核：

SELECT
    pg_is_in_recovery() AS in_recovery,
    current_setting('port') AS postgres_port,
    inet_server_addr() AS server_addr,
    inet_server_port() AS accepted_port;

若 pg list 报告 Leader，而 SQL 的 pg_is_in_recovery() 为 true，不要挑一个自己喜欢的结果继续操作。先停止角色相关变更，确认两条命令是否观察了同一集群、同一实例和同一时刻，再检查 Patroni 与 PostgreSQL 日志。配置标签、控制面判断和内核运行状态不一致，本身就是需要处理的事件。

本节暂不演练切换、连接池模式或代理重载；它们分别属于 ch20《高可用拓扑与容灾目标》和 ch22《服务接入、连接池与路由》。

1.5.3 配置清单、运行状态与监控事实分别来自哪里

Pigsty 环境至少存在三类事实源：

配置清单：系统应当是什么

默认静态清单是 ~/pigsty/pigsty.yml，也可以使用动态 inventory 或 CMDB。它声明节点、集群、初始角色、数据库、用户、服务和参数。清单适合回答“期望如何配置”，不能单独证明变更已经执行并生效。

运行状态：系统现在是什么

运行状态分散在各组件中：

• PostgreSQL：SQL、系统目录、统计视图和日志；
• Patroni：成员与角色状态、DCS 信息、健康接口和日志；
• PgBouncer：连接池状态、管理控制台和日志；
• HAProxy：服务后端、健康检查、运行配置和日志；
• systemd 与主机：进程、端口、文件、资源和服务状态。

例如，“当前谁是主库”首先要看 PostgreSQL 与 Patroni 的实时状态，而不是初始化清单中的 pg_role 标签。

监控事实：系统在一段时间内发生了什么

Pigsty 的采集系统把 PostgreSQL、主机、组件和日志事实转换成带标签的时间序列与日志流。常见身份标签包括：

• cls：集群；
• ins：实例；
• ip：节点地址；
• job：采集任务或日志来源；
• datname、relname、idxname：数据库内部对象。

监控适合回答趋势、持续时间和事件先后，但它仍可能受采集间隔、标签错误、查询权限和数据保留影响。面板显示“主库”时，应能追到指标标签与原生 SQL；SQL 显示瞬时正常时，也不能据此否认五分钟前的告警。

建立一张证据优先级表：

实战：生成 L1 资源快照

在管理节点执行：

cd ~/pigsty

{
  printf 'captured_at=%s\n' "$(date -Is)"
  printf 'host=%s\n' "$(hostname -f 2>/dev/null || hostname)"
  printf 'pigsty_source=%s\n' "$(git describe --tags --always 2>/dev/null || printf unknown)"
  printf '%s\n' '--- inventory graph ---'
  ansible-inventory --graph
  printf '%s\n' '--- cluster runtime ---'
  pg list pg-meta
} > pg36-l1-platform.txt

这段脚本只采集身份与拓扑，不输出完整变量。若环境不是 Git 安装，pigsty_source=unknown 是有效结果，随后应从发行包或发布记录补充版本，而不是编造标签。

在数据库端另存原生快照：

psql -X "$PG36_BOOTSTRAP_URL" -A -t -c "
SELECT jsonb_build_object(
  'captured_at', clock_timestamp(),
  'database', current_database(),
  'user', current_user,
  'server_addr', inet_server_addr(),
  'server_port', inet_server_port(),
  'version', current_setting('server_version'),
  'in_recovery', pg_is_in_recovery()
);" > pg36-l1-postgres.json

两份文件共同构成证据：一份描述平台，一份描述 PostgreSQL。提交或分享前检查是否含有内部地址、用户名或其他不应公开的信息；密码和令牌在任何情况下都不应进入证据包。

本节验收

你应当能够从 L1 环境指出：

• 哪个名字是节点、实例、集群和服务；
• 5432、6432、5433 各由哪个组件接收；
• 配置清单、pg list、SQL 与监控分别回答什么问题；
• 当这些证据冲突时，为什么“重新运行自动化让它一致”不是安全的第一动作。

参考资料

• Pigsty v4.5：PGSQL 集群模型
• Pigsty v4.5：PGSQL 架构
• Pigsty v4.5：服务与接入
• Pigsty v4.5：发布注记

上一节：从数据库实例到数据库服务 · 返回本章目录 · 下一节：最小 psql 生存卡 · 查看全书目录 · 查看索引中心

1.6 最小 psql 生存卡

psql 同时是交互式终端、脚本执行器和 PostgreSQL 取证工具。本节只保留完成第 1 章所需的最小操作；变量、条件、服务文件、失败即停、批量输入输出和可靠脚本会在 ch02《psql 与可复现工作流》中系统展开。

先区分两种输入：

• 以反斜线开头的是 psql 元命令，由客户端解释，通常不加分号；
• SQL 发送给 PostgreSQL 服务器，以分号结束，受事务与权限约束。

看到一个命令时先问“它由客户端还是服务器执行”，很多困惑会自动消失。

1.6.1 用 URI 连接，用 \l、\dn、\d 看对象

使用连接 URI 可以让终端、应用驱动和文档共享同一种参数表达：

psql -X "$PG36_BOOTSTRAP_URL"

连接成功后，第一条元命令应是：

\conninfo

它显示当前数据库、角色、主机或 socket、端口以及 TLS 等连接信息。随后按从大到小的顺序探索对象：

\l+
\dn+
\d
\dt shop.*
\d+ shop.orders
\du+
\dx

它们依次列出数据库、当前数据库中的模式、可见关系、shop 模式中的表、指定对象详情、角色和已安装扩展；+ 表示请求更详细的信息。对象尚未创建时，\d+ shop.orders 会明确报错。

\d 系列支持 psql 自己的对象模式匹配，不是 SQL 的 LIKE。例如 shop.* 表示模式 shop 下的对象；大小写与引号仍遵循 PostgreSQL 标识符规则。

元命令适合人类快速探索，系统目录查询适合明确筛选、保存和自动验证。两者应互相复核：

SELECT nspname
FROM pg_catalog.pg_namespace
ORDER BY nspname;

若 \dn 与查询结果看起来不同，先检查 \dn 是否过滤系统模式、用户是否有可见性权限，以及是否连接了同一个数据库，不要立即断言工具出错。

1.6.2 用 \c 切库，用 -c 与 -f 执行

在交互会话中切换数据库：

\c pg36_shop
\conninfo

\c 实际上会断开当前连接并建立新连接。未显式指定的主机、端口和角色通常沿用当前值；所以切换后必须再次执行 \conninfo 或上下文快照。若切换失败，psql 在交互模式下通常保留原连接，不要误以为已经进入目标库。

从 shell 执行一条 SQL：

psql -X "$PG36_BOOTSTRAP_URL" \
  -c 'SELECT current_database(), session_user, current_user;'

执行一个 SQL 文件：

psql -X "$PG36_BOOTSTRAP_URL" \
  -v ON_ERROR_STOP=1 \
  -f setup.sql

-c 适合短小、可见的一次性观察；-f 让错误消息包含文件与行号，适合可审查脚本。ON_ERROR_STOP=1 要求 psql 遇到脚本错误后停止，避免第一步失败后继续执行一串建立在错误前提上的语句。

不要把多行复杂 SQL 塞进 shell 的 -c 参数：shell 引号、SQL 引号和变量展开叠在一起，很容易产生与屏幕看起来不同的实际输入。复杂内容放入版本控制的 .sql 文件，并在执行前查看差异。

交互会话内也可以执行文件：

\i setup.sql

但自动化与验收更适合从 shell 使用 -f，因为调用方可以读取退出码并保存标准输出、标准错误。

1.6.3 用 \o 或 -A -t 保存输出

人读的表格与机器读的结果需要不同输出形式。

交互式保存随后产生的查询输出：

\o pg36-connection.txt
SELECT current_database(), current_user, pg_is_in_recovery();
\o

第二个不带文件名的 \o 恢复到终端输出。忘记恢复时，后续查询“没有输出”往往只是仍在写文件。

从 shell 生成机器友好的单值或逐行结果：

psql -X "$PG36_BOOTSTRAP_URL" \
  -A -t \
  -v ON_ERROR_STOP=1 \
  -c 'SELECT current_database();'

• -A 使用不对齐输出，去掉表格边框；
• -t 只输出元组，去掉列名与行数提示；
• -X 避免个人 psqlrc 改写格式；
• ON_ERROR_STOP 让失败产生可判断的非成功退出。

若有多列，显式选择分隔符和空值表示，或者直接输出 JSON；不要让下游脚本解析为人类排版的表格：

psql -X "$PG36_BOOTSTRAP_URL" -A -t -c "
SELECT jsonb_build_object(
  'database', current_database(),
  'user', current_user,
  'in_recovery', pg_is_in_recovery()
);"

保存输出不等于保存证据上下文。文件旁还应记录采集时间、客户端入口、服务端版本和命令来源，否则一行 false 很快会失去解释价值。

1.6.4 用 \q、Ctrl-C 安全退出与中断

正常退出：

\q

如果正在输入但尚未发送一条 SQL，Ctrl-C 会清空当前查询缓冲区并回到提示符。可以先用 \p 查看缓冲区内容，用 \r 主动清空：

\p
\r

前者显示尚未发送的查询，后者重置查询缓冲区。

如果服务器正在执行查询，Ctrl-C 会请求取消当前语句，而不是粗暴终止服务器进程。取消可能需要等待服务器到达可中断位置；网络中断时，客户端也未必能确认取消请求是否送达。

取消事务中的语句通常会让当前事务进入失败状态。此时后续 SQL 会收到“current transaction is aborted”，必须明确回滚：

ROLLBACK;

不要连续按键后在不知道状态的情况下继续操作。中断后立即执行：

SELECT
    current_database(),
    current_user,
    pg_is_in_recovery();

若查询能正常执行，说明连接仍可用且不在失败事务中；若连接已经断开，由 psql 明确重连后再重新采集上下文。

Ctrl-Z 只是把本地 psql 挂起，服务器连接和可能的事务仍然存在。它不是安全退出手段。遗留的 idle in transaction 会话可能长期持有快照和锁，是后续并发与膨胀问题的常见来源。

一张够用的生存卡

本节验收

从一个新终端完成以下闭环：

1. 用 URI 进入 postgres，执行 \conninfo；
2. 用 \l+ 查看实例中的数据库，再用 \c postgres 明确重连；
3. 用 \dn+ 找到 public 与系统模式，用系统目录查询复核；
4. 把当前数据库名以无表头单值形式保存到文件；
5. 运行 SELECT pg_sleep(10);，用一次 Ctrl-C 取消；
6. 执行上下文查询确认连接可用，最后用 \q 退出。

验收文件中数据库名必须精确为 postgres，且终端中没有遗留失败事务提示。1.7 创建 pg36_shop 后，再用同一组命令完成章级验收。

参考资料

• PostgreSQL 18：psql
• PostgreSQL 18：错误与消息字段

上一节：Pigsty 的资源模型 · 返回本章目录 · 下一节：实战：建立 pg36_shop 地图与实验基线 · 查看全书目录 · 查看索引中心

1.7 实战：建立 `pg36_shop` 地图与实验基线

现在把前六节的地图落到一个真实对象上。本实验会在 L1 沙箱创建 pg36_shop 数据库、shop 模式和三个专用角色，随后从 PostgreSQL 与 Pigsty 两侧收集证据。

实验分为两个风险级别：

• setup 与快照采集：R1·可逆变更，只创建以 pg36_ 命名的教学对象；
• reset:sql：R2·破坏性演练，会删除整个 pg36_shop 数据库，只能在确认可销毁的 L1 中执行。

准备两个不含密码的连接 URI。将 <L1_HOST> 替换为实际域名或 IP；密码由 psql 询问或使用 ch02 将介绍的安全凭据机制：

export PG36_BOOTSTRAP_URL='postgresql://dbuser_dba@<L1_HOST>:5436/postgres?application_name=pg36-ch01-admin'
export PG36_SHOP_ADMIN_URL='postgresql://dbuser_dba@<L1_HOST>:5436/pg36_shop?application_name=pg36-ch01-admin'

这里使用 5436 default 服务，目的是跟随主库且绕过事务连接池执行管理脚本。若你的环境修改了 Pigsty 默认服务，请根据实际配置替换，不能照抄端口猜路径。

1.7.1 创建数据库、业务模式和最小角色

本章只建立权限骨架，不创建订单、商品或支付表：

对象所有者使用 NOLOGIN，避免应用直接以所有者身份绕过授权边界。两个登录角色在本章故意不设置密码；这既避免在教程中分发固定密码，也使它们在默认密码认证规则下暂时无法远程登录。ch02 会为连接与凭据建立正式工作流。

下载或打开三份伴随实验文件：

• setup.sql：幂等创建角色、数据库、模式和默认权限；
• verify.sql：机器验证状态并输出摘要；
• reset.sql：带确认口令的实验清理。

执行初始化：

psql -X "$PG36_BOOTSTRAP_URL" \
  -v ON_ERROR_STOP=1 \
  -f setup.sql

执行身份为 dbuser_dba 或等价的实验管理员；目标必须是 L1 的 postgres 数据库。脚本会：

1. 仅在缺失时创建三个 pg36_ 角色，并收敛高风险属性；
2. 仅在缺失时从 template0 创建 UTF-8 数据库；
3. 创建由 pg36_owner 拥有的 shop 模式；
4. 撤销 public 模式的公共建对象权限；
5. 为两个运行角色授予模式使用权和未来对象的默认权限；
6. 为 pg36_shop 中的两个运行角色设置 pg_catalog, shop 搜索路径。

成功末尾应出现：

[setup] complete: roles intentionally have no password in this chapter

这条消息只证明脚本执行完毕，不能替代下一目的状态验证。

与 Pigsty 声明式配置对齐

SQL 能证明 PostgreSQL 对象机制，但 Pigsty 管理的长期环境还应把期望状态写入 inventory，避免下次自动化执行时出现配置漂移。最小声明可采用下面的结构；不要把实际明文密码直接写进公开配置：

pg_users:
  - { name: pg36_owner, login: false, pgbouncer: false, comment: pg36_shop object owner }
  - { name: pg36_app,   login: true,  pgbouncer: false, comment: pg36_shop runtime role }
  - { name: pg36_ro,    login: true,  pgbouncer: false, comment: pg36_shop read-only role }

pg_databases:
  - name: pg36_shop
    owner: pg36_owner
    encoding: UTF8
    pgbouncer: true
    schemas:
      - { name: shop, owner: pg36_owner }

本章先将 pgbouncer: false 用于尚无凭据的两个登录角色；数据库本身可以加入连接池。设置正式认证材料后，再把用户加入 PgBouncer。若在既有集群中应用声明，应先评审差异，然后使用：

cd ~/pigsty
bin/pgsql-user pg-meta pg36_owner
bin/pgsql-user pg-meta pg36_app
bin/pgsql-user pg-meta pg36_ro
bin/pgsql-db pg-meta pg36_shop

这些是 R1 操作，目标集群名不一定是 pg-meta。运行前用 ansible-inventory --graph 确认限制范围；若清单里已经存在同名但含义不同的对象，立即停止，不要让自动化强行“收敛”。

1.7.2 生成连接快照、对象树、服务拓扑与环境清单

建立证据目录：

mkdir -p evidence/ch01

连接快照

psql -X "$PG36_SHOP_ADMIN_URL" -A -t -v ON_ERROR_STOP=1 -c "
SELECT jsonb_build_object(
  'captured_at', clock_timestamp(),
  'database', current_database(),
  'session_user', session_user,
  'current_user', current_user,
  'server_addr', inet_server_addr(),
  'server_port', inet_server_port(),
  'backend_pid', pg_backend_pid(),
  'server_version', current_setting('server_version'),
  'search_path', current_setting('search_path'),
  'in_recovery', pg_is_in_recovery()
);" > evidence/ch01/connection.json

这里以管理员会话采集，因此 search_path 不会冒充 pg36_app 的角色级设置。角色设置由 verify.sql 直接查询目录验证。

对象树

psql -X "$PG36_SHOP_ADMIN_URL" > evidence/ch01/objects.txt <<'PSQL'
\pset pager off
\conninfo
\dn+
\du+ pg36_*
\d shop.*
PSQL

此时 shop 模式尚无业务关系，\d shop.* 返回“没有找到任何关系”是正确结果。对象树的目标是证明数据库、模式和角色边界，不是提前制造表。

服务拓扑与环境清单

在 Pigsty 管理节点执行：

cd ~/pigsty

{
  printf 'captured_at=%s\n' "$(date -Is)"
  printf 'node=%s\n' "$(hostname -f 2>/dev/null || hostname)"
  printf 'pigsty=%s\n' "$(git describe --tags --always 2>/dev/null || printf unknown)"
  printf '%s\n' '--- inventory ---'
  ansible-inventory --graph
  printf '%s\n' '--- runtime ---'
  pg list pg-meta
  printf '%s\n' '--- listening ports ---'
  ss -lnt | awk 'NR == 1 || $4 ~ /:(5432|5433|5434|5436|5438|6432)$/'
} > evidence/ch01/platform.txt

将 pg-meta 替换为实际集群名。ss 只证明端口正在监听，不证明后端角色、路由正确或 SQL 可用；它必须与 pg list 和连接快照合读。

版本清单

在客户端记录客户端与服务器版本：

{
  psql --version
  psql -X "$PG36_SHOP_ADMIN_URL" -A -t -c \
    "SELECT 'server=' || current_setting('server_version');"
} > evidence/ch01/versions.txt

客户端与服务端小版本不同不一定是错误，但必须留痕。涉及协议、元命令或版本特性的实验以实际版本为准。

1.7.3 建立 verify:state 与三档 reset

verify:state 不是“脚本没报错”的同义词。它从系统目录重新读取最终状态，检查对象所有者、角色、权限和数据库级搜索路径：

psql -X "$PG36_BOOTSTRAP_URL" \
  -v ON_ERROR_STOP=1 \
  -f verify.sql \
  | tee evidence/ch01/verify.txt

成功输出的值会因环境不同而变化，但必须包含：

status=ok
database=pg36_shop
database_owner=pg36_owner
schema=shop
schema_owner=pg36_owner
in_recovery=false

在备库或错误路由上执行时，脚本不应被“修到能过”；先回到 1.1 确认为什么管理连接没有进入可写主库。

本书使用三档复位，它们按影响范围命名，不代表都要在每章执行：

执行 reset:sql 前必须同时满足：

• 当前是明确标识的可销毁 L1；
• pg36_shop 中没有需要保留的数据；
• 三个 pg36_ 角色没有被其他数据库使用；
• PG36_BOOTSTRAP_URL 指向预期集群的主库管理服务；
• 已阅读 reset.sql，确认它没有被本地修改。

然后使用完整确认口令：

psql -X "$PG36_BOOTSTRAP_URL" \
  -v ON_ERROR_STOP=1 \
  -v confirm_reset=RESET_PG36_SHOP \
  -f reset.sql

脚本会先终止连接到 pg36_shop 的会话，再删除数据库与角色。这不是可回滚事务。未提供精确口令时脚本会拒绝执行。复位后重新运行 setup.sql 与 verify.sql，应得到新的数据库 OID；OID 改变正好说明它不能作为业务稳定标识。

1.7.4 验收：从 SQL 与 Pigsty 两侧指认同一对象

最后把所有名字放回一张表。下面是结构，不是要求实际值与示例相同：

从 PostgreSQL 侧运行最终快照：

SELECT
    current_database() AS database_name,
    (
      SELECT oid
      FROM pg_catalog.pg_database
      WHERE datname = current_database()
    ) AS database_oid,
    inet_server_addr() AS server_addr,
    inet_server_port() AS server_port,
    pg_backend_pid() AS backend_pid,
    pg_is_in_recovery() AS in_recovery;

从 Pigsty 侧用 pg list <cluster> 找到同一 server_addr 对应的实例与角色，再检查 HAProxy 的 default 服务是否把 5436 路由到该实例的 PostgreSQL 5432。这就是“双侧指认”：平台名字最终落到 PostgreSQL 原生事实，SQL 地址也能反查回平台实体。

章级验收清单

只有以下各项全部成立，第 1 章才算完成：

• verify.sql 输出 status=ok，并以非零退出码报告任何不满足项；
• 能解释客户端访问 5436 而服务器接受端口显示 5432 的原因；
• 能区分 pg-meta、pg-meta-1、pg36_shop、shop 与 pg36_app；
• pg36_owner 不可登录，数据库与模式均由它拥有；
• pg36_app 与 pg36_ro 没有超级用户、建库、建角色、复制或绕过 RLS 权限；
• 能从 SQL 判断当前实例是否处于恢复状态，并从 pg list 找到对应平台角色；
• connection.json、objects.txt、platform.txt、versions.txt 和 verify.txt 已生成；
• 证据文件不包含密码、SCRAM verifier、令牌或不必要的完整 inventory；
• 知道三档 reset 的影响范围，但没有为了“练习”执行无关的集群或主机重建；
• 复位演练后可以重新运行 setup → verify，且知道数据库 OID 会改变。

交付给 ch02

下一章将接收本章的五样东西：两个管理员连接 URI、三类角色、pg36_shop.shop 命名约定、证据目录和可运行的 setup/verify/reset 脚本。ch02 会给应用角色配置安全凭据与服务入口，把这些手工命令改造成可审查、可重跑的任务。

本章刻意没有创建业务表。下一步不是凭直觉开始堆 DDL，而是先把工具链和失败语义固定下来。

参考资料

• PostgreSQL 18：CREATE ROLE
• PostgreSQL 18：CREATE DATABASE
• PostgreSQL 18：默认权限
• Pigsty v4.5：用户与角色配置
• Pigsty v4.5：数据库配置

上一节：最小 psql 生存卡 · 返回本章目录 · 下一章：手到擒来：psql 与可复现工作流 · 查看全书目录 · 查看索引中心