postgres

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Postgres

Goal

目标

Use this skill to connect to Postgres, run SQL, inspect schemas, review query performance, design tables and indexes, work with common PostGIS or pgvector patterns, and manage migration release flow through the shipped

scripts/postgres

launcher in the skill package.

使用此技能连接Postgres、执行SQL、检查数据库架构、分析查询性能、设计表和索引、使用常见的PostGIS或pgvector模式，并通过技能包中提供的

scripts/postgres

启动器管理迁移发布流程。

Runtime surface

运行时接口

The only supported runtime entrypoint is the shipped
```
scripts/postgres
```
launcher inside this skill package.
If your current working directory is the skill root, run it as
```
./scripts/postgres
```
.
If you are invoking the skill from another repo, resolve the skill package path first and run
```
<postgres-skill-root>/scripts/postgres
```
.

<postgres-skill-root>/scripts/postgres --version

is the runtime version check.

```
scripts/postgres
```
dispatches to platform-specific Rust binaries under
```
scripts/bin/
```
, currently named
```
postgres-<os>-<arch>
```
.
Supported shipped binary names are
```
postgres-darwin-arm64
```
,
```
postgres-darwin-x86_64
```
,
```
postgres-linux-arm64
```
, and
```
postgres-linux-x86_64
```
; a platform is usable only when its executable is present.
Do not use or reintroduce per-task helper scripts from the pre-Rust runtime surface.
The implementation lives in
```
projects/postgres/
```
and is maintenance-only. Normal usage stays on the
```
scripts/postgres
```
surface.

Canonical persisted config lives at

<project-root>/.skills/postgres/config.toml

This runtime skill does not provide dump, restore, export, or schema-diff workflows. Keep those operator tasks outside this skill.
If a target repo has
```
.skills/postgres/config.toml
```
or legacy
```
.skills/postgres/postgres.toml
```
, use the shipped
```
scripts/postgres
```
launcher for normal app-database work instead of raw
```
psql
```
.
Bare
```
psql
```
is allowed only as an explicit exception for container-local runbooks such as
```
docker compose exec pg psql ...
```
, repo-documented smoke checks, unsupported operator workflows outside this skill's runtime surface, or emergency fallback when the shipped launcher cannot run.

唯一支持的运行时入口是此技能包中提供的
```
scripts/postgres
```
启动器。
如果当前工作目录是技能根目录，可通过
```
./scripts/postgres
```
运行它。
如果从其他仓库调用此技能，请先解析技能包路径，然后运行
```
<postgres-skill-root>/scripts/postgres
```
。

<postgres-skill-root>/scripts/postgres --version

用于检查运行时版本。

```
scripts/postgres
```
会调度到
```
scripts/bin/
```
下的特定平台Rust二进制文件，当前命名为
```
postgres-<os>-<arch>
```
。
支持的二进制文件名包括
```
postgres-darwin-arm64
```
、
```
postgres-darwin-x86_64
```
、
```
postgres-linux-arm64
```
和
```
postgres-linux-x86_64
```
；只有当对应平台的可执行文件存在时，该平台才可使用。
请勿使用或重新引入Rust运行时之前的每个任务辅助脚本。
实现代码位于
```
projects/postgres/
```
，仅用于维护。常规使用请基于
```
scripts/postgres
```
接口。

标准持久化配置位于

<project-root>/.skills/postgres/config.toml

。

此运行时技能不提供备份、恢复、导出或架构对比工作流。请将这些运维任务放在此技能之外执行。
如果目标仓库存在
```
.skills/postgres/config.toml
```
或旧版
```
.skills/postgres/postgres.toml
```
，请使用提供的
```
scripts/postgres
```
启动器进行常规应用-数据库操作，而非直接使用
```
psql
```
。
仅在以下明确例外场景中允许使用原生
```
psql
```
：容器本地运行手册（如
```
docker compose exec pg psql ...
```
）、仓库文档中记录的冒烟测试、此技能运行时接口不支持的运维工作流，或当提供的启动器无法运行时的紧急回退方案。

Fast path

快速路径

Common installed locations for the shipped runtime:
- ```
~/.agents/skills/postgres/scripts/postgres
```
  (typical when this repo’s
```
skills/
```
  are linked into
```
~/.agents/skills
```
  )
- ```
<dotagents>/skills/postgres/scripts/postgres
```
  (when running from this workspace checkout)

Minimal happy path (inside the repo that owns the DB config):

```
export DB_PROJECT_ROOT="$PWD"
```

export POSTGRES_CLI="$HOME/.agents/skills/postgres/scripts/postgres"

```
"$POSTGRES_CLI" --json doctor
```

DB_PROFILE=local "$POSTGRES_CLI" profile test

DB_PROFILE=local "$POSTGRES_CLI" query run -c "select now();"

Resolve the shipped CLI once and reuse it in commands below:
- ```
POSTGRES_CLI=/path/to/postgres-skill/scripts/postgres
```

Doctor / setup status:

DB_PROJECT_ROOT=/path/to/repo "$POSTGRES_CLI" --json doctor

Bootstrap and save a profile:

DB_PROJECT_ROOT=/path/to/repo "$POSTGRES_CLI" profile bootstrap --save

Resolve the active connection:

DB_PROJECT_ROOT=/path/to/repo DB_PROFILE=local "$POSTGRES_CLI" --json profile resolve

Run ad-hoc SQL:

DB_PROJECT_ROOT=/path/to/repo DB_PROFILE=local "$POSTGRES_CLI" query run -c "select now();"

Run a SQL file:

DB_PROJECT_ROOT=/path/to/repo DB_PROFILE=local "$POSTGRES_CLI" query run -f ./query.sql

Safe heredoc for multi-statement SQL /

DO $$

DB_PROJECT_ROOT=/path/to/repo DB_PROFILE=local "$POSTGRES_CLI" query run <<'SQL'

```
select now();
```
```
SQL
```

Connection test:

DB_PROJECT_ROOT=/path/to/repo DB_PROFILE=local "$POSTGRES_CLI" profile test

Schema introspection:

DB_PROJECT_ROOT=/path/to/repo DB_PROFILE=local "$POSTGRES_CLI" schema inspect

Focused catalog and diagnostic commands:

DB_PROJECT_ROOT=/path/to/repo DB_PROFILE=local "$POSTGRES_CLI" profile overview

DB_PROJECT_ROOT=/path/to/repo DB_PROFILE=local "$POSTGRES_CLI" schema list tables

DB_PROJECT_ROOT=/path/to/repo DB_PROFILE=local "$POSTGRES_CLI" query plan -c "select * from public.users limit 10;"

Object search:

DB_PROJECT_ROOT=/path/to/repo DB_PROFILE=local "$POSTGRES_CLI" query find users --types table,column

Release a pending migration file:

DB_PROJECT_ROOT=/path/to/repo DB_PROFILE=local "$POSTGRES_CLI" migration release --summary "Add agent-context prompt sections"

提供的运行时常见安装位置：
- ```
~/.agents/skills/postgres/scripts/postgres
```
  （当此仓库的
```
skills/
```
  链接到
```
~/.agents/skills
```
  时的典型路径）
- ```
<dotagents>/skills/postgres/scripts/postgres
```
  （当从此工作区检出目录运行时）

最简操作路径（在拥有数据库配置的仓库内）：

```
export DB_PROJECT_ROOT="$PWD"
```

export POSTGRES_CLI="$HOME/.agents/skills/postgres/scripts/postgres"

```
"$POSTGRES_CLI" --json doctor
```

DB_PROFILE=local "$POSTGRES_CLI" profile test

DB_PROFILE=local "$POSTGRES_CLI" query run -c "select now();"

解析提供的CLI并在后续命令中复用：

POSTGRES_CLI=/path/to/postgres-skill/scripts/postgres

诊断/设置状态：

DB_PROJECT_ROOT=/path/to/repo "$POSTGRES_CLI" --json doctor

引导并保存配置文件：

DB_PROJECT_ROOT=/path/to/repo "$POSTGRES_CLI" profile bootstrap --save

解析当前连接：

DB_PROJECT_ROOT=/path/to/repo DB_PROFILE=local "$POSTGRES_CLI" --json profile resolve

执行临时SQL：

DB_PROJECT_ROOT=/path/to/repo DB_PROFILE=local "$POSTGRES_CLI" query run -c "select now();"

执行SQL文件：

DB_PROJECT_ROOT=/path/to/repo DB_PROFILE=local "$POSTGRES_CLI" query run -f ./query.sql

用于多语句SQL/

DO $$

的安全 heredoc：

DB_PROJECT_ROOT=/path/to/repo DB_PROFILE=local "$POSTGRES_CLI" query run <<'SQL'

```
select now();
```
```
SQL
```

连接测试：

DB_PROJECT_ROOT=/path/to/repo DB_PROFILE=local "$POSTGRES_CLI" profile test

架构自省：

DB_PROJECT_ROOT=/path/to/repo DB_PROFILE=local "$POSTGRES_CLI" schema inspect

聚焦型目录与诊断命令：

DB_PROJECT_ROOT=/path/to/repo DB_PROFILE=local "$POSTGRES_CLI" profile overview

DB_PROJECT_ROOT=/path/to/repo DB_PROFILE=local "$POSTGRES_CLI" schema list tables

DB_PROJECT_ROOT=/path/to/repo DB_PROFILE=local "$POSTGRES_CLI" query plan -c "select * from public.users limit 10;"

对象搜索：

DB_PROJECT_ROOT=/path/to/repo DB_PROFILE=local "$POSTGRES_CLI" query find users --types table,column

发布待处理迁移文件：

DB_PROJECT_ROOT=/path/to/repo DB_PROFILE=local "$POSTGRES_CLI" migration release --summary "Add agent-context prompt sections"

Session-Proven Replacement Patterns

经验证的替代模式

Search for a feature's tables and columns before writing SQL:

DB_PROJECT_ROOT=/path/to/repo DB_PROFILE=local "$POSTGRES_CLI" query find welcome --types table,column

Apply a pending local migration through the profile-backed connection, then verify the changed column through SQL catalog queries:

DB_PROJECT_ROOT=/path/to/repo DB_PROFILE=local "$POSTGRES_CLI" query run -f db/migrations/prerelease.sql

DB_PROJECT_ROOT=/path/to/repo DB_PROFILE=local "$POSTGRES_CLI" query run -c "select column_name, data_type from information_schema.columns where table_schema = 'public' and table_name = 'example' order by ordinal_position;"

Inspect or edit JSONB payloads with a heredoc and a

RETURNING

clause so the command output proves the exact stored value:

DB_PROJECT_ROOT=/path/to/repo DB_PROFILE=local "$POSTGRES_CLI" query run <<'SQL'

update public.example set payload = jsonb_set(payload, '{enabled}', 'true'::jsonb, true) where id = 1 returning id, jsonb_pretty(payload);

```
SQL
```

After a migration has been applied and checked, move it through the skill release flow instead of manually moving files:
- ```
DB_PROJECT_ROOT=/path/to/repo DB_PROFILE=local "$POSTGRES_CLI" migration release --summary "Add example payload field"
```

在编写SQL前搜索功能对应的表和列：

DB_PROJECT_ROOT=/path/to/repo DB_PROFILE=local "$POSTGRES_CLI" query find welcome --types table,column

通过配置文件支持的连接应用本地待处理迁移，然后通过SQL目录查询验证变更的列：

DB_PROJECT_ROOT=/path/to/repo DB_PROFILE=local "$POSTGRES_CLI" query run -f db/migrations/prerelease.sql

DB_PROJECT_ROOT=/path/to/repo DB_PROFILE=local "$POSTGRES_CLI" query run -c "select column_name, data_type from information_schema.columns where table_schema = 'public' and table_name = 'example' order by ordinal_position;"

使用heredoc和

RETURNING

子句检查或编辑JSONB payload，确保命令输出能证明存储的准确值：

DB_PROJECT_ROOT=/path/to/repo DB_PROFILE=local "$POSTGRES_CLI" query run <<'SQL'

update public.example set payload = jsonb_set(payload, '{enabled}', 'true'::jsonb, true) where id = 1 returning id, jsonb_pretty(payload);

```
SQL
```

迁移应用并检查完成后，通过技能发布流程移动文件，而非手动移动：

DB_PROJECT_ROOT=/path/to/repo DB_PROFILE=local "$POSTGRES_CLI" migration release --summary "Add example payload field"

Workflow

工作流

Confirm connection source:
- If
```
DB_URL
```
  is provided, use it for a one-off connection unless the user explicitly asks to persist it.
- Prefer
```
DB_*
```
  environment variables. Compatibility inputs such as
```
DATABASE_URL
```
  ,
```
POSTGRES_URL
```
  ,
```
POSTGRESQL_URL
```
  , and libpq vars (
```
PGHOST
```
  ,
```
PGPORT
```
  ,
```
PGDATABASE
```
  ,
```
PGUSER
```
  ,
```
PGPASSWORD
```
  ,
```
PGSSLMODE
```
  ) are also accepted.
- ```
PROJECT_ROOT
```
  remains unsupported; use
```
DB_PROJECT_ROOT
```
  .
- If
```
<project-root>/.skills/postgres/config.toml
```
  exists, use it.
- Else if legacy
```
<project-root>/.skills/postgres/postgres.toml
```
  exists, runtime migrates it one-way into canonical
```
config.toml
```
  and continues on the canonical path.
- During that migration, make sure the consuming repo ignores
```
.skills/postgres/config.toml
```
  too; do not leave the canonical file unignored when the legacy
```
postgres.toml
```
  had ignore coverage.
- If the user explicitly asks to create or refresh a saved profile, use the shipped
```
scripts/postgres
```
  launcher from the skill package, for example
```
<postgres-skill-root>/scripts/postgres profile bootstrap
```
  .
Choose action:
- Query or inspect data
- Inspect schema, indexes, roles, or activity
- Review query shape or schema design
- Draft or release migrations
- Search official PostgreSQL docs only when explicitly requested
Execute and report:
- Return the answer first, then only the supporting context needed to trust it.
- Keep backup, restore, export, and schema-diff requests out of this skill's runtime surface.
Persist only if asked:
- Update
```
config.toml
```
  only with explicit user approval, except canonical config migration plus explicit profile bootstrap or
```
set-ssl
```
  flows.
- Treat
```
<project-root>/.skills/postgres/config.toml
```
  as local persisted operator config; consuming repos should gitignore it just as they previously gitignored legacy
```
postgres.toml
```
  .

确认连接源：
- 如果提供了
```
DB_URL
```
  ，将其用于一次性连接，除非用户明确要求持久化该连接。
- 优先使用
```
DB_*
```
  环境变量。同时兼容
```
DATABASE_URL
```
  、
```
POSTGRES_URL
```
  、
```
POSTGRESQL_URL
```
  以及libpq变量（
```
PGHOST
```
  、
```
PGPORT
```
  、
```
PGDATABASE
```
  、
```
PGUSER
```
  、
```
PGPASSWORD
```
  、
```
PGSSLMODE
```
  ）。
- 不支持
```
PROJECT_ROOT
```
  ；请使用
```
DB_PROJECT_ROOT
```
  。
- 如果
```
<project-root>/.skills/postgres/config.toml
```
  存在，使用该配置。
- 否则，如果存在旧版
```
<project-root>/.skills/postgres/postgres.toml
```
  ，运行时会将其单向迁移到标准
```
config.toml
```
  ，并继续使用标准路径。
- 在迁移过程中，确保使用该配置的仓库也忽略
```
.skills/postgres/config.toml
```
  ；如果旧版
```
postgres.toml
```
  已被忽略，请勿让标准文件处于未被忽略的状态。
- 如果用户明确要求创建或刷新已保存的配置文件，请使用技能包中提供的
```
scripts/postgres
```
  启动器，例如
```
<postgres-skill-root>/scripts/postgres profile bootstrap
```
  。
选择操作：
- 查询或检查数据
- 检查架构、索引、角色或活动
- 分析查询形态或架构设计
- 草拟或发布迁移
- 仅在明确请求时搜索PostgreSQL官方文档
执行并报告：
- 先返回结果，再仅提供必要的支持上下文以确保结果可信。
- 将备份、恢复、导出和架构对比请求排除在此技能的运行时接口之外。
仅在请求时持久化：
- 仅在获得用户明确批准后更新
```
config.toml
```
  ，标准配置迁移以及明确的配置文件引导或
```
set-ssl
```
  流程除外。
- 将
```
<project-root>/.skills/postgres/config.toml
```
  视为本地持久化的运维配置；使用该配置的仓库应像之前忽略旧版
```
postgres.toml
```
  一样，通过git忽略该文件。

Command map

命令映射

```
doctor
```
- Validate config resolution and report runtime readiness without mutating config.
```
profile resolve
```
- Show the active URL, profile, and source.
```
profile bootstrap [--save]
```
- Interactively create or print a profile.
```
profile test
```
- Quick connection check.
```
profile info
```
- Print connection details and key server settings.
```
profile overview
```
- Summarize database identity, object counts, activity, and key settings.
```
profile settings autovacuum|memory
```
- Inspect focused PostgreSQL runtime settings.
```
profile version
```
- Show server version.
```
profile migrate-toml
```
- Migrate legacy
```
postgres.toml
```
  into canonical
```
config.toml
```
  using schema
```
2.0.0
```
  , and ensure ignore coverage follows the canonical file.

profile set-ssl <profile> <true|false>

Persist
```
sslmode
```
for a saved profile.

```
query run
```
- Execute SQL from
```
-c
```
  ,
```
-f
```
  , or stdin, preserving per-statement results.
```
query explain
```
- Run
```
EXPLAIN
```
  , defaulting to
```
ANALYZE
```
  .
```
query plan
```
- Return a non-executing JSON query plan by default; use
```
--analyze
```
  to run
```
EXPLAIN ANALYZE
```
  .
```
query find <pattern> [--types ...]
```
- Search schemas, tables, columns, views, and functions by name.

activity overview|active-queries|locks|slow|long-running|cancel|terminate|cancel-pid|terminate-pid|pg-stat-top|replication-slots

Runtime diagnostics and query-control operations.

schema inspect|list|extensions|table-sizes|index-health|invalid-indexes|top-bloated-tables|missing-fk-indexes|vacuum-status|roles

Schema and catalog inspection.

schema list

supports

tables

views

schemas

triggers

indexes

, and

sequences

```
schema extensions
```
supports
```
--installed
```
and
```
--available
```
; installed extensions are the default when neither flag is provided.

```
migration release
```
- Move a pending migration into
```
released/
```
  and update
```
CHANGELOG.md
```
  .
```
docs search
```
- Search official PostgreSQL current docs.

```
doctor
```
- 验证配置解析情况并报告运行时就绪状态，不修改配置。
```
profile resolve
```
- 显示当前URL、配置文件和来源。
```
profile bootstrap [--save]
```
- 交互式创建或打印配置文件。
```
profile test
```
- 快速连接检查。
```
profile info
```
- 打印连接详情和关键服务器设置。
```
profile overview
```
- 汇总数据库标识、对象数量、活动和关键设置。
```
profile settings autovacuum|memory
```
- 检查特定的PostgreSQL运行时设置。
```
profile version
```
- 显示服务器版本。
```
profile migrate-toml
```
- 使用
```
2.0.0
```
  版本的架构将旧版
```
postgres.toml
```
  迁移到标准
```
config.toml
```
  ，并确保忽略规则适用于标准文件。

profile set-ssl <profile> <true|false>

为已保存的配置文件持久化
```
sslmode
```
设置。

```
query run
```
- 执行来自
```
-c
```
  、
```
-f
```
  或标准输入的SQL，保留每条语句的结果。
```
query explain
```
- 运行
```
EXPLAIN
```
  ，默认使用
```
ANALYZE
```
  。
```
query plan
```
- 默认返回非执行的JSON查询计划；使用
```
--analyze
```
  运行
```
EXPLAIN ANALYZE
```
  。
```
query find <pattern> [--types ...]
```
- 按名称搜索架构、表、列、视图和函数。

activity overview|active-queries|locks|slow|long-running|cancel|terminate|cancel-pid|terminate-pid|pg-stat-top|replication-slots

运行时诊断和查询控制操作。

schema inspect|list|extensions|table-sizes|index-health|invalid-indexes|top-bloated-tables|missing-fk-indexes|vacuum-status|roles

schema list

支持

tables

、

views

、

schemas

、

triggers

、

indexes

和

sequences

。

```
schema extensions
```
支持
```
--installed
```
和
```
--available
```
；当未指定任何标志时，默认显示已安装的扩展。

```
migration release
```
- 将待处理迁移移动到
```
released/
```
  目录并更新
```
CHANGELOG.md
```
  。
```
docs search
```
- 搜索PostgreSQL当前官方文档。

Config shape

配置结构

Canonical persisted config uses owner-level

config.toml

toml

schema_version = "2.0.0"

[defaults]
profile = "local"

[tools.postgres]
sslmode = false
migrations_path = "db/migrations"

[tools.postgres.profiles.local]
description = "Local development DB"
host = "127.0.0.1"
port = 5432
database = "app"
user = "postgres"
password = "postgres"
sslmode = false
migrations_path = "db/migrations"

Rules:

```
schema_version
```
is top-level and required in canonical saved configs.
Do not add or rely on
```
[meta]
```
.
Canonical
```
config.toml
```
is local persisted operator config, not normal repo content; consuming repos should gitignore
```
.skills/postgres/config.toml
```
.
When migrating from legacy
```
postgres.toml
```
, update ignore rules in the same rollout so the canonical file stays untracked too.
```
[defaults]
```
stores the default saved profile.
```
[tools.postgres]
```
stores shared Postgres defaults.
```
[tools.postgres.profiles.<name>]
```
stores per-profile overrides.

标准持久化配置使用所有者级别的

config.toml

：

toml

schema_version = "2.0.0"

[defaults]
profile = "local"

[tools.postgres]
sslmode = false
migrations_path = "db/migrations"

[tools.postgres.profiles.local]
description = "Local development DB"
host = "127.0.0.1"
port = 5432
database = "app"
user = "postgres"
password = "postgres"
sslmode = false
migrations_path = "db/migrations"

规则：

```
schema_version
```
是顶级字段，在标准保存配置中为必填项。
请勿添加或依赖
```
[meta]
```
字段。
标准
```
config.toml
```
是本地持久化的运维配置，不属于常规仓库内容；使用该配置的仓库应通过git忽略
```
.skills/postgres/config.toml
```
。
从旧版
```
postgres.toml
```
迁移时，同步更新忽略规则，确保标准文件也处于未追踪状态。
```
[defaults]
```
存储默认的已保存配置文件。
```
[tools.postgres]
```
存储Postgres的共享默认设置。
```
[tools.postgres.profiles.<name>]
```
存储每个配置文件的覆盖设置。

Schema and feature design

架构与功能设计

For schema or table design, start with:

references/postgres_best_practices/schema-design.md

references/postgres_best_practices/advanced-features.md

For geospatial tables, SRIDs, radius search, nearest-neighbor lookups, or spatial indexing, use
```
references/postgres_best_practices/postgis.md
```
.
For embeddings, semantic search, similarity search, vector indexes, or retrieval/RAG in Postgres, use
```
references/postgres_best_practices/pgvector.md
```
.

对于架构或表设计，请从以下文档开始：

references/postgres_best_practices/schema-design.md

references/postgres_best_practices/advanced-features.md

对于地理空间表、SRID、半径搜索、最近邻查找或空间索引，请使用
```
references/postgres_best_practices/postgis.md
```
。
对于嵌入、语义搜索、相似度搜索、向量索引或Postgres中的检索/RAG，请使用
```
references/postgres_best_practices/pgvector.md
```
。

Backend query performance review

后端查询性能分析

Inventory read queries separately from write queries before recommending changes.
Unless the user explicitly includes writes, optimize only read-side paths.
Prioritize:
- N+1 query patterns
- repeated correlated subqueries
- dynamic
```
IN (...)
```
  SQL that should become parameterized arrays
- missing composite indexes matching real join and filter predicates
Validate with schema and catalog inspection first (
```
schema inspect
```
,
```
schema table-sizes
```
,
```
schema index-health
```
,
```
activity slow
```
) before asking for live benchmarking.

在推荐变更前，分别统计读查询和写查询。
除非用户明确包含写查询，否则仅优化读侧路径。
优先处理：
- N+1查询模式
- 重复的关联子查询
- 应转为参数化数组的动态
```
IN (...)
```
  SQL
- 缺少与实际连接和过滤谓词匹配的复合索引
在请求实时基准测试前，先通过架构和目录检查（
```
schema inspect
```
、
```
schema table-sizes
```
、
```
schema index-health
```
、
```
activity slow
```
）验证问题。

SQL safety

SQL安全

Prefer
```
query run
```
with heredoc or
```
-f
```
for multi-statement SQL.
Do not inline
```
DO $$ ... $$
```
into double-quoted shell strings.

Replace raw one-off

psql

queries with

query run

Raw pattern:

PGPASSWORD=... psql -h host -U user -d app -c "select now();"

Skill pattern:

DB_PROJECT_ROOT=/path/to/repo DB_PROFILE=local "$POSTGRES_CLI" query run -c "select now();"

Replace raw heredoc DDL with
```
query run
```
heredocs:

bash

DB_PROJECT_ROOT=/path/to/repo DB_PROFILE=local "$POSTGRES_CLI" query run <<'SQL'
ALTER TABLE public.example
  ADD COLUMN IF NOT EXISTS description text;

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'example'
ORDER BY ordinal_position;
SQL

Replace
```
psql
```
catalog meta-commands such as
```
\d+ public.example
```
with SQL catalog queries because
```
query run
```
executes SQL, not
```
psql
```
meta-commands:

bash

DB_PROJECT_ROOT=/path/to/repo DB_PROFILE=local "$POSTGRES_CLI" query run -c "
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'example'
ORDER BY ordinal_position;
"

对于多语句SQL，优先使用带heredoc或
```
-f
```
参数的
```
query run
```
。
请勿将
```
DO $$ ... $$
```
内联到双引号包裹的shell字符串中。

使用

query run

替代原生一次性

psql

查询：

原生模式：

PGPASSWORD=... psql -h host -U user -d app -c "select now();"

技能模式：

DB_PROJECT_ROOT=/path/to/repo DB_PROFILE=local "$POSTGRES_CLI" query run -c "select now();"

使用
```
query run
```
heredoc替代原生heredoc DDL：

bash

DB_PROJECT_ROOT=/path/to/repo DB_PROFILE=local "$POSTGRES_CLI" query run <<'SQL'
ALTER TABLE public.example
  ADD COLUMN IF NOT EXISTS description text;

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'example'
ORDER BY ordinal_position;
SQL

使用SQL目录查询替代
```
psql
```
目录元命令（如
```
\d+ public.example
```
），因为
```
query run
```
执行的是SQL而非
```
psql
```
元命令：

bash

DB_PROJECT_ROOT=/path/to/repo DB_PROFILE=local "$POSTGRES_CLI" query run -c "
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'example'
ORDER BY ordinal_position;
"

Data-copy migrations

数据复制迁移

When copying selected rows from dev or local into a production SQL file:
- inspect source values and target table shape first
- treat copied values as a draft for production
- do not preserve generated primary-key values by default
- prefer
```
INSERT ... RETURNING
```
  and stable business keys when dependent rows need new IDs
Keep DDL reasoning separate from requested data-copy SQL.

当从开发环境或本地环境选择行复制到生产SQL文件时：
- 先检查源值和目标表结构
- 将复制的值视为生产环境的草稿
- 默认不保留生成的主键值
- 当依赖行需要新ID时，优先使用
```
INSERT ... RETURNING
```
  和稳定的业务键
将DDL逻辑与请求的数据复制SQL分开处理。

Trigger rules

触发规则

If
```
.skills/postgres/config.toml
```
exists, use it without scanning unless the user asks to bootstrap or refresh.
Else if only legacy
```
.skills/postgres/postgres.toml
```
exists, use it as migration input to generate canonical
```
config.toml
```
, and make sure ignore coverage follows the canonical path too.
If
```
DB_PROFILE
```
is unset and exactly one profile exists, use it.
If multiple profiles exist, prefer the saved
```
[defaults].profile
```
when present; otherwise require an explicit profile or interactive selection.
If the user asks to bootstrap or refresh a saved profile, use
```
profile bootstrap
```
.
Do not run
```
docs search
```
unless the user explicitly asks for official docs lookup or verification.
For migrations path resolution and schema-change workflow, follow
```
references/postgres_guardrails.md
```
.
If a pending migration file contains its own
```
BEGIN
```
or
```
COMMIT
```
, do not wrap it in an outer rollback transaction during scratch validation.
If the user explicitly marks a pending migration as migrated, released, or run in production, perform
```
migration release
```
immediately unless they ask for a dry run only.
Do not use this runtime skill to refresh best-practices references or otherwise upgrade the skill package itself.

如果
```
.skills/postgres/config.toml
```
存在，直接使用该配置，除非用户要求引导或刷新。
否则，如果仅存在旧版
```
.skills/postgres/postgres.toml
```
，将其作为迁移输入生成标准
```
config.toml
```
，并确保忽略规则适用于标准路径。
如果
```
DB_PROFILE
```
未设置且仅存在一个配置文件，使用该配置文件。
如果存在多个配置文件，优先使用已保存的
```
[defaults].profile
```
；否则需要明确指定配置文件或进行交互式选择。
如果用户要求引导或刷新已保存的配置文件，使用
```
profile bootstrap
```
。
仅在用户明确要求查找或验证官方文档时，才运行
```
docs search
```
。
对于迁移路径解析和架构变更工作流，请遵循
```
references/postgres_guardrails.md
```
。
如果待处理迁移文件包含自己的
```
BEGIN
```
或
```
COMMIT
```
，在临时验证期间请勿将其包裹在外部回滚事务中。
如果用户明确标记待处理迁移为已迁移、已发布或已在生产环境运行，请立即执行
```
migration release
```
，除非用户要求仅进行试运行。
请勿使用此运行时技能刷新最佳实践参考文档或升级技能包本身。

Guardrails

防护规则

Always ask for approval before making DDL changes.
Keep pending changes in prerelease migration files and maintain a changelog.

Use

pending migration file

and

released migration file

as the canonical workflow terms.

Do not edit existing released SQL files.
Do not create a new file under
```
released/
```
for pending work.
Only create a released migration file by moving a pending prerelease file when the user explicitly confirms release.
After any schema change, run the least expensive validation query that proves the change landed.
For full rules and migration workflow, read
```
references/postgres_guardrails.md
```
.

在进行DDL变更前始终请求批准。
将待处理变更保存在预发布迁移文件中，并维护变更日志。
使用"待处理迁移文件"和"已发布迁移文件"作为标准工作流术语。
请勿编辑已存在的已发布SQL文件。
请勿在
```
released/
```
目录下为待处理工作创建新文件。
仅在用户明确确认发布时，将待处理的预发布文件移动为已发布迁移文件。
任何架构变更后，运行成本最低的验证查询以确认变更已生效。
完整规则和迁移工作流请阅读
```
references/postgres_guardrails.md
```
。

CLI Maintenance

CLI维护

Keep normal execution on the shipped
```
scripts/postgres
```
launcher.
Treat
```
projects/postgres/Cargo.toml
```
as the single source of truth for the CLI semver, and use the shipped
```
scripts/postgres --version
```
to verify the runtime version.
Open
```
projects/postgres/
```
only when fixing bugs, improving performance, rebuilding the shipped binary, or extending the CLI contract.
Make maintenance changes in
```
projects/postgres/
```
, then rebuild the affected
```
scripts/bin/postgres-<os>-<arch>
```
binaries so shipped runtimes stay current.
Treat compiled outputs in
```
projects/postgres/target/
```
as intermediates, not supported runtime entrypoints.
Keep project-local ignore rules in
```
projects/postgres/.gitignore
```
. Only add a skill-root
```
.gitignore
```
if new generated state truly lives at the skill root.
Follow semver for shipped CLI changes:
- major for breaking CLI contract changes
- minor for backward-compatible new features or meaningful capability additions
- patch for backward-compatible bug fixes and corrections

After maintenance changes, re-verify through the shipped launcher with:

from the skill root:
```
./scripts/postgres --help
```
from the skill root:
```
./scripts/postgres --version
```

from any cwd:

DB_PROJECT_ROOT=/path/to/repo <postgres-skill-root>/scripts/postgres --json doctor

Keep config migration one-way from legacy
```
postgres.toml
```
to canonical
```
config.toml
```
.
Keep the runtime surface focused on query, inspection, and migration release. Do not reintroduce dump, restore, export, or schema-diff commands.

常规执行请基于提供的
```
scripts/postgres
```
启动器。
将
```
projects/postgres/Cargo.toml
```
视为CLI语义化版本的唯一来源，并使用提供的
```
scripts/postgres --version
```
验证运行时版本。
仅在修复bug、提升性能、重建提供的二进制文件或扩展CLI契约时，才修改
```
projects/postgres/
```
中的内容。
在
```
projects/postgres/
```
中进行维护变更后，重建受影响的
```
scripts/bin/postgres-<os>-<arch>
```
二进制文件，确保提供的运行时保持最新。
将
```
projects/postgres/target/
```
中的编译输出视为中间产物，而非受支持的运行时入口。
将项目本地的忽略规则放在
```
projects/postgres/.gitignore
```
中。仅当新生成的状态确实位于技能根目录时，才添加技能根目录的
```
.gitignore
```
。
提供的CLI变更遵循语义化版本：
- 主版本变更：CLI契约发生破坏性变更
- 次版本变更：向后兼容的新功能或有意义的能力新增
- 补丁版本变更：向后兼容的bug修复和修正

维护变更后，通过提供的启动器重新验证：

从技能根目录：
```
./scripts/postgres --help
```
从技能根目录：
```
./scripts/postgres --version
```

从任意工作目录：

DB_PROJECT_ROOT=/path/to/repo <postgres-skill-root>/scripts/postgres --json doctor

保持配置从旧版
```
postgres.toml
```
到标准
```
config.toml
```
的单向迁移。
保持运行时接口聚焦于查询、检查和迁移发布。请勿重新引入备份、恢复、导出或架构对比命令。

Usage references

使用参考

Setup and runtime usage:
```
references/postgres_usage.md
```
Env var contract:
```
references/postgres_env.md
```
Config schema:
```
references/postgres_skill_schema.md
```
Migration guardrails:
```
references/postgres_guardrails.md
```

Design guidance:

references/postgres_best_practices/README.md

Local/Docker recovery:
```
references/postgres_local_recovery.md
```

设置与运行时使用：
```
references/postgres_usage.md
```
环境变量契约：
```
references/postgres_env.md
```
配置架构：
```
references/postgres_skill_schema.md
```
迁移防护规则：
```
references/postgres_guardrails.md
```

设计指南：

references/postgres_best_practices/README.md

本地/Docker恢复：
```
references/postgres_local_recovery.md
```