GeekIt

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 HEADERpsql 客户端导入 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.dump
慢查询 Top10SELECT query, calls, mean_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10
长事务与锁等待SELECT pid, now()-xact_start FROM pg_stat_activity ORDER BY 2 DESC LIMIT 5 与 SELECT * FROM pg_locks WHERE NOT granted
只读账号开通GRANT CONNECT ON DATABASE app_db TO ro; GRANT USAGE ON SCHEMA public TO ro; GRANT SELECT ON ALL TABLES IN SCHEMA public TO ro
在线建索引CREATE 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

共 146 条 PostgreSQL 命令 · psql 里用 \h 查看语法、\? 查看元命令帮助

关于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 面试与迁移评估。