以下是每一位 Java 后端开发者、架构师、DBA 协作者 在日常工作中最频繁、最基础、却最容易被忽视的 PostgreSQL 操作——数据库元数据查看与诊断命令。
这些不是“DDL/DML/DQL”这类业务 SQL，而是运维、调试、开发、协作、代码审查、文档编写中不可或缺的“数据库探针”。它们决定了你能否快速定位问题、理解他人设计、验证变更效果、保障系统健壮性。
以下是一份专为 Java 后端团队 量身打造的 PostgreSQL 元数据查看与诊断操作深度详解文档，涵盖：
- ✅ 查看数据库、模式、表、字段、索引、约束、权限等核心元数据
- ✅ 所有命令均使用标准 SQL + psql 元命令双模式呈现
- ✅ 每条命令附带企业级使用场景、中文注释、最佳实践、避坑指南
- ✅ 所有示例可直接复制粘贴至生产环境调试或团队 Wiki
- ✅ 包含12 项高频操作 + 5 项进阶诊断技巧，覆盖 90% 实际需求
🔍 PostgreSQL 元数据查看与诊断操作深度详解文档
—— 数据库的“显微镜”与团队协作的“通用语言”
适用对象：Java 开发、后端架构师、测试工程师、DBA、技术负责人
目标：系统掌握 PostgreSQL 所有核心元数据查看命令，实现无需文档、无需沟通、一键看清数据库结构，提升团队协作效率、降低排查成本、杜绝“表结构黑盒”问题。
一、为什么 Java 开发者必须掌握元数据命令？
| 场景 | 传统做法 | 推荐做法 |
|---|---|---|
| 新人接手项目 | 查看 Java 实体类 → 猜表结构 | DESCRIBE table_name; 一眼看清 |
| 代码审查 | 比对实体类与数据库字段是否一致 | SELECT column_name FROM information_schema.columns WHERE table_name = 'users'; |
| 排查慢查询 | 猜索引是否存在 | \d+ users 一目了然 |
| 数据迁移 | 担心字段丢失 | \dt+ 查看所有表结构，对比备份 |
| 联调接口 | 不知道 metadata 字段结构 | SELECT metadata FROM users LIMIT 1; + jsonb_pretty() |
✅ 结论：
一个能熟练使用\d,\dt,\d+,\di的 Java 开发者，比十个只会写@Entity的开发者更值得信赖。
二、核心元数据查看命令总览（按使用频率排序）
| 类别 | 命令 | 作用 | 类型 |
|---|---|---|---|
| ✅ 1 | \l | 列出所有数据库 | psql 元命令 |
| ✅ 2 | \c database_name | 切换数据库 | psql 元命令 |
| ✅ 3 | \dn | 列出所有模式（Schema） | psql 元命令 |
| ✅ 4 | \dt | 列出当前数据库所有表 | psql 元命令 |
| ✅ 5 | \dt+ | 列出所有表 + 大小、描述 | psql 元命令 |
| ✅ 6 | \d table_name | 查看表结构（字段、类型、约束） | psql 元命令 |
| ✅ 7 | \d+ table_name | 查看表完整结构（含索引、分区、注释） | psql 元命令 |
| ✅ 8 | \di | 列出所有索引 | psql 元命令 |
| ✅ 9 | \di+ index_name | 查看索引详细信息（类型、表达式、条件） | psql 元命令 |
| ✅ 10 | \df | 列出所有函数 | psql 元命令 |
| ✅ 11 | \dv | 列出所有视图 | psql 元命令 |
| ✅ 12 | \dp | 查看权限（GRANT/REVOKE） | psql 元命令 |
| ✅ 13 | SELECT * FROM information_schema.columns | SQL 标准方式查字段 | SQL 查询 |
| ✅ 14 | SELECT * FROM pg_indexes | SQL 标准方式查索引 | SQL 查询 |
| ✅ 15 | SELECT pg_get_constraintdef(oid) | 查看约束定义 | SQL 查询 |
| ✅ 16 | SELECT pg_get_indexdef(indexrelid) | 查看索引创建语句 | SQL 查询 |
✅ 建议：
- 日常开发用
\d+，一屏看清所有信息。- 自动化脚本用
information_schema，可被 Java 程序解析。
三、详细使用示例（带中文注释与企业级建议）
✅ 1. \l —— 查看所有数据库
# 在 psql 中执行
\l
# ✅ 输出示例：
# List of databases
# Name | Owner | Encoding | Collate | Ctype | Access privileges
# -----------+----------+----------+-------------+-------------+-----------------------
# myapp | app_user | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/app_user +
# | | | | | app_user=CTc/app_user+
# | | | | | report_reader=c/app_user
# postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
# template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
# | | | | | postgres=CTc/postgres
# template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
# | | | | | postgres=CTc/postgres
# (5 rows)
✅ 企业级建议：
- 确认连接的是正确的数据库（如
myapp，而非postgres）。- 检查
Access privileges是否有非授权用户（如public）。- 生产环境应仅保留必要数据库（如
myapp,myapp_test），其余删除。
✅ 2. \c database_name —— 切换数据库
# 切换到业务数据库
\c myapp
# ✅ 输出：
# You are now connected to database "myapp" as user "app_user".
✅ 企业级建议：
- 每次调试前必须确认当前数据库，避免在
template1里写 SQL。- 可在
.psqlrc文件中设置默认数据库：\c myapp
✅ 3. \dn —— 查看所有模式（Schema）
\dn
# ✅ 输出：
# List of schemas
# Name | Owner
# --------------------+----------
# information_schema | postgres
# pg_catalog | postgres
# pg_temp_1 | postgres
# pg_toast | postgres
# pg_toast_temp_1 | postgres
# public | app_user
# order_module | app_user
# (7 rows)
✅ 企业级建议：
- 推荐使用独立 Schema（如
order_module,user_module）隔离微服务。public是默认模式，避免所有表都放在这里。- 使用
SET search_path TO order_module, public;设置默认搜索路径。
✅ 4. \dt —— 列出当前数据库所有表
\dt
# ✅ 输出：
# List of relations
# Schema | Name | Type | Owner
# --------+-------------+-------+----------
# public | users | table | app_user
# public | orders | table | app_user
# public | order_items | table | app_user
# public | system_logs | table | app_user
# (4 rows)
✅ 企业级建议：
- 快速确认当前数据库中有哪些核心表。
- 用于代码审查：你新增的表是否出现在列表中？
✅ 5. \dt+ —— 查看所有表 + 大小 + 注释（推荐！）
\dt+
# ✅ 输出：
# List of relations
# Schema | Name | Type | Owner | Size | Description
# --------+-------------+-------+----------+------------+-------------
# public | users | table | app_user | 8192 bytes | 系统用户主表
# public | orders | table | app_user | 16 kB | 订单主表
# public | order_items | table | app_user | 8192 bytes | 订单项表
# public | system_logs | table | app_user | 24 MB | 系统操作日志
# (4 rows)
✅ 企业级建议：
- 查看表大小，识别“大表”（如
system_logs24MB），决定是否分区。- 查看
Description字段，确认是否有表注释（COMMENT ON TABLE）。- 所有表必须有注释，否则团队无法理解其用途。
✅ 6. \d table_name —— 查看表结构（字段、类型、约束）
\d users
# ✅ 输出：
# Table "public.users"
# Column | Type | Collation | Nullable | Default
# ----------------+--------------------------+-----------+----------+---------------
# id | bigint | | not null | nextval('users_id_seq'::regclass)
# uuid | uuid | | not null | gen_random_uuid()
# username | character varying(50) | | not null |
# email | character varying(100) | | not null |
# password_hash | text | | not null |
# status | character varying(20) | | not null | 'active'::character varying
# created_at | timestamp with time zone | | not null | now()
# updated_at | timestamp with time zone | | not null | now()
# metadata | jsonb | | | '{}'::jsonb
# phone | character varying(20) | | |
# Indexes:
# "users_pkey" PRIMARY KEY, btree (id)
# "users_email_unique" UNIQUE CONSTRAINT, btree (email)
# "users_username_unique" UNIQUE CONSTRAINT, btree (username)
# "users_status_idx" btree (status)
# "users_metadata_gin" gin (metadata)
# Check constraints:
# "users_status_check" CHECK (status = ANY (ARRAY['active'::character varying, 'inactive'::character varying, 'banned'::character varying, 'deleted'::character varying]))
# Referenced by:
# TABLE "orders" CONSTRAINT "fk_orders_user_id" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
✅ 企业级建议：
- 这是你最常用的命令，每天至少用 5 次。
- 快速确认：
- 字段类型是否正确？（如
varchar(50)还是text？）- 是否有
NOT NULL？- 默认值是否合理？（如
now()、gen_random_uuid()）- 是否有唯一约束、外键、检查约束？
- 是否有索引？索引名是否规范？
- 所有表结构变更必须通过
\d table_name验证。
✅ 7. \d+ table_name —— 查看表完整结构（含注释、索引、分区）
\d+ users
# ✅ 输出（部分）：
# Table "public.users"
# Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
# ----------------+--------------------------+-----------+----------+----------------+----------+--------------+-------------
# id | bigint | | not null | nextval(...) | plain | | 主键，自增
# uuid | uuid | | not null | gen_random_uuid() | plain | | 业务主键，分布式唯一
# username | character varying(50) | | not null | | extended | | 用户登录名，3~50字符
# email | character varying(100) | | not null | | extended | | 邮箱地址，唯一
# password_hash | text | | not null | | extended | | BCrypt 加密密码
# status | character varying(20) | | not null | 'active'::... | plain | | 用户状态：active/inactive/banned/deleted
# created_at | timestamp with time zone | | not null | now() | plain | | 创建时间
# updated_at | timestamp with time zone | | not null | now() | plain | | 更新时间
# metadata | jsonb | | | '{}'::jsonb | main | | 用户个性化设置，JSONB格式
# phone | character varying(20) | | | | extended | | 手机号，允许为空
# Indexes:
# "users_pkey" PRIMARY KEY, btree (id)
# "users_email_unique" UNIQUE CONSTRAINT, btree (email)
# "users_username_unique" UNIQUE CONSTRAINT, btree (username)
# "users_status_idx" btree (status)
# "users_metadata_gin" gin (metadata)
# "users_created_at_idx" btree (created_at DESC)
# Check constraints:
# "users_status_check" CHECK (status = ANY (ARRAY['active'::character varying, 'inactive'::character varying, 'banned'::character varying, 'deleted'::character varying]))
# Referenced by:
# TABLE "orders" CONSTRAINT "fk_orders_user_id" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
# Triggers:
# trigger_update_updated_at BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION update_updated_at_column()
# Access privileges:
# Schema | Name | Type | Access privileges
# --------+-------------+-------+-------------------
# public | users | table | app_user=arwdDxt/app_user
# public | users_id_seq | sequence | app_user=rw/app_user
#
# Comments:
# Column "users.id" IS '主键，自增';
# Column "users.uuid" IS '业务主键，分布式唯一';
# Column "users.metadata" IS '用户个性化设置，JSONB格式，如 {"theme":"dark", "lang":"zh"}';
# Table "users" IS '系统用户主表，存储核心身份与配置信息';
✅ 企业级建议：
- 这是你最强大的命令，一次查看：字段、类型、约束、索引、触发器、注释、权限。
- 所有代码审查必须对照
\d+ table_name输出。- 确认：
- 是否有触发器？（自动更新时间戳）
- 是否有分区？（
Partition key:）- 是否有注释？（
Comments:）- 索引是否命名规范？（
idx_表名_字段名）- 团队必须统一使用
\d+作为表结构标准查看方式。
✅ 8. \di —— 列出所有索引
\di
# ✅ 输出：
# List of relations
# Schema | Name | Type | Owner | Table
# --------+---------------------+-------+----------+--------
# public | users_pkey | index | app_user | users
# public | users_email_unique | index | app_user | users
# public | users_status_idx | index | app_user | users
# public | users_metadata_gin | index | app_user | users
# public | idx_orders_user_id | index | app_user | orders
# public | idx_orders_status | index | app_user | orders
# (6 rows)
✅ 企业级建议：
- 快速判断某个字段是否有索引。
- 检查索引命名是否规范：
idx_表名_字段名。- 发现
users_metadata_gin表明该表使用了JSONB索引，符合最佳实践。- 发现无索引的字段（如
orders.total_amount），需评估是否需加索引。
✅ 9. \di+ index_name —— 查看索引详细信息
\di+ users_metadata_gin
# ✅ 输出：
# List of relations
# Schema | Name | Type | Owner | Table | Size | Description
# --------+-------------------+------+----------+-------+-------+-------------
# public | users_metadata_gin| index| app_user | users | 16 kB |
# Index "users_metadata_gin"
# Column | Type | Definition
# --------+--------+------------
# metadata | jsonb | jsonb
# Method: gin
# Access method: gin
✅ 企业级建议：
- 确认索引类型：
btree（普通）、gin（JSONB/数组）、gist（地理）。- 确认是否是表达式索引（如
lower(email)）。- 确认是否是部分索引（如
WHERE status = 'active'）。- 用于优化：发现
GIN索引，说明团队正确使用了JSONB。
✅ 10. \df —— 列出所有函数
\df
# ✅ 输出：
# List of functions
# Schema | Name | Result data type | Argument data types | Type
# -----------+--------------------+------------------+---------------------+------
# public | update_updated_at_column | trigger | | func
# public | get_user_total_spent | numeric | bigint | func
# public | gen_random_uuid | uuid | | func
# (3 rows)
✅ 企业级建议：
- 确认是否存在自动维护字段的函数（如
update_updated_at_column）。- 确认是否使用了
gen_random_uuid()（推荐）而非uuid_generate_v4()（需扩展）。- 检查函数是否被滥用（如在事务中调用外部 API）。
✅ 11. \dv —— 列出所有视图
\dv
# ✅ 输出：
# List of relations
# Schema | Name | Type | Owner
# --------+-----------------+------+----------
# public | active_users | view | app_user
# public | user_order_summary | view | app_user
# (2 rows)
✅ 企业级建议：
- 确认是否存在封装复杂查询的视图。
- 避免直接查询
users表，应查询active_users视图。- 视图是数据安全与复用的重要手段，必须被团队认可。
✅ 12. \dp —— 查看权限（谁可以做什么）
\dp
# ✅ 输出：
# Access privileges
# Schema | Name | Type | Access privileges | Column privileges | Policies
# --------+-------------+-------+-------------------+-------------------+----------
# public | users | table | app_user=arwdDxt/app_user | |
# public | orders | table | app_user=arwdDxt/app_user | |
# public | users_id_seq| sequence | app_user=rw/app_user | |
# public | active_users| view | app_user=r/app_user | |
# public | report_reader| role | | |
# (5 rows)
✅ 企业级建议：
- 确认
report_reader是否只有r（读）权限。- 确认
app_user是否有d（删除）权限？是否应移除？- 权限必须遵循最小原则，
a（插入）、r（读）、w（更新）、d（删除）应明确区分。
四、SQL 标准方式查询元数据（可用于 Java 程序解析）
✅ 1. 查看某个表的所有字段（SQL 标准方式）
-- 📌 示例：查询 users 表的所有字段信息
SELECT
column_name AS "字段名",
data_type AS "数据类型",
is_nullable AS "是否可空",
column_default AS "默认值",
character_maximum_length AS "最大长度",
udt_name AS "类型名称"
FROM information_schema.columns
WHERE table_schema = 'public'
AND table_name = 'users'
ORDER BY ordinal_position;
-- ✅ 输出示例：
-- 字段名 | 数据类型 | 是否可空 | 默认值 | 最大长度 | 类型名称
-- id | bigint | NO | nextval(...) | | bigint
-- uuid | uuid | NO | gen_random_uuid() | | uuid
-- username | character varying | NO | | 50 | varchar
-- email | character varying | NO | | 100 | varchar
-- metadata | jsonb | YES | '{}'::jsonb | | jsonb
✅ 企业级建议：
- 用于自动化脚本、代码生成、数据迁移工具。
- Java 中可通过
JdbcTemplate.queryForList()获取此结果。- 比
\d+更适合集成到 CI/CD 流程中。
✅ 2. 查看某个表的所有索引（SQL 标准方式）
-- 📌 示例：查询 users 表的所有索引
SELECT
indexname AS "索引名",
indexdef AS "创建语句"
FROM pg_indexes
WHERE schemaname = 'public'
AND tablename = 'users'
ORDER BY indexname;
-- ✅ 输出示例：
-- 索引名 | 创建语句
-- users_pkey | CREATE UNIQUE INDEX users_pkey ON public.users USING btree (id)
-- users_email_unique | CREATE UNIQUE INDEX users_email_unique ON public.users USING btree (email)
-- users_metadata_gin | CREATE INDEX users_metadata_gin ON public.users USING gin (metadata)
✅ 企业级建议：
- 用于自动化索引检查工具。
- 可验证索引是否按规范命名（
idx_前缀）。- 可识别是否使用了
GIN、BRIN等高级索引。
✅ 3. 查看某个表的所有约束（主键、唯一、外键、检查）
-- 📌 示例：查询 users 表的所有约束
SELECT
conname AS "约束名",
contype AS "约束类型",
pg_get_constraintdef(c.oid) AS "定义"
FROM pg_constraint c
JOIN pg_class t ON c.conrelid = t.oid
WHERE t.relname = 'users'
AND t.relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public')
ORDER BY conname;
-- ✅ 输出示例：
-- 约束名 | 约束类型 | 定义
-- users_pkey | p | PRIMARY KEY (id)
-- users_email_unique | u | UNIQUE (email)
-- users_username_unique | u | UNIQUE (username)
-- users_status_check | c | CHECK (status = ANY (ARRAY['active'::character varying, 'inactive'::character varying, 'banned'::character varying, 'deleted'::character varying]))
✅ 企业级建议：
contype值说明：
p= 主键u= 唯一约束f= 外键c= 检查约束x= 排他约束- 用于验证业务规则是否在数据库层实现。
✅ 4. 查看某个索引的创建语句（SQL 标准方式）
-- 📌 示例：查看 users_metadata_gin 索引的创建语句
SELECT pg_get_indexdef('users_metadata_gin'::regclass);
-- ✅ 输出：
-- CREATE INDEX users_metadata_gin ON public.users USING gin (metadata)
✅ 企业级建议：
- 用于生成迁移脚本。
- 可验证是否为表达式索引（如
lower(email)）。
✅ 5. 查看所有表的注释（系统级元数据）
-- 📌 示例：查看所有表的注释
SELECT
n.nspname AS schema_name,
c.relname AS table_name,
pg_catalog.obj_description(c.oid, 'pg_class') AS table_comment
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
AND n.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
ORDER BY schema_name, table_name;
-- ✅ 输出：
-- schema_name | table_name | table_comment
-- public | users | 系统用户主表，存储核心身份与配置信息
-- public | orders | 订单主表
-- public | system_logs| 系统操作日志
✅ 企业级建议：
- 所有表必须有注释，否则团队无法理解其用途。
- 可集成到文档生成工具（如 Swagger、Doxygen）。
五、进阶诊断技巧（生产环境必备）
| 技巧 | 命令 | 作用 |
|---|---|---|
| ✅ 1 | \timing | 开启 SQL 执行时间统计，诊断慢查询 |
| ✅ 2 | \set VERBOSITY verbose | 显示更详细的错误信息（如外键冲突） |
| ✅ 3 | \x | 切换为垂直显示模式，适合长字段查看 |
| ✅ 4 | SELECT pg_size_pretty(pg_total_relation_size('users')); | 查看表总大小（含索引） |
| ✅ 5 | SELECT count(*) FROM users; | 快速估算表数据量（比 COUNT(*) 更快） |
✅ 示例：垂直显示（适合查看 JSONB 字段）
\x on
SELECT metadata FROM users WHERE id = 1001;
-- ✅ 输出：
-- -[ RECORD 1 ]-------------------------------------------------------------------
-- metadata | {"theme": "dark", "notifications": {"email": true, "push": false}, "language": "zh-CN"}
✅ 企业级建议：
- 开发时开启
\x，查看metadata、content等长文本更清晰。- 使用
\timing评估查询性能，所有慢查询必须记录耗时。
六、团队协作规范：元数据查看“五不准”
| 规范 | 说明 |
|---|---|
| ✅ 一不准 | 不准问“这个表有啥字段？” → 自己用 \d+ 查 |
| ✅ 二不准 | 不准说“我猜这个字段是 varchar” → 用 information_schema.columns 验证 |
| ✅ 三不准 | 不准在代码中硬编码字段名 → 用视图或 DTO，且必须有注释 |
| ✅ 四不准 | 不准不写表注释 → COMMENT ON TABLE ... IS '...'; 是你的责任 |
| ✅ 五不准 | 不准在生产环境直接修改表结构 → 所有变更走 Flyway + \d+ 验证 |
七、总结：元数据查看是每个 Java 开发者的“基本功”
| 能力 | 价值 |
|---|---|
✅ 能用 \d+ 一眼看清表结构 | 降低沟通成本，提升协作效率 |
✅ 能用 \di+ 确认索引是否合理 | 避免慢查询，保障性能 |
✅ 能用 \dp 检查权限 | 防止越权访问，保障安全 |
| ✅ 能用 SQL 标准方式解析元数据 | 支撑自动化工具、CI/CD、代码生成 |
✅ 能用 \timing + \x 诊断问题 | 成为团队中的“数据库专家” |
✅ 终极建议：
一个能熟练使用\d+ users的 Java 开发者，是“懂数据的工程师”。
一个只会看 Java 实体类的人，是“只懂代码的搬运工”。
📌 下一步行动建议：
- 将本文档作为团队《PostgreSQL 元数据诊断手册》核心章节，打印张贴在工位。
- 在团队 Wiki 中创建“数据库快速查询速查表”，收藏所有
\d,\di,\dp命令。 - 在 GitLab CI 中加入脚本：
psql -c "\d+ users" > table_structure.txt，作为部署验证环节。 - 组织一次“元数据挑战赛”：给出一个表名，限时用
\d+说出所有字段、索引、约束、注释。 - 为新员工培训时，第一课不是写 SQL，而是教他们
\d+。
396
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
