1. 拿到一个PG实例先看清它装了什么事情是这样的前几天同事丢给我一个 PostgreSQL 连接串说是帮我看下这个库里都有什么表。我习惯性敲了\dt结果屏幕上干干净净什么都没有。第一反应是权限不够查了一下才发现表根本不在 public 模式下。这个插曲很典型——PostgreSQL 的对象层级是实例 → 数据库 → 模式schema → 表 → 列比 MySQL 的实例 → 数据库 → 表多了一层。很多从 MySQL 转过来的人第一步就在这儿迷路了。所以这篇东西我不打算只罗列命令而是按照从实例一路看到列的顺序把查看数据库和表结构的常用手段系统过一遍。无论你是做课程设计、日常运维还是刚从 MySQL 切到 PG照着走一遍就能对实例里的东西门儿清。1.1 版本和实例信息一切查询的前提连上 PG 的第一件事先搞清楚你连的是什么版本、什么角色。版本不同有些系统视图的字段会有差异。比如pg_stat_user_tables里的n_live_tup在 PG 12 之后表现更准确而pg_index的indisvalid从很早就有了但 PG 14 之后对CONCURRENTLY建索引的校验更严格。所以查看版本是第一步SELECT version();这个命令会返回完整的版本字符串包括 PostgreSQL 版本号和编译信息。配合当前连接信息一起看SELECT current_database(), current_user, session_user;current_database()返回当前会话所在的数据库名current_user和session_user的区别在于current_user可能是通过SET ROLE切换后的角色而session_user是登录时的原始角色。检查权限问题的时候这两个值经常能帮上大忙。1.2 数据库列表psql 元命令与系统表对照查看当前实例下有哪些数据库最直接的方式是 psql 的\l或\l\l\l还会额外显示每个库的磁盘占用大小、表空间和描述信息。对应的 SQL 查询是SELECT datname, datdba, encoding, datcollate, datctype FROM pg_database;datdba是数据库属主的 OID想显示具体用户名可以关联pg_roles表。encoding是库的编码格式常见的有 UTF8datcollate和datctype是排序规则和字符分类规则这两个参数在建库之后就不能轻易修改直接决定了字符串比较和排序的行为。这里要特别提醒pg_database里能看到所有数据库的列表但你的角色并不一定有权限访问其中的数据。看到列表不等于能进去真正操作时还是需要库级别的 CONNECT 权限。1.3 和 MySQL 的直观差异从 MySQL 过来的人对SHOW DATABASES很熟悉。在 PG 里没有直接等价的关键字\l是最接近的体验。而USE database_name这个切换命令在 PG 里也不存在PG 只能在连接时指定数据库或者断开重连。连接时指定库的方式是psql -h host -p port -U username -d database_name连接后想换库只能退出再重连或者用\c database_namepsql 的元命令本质上是重新建立了一次连接其实也会校验连接权限。这个设计差异不算坑但新人经常会在这里疑惑为什么我 use 不了。2. schema是表结构的命名空间看不到表多半是它的问题之前我\dt查不到表就是因为表的归属模式不是默认的 public。PostgreSQL 的 schema 概念本质上相当于操作系统里面的目录数据库是整个磁盘schema 就是文件夹表是文件。同一张表在数据库里是否同名取决于它归属于哪个 schema。 查看命令是\d不带参数时显示当前模式下的所有可见对象表、视图、索引、序列等。\dt显示表\dt显示表及其 OID、大小和描述信息。这和\d的粒度不一样\d是所有对象混合在一起\dt专门查表。对应 SQLSELECT schemaname, tablename, tableowner, tablespace FROM pg_tables WHERE schemaname public;这里pg_tables视图已经帮我们过滤好了类型只看表不看索引和序列。schemaname是模式名tablename是表名tableowner是属主tablespace是表空间NULL 表示使用默认表空间。\dt还支持通配符模式过滤。比如只查users开头的表\dt users*这个通配符和 SQL 里的 LIKE 模式不完全一样需要匹配的是模式名.表名例如所有模式下的 users 表\dt *.*users*。psql 元命令的匹配规则默认是接在.后面那一段才算表名需要跨模式搜索的时候最好加上*.*。3.2 information_schema 与 pg_catalog两个查询入口的差别除了pg_tables还有一套 SQL 标准视图叫information_schema它最初设计出来是为了兼容不同数据库的查询习惯。SELECT table_schema, table_name, table_type FROM information_schema.tables WHERE table_schema NOT IN (pg_catalog, information_schema) ORDER BY table_schema, table_name;information_schema.tables里除了表还有视图table_typeVIEW和外部表FOREIGN。两套体系怎么选我的经验是日常 psql 里干活用\dt最省事写自动化脚本、检测表是否存在这类场景用pg_tables更可靠因为information_schema某些字段在不同版本之间存在细微差异而需要严格遵循 SQL 标准、跨数据库移植时information_schema是唯一选择。注意一点information_schema默认只显示当前用户有权限访问的对象而pg_catalog在大多数情况下需要配合权限过滤条件才能看到全部对象否则受行级安全策略影响列表并不完整。3.3 行数与活性哪些表真正被用过光看到有哪些表还不够很多时候你还想知道这些表到底有多少数据、什么时候被更新过。这时就要查统计信息SELECT schemaname, relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables ORDER BY n_live_tup DESC;n_live_tup是表里的活跃行数估计值n_dead_tup是死行数更新或删除后残留的旧版本。死行过多说明该表需要 VACUUM。last_autovacuum显示自动清理的时间如果一张表常年没有 autovacuum且死行持续增长通常意味着自动清理在这个库上被关闭了或者表过大导致清理频率不足。需要注意的是n_live_tup是估算值不是精确的 COUNT(*)。对于精确行数可以直接SELECT count(*) FROM 表名但在大表上这个操作会全表扫描代价比较高。日常监控和容量规划场景统计信息完全够用。4. 深入到列类型、默认值、约束一次看全表找到了接下来就是翻转出每个字段的细节。这是 PostgreSQL 和 MySQL 差异比较明显的地方之一MySQL 的DESCRIBE table返回的结果比较粗PG 的\d table则是把列、索引、外键、触发器混合在一起展示信息密度更高但一开始会让人有点摸不着头脑。4.1 \d 与 \d 的信息拼图假设表名是public.users在 psql 里执行\d public.users会看到类似这样的输出Table public.users Column | Type | Collation | Nullable | Default ------------------------------------------------------------------ id | integer | | not null | nextval(users_id_seq::regclass) username | character varying(64) | | not null | email | character varying(255) | | | created_at | timestamp with time zone | | | now() Indexes: users_pkey PRIMARY KEY, btree (id) users_username_key UNIQUE CONSTRAINT, btree (username)这个输出把列和索引都列出来了。Nullable字段显示not null表示非空。Default里如果有nextval(序列名::regclass)说明列是序列自增的。timestamp with time zone是带时区的时间戳缩写是timestamptz。\d还会显示列的注释如果存在的话以及表的 OID。日常快速浏览结构时\d够了要确认某个字段是否有注释、或者奇怪的字符类型长度\d更全面。4.2 用系统表查列format_type 与 pg_attributepsql 的\d背后实际上是查询了系统表。当你需要把列信息嵌入到脚本或 SQL 报表中时就要自己写查询了。最常用的查询是SELECT a.attname AS column_name, format_type(a.atttypid, a.atttypmod) AS data_type, a.attnotnull AS not_null, a.attdefault AS default_value, d.description AS column_comment FROM pg_attribute a LEFT JOIN pg_description d ON d.objoid a.attrelid AND d.objsubid a.attnum WHERE a.attrelid public.users::regclass AND a.attnum 0 AND NOT a.attisdropped ORDER BY a.attnum;解释一下几个关键点a.attrelid public.users::regclass这里把字符串直接转成 regclass 类型PostgreSQL 会自动解析出表的 OID。如果你不加 schema 前缀它会依赖当前的search_path。所以脚本里最好写明 schema。format_type(a.atttypid, a.atttypmod)这个函数把类型 OID 和修饰符组合成人类可读的完整类型名比如character varying(64)。直接查a.atttypid只能拿到 OID 数字没法看。attnum 0系统列的attnum是负数比如ctid、xmin这些我们要过滤掉。attisdropped被 DROP COLUMN 后列不会立刻物理删除而是标记为 dropped。过滤条件是NOT a.attisdropped。attdefault默认值表达式如果没有默认值则为 NULL。注意它显示的是表达式本身比如nextval(...)或now()::text不是计算后的值。4.3 从 MySQL 迁移时的列类型对照如果你是从 MySQL 转过来的下面的对应关系很常用MySQL 类型PostgreSQL 类型说明int / bigintinteger / bigint名称基本一致varchar(n)character varying(n)缩写 varchar(n) 同样可用timestamptimestamp without time zonePG 默认不带时区如需要带时区用 timestamptzdatetimetimestamp without time zone语义接近enum自定义类型或 check 约束PG 原生 enum 类型但加值有锁风险谨慎使用auto_incrementserial / identity更推荐 identityPG 10text / blobtext / byteatext 长度不受限制bytea 存储二进制这个表不需要背遇到具体迁移需求时拿来查就行。5. 索引、外键、序列、触发器结构不止是表和列很多人在这一步就停了觉得看完了表结构就算摸透了。但实际上表之间的血缘关系、索引的生效情况、序列的当前值往往才是排查性能问题和数据不一致的关键。这部分建议也不要跳过。5.1 索引清单与索引定义查看索引有两类需求一是这个库有哪些索引二是某张表上有哪些索引、是不是有效。查所有索引SELECT schemaname, tablename, indexname, indexdef FROM pg_indexes WHERE schemaname public ORDER BY tablename, indexname;indexdef列会直接给出完整的 CREATE INDEX 语句这是最清爽的查看方式比你从系统表里拼出来要快得多。那pg_index表和pg_indexes视图有什么区别pg_index是基表里面存了索引是否唯一indisunique、是否主键indisprimary、是否有效indisvalid等布尔标志。pg_indexes是外层的视图把pg_index和pg_class等基础信息拼在了一起适合日常直接查。判断一个索引是否被查询计划生效有个简单办法SELECT indexname, indexdef FROM pg_indexes WHERE tablename users;然后到对应表上EXPLAIN SELECT ...看执行计划里有没有走这个索引。比系统表里翻indisvalid更直接。5.2 外键关系谁引用了谁PostgreSQL 里外键约束存储在pg_constraint表中contype f表示 FOREIGN KEY。要查看某张表的外键以及被谁引用SELECT conname AS constraint_name, conrelid::regclass AS source_table, confrelid::regclass AS target_table, pg_get_constraintdef(oid) AS constraint_definition FROM pg_constraint WHERE contype f ORDER BY source_table;有用的扩展是加一个WHERE conrelid public.orders::regclass来看这张表引用了谁反向查谁引用了它则把条件换成confrelid public.orders::regclass。pg_get_constraintdef(oid)是一个很实用的函数它能把约束的定义还原成可读的 SQL 描述比如FOREIGN KEY (user_id) REFERENCES users(id)。如果你需要导出完整约束定义写这一句就能拿到不需要自己拼字段名。5.3 序列和触发器别忘了这两类隐藏对象序列sequence在 PG 里是一等公民很多自增主键依赖它。查看序列及其当前值\ds对应的 SQLSELECT sequence_schema, sequence_name, start_value, increment_by, max_value FROM information_schema.sequences;查看序列当前值可以用SELECT last_value, is_called FROM 序列名;但注意last_value是会话缓存的并不是全局实时的也可能因为缓存设置而跳号。它用来参考没问题但不要假设它和下一次nextval()严格关系。触发器用\dy查看或者在information_schema.triggers里查询SELECT event_object_schema, event_object_table, trigger_name, action_timing, event_manipulation FROM information_schema.triggers;对大多数日常查看需求知道有这些触发器、挂在哪张表上、什么时候触发就够了。6. 批量摸家底统计行数、导出定义、迁移避坑单表的查看命令到这里基本齐了。但工作中更常遇到的场景是一个库里几十上百张表我要一次性把所有表的基本信息拉出来或者把整个库的结构导出来做基线存档。这一节就是干这个的。6.1 一行脚本统计库里所有表的行数前面提过pg_stat_user_tables.n_live_tup是估算值不是精确值。如果要用精确值但又不想一张张COUNT(*)手动跑可以用 DO 块或者\gexec技巧。先看\gexec方案SELECT SELECT || quote_ident(schemaname) || . || quote_ident(tablename) || , count(*) FROM || quote_ident(schemaname) || . || quote_ident(tablename) || GROUP BY 1; FROM pg_tables WHERE schemaname public;在 psql 里执行后它只会生成一串 SQL 文本。这时候在语句末尾加上\gexecSELECT SELECT || quote_ident(schemaname) || . || quote_ident(tablename) || , count(*) FROM || quote_ident(schemaname) || . || quote_ident(tablename) || GROUP BY 1; FROM pg_tables WHERE schemaname public \gexecpsql 会把生成出来的每条 SQL 依次执行把结果拼成一张大结果集返回。这张表就是所有表的精确行数。quote_ident确保表名里的特殊字符被安全转义防止注入或语法错误。这个在数据库课程设计、数据迁移核对行数时都非常好用。6.2 定义导出pg_dump 只拿表结构如果要导出全库的表结构DDL首选不是自己拼 SQL而是用 pg_dumppg_dump -h host -p 5432 -U username -d database_name -s -n public -f schema.sql参数说明-s是--schema-only只导出对象定义不包含数据。-n public指定只导出 public 这个 schema避免把系统对象也带出来。-f schema.sql输出到文件。导出的文件里不仅包含 CREATE TABLE还包括索引、外键、序列、触发器等完整定义。这是最省心、最权威的方式。如果只想看一眼单表的创建语句也可以使用SELECT pg_get_tabledef(public.users);这个函数在较新的 PG 版本中可用但不是所有环境都有pg_dump 才是跨版本最稳妥的选择。6.3 从 MySQL 迁移过来最容易踩的三个坑最后说几个我在实际迁移和排查中反复遇到的点都是血泪经验\dt查不到表但表确实存在。先检查search_path把SET search_path TO 目标模式, public;加上再查。这个问题 90% 是 schema 路径没指对。字段大小写问题。MySQL 里SELECT * FROM Users和select * from users效果一样PG 完全不同。PG 会把没有引号的标识符强制转为小写所以建表时用了Users查询就必须写成Users这可能是你在 PG 里反复报relation does not exist的最常见原因。information_schema在 PG 里经常不显示默认值。比如information_schema.columns.column_default在某些约束下返回的是 NULL而pg_attribute.attdefault里却有值。如果你在做结构对比工具别只依赖 information_schema混合查询 pg_catalog 更稳。查看表结构这件事看起来简单但背后牵涉到 psql 元命令、系统目录、信息模式视图、统计信息、甚至 pg_dump 等多条路径。实际工作中我自己的习惯是交互式排查用 psql 元命令写脚本和做监控用 pg_catalog 系列视图跨数据库移植时用 information_schema 和 pg_dump 做交叉验证。三条路并不矛盾反而能互补。能把结构看清楚你就能回答很多业务问题这张表有哪些字段可以直接 join 上、那个字段默认值是不是符合预期、索引到底建没建上。这些问题的答案往往就在上面这些命令里。