PostgreSQL 部署、配置与项目接入

高谷深陵大约 37 分钟约 11155 字

PostgreSQL 部署、配置与项目接入参考图PostgreSQL 从部署入口、role / database / schema、项目验证、权限凭证到团队环境治理的落地链路。高清原图

从“容器健康，应用仍然失败”开始

PostgreSQL 容器显示 healthy，Spring Boot 却报 permission denied for schema public；另一个开发者改完密码后仍然认证失败；迁移脚本偶尔又把表建进了错误的 schema。这些现象不是一个连接串能解释的。服务进程就绪，只证明 PostgreSQL 能响应；role、database、schema、search_path、初始化时机和旧 volume 才决定应用能否正确工作。

先确认运行入口和端口，不做任何写操作：

docker version
docker compose version
docker ps --format "table {{.Names}}\t{{.Ports}}"

Windows 可以用：

netstat -ano | findstr 5432
netstat -ano | findstr 5433

Linux / macOS 可以用：

lsof -i :5432
lsof -i :5433

如果 5432 已被占用，后面的实例映射为 5433:5432。实验目录只保存 Compose、初始化脚本和占位配置；真实密码、域名与生产连接串留在不提交的 .env 或密钥系统中。客户端可以使用本机 psql、pg_isready、pg_dump，也可以直接调用容器内命令；Java 接入实验再准备 JDK、Maven 或 Gradle。

先固定可升级的版本基线

PostgreSQL 大约每年发布一个 major version，每个 major 支持 5 年；同一 major 的 minor release 只包含修复，官方建议持续升级到该 major 的当前 minor。开发、CI 与生产应使用同一 major，补丁升级先过恢复演练和应用回归。Versioning Policy给出了仍受支持的版本和最终支持日期，预发布版本只用于兼容测试。

PostgreSQL 核心采用宽松的 PostgreSQL License，许可风格接近 BSD / MIT；这不代表整套方案里的 extension、Operator、备份工具、容器基础镜像和云服务自动采用相同条款。引入 PostGIS、Patroni、PgBouncer、商业扩展或托管服务时，要把软件许可、镜像来源、支持责任、升级兼容和退出成本分别写进选型记录。

下面的可复现实验固定 postgres:18.4。这是一个明确的依赖基线，不是“永远使用 18.4”的承诺；升级机器人或平台 owner 应定期检查同一 major 的新 minor，验证后同时更新 Compose、CI 和恢复镜像。PostgreSQL Docker Official Image说明 18 及以后使用版本相关 PGDATA，18 的默认值是 /var/lib/postgresql/18/docker，volume 应挂到 /var/lib/postgresql 父目录；17 及更早的旧模板不能直接照搬到 18。

初始化时还要分清四个安全事实：POSTGRES_USER 创建的是高权限角色，应用不能长期复用；POSTGRES_HOST_AUTH_METHOD=trust 不应进入共享环境；初始化变量与 /docker-entrypoint-initdb.d/ 只在空数据目录执行；pg_isready 只证明服务响应，不证明密码、schema、extension 或 migration 已就绪。密码可通过 POSTGRES_PASSWORD_FILE 等 _FILE 入口注入，role、database 与 schema 则要分别创建和授权。

PostgreSQL 的流复制只传递 WAL。自动故障发现、primary 提升、旧主隔离、客户端路由和重连需要 Patroni、repmgr、pg_auto_failover、PgBouncer、pgpool-II、HAProxy 或云平台控制面协作。任何组合都要演练脑裂隔离、路由收敛和恢复，而不能把“standby 正在追 WAL”当成完整高可用。

判断“数据库已经可用”时要逐层加证据。pg_isready 成功只说明 postmaster 接受连接；业务 role 能连到目标 database，并在指定 schema 完成写读删，才说明认证和授权闭环；迁移、连接池、extension 与测试数据能重复创建，才说明项目闭环；base backup 能在临时实例恢复，连续 WAL 能推进到目标时间点，才说明恢复链路闭环。四层证据不能互相替代。

major upgrade 也不能靠直接换镜像 tag。先复制数据或恢复备份，在隔离实例上执行 pg_upgrade、dump/restore 或逻辑复制方案；随后比较 extension、collation、执行计划、序列、权限和应用回归；最后才进入共享环境。minor upgrade 不改变数据格式的承诺降低了风险，但仍要验证驱动、extension 和故障恢复工具，并保留可启动的旧镜像与升级前备份。

PostgreSQL 入口要先区分“生产部署形态”和“开发验证入口”。只会启动一个容器，不等于理解 PostgreSQL 架构。

入口	定义	特点	适用场景	主要风险
单机单实例	一台服务器 / VM 运行一个 PostgreSQL cluster	简单、成本低、链路短	本地开发、测试、小型内部系统、非核心业务	单点故障，备份恢复必须可靠
单机多实例	一台机器运行多个 cluster，端口、数据目录、systemd 服务隔离	资源复用，多版本测试方便	迁移演练、扩展兼容测试、轻量隔离	CPU / IO 互相抢占，目录和服务名易混
物理机 / VM 主备	primary + standby，基于 WAL 流复制或日志传送	生产可控，便于自建 HA	核心业务、自建 IDC、强治理团队	切换、fencing、路由、备份和监控要自建
K8S Operator	用 CloudNativePG、Crunchy、Zalando 等 Operator 管理集群	声明式、自动化程度高	云原生平台、统一 K8S 运维团队	存储、网络、备份、权限和 Operator 版本复杂
云化托管	云厂商 RDS / AlloyDB / Aurora PostgreSQL 兼容服务等	运维负担低，控制台化	中小团队、弹性业务、DBA 能力不足时	成本、权限、参数透明度和厂商绑定

开发验证入口单独看：

入口	主要用途	能验证什么	不能证明什么
本机安装	`psql`、`pg_dump`、扩展、客户端兼容实验	本机服务、CLI、认证、locale 行为	团队一致性、清理、版本可复现
Docker 单容器	快速验证 role / schema / extension 行为	初始化变量、端口、账号、最小 SQL	生产容量、高可用、备份恢复
Docker Compose	项目本地联调和 CI 临时依赖	服务名、网络、volume、healthcheck、项目接入	生产切换、长期审计、真实存储故障
本地多实例	复制、升级、恢复和故障切换演练	WAL、replica、端口目录隔离、基础切换	真实网络、磁盘、机房故障和生产 SLA

架构师真正要回答的是：这套 PostgreSQL 是临时验证、共享开发、核心生产还是数据平台基础设施；能接受多久不可用；能接受丢多少 WAL 窗口；团队是否具备复制、备份、VACUUM、权限、迁移和恢复演练能力。

如果只是开发侧选择，可以按下面的流程定入口：

选择建议：

方式	适合场景	不适合
本机安装	需要系统服务、`psql` / `pg_dump` / `pg_restore`、扩展和客户端兼容验证	新人快速清理、多版本并存、项目依赖复现
Docker 单容器	个人快速验证、权限实验、连接问题复现	团队长期模板、多服务协同
Docker Compose	项目级联调、CI 本地复现、新人一键启动	不能证明生产存储、切换和审计能力
共享开发实例	多服务共用测试数据、低配置电脑无法跑本地依赖	无 owner、无权限分层、无 schema 规范的团队

本机安装边界

本机安装从 PostgreSQL Downloads 选择 Windows、macOS、Linux、BSD、Solaris 对应的软件包或安装器。安装动作完成后，还要把机器上的隐式状态变成团队可检查的信息：

本机安装必须记录来源、版本、服务名、端口、数据目录、配置文件、卸载方式和 psql 客户端路径。新项目默认不建议依赖“每个人本机都装好了 PostgreSQL”。否则新人排障会卡在服务、pg_hba.conf、locale、历史数据目录和客户端版本。如果本机安装只是为了 psql、pg_dump、pg_restore，优先安装客户端工具，服务端仍然放在 Compose 里。

本机多版本并存时，要区分 server 版本和 psql 客户端版本。客户端版本不匹配可能让导入导出、元命令和脚本行为变得不一致。

本机安装后至少验证：

psql --version
pg_isready -h 127.0.0.1 -p 5432
psql "host=127.0.0.1 port=5432 dbname=postgres user=YOUR_POSTGRES_USER" -c "SELECT version();"

如果 pg_isready 失败，先不要改项目配置，先确认服务是否监听、端口是否冲突、系统服务是否启动。

Docker 单容器快速验证

单容器适合个人验证 PostgreSQL 行为：role / schema / extension / search_path / 端口是否按预期工作。它不是团队长期模板，因为依赖、网络、healthcheck、初始化脚本和清理策略都很难沉淀。

docker volume create pg18-sandbox-data

docker run -d \
  --name pg18-sandbox \
  -p 127.0.0.1:5433:5432 \
  -e POSTGRES_USER=postgres \
  -e POSTGRES_PASSWORD=YOUR_POSTGRES_SUPERUSER_PASSWORD \
  -e POSTGRES_DB=app_dev \
  -e POSTGRES_INITDB_ARGS="--encoding=UTF8 --locale-provider=builtin --builtin-locale=C.UTF-8 --auth-host=scram-sha-256" \
  -v pg18-sandbox-data:/var/lib/postgresql \
  postgres:18.4

验证服务、创建 schema、确认写读删：

docker exec pg18-sandbox pg_isready -h 127.0.0.1 -U postgres -d app_dev

docker exec -e PGPASSWORD=YOUR_POSTGRES_SUPERUSER_PASSWORD pg18-sandbox \
  psql -h 127.0.0.1 -U postgres -d app_dev -v ON_ERROR_STOP=1 \
  -c "CREATE SCHEMA IF NOT EXISTS app_dev;" \
  -c "CREATE TABLE IF NOT EXISTS app_dev.tool_verify(id int GENERATED BY DEFAULT AS IDENTITY, marker text);" \
  -c "INSERT INTO app_dev.tool_verify(marker) VALUES ('single-container-ready');" \
  -c "SELECT marker FROM app_dev.tool_verify ORDER BY id DESC LIMIT 1;" \
  -c "DROP TABLE app_dev.tool_verify;"

清理：

docker rm -f pg18-sandbox
docker volume rm pg18-sandbox-data

这条路径只解决“我能不能很快跑通 PostgreSQL”。一旦要给项目组复用，就进入下面的 Compose 模板，把账号分层、init 脚本、healthcheck、验证 SQL、清理脚本和文档一起提交。

推荐把 PostgreSQL 开发依赖收进项目目录，而不是散落在每个人机器上。

your-project/
  compose.yaml
  .env.example
  db/
    postgres/
      init/
        01-bootstrap.sh
        02-app-schema.sh
      verify/
        verify.sql
      dumps/
        .gitkeep
  docs/
    dependency-setup.md
  scripts/
    dev-up.sh
    dev-reset.sh

Windows 上创建 *.sh 后要确保文件是 LF 换行。入口脚本在 Linux 容器里执行，CRLF 可能导致 bash 报错。

`.env.example`

POSTGRES_IMAGE=postgres:18.4
POSTGRES_HOST_PORT=5433
POSTGRES_SUPERUSER=postgres
POSTGRES_SUPERUSER_PASSWORD=YOUR_POSTGRES_SUPERUSER_PASSWORD
POSTGRES_DATABASE=app_dev
POSTGRES_MIGRATION_USER=app_dev_migration
POSTGRES_MIGRATION_PASSWORD=YOUR_POSTGRES_MIGRATION_PASSWORD
POSTGRES_APP_USER=app_dev
POSTGRES_APP_PASSWORD=YOUR_POSTGRES_APP_PASSWORD
POSTGRES_SCHEMA=app_dev
POSTGRES_TIME_ZONE=UTC
POSTGRES_INITDB_ARGS=--encoding=UTF8 --locale-provider=builtin --builtin-locale=C.UTF-8 --auth-host=scram-sha-256

真实 .env 不提交。.env.example 只告诉团队需要哪些变量。

`compose.yaml`

services:
  postgres:
    image: ${POSTGRES_IMAGE:-postgres:18.4}
    container_name: your-project-postgres
    ports:
      - "127.0.0.1:${POSTGRES_HOST_PORT:-5433}:5432"
    environment:
      POSTGRES_USER: ${POSTGRES_SUPERUSER:-postgres}
      POSTGRES_PASSWORD: ${POSTGRES_SUPERUSER_PASSWORD}
      POSTGRES_DB: ${POSTGRES_DATABASE:-app_dev}
      POSTGRES_MIGRATION_USER: ${POSTGRES_MIGRATION_USER:-app_dev_migration}
      POSTGRES_MIGRATION_PASSWORD: ${POSTGRES_MIGRATION_PASSWORD}
      POSTGRES_APP_USER: ${POSTGRES_APP_USER:-app_dev}
      POSTGRES_APP_PASSWORD: ${POSTGRES_APP_PASSWORD}
      POSTGRES_SCHEMA: ${POSTGRES_SCHEMA:-app_dev}
      POSTGRES_INITDB_ARGS: ${POSTGRES_INITDB_ARGS}
      POSTGRES_HOST_AUTH_METHOD: scram-sha-256
      TZ: ${POSTGRES_TIME_ZONE:-UTC}
    command:
      - postgres
      - -c
      - timezone=UTC
      - -c
      - log_timezone=UTC
      - -c
      - password_encryption=scram-sha-256
      - -c
      - max_connections=120
    volumes:
      - postgres-data:/var/lib/postgresql
      - ./db/postgres/init:/docker-entrypoint-initdb.d:ro
    shm_size: 128mb
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -h 127.0.0.1 -U \"$${POSTGRES_USER}\" -d \"$${POSTGRES_DB}\""]
      interval: 10s
      timeout: 5s
      retries: 12
    restart: unless-stopped

volumes:
  postgres-data:
    name: your-project-postgres-data

这里有几件事是故意写清楚的：

宿主机端口用 5433，容器内仍是 5432，并默认只绑定 127.0.0.1，开发机不向局域网暴露。PostgreSQL 18 的 Compose 把 volume 挂到 /var/lib/postgresql，不要沿用旧模板里的 /var/lib/postgresql/data。POSTGRES_USER 只作为初始化和管理 superuser，应用连接用 POSTGRES_APP_USER。

初始化脚本放在 /docker-entrypoint-initdb.d/，但只会在空数据目录时执行。POSTGRES_INITDB_ARGS 固定 encoding、locale provider 和 host auth。团队如果仍在 PostgreSQL 17 或更早版本，需要重新按对应官方镜像文档调整 PGDATA 和 locale 参数。

healthcheck 只说明 PostgreSQL 可以响应，不说明 app role、schema、extension 和 search_path 都正确。pg_isready 甚至可能在用户名或数据库名不符合预期时仍返回服务状态，所以后面还要跑 SQL 验证。

如果同一个 Compose 文件里还有应用服务，不要只写短语法 depends_on: [postgres]。应用应该等待 PostgreSQL healthcheck 通过后再启动：

services:
  app:
    depends_on:
      postgres:
        condition: service_healthy

`db/postgres/init/01-bootstrap.sh`

这个脚本创建迁移 role、应用运行 role、schema、extension，并把 role 的 search_path 指向项目 schema。重点是把 DDL 权限交给迁移账号，把应用账号限制在运行期 DML 边界。

#!/usr/bin/env bash
set -euo pipefail

: "${POSTGRES_APP_USER:?POSTGRES_APP_USER is required}"
: "${POSTGRES_APP_PASSWORD:?POSTGRES_APP_PASSWORD is required}"
: "${POSTGRES_MIGRATION_USER:?POSTGRES_MIGRATION_USER is required}"
: "${POSTGRES_MIGRATION_PASSWORD:?POSTGRES_MIGRATION_PASSWORD is required}"
: "${POSTGRES_SCHEMA:?POSTGRES_SCHEMA is required}"

psql -v ON_ERROR_STOP=1 \
  --username "$POSTGRES_USER" \
  --dbname "$POSTGRES_DB" \
  --set=database="$POSTGRES_DB" \
  --set=migration_user="$POSTGRES_MIGRATION_USER" \
  --set=migration_password="$POSTGRES_MIGRATION_PASSWORD" \
  --set=app_user="$POSTGRES_APP_USER" \
  --set=app_password="$POSTGRES_APP_PASSWORD" \
  --set=app_schema="$POSTGRES_SCHEMA" <<'SQL'
SELECT format('CREATE ROLE %I LOGIN PASSWORD %L', :'migration_user', :'migration_password')
WHERE NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = :'migration_user')\gexec
SELECT format('ALTER ROLE %I LOGIN PASSWORD %L', :'migration_user', :'migration_password')\gexec
SELECT format('CREATE ROLE %I LOGIN PASSWORD %L', :'app_user', :'app_password')
WHERE NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = :'app_user')\gexec
SELECT format('ALTER ROLE %I LOGIN PASSWORD %L', :'app_user', :'app_password')\gexec
SELECT format('GRANT CONNECT, TEMPORARY ON DATABASE %I TO %I, %I', :'database', :'migration_user', :'app_user')\gexec
SELECT format('CREATE SCHEMA IF NOT EXISTS %I AUTHORIZATION %I', :'app_schema', :'migration_user')\gexec
SELECT format('GRANT USAGE, CREATE ON SCHEMA %I TO %I', :'app_schema', :'migration_user')\gexec
SELECT format('GRANT USAGE ON SCHEMA %I TO %I', :'app_schema', :'app_user')\gexec
SELECT format('ALTER ROLE %I SET search_path TO %I, public', :'migration_user', :'app_schema')\gexec
SELECT format('ALTER ROLE %I SET search_path TO %I, public', :'app_user', :'app_schema')\gexec
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
SELECT format('CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA %I', :'app_schema')\gexec
SELECT format('ALTER DEFAULT PRIVILEGES FOR ROLE %I IN SCHEMA %I GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO %I', :'migration_user', :'app_schema', :'app_user')\gexec
SELECT format('ALTER DEFAULT PRIVILEGES FOR ROLE %I IN SCHEMA %I GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO %I', :'migration_user', :'app_schema', :'app_user')\gexec
SQL

为什么不用应用账号直接初始化一切？因为 extension、schema 权限和 role 创建需要明确管理边界。开发环境可以简化，但不能把 superuser 交给应用长期使用，也不能让运行账号长期拥有 CREATE 权限。这里把 pgcrypto 安装到项目 schema，是为了避免把 extension 对象默认散落到 public；共享环境如果有更严格规范，可以改成独立 app_ext schema，并把它放在受控 search_path 位置。

`db/postgres/init/02-app-schema.sh`

这个脚本用迁移 role 创建最小业务对象，再把运行期 DML 权限授给应用 role。应用账号是否真的具备写入权限，交给后面的 verify.sql 验证。

#!/usr/bin/env bash
set -euo pipefail

psql -v ON_ERROR_STOP=1 \
  --username "$POSTGRES_MIGRATION_USER" \
  --dbname "$POSTGRES_DB" \
  --set=app_user="$POSTGRES_APP_USER" \
  --set=app_schema="$POSTGRES_SCHEMA" <<'SQL'
CREATE TABLE IF NOT EXISTS :"app_schema".user_demo (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  username TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS :"app_schema".tool_verify (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  marker TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO :"app_schema".user_demo(username)
SELECT 'demo'
WHERE NOT EXISTS (
  SELECT 1 FROM :"app_schema".user_demo WHERE username = 'demo'
);

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA :"app_schema" TO :"app_user";
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA :"app_schema" TO :"app_user";
SQL

初始化脚本要幂等。旧 volume 存在时脚本不会再次执行；空 volume 初始化时，脚本也不能因为重跑而污染数据。

`db/postgres/verify/verify.sql`

SHOW server_version;
SHOW server_encoding;
SHOW lc_collate;
SHOW lc_ctype;
SHOW TimeZone;

SELECT current_database() AS current_database,
       current_user AS current_user,
       current_schema() AS current_schema,
       current_schemas(false) AS resolved_search_path;

SELECT gen_random_uuid() IS NOT NULL AS pgcrypto_ready;

INSERT INTO tool_verify(marker) VALUES ('postgres-dev-ready')
RETURNING id, marker, created_at;

SELECT marker
FROM tool_verify
WHERE marker = 'postgres-dev-ready'
ORDER BY id DESC
LIMIT 1;

DELETE FROM tool_verify WHERE marker = 'postgres-dev-ready';

这份验证脚本比 SELECT 1 更有价值，因为它同时验证了版本、encoding、locale、时区、当前库、当前用户、schema、extension、写入、读取和清理。

先把 .env.example 复制成 .env，填入本机开发密码。密码只放本地，不提交。

启动 PostgreSQL：

docker compose up -d postgres
docker compose ps postgres

看日志：

docker compose logs postgres --tail 120

容器健康后，用容器内客户端验证。这里故意读取容器里的环境变量，不依赖宿主机 shell 是否加载 .env。

PowerShell：

Get-Content -Raw .\db\postgres\verify\verify.sql | docker compose exec -T postgres sh -lc 'PGPASSWORD="$POSTGRES_APP_PASSWORD" psql -h 127.0.0.1 -U "$POSTGRES_APP_USER" -d "$POSTGRES_DB" -v ON_ERROR_STOP=1'

Bash / Zsh：

docker compose exec -T postgres sh -lc 'PGPASSWORD="$POSTGRES_APP_PASSWORD" psql -h 127.0.0.1 -U "$POSTGRES_APP_USER" -d "$POSTGRES_DB" -v ON_ERROR_STOP=1' < db/postgres/verify/verify.sql

预期结果至少能看到：

server_version
18.4

server_encoding
UTF8

current_user
app_dev

current_schema
app_dev

pgcrypto_ready
t

marker
postgres-dev-ready

再从宿主机验证端口映射。不要把密码直接写进命令历史里：

PowerShell：

$env:PGPASSWORD = "YOUR_POSTGRES_APP_PASSWORD"
psql -h 127.0.0.1 -p 5433 -U app_dev -d app_dev -c "SELECT version(), current_database(), current_user, current_schema();"
Remove-Item Env:\PGPASSWORD

Bash / Zsh：

read -rsp "PostgreSQL password: " PGPASSWORD
export PGPASSWORD
psql -h 127.0.0.1 -p 5433 -U app_dev -d app_dev \
  -c "SELECT version(), current_database(), current_user, current_schema();"
unset PGPASSWORD

最后确认你没有连错环境：

psql -h 127.0.0.1 -p 5433 -U app_dev -d app_dev -c "\conninfo"

如果 current_schema 不是 app_dev，不要继续接项目。先修 search_path 或 JDBC currentSchema。

JDBC URL

pgJDBC 官方文档给出的 URL 基本形式包括：

jdbc:postgresql://host:port/database

本地项目建议显式写 host、port、database、schema 和应用名：

jdbc:postgresql://localhost:5433/app_dev?currentSchema=app_dev&sslmode=disable&ApplicationName=your-project-local

注意：

localhost 是宿主机连容器映射端口时使用。Compose 网络里的应用服务要连 postgres:5432，不是 localhost:5433。currentSchema=app_dev 只能解决默认 schema，不等于自动授予 schema 权限。

XML 里要把 & 写成 &。共享环境不要默认 sslmode=disable。是否启用 TLS 要按网络边界和团队策略决定。

Maven 依赖示例：

<dependency>
  <groupId>org.postgresql</groupId>
  <artifactId>postgresql</artifactId>
  <version>42.7.13</version>
</dependency>

驱动版本应由依赖更新工具持续跟踪，并按团队 JDK 与 PostgreSQL major 做连接、迁移、时区和数据类型回归；不要让示例版本永久冻结在项目模板里。

Gradle 示例：

dependencies {
  runtimeOnly("org.postgresql:postgresql:42.7.13")
}

Spring Boot 本地 profile

spring:
  datasource:
    url: jdbc:postgresql://localhost:5433/app_dev?currentSchema=app_dev&sslmode=disable&ApplicationName=your-project-local
    username: ${POSTGRES_APP_USER}
    password: ${POSTGRES_APP_PASSWORD}
    hikari:
      maximum-pool-size: 10
      minimum-idle: 1
      connection-timeout: 5000
  flyway:
    enabled: true
    url: jdbc:postgresql://localhost:5433/app_dev?currentSchema=app_dev&sslmode=disable
    user: ${POSTGRES_MIGRATION_USER:${POSTGRES_APP_USER}}
    password: ${POSTGRES_MIGRATION_PASSWORD:${POSTGRES_APP_PASSWORD}}
    default-schema: app_dev
    schemas:
      - app_dev

开发环境可以让迁移账号和应用账号暂时相同，但共享实例要分开：

账号	用途	权限
`postgres`	初始化和管理	superuser，只能 owner 使用
`app_dev_migration`	执行迁移	schema 内 DDL，受变更审查
`app_dev`	应用运行	schema 内 DML，不使用 superuser
`app_readonly`	查询和排障	SELECT，只读

迁移工具接入

Flyway / Liquibase 的关键不是“能不能跑”，而是迁移对象落在哪个 schema、用哪个账号跑、失败后怎么恢复。

建议：

迁移脚本显式使用 app_dev schema。迁移账号和应用运行账号分离，至少在共享环境分离。每个迁移脚本在本地 Compose 先跑，再进入共享环境。

迁移失败后不要手工改 flyway_schema_history 或 Liquibase changelog 表，先保留日志和失败 SQL，再决定 repair 或回滚。extension 创建要单独评审。比如 pgcrypto 在开发环境常见，但 PostGIS、FDW、C 语言扩展等不能默认交给应用账号。

最小验证：

mvn -q dependency:get -Dartifact=org.postgresql:postgresql:42.7.13

如果依赖下载失败，先排查 Maven 私服、代理和证书，不要改数据库配置。

客户端与驱动矩阵

工具	用途	最小验证	风险边界
`psql`	命令行连接、元命令、排障	`\conninfo`、`SELECT current_user`	容易把生产连接和开发连接混在历史记录里
`pg_isready`	健康检查	返回 accepting connections	只证明连接状态，不证明 schema、账号和 extension 正确
`pg_dump` / `pg_restore`	逻辑备份和恢复	导出开发 schema 并恢复到临时库	单库备份不含全部全局对象
`pg_basebackup`	物理基础备份、standby 起点	拉取 base backup 并用 `pg_verifybackup` 校验	需要 WAL 策略、磁盘和权限配套
pgJDBC	Java 项目接入	应用 profile 连接并跑 migration	升级要通过 JDK、连接、迁移和数据类型回归
pgAdmin / DBeaver / DataGrip	GUI 管理和查询	只读账号连接，能看到目标 schema	保存密码、误连生产、导出敏感数据

GUI 客户端不是生产运维平台。团队模板应默认给只读账号，连接名必须带环境前缀，生产连接不能保存高权限密码。

从开发实例走向生产

本地 Compose 能稳定启动，只证明团队拥有一个可复现的开发入口。走向共享环境和生产之前，决策重点会从“服务能不能跑”变成四件事：故障时能否恢复、并发下是否保持正确、容量增长时是否可维护、团队是否有能力长期接管。

最容易犯的错误，是把组件名称当成能力证明。存在 standby 不等于具备高可用，启用了 WAL 归档不等于能够按时间点恢复，接入 PgBouncer 也不等于连接数已经受控。每项能力都要落到可观测证据和演练结果。

先把开发基线做实

开发实例进入团队模板前，至少形成三层证据：

层次	要回答的问题	可接受证据
进程与网络	PostgreSQL 是否在预期地址接受连接	`docker compose ps`、`pg_isready`、端口监听结果
身份与命名空间	应用以哪个 role 连接，落在哪个 database 和 schema	`current_user`、`current_database()`、`current_schema()`
业务闭环	迁移、写入、查询、回滚和清理能否重复执行	migration 记录、验证 SQL 输出、重建环境结果

下面这组查询适合放进应用启动诊断或环境验收脚本，输出中不包含密码：

SELECT current_user, current_database(), current_schema(), current_schemas(false);
SHOW server_version;
SHOW search_path;
SHOW transaction_read_only;

预期结果要与环境约定一致：应用账号不是 superuser，database 与项目匹配，首个有效 schema 是项目 schema，共享开发实例允许写入，生产只读入口则应返回 transaction_read_only = on。反向验证要故意使用错误端口、错误密码和无权 schema 的账号，分别确认网络拒绝、认证失败和授权失败能够被日志与监控区分。

生产形态由约束决定

选择部署形态前，先写下业务能够接受的数据丢失窗口、恢复时长、峰值连接数、写入增长、运维人数和预算。没有这些约束，讨论 Patroni、Operator 或云数据库只是在比较产品名。

业务状态	合理起点	必须补齐的能力	不宜直接做的事
本地开发与 CI	Compose 单实例	固定版本、初始化幂等、最小验证、可安全重置	搭建伪生产 HA
小型内部系统	单实例或托管单节点	自动备份、恢复演练、连接与磁盘监控	把应用账号设为 superuser
一般生产业务	primary + standby 或托管高可用	RPO/RTO、复制监控、切换 runbook、旧主隔离	把流复制等同于自动故障转移
核心交易业务	经演练的 HA 控制面或托管多可用区	fencing、路由收敛、同步策略、跨故障域恢复	只验证“能 promote”
大量短连接服务	应用连接池 + PgBouncer	总连接预算、池模式兼容性、超时与降级	只提高 `max_connections`
分析与报表压力增长	独立 replica、数仓或分析平台	延迟语义、资源隔离、数据同步监控	让重查询长期争抢 OLTP

PostgreSQL 每个客户端连接通常对应一个后端进程。数据库连接预算应按“服务副本数 × 每副本池上限 + migration、批处理和管理连接 + 故障余量”计算。12 个服务副本各配置 30 个连接，理论上已经需要 360 个业务连接。此时把 max_connections 从 200 改成 500，可能只是把失败推迟成内存、上下文切换和尾延迟问题；应先检查池大小、连接占用和长事务，再评估 PgBouncer。

两条深入学习路径

当问题已经表现为执行计划漂移、索引选择错误、锁等待、长事务、表膨胀或 autovacuum 跟不上时，继续修改部署模板不会解决根因。PostgreSQL 查询、事务与 VACUUM从 Heap、索引和统计信息开始，给出查询计划、事务隔离、死锁、HOT、VACUUM、freeze、分区和容量治理的实验链路。

当系统需要复制、自动故障转移、连接路由、备份恢复或 PITR 时，应转向PostgreSQL 高可用、恢复与治理。其中拆解 WAL、replication slot、同步复制、fencing、timeline、Patroni / repmgr、PgBouncer / HAProxy、物理备份和恢复演练，不把“有从库”误判为“具备容灾”。

两条路径都建立在本页的部署基线上。开发模板中的版本、role、schema、迁移和验证如果没有先稳定下来，后续复制的只会是一套身份和配置都不确定的实例。

上线评审要留下什么

从开发实例迁往共享环境或生产时，应留下版本与参数差异、账号授权、TLS 与连接预算、migration 回滚、RPO/RTO 与恢复演练、监控责任人以及服务退出方案。上线异常先用证据判断属于网络、认证、授权、SQL、容量还是复制问题，再决定回滚应用、回滚 migration、切换入口或进入恢复流程，避免用“重启数据库”掩盖状态错误。

查看状态

docker compose ps postgres
docker compose logs postgres --tail 120
docker volume inspect your-project-postgres-data

进入 `psql`

docker compose exec postgres sh -lc 'PGPASSWORD="$POSTGRES_APP_PASSWORD" psql -h 127.0.0.1 -U "$POSTGRES_APP_USER" -d "$POSTGRES_DB"'

进入后先看连接目标：

\conninfo
SELECT current_database(), current_user, current_schema(), current_schemas(false);

查看对象和权限

\dn+
\dt app_dev.*
\du
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_schema = 'app_dev'
ORDER BY grantee, privilege_type;

导出开发数据

开发环境小样本导出：

docker compose exec postgres sh -lc '
  PGPASSWORD="$POSTGRES_APP_PASSWORD" pg_dump \
    -h 127.0.0.1 \
    -U "$POSTGRES_APP_USER" \
    -d "$POSTGRES_DB" \
    --schema="$POSTGRES_SCHEMA" \
    --format=custom \
    --no-owner \
    --no-privileges \
    --file=/tmp/app_dev.dump
'
docker compose cp postgres:/tmp/app_dev.dump ./db/postgres/dumps/app_dev.dump

不要把真实用户数据、手机号、邮箱、订单号、访问 token 或日志上下文导出到仓库。

导入测试数据

docker compose cp ./db/postgres/dumps/app_dev.dump postgres:/tmp/app_dev.dump
docker compose exec postgres sh -lc '
  PGPASSWORD="$POSTGRES_APP_PASSWORD" pg_restore \
    -h 127.0.0.1 \
    -U "$POSTGRES_APP_USER" \
    -d "$POSTGRES_DB" \
    --clean \
    --if-exists \
    /tmp/app_dev.dump
'

导入前先确认目标环境：

docker compose exec postgres sh -lc 'echo "$POSTGRES_DB $POSTGRES_SCHEMA $POSTGRES_APP_USER"'

停止但保留数据

docker compose stop postgres

删除容器但保留 volume

docker compose rm -f postgres

重置开发库

个人本机可以删除 volume 重建：

docker compose down
docker volume rm your-project-postgres-data
docker compose up -d postgres

共享环境不能这么做。共享环境要由 owner 用迁移脚本、备份恢复或明确变更窗口处理。

端口已经被占用

现象：

Bind for 0.0.0.0:5433 failed: port is already allocated

判断：

docker ps --format "table {{.Names}}\t{{.Ports}}"

Windows：

netstat -ano | findstr 5433

修复：

换 POSTGRES_HOST_PORT，例如 55433。停掉占用端口的本机 PostgreSQL 或旧容器。不要把 Compose 里的应用连接地址也改成宿主机端口。应用容器仍然连 postgres:5432。

再验证：

docker compose up -d postgres
docker compose ps postgres

改了密码但一直不生效

现象：

password authentication failed for user "app_dev"

你改了 .env 里的 POSTGRES_APP_PASSWORD，但连接仍然失败。

原因通常是旧 volume 还在。Docker Official Image 的初始化变量和 init 脚本只在空数据目录时执行。

判断：

docker volume inspect your-project-postgres-data
docker compose logs postgres --tail 80

个人本机修复：

docker compose down
docker volume rm your-project-postgres-data
docker compose up -d postgres

共享环境不能这么修。共享环境要由 owner 执行 ALTER ROLE，并记录谁改了密码、何时轮换、哪些服务需要同步。

初始化脚本没有执行

现象：

schema "app_dev" does not exist
relation "tool_verify" does not exist

判断：

docker compose logs postgres --tail 200
docker compose exec postgres sh -lc 'ls -l /docker-entrypoint-initdb.d'

常见原因：

旧 volume 已经初始化过，脚本不会再次执行。Windows 创建的 *.sh 是 CRLF 换行，容器内执行失败。脚本没有可读权限，或者挂载路径写错。

脚本中某条 SQL 失败，entrypoint 已经退出，但重启后旧数据目录让后续脚本不再继续。

修复：

个人本机删除 volume 重建。共享环境用显式迁移脚本补齐 schema / role / extension，不删数据目录。所有 init 脚本加 set -euo pipefail 和 psql -v ON_ERROR_STOP=1。

`permission denied for schema public`

现象：

ERROR: permission denied for schema public
ERROR: no schema has been selected to create in

原因：运行账号没有目标 schema 的 USAGE 权限，迁移账号没有 CREATE 权限，或者 search_path 没有解析到可用 schema。PostgreSQL 会静默忽略 search_path 中不存在或无权限的 schema。

判断：

SELECT current_user, current_schema(), current_schemas(false);
\dn+
SHOW search_path;

修复：

GRANT USAGE, CREATE ON SCHEMA app_dev TO app_dev_migration;
GRANT USAGE ON SCHEMA app_dev TO app_dev;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app_dev TO app_dev;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA app_dev TO app_dev;
ALTER ROLE app_dev SET search_path TO app_dev, public;

再验证：

docker compose exec postgres sh -lc 'PGPASSWORD="$POSTGRES_APP_PASSWORD" psql -h 127.0.0.1 -U "$POSTGRES_APP_USER" -d "$POSTGRES_DB" -c "SELECT current_schema(), current_schemas(false);"'

`CREATE EXTENSION` 权限不足

现象：

ERROR: permission denied to create extension
ERROR: must be superuser to create this extension

判断：

SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE name IN ('pgcrypto', 'uuid-ossp', 'postgis');

原因：

extension 没有安装到镜像或系统包里。extension 不是 trusted，普通应用账号不能创建。extension 目标 schema 的 CREATE 权限不合适。

修复：

开发 Compose 里由 bootstrap 脚本用 superuser 创建必要 extension。共享环境由 owner 统一创建，不让应用账号临时申请 superuser。如果是 PostGIS 这类额外包，先确认镜像和包来源，不要在运行中手工安装。

再验证：

SELECT extname, extnamespace::regnamespace
FROM pg_extension
WHERE extname = 'pgcrypto';

SELECT gen_random_uuid() IS NOT NULL AS pgcrypto_ready;

数据目录和 server 大版本不兼容

现象：

database files are incompatible with server
The data directory was initialized by PostgreSQL version 17

原因：旧 volume 是另一个 major version 初始化的。PostgreSQL major upgrade 需要 pg_upgrade、dump / restore 或逻辑复制；不能靠换镜像 tag 直接启动。

个人开发修复：

docker compose down
docker volume rm your-project-postgres-data
docker compose up -d postgres

共享环境修复：

停止直接换 tag。备份旧数据。按官方 major upgrade 路径设计迁移。

迁移前后跑最小验证和应用回归。

locale、encoding 或排序结果不一致

现象：

本机排序和共享环境排序不同。创建 database 时报 locale / encoding 不兼容。中文、大小写、重音字符排序结果和预期不同。

判断：

SHOW server_encoding;
SHOW lc_collate;
SHOW lc_ctype;
SHOW TimeZone;
SELECT datname, datcollate, datctype FROM pg_database WHERE datname = current_database();

结论：

database encoding 和 locale 是初始化时的重要边界，不是后面随便改一个变量就能统一。PostgreSQL 官方文档明确 encoding 必须与 locale 兼容；SQL_ASCII 有风险，不应作为新项目默认。团队如果依赖特定排序规则，必须在开发模板、共享环境和 CI 验证里固定口径。

修复：

个人本机先重建空 volume，确认 .env 和 POSTGRES_INITDB_ARGS 生效。共享环境不要在原库上临时“修 locale”。应新建符合基线的 database / cluster，迁移数据后做排序、唯一索引和业务查询回归。CI 加一个最小排序样本，避免 Windows、macOS、Linux 或不同镜像版本产生隐性差异。

再验证：

SHOW server_encoding;
SHOW lc_collate;
SHOW lc_ctype;
SELECT value FROM (VALUES ('a'), ('A'), ('中'), ('重')) AS t(value) ORDER BY value;

JDBC 连接失败

现象：

Connection refused
FATAL: password authentication failed
FATAL: database "app_dev" does not exist
no schema has been selected to create in

判断路径：

Connection refused：先看 host / port。宿主机用 localhost:5433，Compose 内应用用 postgres:5432。password authentication failed：看旧 volume、密码是否轮换、应用是否用错账号。database does not exist：看 POSTGRES_DB 是否只在旧 volume 初始化前生效。

schema 问题：看 currentSchema、role search_path 和 schema 权限。

修复：

宿主机连接用 jdbc:postgresql://127.0.0.1:5433/app_dev?currentSchema=app_dev；容器内应用连接用 jdbc:postgresql://postgres:5432/app_dev?currentSchema=app_dev。密码变更不生效时先判断是否旧 volume，而不是反复改 Spring Boot profile。schema 问题先用 psql 查 current_schema()，确认数据库侧权限正确后再改 JDBC URL。

不要在排障时把 postgres superuser 填进应用配置。

再验证：

docker compose exec postgres sh -lc 'PGPASSWORD="$POSTGRES_APP_PASSWORD" psql -h 127.0.0.1 -U "$POSTGRES_APP_USER" -d "$POSTGRES_DB" -c "\conninfo"'

连接数打满

现象：

FATAL: sorry, too many clients already

判断：

SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state
ORDER BY count(*) DESC;

原因：

应用副本数乘以连接池最大值超过数据库承载。慢 SQL、锁等待或长事务让连接无法归还。短连接服务没有经过连接池或 PgBouncer。

修复：

先算应用副本数乘以连接池最大值，不要只改 max_connections。短连接和高并发服务优先评估 PgBouncer。查慢 SQL 和长事务，连接满通常是结果，不一定是原因。

再验证：

SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state
ORDER BY count(*) DESC;

SELECT pid, state, now() - xact_start AS xact_age, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_age DESC
LIMIT 10;

问题已经进入内核或高可用链路

如果现象是 pg_wal 持续增长、replication slot 长期不活跃、standby 延迟、表膨胀、长事务阻塞回收或 autovacuum 跟不上，不要在开发 Compose 里直接删除 slot、执行 VACUUM FULL 或拍脑袋修改全局参数。

先保留现场证据：

SELECT slot_name, slot_type, active, restart_lsn, confirmed_flush_lsn
FROM pg_replication_slots;

SELECT pid, state, now() - xact_start AS xact_age, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_age DESC
LIMIT 10;

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

随后按问题类型继续处理：

查询、锁、长事务、dead tuple、bloat 与 autovacuum：进入PostgreSQL 查询、事务与 VACUUM。WAL、slot、standby、故障转移、备份与 PITR：进入PostgreSQL 高可用、恢复与治理。

这类问题已经超出基础连接故障。任何删除 slot、重写表、提升 standby 或恢复备份的动作，都必须先确认影响对象、停止条件、回滚路径和责任人。

PostgreSQL 开发环境的权限治理要从第一天就做。PostgreSQL 的权限模型比 MySQL 更细，省事地用 superuser 会把问题推迟到共享环境和生产环境。

账号分层

账号	用途	权限边界
`postgres`	初始化、extension、紧急管理	superuser，仅 owner 使用
`app_dev_migration`	本地和共享环境迁移	schema DDL，受审查
`app_dev`	应用运行	指定 database 和 schema 内 DML
`app_readonly`	排障查询	SELECT，只读

创建只读账号示例：

CREATE ROLE app_readonly LOGIN PASSWORD 'YOUR_READONLY_PASSWORD';
GRANT CONNECT ON DATABASE app_dev TO app_readonly;
GRANT USAGE ON SCHEMA app_dev TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA app_dev TO app_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA app_dev GRANT SELECT ON TABLES TO app_readonly;

不要把 YOUR_READONLY_PASSWORD 写进正式脚本。真实密码应来自 .env、团队密码管理器或受控 secret。

凭证落地规则

.env 不提交。.env.example 只写变量名和占位值。本机临时使用 PGPASSWORD 后及时清理环境变量。

.pgpass 如果使用，必须限制文件权限，并且不要放生产密码。Docker / Compose 需要 secret 时优先评估 POSTGRES_PASSWORD_FILE 这类 _FILE 入口，不把真实密码写入 YAML。pg_service.conf 适合隐藏连接细节，但也要管理文件权限和环境命名。

GUI 客户端连接生产库时必须使用只读账号，并给连接命名加环境前缀。离职、项目交接、共享实例 owner 变更时，轮换共享账号密码。

GUI 客户端保存密码治理

pgAdmin、DataGrip、DBeaver 这类客户端能显著提升排障效率，也最容易把“临时方便”变成长期风险。

现象：

个人电脑保存了共享库或生产库密码。GUI 连接名称只有 postgres、prod，看不出环境和权限。排障时用 superuser 打开 GUI，随后误导出、误删或误改对象。

判断：

连接名称必须包含环境、权限和用途，例如 dev-readonly-app-dev、prod-readonly-order-audit。生产连接默认只读；需要 DDL 或 DML 时走变更流程，不在 GUI 里临时提权。客户端保存密码必须符合团队密码管理和设备加密要求；不符合时只能使用临时会话或密码管理器填充。

团队禁令：

禁止在 GUI 客户端保存 superuser 密码。禁止把生产连接配置导出后发到群里。禁止在共享文档里粘贴完整 JDBC URL、堡垒机地址、真实用户名和真实密码。

再验证：

SELECT current_user, current_database(), current_schema();
SHOW transaction_read_only;

如果 current_user 不是只读账号，或者连接名称无法让旁观者一眼判断环境，就不要继续执行查询。

代理和内网

PostgreSQL 本身通常不需要 HTTP 代理，但开发链路会受这些因素影响：

Docker 拉取 postgres 镜像需要镜像源、企业代理或内部镜像仓库。Maven / Gradle 下载 pgJDBC 需要私服、代理和根证书。公司内网 PostgreSQL 可能只允许堡垒机、VPN、SSH tunnel 或特定网段访问。

SSL / TLS 连接要区分 sslmode=disable、require、verify-ca、verify-full。共享环境不要不加判断地复制本地 sslmode=disable。不要把个人临时代理地址、内网 host、跳板机名称写进项目模板。

一篇 PostgreSQL 文档写完，不代表团队已经落地。落地要把模板、责任和回滚固定下来。

项目模板必须保留

compose.yaml
.env.example
db/postgres/init/
db/postgres/verify/
db/postgres/dumps/
docs/dependency-setup.md
scripts/dev-up.sh
scripts/dev-reset.sh

模板里要写清：

镜像 tag、digest 与最近一次升级验证记录。宿主机端口和容器端口。数据 volume 名称。

初始化脚本执行条件。应用 role、迁移 role、只读 role 的权限边界。验证 SQL 和预期结果。

重置命令和共享环境禁止事项。

命名规则

对象	建议命名
volume	`your-project-postgres-data`
database	`app_dev`、`app_test`
schema	`app_dev`、`billing_dev`
应用 role	`app_dev`
只读 role	`app_readonly`
迁移 role	`app_migration`

不要用 postgres、test、demo 作为长期共享环境应用账号或 schema。它们太容易被误删、误连和误解。

共享实例规则

必须有 owner。必须有 schema 命名规则。应用账号不能是 superuser。

extension 由 owner 统一创建。只读账号和迁移账号分开。每个项目有清理窗口和测试数据归属。

导出数据默认脱敏。生产连接和共享开发连接在客户端里必须有明显命名差异。

PostgreSQL 18 的 PGDATA 变化会让旧模板失真

现象：同样的 Compose 文件，在 PostgreSQL 17 可用，换到 PostgreSQL 18 后数据没有按预期持久化，或者升级时 volume 目录结构和预期不一致。

判断：

docker compose exec postgres sh -lc 'echo "$PGDATA"'
docker volume inspect your-project-postgres-data

结论：PostgreSQL 18 及以后 Docker Official Image 的 PGDATA 默认路径和 volume 目标有重要变化。团队模板必须记录适用 major、实际 PGDATA 和升级验证结果，不要把老路径当永久答案。

取舍：新项目可以跟随当前官方镜像路径；老项目升级前要先读官方 Docker Image 说明，确认 volume 目录迁移路径。

role、database、schema 混在一起会拖垮共享环境

现象：应用能连上 app_dev database，但表建在 public schema；另一个项目也在 public 建表；迁移脚本在本地成功，到共享环境失败。

判断：

SELECT current_database(), current_user, current_schema(), current_schemas(false);
\dn+
\dt *.*

结论：PostgreSQL 的 role、database、schema 是不同对象。共享环境至少要做到一个项目一个 schema，应用账号只拿到自己 schema 的权限；排障时也要分别确认登录身份、连接数据库和对象命名空间。

`search_path` 会静默掩盖权限问题

现象：SHOW search_path 看起来有 app_dev，但 current_schema() 还是 public 或创建对象时报错。

原因：官方文档说明，search_path 中不存在或没有 USAGE 权限的 schema 会被忽略。

判断：

SHOW search_path;
SELECT current_schemas(false);
SELECT has_schema_privilege(current_user, 'app_dev', 'USAGE');

修复：

GRANT USAGE, CREATE ON SCHEMA app_dev TO app_dev_migration;
GRANT USAGE ON SCHEMA app_dev TO app_dev;
ALTER ROLE app_dev SET search_path TO app_dev, public;

取舍：团队可以在 JDBC URL 里写 currentSchema=app_dev，但这不是权限治理的替代品。应用运行账号不应因为一次排障就长期保留 CREATE 权限。

extension 不是普通表

现象：本地 superuser 执行 CREATE EXTENSION 成功，共享环境应用账号执行失败。

判断：

SELECT name, default_version, installed_version
FROM pg_available_extensions
ORDER BY name;

结论：extension 通常涉及额外对象、权限和安全信任。trusted extension 可以降低门槛，但团队仍应由 owner 决定哪些 extension 进入共享环境。PostGIS、FDW、C 语言扩展和需要系统包的 extension 不能被应用临时安装。

取舍：开发模板可以由 bootstrap 脚本安装少量经过确认的 extension；共享环境要把 extension 清单纳入评审。安装 extension 的 schema 不应处在不可信用户可写的 search_path 前部。

MD5、trust 和明文密码会留下长期风险

现象：开发环境为了省事设置 POSTGRES_HOST_AUTH_METHOD=trust；共享实例复制了这份配置；后来任何人都能连库。

判断：

SHOW password_encryption;

检查 pg_hba.conf 或容器初始化参数。

结论：PostgreSQL 官方已经把 MD5-encrypted passwords 标记为 deprecated，trust 更不应进入共享环境。开发模板默认使用 SCRAM 口径，旧项目迁移要先确认客户端库支持，再轮换密码。

locale、encoding 和 collation 不是后期小修

现象：中文排序、大小写比较、唯一索引行为或导入数据在不同机器上表现不一致。

判断：

SHOW server_encoding;
SHOW lc_collate;
SHOW lc_ctype;
SELECT datname, datcollate, datctype FROM pg_database WHERE datname = current_database();

结论：database 的 encoding 和 locale 与初始化过程绑定很深。团队不要在本机随手创建 database，再把导出的数据拿到共享环境当标准样本。

清理脚本必须有环境保护

危险脚本：

DROP SCHEMA public CASCADE;
DROP DATABASE app_dev;

开发本机也要加保护，至少先输出目标：

docker compose exec postgres sh -lc '
  echo "target database=$POSTGRES_DB schema=$POSTGRES_SCHEMA user=$POSTGRES_APP_USER"
'

共享环境清理建议：

先 dry run，列出要删的 schema、表、测试数据条件。只允许 owner 执行破坏性命令。清理脚本必须检查 host、database、current_user、current_schema。

生产清理必须走正式变更流程，不能复用开发脚本。

云托管 PostgreSQL 省运维但不省治理

云厂商会帮你处理一部分高可用、备份、监控和升级入口，但架构责任不会消失。托管 PostgreSQL 仍要检查：

哪些 extension 可用，哪些需要工单或特定版本。是否能拿到足够的慢查询、锁等待、WAL、连接数和 autovacuum 指标。PITR 粒度、备份保留、跨账号恢复、跨区域恢复和下载权限。

参数可修改范围、重启窗口和 major upgrade 路径。成本：存储、IO、备份、只读实例、跨区流量和代理费用。

结论：云 RDS 可以降低底层运维成本，但不能替代权限治理、恢复演练、SQL 变更审查和容量规划。

部署方式：

镜像 tag 已固定，不使用 latest。明确是单机、主备、HA 控制面、K8S Operator 还是云托管。docker compose ps postgres 显示健康。

volume 目标和 PostgreSQL major version 匹配。Compose 开发端口默认只绑定 127.0.0.1。db/postgres/verify/verify.sql 能完成 extension、写入、查询和删除。

encoding、locale、时区符合团队约定。端口没有和本机或其他项目冲突。

项目接入：

宿主机连接和 Compose 网络连接的 host 已区分。JDBC URL 没有真实密码。currentSchema 和 role search_path 已对齐。

应用账号不是 postgres superuser。迁移工具账号和业务运行账号已分开，至少共享环境分开。.env 未提交，.env.example 完整。

架构选型：

primary / standby / 逻辑复制 / 云托管的选择理由已写进 ADR。HA 方案包含故障发现、提升、路由、旧主隔离和回滚。读写分离有复制延迟兜底，核心读不盲目走 standby。

PgBouncer / 连接池总量已按服务副本数计算。extension、分区、逻辑复制和升级路径已评估。

性能与稳定性：

已启用或规划 pg_stat_statements、慢查询日志和关键指标。连接数、锁等待、WAL、replication slot、autovacuum 有监控。大表 bloat、索引膨胀、分区维护有巡检。

生产 DDL 和大批量更新有锁影响评估。

恢复能力：

有逻辑备份和全局对象备份策略。有 base backup、WAL 归档和 PITR 演练。恢复到临时实例后有行数、抽样查询和业务冒烟校验。

RPO、RTO、备份加密、异地保留和下载权限已记录。

团队治理：

owner 已明确。共享实例有应用账号、迁移账号和只读账号。extension 清单由 owner 管理。

GUI 客户端生产连接默认只读。重置脚本有环境保护。版本选择理由、升级 owner 和最近一次恢复演练结果已记录。

深水区风险已在团队文档或模板里转成检查项。