PostgreSQL常用命令全解析：从基础连接到高级运维实战

1. 从“会用”到“精通”：为什么你需要掌握PostgreSQL常用命令

如果你刚开始接触PostgreSQL，或者已经用它做过几个项目，但每次遇到问题还是习惯性地去搜索引擎里翻找命令，那么这篇文章就是为你准备的。我见过太多开发者，包括早期的我自己，把PostgreSQL当作一个“黑箱”——通过图形化工具（比如pgAdmin、DBeaver）点点鼠标，完成基本的增删改查，一旦需要深入排查问题、优化性能或者进行一些高级管理操作，就立刻抓瞎。这种状态非常危险，它意味着你对数据库的掌控力非常薄弱，线上一个小问题就可能让你手忙脚乱。

掌握常用命令，绝不仅仅是背几个SELECT、INSERT的语法。它的核心价值在于让你获得对数据库的“透视能力”和“直接操控能力”。当应用响应变慢时，你能快速连上数据库，用pg_stat_activity查看当前有哪些“捣蛋”的慢查询在占用资源；当需要部署变更时，你能用\i命令干净利落地执行SQL脚本，而不是在图形界面里复制粘贴大段代码；当磁盘空间告警时，你能用VACUUM和pg_database_size系列命令精准定位是哪个表膨胀了，而不是盲目地重启服务。

更重要的是，这些命令是DBA（数据库管理员）和高级后端开发者沟通的“普通话”。无论是阅读官方文档、排查开源项目的数据库问题，还是与运维同事协作，命令行下的操作都是最直接、最通用、也往往是最高效的方式。这篇文章的目的，就是帮你把这套“普通话”练到流利，让你从“图形界面用户”成长为能真正驾驭PostgreSQL的“从业者”。我们会从最基础的连接和元信息查询开始，逐步深入到日常开发、运维管理、性能观测等核心场景，每个命令都会解释“为什么用”和“怎么用好”，并附上我踩过坑后总结的实操心得。

2. 基础入门：连接、信息查看与基本对象操作

刚开始和PostgreSQL打交道，第一步就是建立连接并搞清楚当前环境里有什么。这部分命令是你的“导航仪”和“望远镜”，能让你迅速熟悉战场。

2.1 连接数据库与PSQL元命令

PostgreSQL的官方命令行客户端是psql。连接数据库的基本命令是：

psql -h <主机名> -p <端口> -U <用户名> -d <数据库名>

例如，连接本地默认端口（5432）上的mydb数据库：psql -h localhost -U myuser -d mydb。连接成功后，你会进入psql的交互界面，提示符通常像mydb=>。

进入psql后，有一组以反斜杠\开头的“元命令”（Meta-commands）是你必须熟悉的。它们不是SQL，而是psql提供的快捷工具。

• \l或\list：列出当前数据库集群中的所有数据库。这是你登录后第一件该做的事，确认目标数据库是否存在。
• \c <database_name>：切换到另一个数据库，无需断开重连。非常方便。
• \dt：列出当前数据库中的所有普通表。类似的还有\di（索引）、\dv（视图）、\ds（序列）。如果想查看所有关系（包括系统表），可以用\d+。
• \d <table_name>：显示指定表的定义，包括列名、数据类型、约束等。在后面加上+号（\d+ table_name）可以显示更详细的信息，如存储大小、描述等。
• \x：切换输出格式为扩展显示。当查询结果字段较多，一行显示很乱时，用这个命令会让结果以键值对的形式垂直排列，更易读。这是一个开关命令，再执行一次就切回横向模式。
• \timing：开关SQL语句的执行时间显示。打开后，每个SQL语句执行完后都会显示耗时，对性能初判很有帮助。
• \?：获取所有元命令的帮助。\h：获取SQL命令的帮助（例如\h SELECT）。

实操心得：我习惯一进入psql就先执行\timing on和\x auto。\x auto会让psql根据终端宽度自动决定是否使用扩展显示。这两个设置可以写进~/.psqlrc配置文件，实现自动加载，能极大提升日常使用体验。

2.2 核心的增删改查（CRUD）SQL命令

这是所有数据库操作的基础，但PostgreSQL有一些自己的特性和最佳实践。

• 查询（SELECT）：除了标准语法，务必掌握LIMIT/OFFSET分页，以及DISTINCT、CASE WHEN等常用子句。对于JSONB类型的数据，要熟悉->、->>、@>等操作符。
• 插入（INSERT）：多行插入时，使用VALUES (), (), ...的语法比多个INSERT语句高效得多。INSERT ... ON CONFLICT DO UPDATE/NOTHING（UPSERT）是处理唯一冲突的神器，必须掌握。
• 更新（UPDATE）：一定要带WHERE条件！除非你明确想更新全表。使用FROM子句可以基于其他表来更新当前表，非常强大。
• 删除（DELETE）：同样，必须谨慎使用WHERE子句。在执行不确定的DELETE或UPDATE前，可以先将其改为SELECT语句验证影响的行数，这是一个铁律。

-- 一个包含UPSERT和JOIN UPDATE的示例 -- 1. 插入或更新用户最后登录时间 INSERT INTO user_logins (user_id, last_login_ip, login_count) VALUES (123, '192.168.1.1', 1) ON CONFLICT (user_id) DO UPDATE SET last_login_ip = EXCLUDED.last_login_ip, login_count = user_logins.login_count + 1, updated_at = NOW(); -- 2. 基于订单表更新用户消费总额 UPDATE users u SET total_spent = sub.sum_amount FROM ( SELECT user_id, SUM(amount) as sum_amount FROM orders WHERE status = 'completed' GROUP BY user_id ) sub WHERE u.id = sub.user_id;

2.3 模式（Schema）与权限管理命令

PostgreSQL使用模式（Schema）来组织数据库对象，类似于命名空间。

• CREATE SCHEMA <schema_name>：创建新模式。通常会把不同业务模块的表放在不同的模式里，如app、reporting等。
• SET search_path TO <schema1>, <schema2>, public;：设置当前会话的搜索路径。当你执行SELECT * FROM mytable时，PostgreSQL会按search_path中列出的模式顺序去寻找mytable。这个命令可以避免写冗长的模式名前缀。
• 权限管理：核心命令是GRANT和REVOKE。

  -- 将schema_app模式下的所有表的所有权限授予角色user_role GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA schema_app TO user_role; -- 将未来在schema_app下创建的表的所有权限也默认授予user_role ALTER DEFAULT PRIVILEGES IN SCHEMA schema_app GRANT ALL ON TABLES TO user_role;

注意事项：public模式是默认存在的，所有用户都有在其上创建对象的权限。在生产环境中，出于安全考虑，我通常会撤销public模式上的CREATE权限：REVOKE CREATE ON SCHEMA public FROM PUBLIC;。这里的PUBLIC是一个特殊的关键字，代表所有用户。

3. 运维核心：备份恢复、性能监控与维护

当你的应用正式上线，这部分命令就成了你的“急救包”和“听诊器”。它们关乎数据的安全性和服务的稳定性。

3.1 备份与恢复：pg_dump与pg_restore

逻辑备份是数据迁移、版本升级和灾难恢复的基石。

• pg_dump：用于导出单个数据库。强烈建议使用自定义格式（-Fc），因为它支持并行恢复和选择性恢复，且体积更小。

  # 备份mydb数据库到自定义格式文件 pg_dump -h localhost -U myuser -Fc mydb > mydb_backup.dump # 仅备份表结构（-s） pg_dump -h localhost -U myuser -s mydb > mydb_schema.dump # 备份单个大表，并使用gzip压缩 pg_dump -h localhost -U myuser -t my_large_table -Fc mydb | gzip > large_table.dump.gz

• pg_dumpall：用于导出整个数据库集群（所有数据库、角色、表空间等全局对象）。通常用于全集群迁移或搭建从库。注意：它只能输出纯SQL脚本格式（-Fp），恢复时是单线程的，对于大型集群可能较慢。

  pg_dumpall -h localhost -U postgres --globals-only > roles_and_globals.sql

• pg_restore：用于恢复由pg_dump -Fc创建的备份文件。它的强大之处在于灵活性和并行能力。

  # 先创建空数据库 createdb -h localhost -U myuser newdb # 并行恢复（-j 4），仅恢复数据（-a），不恢复表结构（已有结构时使用） pg_restore -h localhost -U myuser -d newdb -j 4 -a mydb_backup.dump # 列出备份文件内容，查看有哪些对象 pg_restore -l mydb_backup.dump > list.txt

踩坑实录：有一次我需要从生产库恢复一张被误删的表到测试库。生产库很大，全库恢复不现实。我的做法是：1) 用pg_restore -l列出备份内容；2) 编辑生成的list.txt文件，只保留我需要的那张表及其索引、约束的条目（用;注释掉不需要的）；3) 使用pg_restore -L list.txt来按清单恢复。这个功能救了我很多次。另外，务必在恢复前，在测试环境验证备份文件的完整性和恢复流程。

3.2 性能监控与诊断命令

问题发生时，快速定位瓶颈是关键。

• 查看活动连接与查询：

  -- 查看当前所有活动连接和正在执行的查询（最常用） SELECT pid, usename, application_name, client_addr, state, query, query_start FROM pg_stat_activity WHERE state != 'idle' -- 过滤空闲连接 ORDER BY query_start;

  pg_stat_activity是实时监控的第一现场。state字段为active表示正在执行查询，idle in transaction表示在事务中空闲（可能持有锁，需警惕）。

• 查看锁信息：

  -- 查询当前阻塞和被阻塞的锁信息 SELECT blocked_locks.pid AS blocked_pid, blocked_activity.query AS blocked_query, blocking_locks.pid AS blocking_pid, blocking_activity.query AS blocking_query FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid != blocked_locks.pid JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid WHERE NOT blocked_locks.granted;

  这个查询能帮你找到“谁阻塞了谁”。死锁或长事务锁等待是导致应用卡顿的常见原因。

• 查看表与索引大小：

  -- 查询数据库中所有表的大小（包括索引） SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as total_size, pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) as table_size, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) as index_size FROM pg_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema') ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

  定期运行此查询，可以快速发现哪些表膨胀了，是否需要清理或分区。

3.3 维护命令：VACUUM与REINDEX

PostgreSQL的MVCC机制会导致“表膨胀”，即已删除或更新的数据行仍占据物理空间。VACUUM就是用来回收这些空间的。

• VACUUM：常规清理，回收空间供本表复用，但一般不返还给操作系统。它不会锁表，可以线上执行。

  VACUUM (VERBOSE, ANALYZE) my_table; -- VERBOSE输出详细信息，ANALYZE同时更新统计信息

• VACUUM FULL：激进清理，会锁表，并尽可能将空间返还给操作系统。对业务影响大，需在维护窗口进行。
• ANALYZE：更新表的统计信息，帮助查询规划器选择最优执行计划。通常和VACUUM一起做。
• REINDEX：重建索引，消除索引膨胀，恢复查询性能。REINDEX CONCURRENTLY可以在不阻塞读写的情况下重建索引，是PostgreSQL 12及以上版本的福音，但耗时更长。

  REINDEX INDEX CONCURRENTLY my_index; -- 并发重建单个索引 REINDEX TABLE CONCURRENTLY my_table; -- 并发重建表的所有索引

重要提示：从PostgreSQL 13开始，引入了“自动清理守护进程”（autovacuum），它通常能很好地处理常规的清理工作。你不需要手动频繁执行VACUUM。但是，对于更新/删除特别频繁的大表，或者一次性删除大量数据后，监控表膨胀情况并考虑手动干预仍然是必要的。不要轻易禁用autovacuum。

4. 高级技巧与扩展管理

掌握了基础运维后，这些命令能让你更游刃有余地利用PostgreSQL的高级特性。

4.1 扩展（Extension）管理

PostgreSQL通过扩展来增加功能，如支持GIS的PostGIS、生成UUID的uuid-ossp等。

• CREATE EXTENSION：安装扩展。

  CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; CREATE EXTENSION IF NOT EXISTS "pgcrypto"; -- 提供加密函数

• \dx：列出当前数据库中已安装的扩展。
• ALTER EXTENSION ... UPDATE：更新扩展版本。

注意事项：安装扩展通常需要超级用户权限。在生产环境，应由DBA统一管理。有些扩展（如postgis）会创建大量函数和类型，安装前请评估影响。

4.2 事务与保存点

在复杂的数据操作中，保存点（Savepoint）提供了事务内的“子回滚”能力。

BEGIN; -- 开始事务 INSERT INTO orders (user_id, amount) VALUES (1, 100); SAVEPOINT sp1; -- 设置保存点sp1 UPDATE accounts SET balance = balance - 100 WHERE user_id = 1; -- 假设这里有个检查，发现余额不足或其他业务逻辑错误 ROLLBACK TO SAVEPOINT sp1; -- 回滚到sp1，即撤销UPDATE，但INSERT仍然有效 -- 执行其他补救操作 INSERT INTO failed_logs (reason) VALUES ('Insufficient balance'); COMMIT; -- 最终提交，INSERT orders和INSERT failed_logs生效

这个机制在实现复杂业务逻辑的补偿动作时非常有用，避免了整个事务全部回滚。

4.3 复制与高可用相关命令（入门）

如果你涉及搭建主从复制，会用到这些命令。

• 查看复制状态：

  -- 在主库上查看发送状态 SELECT application_name, client_addr, state, sync_state, sent_lsn, write_lsn, flush_lsn, replay_lag FROM pg_stat_replication; -- 在从库上查看接收和应用状态 SELECT * FROM pg_stat_wal_receiver;

• 创建复制槽（用于逻辑复制或确保WAL日志不被过早删除）：

  SELECT * FROM pg_create_physical_replication_slot('my_slot_name');

• 提升从库为主库（故障切换时）：

  # 在从库服务器上执行 pg_ctl promote -D $PGDATA

这部分命令通常由自动化工具（如Patroni、repmgr）或运维脚本封装，但了解其底层原理对于排查复制延迟、切换失败等问题至关重要。

5. 常见问题排查与实用脚本速查

最后，我把一些高频的故障排查场景和实用的自检脚本整理出来，你可以把它们存成.sql文件，需要时直接运行。

5.1 连接数满额

应用报错“FATAL: sorry, too many clients already”。

1. 紧急处理：以超级用户身份连接（可能需要通过本地peer认证或修改pg_hba.conf临时允许），然后：

   -- 查看当前连接数限制 SHOW max_connections; -- 查看当前总连接数 SELECT count(*) FROM pg_stat_activity; -- 终止非活跃的、特定的或所有后端（谨慎！） SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid <> pg_backend_pid() -- 不终止自己 AND state = 'idle' -- 例如，终止所有空闲连接 AND (now() - state_change) > interval '10 minutes';

2. 根治：调整postgresql.conf中的max_connections参数并重启。但更重要的是，优化应用连接池配置（如HikariCP、DBCP），避免创建过多短连接。

5.2 查询慢

1. 定位慢查询：首先检查pg_stat_activity，找到state='active'且执行时间长的查询。
2. 分析执行计划：使用EXPLAIN (ANALYZE, BUFFERS)，它会实际执行语句并输出详细的计划树和耗时。

   EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM large_table WHERE some_column = 'value';

   重点关注：Seq Scan（全表扫描）是否在预期内？Index Scan是否被正确使用？Actual Rows和Estimate Rows是否相差巨大（统计信息可能过时）？Buffers显示了缓存命中情况。
3. 检查索引：确认查询条件列是否有索引，索引是否失效。

   -- 查看表上的索引 \d my_table -- 或使用SQL查询 SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'my_table';

5.3 磁盘空间不足

1. 定位空间占用：使用前面提到的查看表大小的脚本，找到最大的几张表。
2. 检查WAL日志：pg_wal目录（PostgreSQL 10之前是pg_xlog）可能因复制延迟或归档失败而堆积。

   du -sh $PGDATA/pg_wal/

3. 检查日志文件：log目录也可能很大。
4. 紧急清理：对于表膨胀，可以在业务低峰期对关键大表执行VACUUM FULL。但这是治标，需从业务上优化频繁更新/删除的模式，并调整autovacuum相关参数。

5.4 实用自检脚本合集

这里提供一个我常用的“健康检查”脚本，可以定期运行（例如通过cron job），将输出记录到日志中。

-- health_check.sql SELECT now() AS check_time; -- 1. 数据库大小排名 SELECT datname, pg_size_pretty(pg_database_size(datname)) as size FROM pg_database ORDER BY pg_database_size(datname) DESC LIMIT 5; -- 2. 表大小排名（前10） SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as total_size FROM pg_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema') ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC LIMIT 10; -- 3. 长事务（超过10分钟） SELECT pid, usename, application_name, client_addr, state, xact_start, now() - xact_start as duration, query FROM pg_stat_activity WHERE state LIKE '%transaction%' AND (now() - xact_start) > interval '10 minutes'; -- 4. 非活跃但未关闭的连接（超过1小时） SELECT pid, usename, application_name, client_addr, state, state_change, now() - state_change as idle_duration FROM pg_stat_activity WHERE state = 'idle' AND (now() - state_change) > interval '1 hour'; -- 5. 复制延迟（如果是从库） SELECT CASE WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() THEN 0 ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp()) END AS replay_lag_seconds;

掌握这些命令，并理解其背后的原理和应用场景，你就能在面对大多数PostgreSQL相关任务时保持从容。真正的熟练，来自于在具体项目中的反复实践和踩坑。建议你搭建一个自己的测试环境，把这些命令都亲手敲一遍，并结合EXPLAIN去分析不同的查询，感受索引和统计信息带来的变化。当你不再惧怕黑色的终端窗口，而是能通过它清晰地感知数据库的每一次脉搏时，你就真正拥有了驾驭数据的能力。