PostgreSQL命令速查
psql 元命令、库表管理、JSONB、执行计划、备份复制与诊断等 146 条命令
共 146 条命令 · 10 类场景 · 点击右侧图标可复制
| 命令 / SQL | 说明 | 复制 |
|---|---|---|
psql -h 10.0.0.5 -p 5432 -U postgres -d app_db | 连接指定主机、端口、用户与库 | |
psql "postgresql://app:密码@10.0.0.5:5432/app_db?sslmode=require" | 用连接串直接连接并强制 SSL | |
psql -c "SELECT now();" -d app_db | 不进入交互界面执行单条 SQL | |
psql -f migration.sql -d app_db | 执行 SQL 脚本文件 | |
psql -v ON_ERROR_STOP=1 -f init.sql | 遇错立即中止,脚本化执行必加 | |
\l | 列出所有数据库(等价 \list) | |
\c app_db | 切换到另一个数据库 | |
\dt | 列出当前 schema 的表(\dt+ 带大小) | |
\d user | 查看表结构、索引与约束 | |
\d+ user | 查看表结构并附带存储与描述信息 | |
\dn | 列出所有 schema | |
\du | 列出数据库角色与权限 | |
\df | 列出函数(\df+ 查看定义) | |
\dv 与 \di | 列出视图与索引 | |
\x | 切换纵向显示,宽表查询更易读 | |
\timing | 显示每条语句执行耗时 | |
\pset pager off | 关闭分页器,避免结果卡在 less 里 | |
\conninfo | 查看当前连接信息 | |
\h SELECT | 查看 SQL 语法帮助 | |
\q | 退出 psql |
| 命令 / SQL | 说明 | 复制 |
|---|---|---|
CREATE DATABASE app_db WITH ENCODING "UTF8" TEMPLATE template0; | 创建指定编码的数据库 | |
ALTER DATABASE app_db RENAME TO app_db_old; | 重命名数据库 | |
DROP DATABASE app_db; | 删除数据库(需无活动连接) | |
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = "app_db"; | 先踢掉连接再删库 | |
CREATE SCHEMA app; | 创建 schema 隔离业务 | |
SET search_path TO app, public; | 设置当前会话的 schema 搜索顺序 | |
CREATE TABLE app.user (id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name TEXT NOT NULL, created_at TIMESTAMPTZ DEFAULT now()); | 建表并指定自增主键、非空与默认值 | |
CREATE TABLE log (LIKE user INCLUDING ALL); | 复制表结构(含索引与约束) | |
ALTER TABLE user ADD COLUMN email TEXT; | 新增列 | |
ALTER TABLE user ALTER COLUMN email SET NOT NULL; | 给已有列加非空约束 | |
ALTER TABLE user RENAME COLUMN email TO mail; | 重命名列 | |
ALTER TABLE user DROP COLUMN mail; | 删除列 | |
TRUNCATE TABLE log RESTART IDENTITY CASCADE; | 清空表并重置自增、级联删除关联数据 | |
DROP TABLE IF EXISTS log CASCADE; | 删除表及依赖对象 | |
COMMENT ON TABLE user IS "用户表"; | 添加表注释,便于协作 | |
SELECT table_name, pg_size_pretty(pg_total_relation_size(quote_ident(table_name))) FROM information_schema.tables WHERE table_schema = "public" ORDER BY pg_total_relation_size(quote_ident(table_name)) DESC LIMIT 10; | 找出占用空间最大的 10 张表 |
| 命令 / SQL | 说明 | 复制 |
|---|---|---|
SELECT * FROM user LIMIT 20 OFFSET 40; | 分页查询 | |
SELECT * FROM user ORDER BY created_at DESC NULLS LAST; | 排序并指定空值位置 | |
SELECT DISTINCT ON (city) city, name FROM user ORDER BY city, created_at DESC; | PG 专有:每个城市取最新一条 | |
SELECT * FROM user WHERE name ILIKE "%张%"; | 忽略大小写的模糊匹配 | |
SELECT * FROM user WHERE name ~ "^张"; | 正则匹配;~* 忽略大小写 | |
SELECT * FROM user WHERE created_at >= now() - INTERVAL "7 day"; | 相对时间范围查询 | |
SELECT date_trunc("day", created_at) d, COUNT(*) FROM orders GROUP BY d ORDER BY d DESC LIMIT 7; | 按天统计,date_trunc 支持 hour/month 等 | |
SELECT id, name, amount::numeric(10,2) FROM orders; | 类型转换写法(:: 或 CAST) | |
SELECT * FROM user WHERE id = ANY(ARRAY[1,2,3]); | 数组条件匹配 | |
SELECT unnest(ARRAY["a","b","c"]) AS item; | 把数组展开成多行 | |
SELECT data->"name", data->>"age" FROM events; | JSONB 取字段:->> 返回文本 | |
SELECT * FROM events WHERE data @> '{"type":"login"}'; | JSONB 包含匹配,可走 GIN 索引 | |
SELECT * FROM events WHERE data->>"level" = "error"; | 按 JSON 字段过滤 | |
SELECT generate_series(1, 10) AS n; | 生成序列,造测试数据很方便 | |
INSERT INTO user (name) VALUES ("tom") RETURNING id, created_at; | 插入并返回生成值,免二次查询 | |
SELECT * FROM user WHERE name = "tom" UNION ALL SELECT * FROM user_archive WHERE name = "tom"; | 合并查询结果(保留重复) | |
WITH t AS (SELECT * FROM orders WHERE amount > 1000) SELECT COUNT(*) FROM t; | CTE 写法,复杂查询可读性更好 |
| 命令 / SQL | 说明 | 复制 |
|---|---|---|
EXPLAIN SELECT * FROM user WHERE name = "tom"; | 查看执行计划(预估) | |
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM user WHERE name = "tom"; | 实际执行并输出耗时与缓存命中,排障首选 | |
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ...; | JSON 格式计划,便于工具分析 | |
CREATE INDEX idx_user_name ON user (name); | 创建 B-tree 索引 | |
CREATE INDEX CONCURRENTLY idx_user_email ON user (email); | 在线建索引,不阻塞写入(失败会留无效索引) | |
CREATE INDEX idx_events_data ON events USING GIN (data); | JSONB 字段建 GIN 索引,加速 @> 查询 | |
CREATE INDEX idx_user_city_partial ON user (city) WHERE status = 1; | 部分索引,只索引活跃数据 | |
CREATE INDEX idx_user_lower_name ON user (lower(name)); | 表达式索引,配合 lower 查询使用 | |
DROP INDEX CONCURRENTLY idx_user_email; | 在线删除索引 | |
REINDEX INDEX idx_user_name; | 重建索引(表膨胀或损坏时用) | |
SELECT relname, indexrelname, idx_scan FROM pg_stat_user_indexes ORDER BY idx_scan LIMIT 10; | 找出从未被使用的索引 | |
VACUUM VERBOSE ANALYZE user; | 回收死元组并更新统计信息 | |
SELECT * FROM pg_stat_progress_vacuum; | 查看 vacuum 进度 | |
ALTER TABLE user SET (autovacuum_vacuum_scale_factor = 0.05); | 针对大表调紧 autovacuum 触发阈值 |
| 命令 / SQL | 说明 | 复制 |
|---|---|---|
BEGIN; | 开启事务 | |
COMMIT; | 提交事务 | |
ROLLBACK; | 回滚事务 | |
SAVEPOINT sp1; 与 ROLLBACK TO sp1; | 部分回滚到保存点 | |
SHOW transaction_isolation; | 查看当前隔离级别(默认 read committed) | |
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; | 当前事务改为可重复读 | |
SELECT * FROM task WHERE status = 0 LIMIT 10 FOR UPDATE SKIP LOCKED; | 队列取任务的标准写法,跳过被锁行 | |
SELECT pg_try_advisory_lock(12345); | 尝试获取会话级建议锁,做分布式互斥 | |
SELECT pg_advisory_unlock(12345); | 释放建议锁 | |
SELECT pid, state, wait_event_type, query FROM pg_stat_activity WHERE state != "idle"; | 查看活动会话与等待事件 | |
SELECT * FROM pg_locks WHERE NOT granted; | 查看未获得锁的等待者 | |
SELECT pg_terminate_backend(12345); | 终止指定会话(先确认 SQL 内容) | |
SELECT pg_cancel_backend(12345); | 取消当前语句但保留连接,比终止更温和 | |
SELECT pid, now() - xact_start AS dur FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY dur DESC LIMIT 5; | 找出最长的长事务 |
| 命令 / SQL | 说明 | 复制 |
|---|---|---|
CREATE ROLE app LOGIN PASSWORD "密码"; | 创建可登录账号 | |
CREATE ROLE readonly NOLOGIN; | 创建组角色,便于批量授权 | |
ALTER ROLE app WITH CONNECTION LIMIT 50; | 限制账号最大连接数 | |
GRANT CONNECT ON DATABASE app_db TO app; | 允许连接数据库 | |
GRANT USAGE ON SCHEMA app TO readonly; | 授予 schema 使用权限(不加这句查不到表) | |
GRANT SELECT ON ALL TABLES IN SCHEMA app TO readonly; | 授予现有表只读权限 | |
ALTER DEFAULT PRIVILEGES IN SCHEMA app GRANT SELECT ON TABLES TO readonly; | 让后续新建表自动继承权限 | |
REVOKE DELETE ON app.user FROM app; | 回收删除权限 | |
GRANT app TO readonly; | 把权限角色授予用户,继承其权限 | |
\du+ | 查看角色及其成员关系 | |
SELECT rolname, rolsuper, rolcanlogin, rolconnlimit FROM pg_roles; | 查询角色属性 | |
SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE table_name = "user"; | 查看某张表的授权情况 | |
SELECT pg_reload_conf(); | 修改 pg_hba.conf 后重载配置 | |
SELECT * FROM pg_hba_file_rules WHERE error IS NOT NULL; | 校验 pg_hba 配置是否有语法错误 |
| 命令 / SQL | 说明 | 复制 |
|---|---|---|
pg_dump -h 10.0.0.5 -U postgres -Fc app_db > app_db.dump | 自定义格式备份,支持并行恢复 | |
pg_dump -h 10.0.0.5 -U postgres -Fp -f app_db.sql app_db | 导出纯 SQL 文本,便于查看与修改 | |
pg_dump -t user -t orders -U postgres app_db > part.sql | 只备份指定表 | |
pg_dump -s -U postgres app_db > schema.sql | 只导出结构(schema) | |
pg_dumpall -U postgres > all.sql | 备份全部数据库与全局对象(含角色) | |
pg_restore -h 10.0.0.5 -U postgres -d app_db -j 4 app_db.dump | 并行恢复,4 个任务提升速度 | |
pg_restore -l app_db.dump > toc.list | 导出备份目录清单,可编辑后选择性恢复 | |
psql -U postgres -d app_db -f app_db.sql | 纯文本备份用 psql 恢复 | |
psql -U postgres -c "COPY user TO STDOUT WITH CSV HEADER" > user.csv | 导出 CSV(服务端 COPY) | |
\copy user FROM "/data/user.csv" WITH CSV HEADER | psql 客户端导入 CSV,权限要求更低 | |
pg_basebackup -h 10.0.0.5 -D /data/pgdata -U replicator -Fp -Xs -P -R | 物理基础备份并生成复制配置 | |
createdb -h 10.0.0.5 -U postgres app_new && pg_isready -h 10.0.0.5 | 快速建库并检测服务可用性 |
| 命令 / SQL | 说明 | 复制 |
|---|---|---|
SELECT * FROM pg_stat_replication; | 主库查看从库状态、发送与回放位点 | |
SELECT * FROM pg_stat_wal_receiver; | 从库查看接收状态与上游地址 | |
SELECT pg_current_wal_lsn(); | 查看主库当前 WAL 位点 | |
SELECT pg_last_wal_replay_lsn(); | 从库查看已回放位点,算延迟用 | |
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes FROM pg_stat_replication; | 计算复制延迟字节数 | |
SHOW synchronous_commit; | 查看同步提交级别(on/remote_apply 影响一致性) | |
SELECT * FROM pg_replication_slots; | 查看复制槽,确认是否有卡住的槽导致 WAL 堆积 | |
SELECT pg_create_physical_replication_slot("replica1"); | 创建物理复制槽 | |
pg_ctl promote -D /data/pgdata | 从库提升为主库(故障切换) | |
touch /data/pgdata/standby.signal | 标记为从库并重启进入恢复模式 | |
pg_rewind -D /data/pgdata --source-server="host=10.0.0.5 port=5432 user=postgres" | 主从切换后把旧主快速回同步为从库 | |
SELECT pg_switch_wal(); | 手动切换 WAL 段,便于归档测试 |
| 命令 / SQL | 说明 | 复制 |
|---|---|---|
SELECT pid, usename, state, query_start, query FROM pg_stat_activity WHERE state = "active" ORDER BY query_start; | 查看正在执行的语句及开始时间 | |
CREATE EXTENSION IF NOT EXISTS pg_stat_statements; | 启用语句级统计扩展,慢查询分析必备 | |
SELECT calls, round(total_exec_time::numeric,1) AS ms, round(mean_exec_time::numeric,1) AS avg_ms, query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10; | 按总耗时排序找出最耗资源的 SQL | |
SELECT round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS hit_ratio FROM pg_stat_database WHERE datname = "app_db"; | 缓冲池命中率,低于 95% 需关注 | |
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10; | 查看死元组最多的表,判断是否需要 vacuum | |
SELECT pg_size_pretty(pg_database_size("app_db")); | 查看数据库总大小 | |
SELECT pg_size_pretty(pg_total_relation_size("user")); | 查看表含索引的总大小 | |
SELECT pg_size_pretty(pg_table_size("user")), pg_size_pretty(pg_indexes_size("user")); | 分别查看数据与索引占用 | |
SELECT schemaname, relname, seq_scan, idx_scan FROM pg_stat_user_tables ORDER BY seq_scan DESC LIMIT 10; | 全表扫描最多的表,往往缺索引 | |
ALTER SYSTEM SET log_min_duration_statement = 1000; | 记录超过 1 秒的 SQL 到日志 | |
ALTER SYSTEM SET shared_preload_libraries = "pg_stat_statements, auto_explain"; | 加载慢日志与自动执行计划扩展(需重启) | |
SHOW work_mem 与 SHOW shared_buffers 与 SHOW max_connections; | 查看关键内存与连接参数 |
| 命令 / SQL | 说明 | 复制 |
|---|---|---|
SHOW config_file; | 查看 postgresql.conf 路径 | |
SHOW all; | 查看全部配置项 | |
ALTER SYSTEM SET work_mem = "16MB"; | 持久化修改参数到 auto.conf | |
SELECT pg_reload_conf(); | 重载配置(仅对 reload 生效的参数有效) | |
ALTER SYSTEM RESET work_mem; | 恢复参数默认值 | |
pg_ctl status -D /data/pgdata | 查看实例运行状态 | |
pg_ctl reload -D /data/pgdata | 命令行重载配置 | |
pg_ctl restart -D /data/pgdata -m fast | 快速重启实例 | |
pg_ctl stop -D /data/pgdata -m smart | 等待连接结束后优雅停止 | |
pg_isready -h 10.0.0.5 -p 5432 | 检测服务是否可连接,监控脚本常用 | |
CHECKPOINT; | 手动触发检查点,落盘脏页 | |
SELECT pg_control_checkpoint(); | 查看检查点与 WAL 信息 | |
pg_controldata /data/pgdata | 查看控制文件状态与集群版本 | |
SELECT * FROM pg_stat_bgwriter; | 查看后台写进程统计,评估 IO 压力 | |
du -sh /data/pgdata/pg_wal | 检查 WAL 目录体积,异常增大常有复制槽卡住 |
运维常用组合
psql -h 10.0.0.5 -U postgres -d app_db -c "\dt+"pg_dump -Fc app_db > app_db.dump 与 pg_restore -d app_db -j 4 app_db.dumpSELECT query, calls, mean_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10SELECT pid, now()-xact_start FROM pg_stat_activity ORDER BY 2 DESC LIMIT 5 与 SELECT * FROM pg_locks WHERE NOT grantedGRANT CONNECT ON DATABASE app_db TO ro; GRANT USAGE ON SCHEMA public TO ro; GRANT SELECT ON ALL TABLES IN SCHEMA public TO roCREATE INDEX CONCURRENTLY idx_name ON app.user (name)常见问题排查
1. password authentication failed
多为 pg_hba.conf 认证方式或密码不对:改完执行 SELECT pg_reload_conf() 生效
2. too many connections
连接打满:查 pg_stat_activity 找来源,调整 max_connections 或上连接池(pgbouncer)
3. 查询突然变慢
先 EXPLAIN (ANALYZE, BUFFERS),再 ANALYZE 表更新统计,必要时补索引或调整 work_mem
4. 表持续膨胀
长事务阻止 vacuum:查 xact_start 最久的会话,并检查 autovacuum 参数是否过松
5. 主从延迟变大
pg_stat_replication 看 replay_lsn 差值,排查大事务、IO 瓶颈与网络带宽
6. WAL 目录暴涨
检查 pg_replication_slots 是否有失效槽,及时 pg_drop_replication_slot
相关工具
Linux命令速查
文件、文本、权限、进程、网络、磁盘、systemd与防火墙等 Linux 常用命令速查手册
Docker命令速查
镜像、容器、网络、数据卷、Compose、Swarm、清理与排障等 Docker 常用命令速查手册
Nginx命令速查
服务管理、配置指令、反向代理、HTTPS、限流缓存、日志排查等 Nginx 常用命令速查手册
K8S命令速查
集群上下文、Pod调试、工作负载、Service与Ingress、存储、权限、排障等 kubectl 常用命令速查手册
Windows CMD命令速查
文件目录、网络诊断、进程服务、系统信息、用户权限、注册表、批处理等 Windows CMD 常用命令速查手册
systemctl服务管理速查
服务启停、开机自启、unit文件编写、日志查看、定时器、依赖分析与排障等 systemctl 命令速查手册
关于PostgreSQL命令速查
在线 PostgreSQL 命令速查手册,按 psql 连接与元命令、库表与模式管理、查询与数据类型、索引与执行计划、事务与并发、用户与权限、备份恢复与迁移、复制与高可用、性能诊断、配置与维护等 10 类场景整理 140+ 条常用命令与 SQL,每条附中文说明与典型写法,支持关键词搜索、分类折叠和一键复制,打开网页即可查阅。涵盖 psql -h -U -d -c -f、\l \c \dt \d \dn \du \x \timing 元命令、CREATE DATABASE 与 schema/search_path、serial 与 IDENTITY、JSONB 的 -> 与 ->> 操作符、generate_series、date_trunc、RETURNING、CREATE INDEX CONCURRENTLY 与 GIN/GiST/BRIN、EXPLAIN (ANALYZE, BUFFERS)、VACUUM 与 ANALYZE、SELECT FOR UPDATE SKIP LOCKED、pg_stat_activity 与 pg_terminate_backend、grants 与 default privileges、pg_dump -Fc 与 pg_restore、COPY 与 \copy、pg_basebackup、pg_stat_replication、pg_stat_statements 慢查询排行、pg_database_size 与表膨胀检查、ALTER SYSTEM 与 pg_reload_conf 等高频用法。适用于 PostgreSQL 日常运维、慢查询与锁等待排查、备份恢复与主从搭建、表膨胀与 vacuum 维护,以及 DBA 面试与迁移评估。
核心能力
- 涵盖 psql -h -U -d -c -f、\l \c \dt \d \dn \du \x \timing 元命令、CREATE DATABASE 与 schema/search_path、serial 与 IDENTITY、JSONB 的 -> 与 ->> 操作符、generate_series、date_trunc、RETURNING、CREATE INDEX CONCURRENTLY 与 GIN/GiST/BRIN、EXPLAIN (ANALYZE, BUFFERS)、VACUUM 与 ANALYZE、SELECT FOR UPDATE SKIP LOCKED、pg_stat_activity 与 pg_terminate_backend、grants 与 default privileges、pg_dump -Fc 与 pg_restore、COPY 与 \copy、pg_basebackup、pg_stat_replication、pg_stat_statements 慢查询排行、pg_database_size 与表膨胀检查、ALTER SYSTEM 与 pg_reload_conf 等高频用法
适用场景
适用于 PostgreSQL 日常运维、慢查询与锁等待排查、备份恢复与主从搭建、表膨胀与 vacuum 维护,以及 DBA 面试与迁移评估。PostgreSQL命令速查全程在浏览器中完成,无需安装插件,可放心用于日常办公与排障。
常见问题
- PostgreSQL命令速查是免费的吗?需要注册或安装吗?
- PostgreSQL命令速查完全免费,无需注册、无需下载安装任何软件,用浏览器打开网页即可直接使用,也不限制使用次数。
- PostgreSQL命令速查支持哪些功能?
- 涵盖 psql -h -U -d -c -f、\l \c \dt \d \dn \du \x \timing 元命令、CREATE DATABASE 与 schema/search_path、serial 与 IDENTITY、JSONB 的 -> 与 ->> 操作符、generate_series、date_trunc、RETURNING、CREATE INDEX CONCURRENTLY 与 GIN/GiST/BRIN、EXPLAIN (ANALYZE, BUFFERS)、VACUUM 与 ANALYZE、SELECT FOR UPDATE SKIP LOCKED、pg_stat_activity 与 pg_terminate_backend、grants 与 default privileges、pg_dump -Fc 与 pg_restore、COPY 与 \copy、pg_basebackup、pg_stat_replication、pg_stat_statements 慢查询排行、pg_database_size 与表膨胀检查、ALTER SYSTEM 与 pg_reload_conf 等高频用法。
- PostgreSQL命令速查适合哪些场景使用?
- 适用于 PostgreSQL 日常运维、慢查询与锁等待排查、备份恢复与主从搭建、表膨胀与 vacuum 维护,以及 DBA 面试与迁移评估。