learnNote

主题

附录 B：PostgreSQL 常用命令速查表 ​

开机即用的 PG 工具书。每条给「语法 → 例子 → 说明」三件套，Ctrl+F 搜关键词即可。 基于 PostgreSQL 16，低版本差异会标注。

目录 ​

  1. psql 元命令
  2. 数据库管理
  3. 角色与权限
  4. 表与索引
  5. DML 常用
  6. 事务
  7. 常用函数速查
  8. 执行计划与诊断
  9. 系统视图速查
  10. 服务端控制函数
  11. WAL / 复制
  12. 备份恢复 CLI
  13. 常调参数（最常见 20 个 GUC）
  14. 推荐 .psqlrc 配置 + 别名

1. psql 元命令 ​

1.1 连接 / 会话 ​

命令作用
\c dbname [user]切换数据库 / 用户
\conninfo显示当前连接
\q退出
\password [user]修改密码（不记日志）
\encoding utf8查看 / 设客户端字符集
bash
psql -h 127.0.0.1 -p 5432 -U postgres -d learn_pg

1.2 查看对象 ​

命令作用
\l / \l+列库（带 +：大小/权限）
\dn / \dt / \di / \dv / \dmv / \dsschema / 表 / 索引 / 视图 / 物化视图 / 序列
\df / \dT / \du / \dx函数 / 自定义类型 / 角色 / 扩展
\d tbl / \d+ tbl描述表（+ 带存储/统计）
\sf fn / \ef fn查看 / 编辑函数源代码
\dp / \z权限

1.3 输出控制 ​

命令作用
\x [on/off/auto]扩展输出（竖排显示），宽表必备
\timing on显示耗时
\pset format aligned/csv/html结果格式
\pset null '(NULL)'NULL 显示
\o file / \o结果转文件 / 取消

1.4 执行 / 编辑 ​

命令作用
\i file.sql / \ir file.sql执行 SQL 文件
\e用编辑器写 SQL
\! cmd执行 shell 命令
\watch 2每 2 秒重复上条 SQL（监控神器）

1.5 数据导入导出 ​

sql
-- 客户端路径（推荐）
\copy users(id,email) FROM '/tmp/users.csv' CSV HEADER;
\copy (SELECT * FROM orders WHERE created_at>=now()-interval '1 day') TO '/tmp/o.csv' CSV HEADER;

-- 服务端路径（需超级权 + postgres 进程能读）
COPY users FROM '/var/lib/pg/users.csv' CSV HEADER;

大坑：\copy 走客户端路径，COPY 走服务端路径，权限截然不同。

1.6 变量 ​

sql
\set uid 42
SELECT * FROM users WHERE id = :uid;      -- 引用
SELECT * FROM users WHERE name = :'NAME'; -- 单引号字符串

2. 数据库管理 ​

sql
CREATE DATABASE learn_pg
  OWNER postgres ENCODING 'UTF8'
  TEMPLATE template0 CONNECTION LIMIT 200;

ALTER DATABASE learn_pg SET timezone = 'Asia/Shanghai';
ALTER DATABASE learn_pg CONNECTION LIMIT 100;
DROP DATABASE IF EXISTS learn_pg WITH (FORCE);   -- PG13+ 可踢连接

-- Schema
CREATE SCHEMA app AUTHORIZATION app_user;
DROP SCHEMA app CASCADE;
SET search_path = app, public;                   -- 会话
ALTER ROLE app_user SET search_path = app, public;
ALTER DATABASE learn_pg SET search_path = app, public;

3. 角色与权限 ​

sql
-- 角色（USER 自带 LOGIN）
CREATE ROLE app_user LOGIN PASSWORD 'xx';
CREATE ROLE readonly;                        -- 权限组
ALTER  ROLE app_user CONNECTION LIMIT 50 NOSUPERUSER;
GRANT  readonly TO app_user;
DROP   ROLE IF EXISTS app_user;              -- 先 REASSIGN/DROP OWNED

-- 权限（由粗到细）
GRANT CONNECT ON DATABASE learn_pg TO readonly;
GRANT USAGE   ON SCHEMA app        TO readonly;
GRANT SELECT  ON ALL TABLES IN SCHEMA app TO readonly;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO app_user;

-- 默认权限（对未来新建对象生效）
ALTER DEFAULT PRIVILEGES IN SCHEMA app GRANT SELECT ON TABLES TO readonly;

SET ROLE app_user; RESET ROLE;
REASSIGN OWNED BY old_user TO new_user;
DROP OWNED BY old_user CASCADE;

4. 表与索引 ​

4.1 建表（含分区） ​

sql
CREATE TABLE users (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      text UNIQUE NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  extra      jsonb NOT NULL DEFAULT '{}'::jsonb,
  CHECK (length(email) <= 256)
);
COMMENT ON TABLE users IS '用户主表';

-- 改表（PG11+ 带常量默认值秒级）
ALTER TABLE users ADD COLUMN state smallint NOT NULL DEFAULT 0;
ALTER TABLE users ALTER COLUMN email TYPE citext USING email::citext;
ALTER TABLE users RENAME COLUMN state TO status;
ALTER TABLE users DROP COLUMN status;

-- 分区表（RANGE）
CREATE TABLE orders (
  id bigint, user_id bigint,
  created_at timestamptz NOT NULL,
  amount numeric(14,2)
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2026_01 PARTITION OF orders
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

4.2 索引 / 维护 ​

sql
CREATE INDEX idx_users_email ON users (email);
CREATE UNIQUE INDEX ON users (lower(email));
CREATE INDEX CONCURRENTLY idx_orders_uid ON orders (user_id);   -- 生产必备
CREATE INDEX ON orders (user_id, created_at DESC) INCLUDE (amount);  -- 覆盖
CREATE INDEX ON orders (user_id) WHERE status = 'active';            -- 部分
CREATE INDEX ON users (split_part(email,'@',2));                     -- 表达式
CREATE INDEX ON users USING GIN (extra jsonb_path_ops);              -- JSONB
CREATE INDEX ON docs  USING GIN (to_tsvector('english', body));      -- 全文
CREATE INDEX ON logs  USING BRIN (created_at);                       -- 时序大表

-- 维护
REINDEX INDEX CONCURRENTLY idx_users_email;
REINDEX TABLE CONCURRENTLY users;
CLUSTER users USING idx_users_email;          -- 物理排序，阻塞
TRUNCATE users RESTART IDENTITY CASCADE;
VACUUM (ANALYZE) users;                       -- 回收 + 统计
VACUUM (FULL) users;                          -- 阻塞，生产慎用！
ANALYZE users;

5. DML 常用 ​

sql
-- 行锁
SELECT * FROM orders WHERE id=1 FOR UPDATE;
SELECT * FROM orders WHERE id=1 FOR UPDATE NOWAIT;          -- 失败而非等待
SELECT id FROM jobs WHERE state='ready'
 ORDER BY id LIMIT 10
 FOR UPDATE SKIP LOCKED;                                     -- 任务队列

-- UPSERT
INSERT INTO users(id,email,name) VALUES(1,'a@x.com','Ann')
ON CONFLICT (email) DO UPDATE
  SET name = EXCLUDED.name, updated_at = now()
RETURNING id, xmax = 0 AS inserted;                          -- true 表示新插入

-- UPDATE FROM / DELETE USING
UPDATE orders o SET status='paid'
  FROM payments p WHERE o.id=p.order_id AND p.status='success';
DELETE FROM orders o
 USING payments p WHERE o.id=p.order_id AND p.refunded=true;

-- RETURNING
INSERT INTO users(email) VALUES('b@x.com') RETURNING id, created_at;
UPDATE users SET name='X' WHERE id=1 RETURNING *;
DELETE FROM users WHERE state=0 RETURNING id;

-- COPY（超高速）
COPY users(id,email) FROM STDIN WITH (FORMAT csv, HEADER true);
COPY (SELECT * FROM orders WHERE created_at>=now()-interval '1 day')
  TO '/tmp/orders.csv' WITH (FORMAT csv, HEADER true);

6. 事务 ​

sql
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  -- 默认
-- 或 REPEATABLE READ / SERIALIZABLE / READ ONLY

SAVEPOINT sp1;
  UPDATE users SET state=1 WHERE id=1;
ROLLBACK TO SAVEPOINT sp1;                       -- 回保存点，事务继续
RELEASE SAVEPOINT sp1;

COMMIT;  -- / END; / ROLLBACK;

-- 两阶段提交
BEGIN;
  INSERT INTO a VALUES (1);
PREPARE TRANSACTION 'tx_abc';
-- 之后
COMMIT PREPARED 'tx_abc';    -- 或 ROLLBACK PREPARED

-- 临时参数（仅本事务）
BEGIN;
  SET LOCAL work_mem = '256MB';
  SELECT ...;
COMMIT;

📌 PG 大坑：事务内一条出错后，后续 SQL 全被拒；用 SAVEPOINT 化解，或 psql 开 \set ON_ERROR_ROLLBACK interactive。

7. 常用函数速查 ​

7.1 字符串 ​

函数说明
length(s) / octet_length(s)字符数 / 字节数
lower / upper / substring(s FROM m FOR n)-
position(sub IN s)找不到返 0
regexp_replace(s,r,rep,'g')regexp_replace('abc123','\d','*','g') → abc***
regexp_matches(s,r)返 text[]
split_part('a@b.c','@',2) → b.c-
format('%I = %L', col, val)%I 标识符 / %L 文字量
concat / concat_ws(',',...)跳 NULL
coalesce(a,b,c) / nullif(a,b)空值处理
lpad('7',3,'0') → 007 / md5 / sha256-

7.2 数值 ​

sql
SELECT round(123.456, 2);         -- 123.46
SELECT ceil(1.1), floor(1.9), abs(-5), mod(10,3);
SELECT random();                  -- [0,1)
SELECT generate_series(1, 5);
SELECT generate_series('2026-01-01'::date, '2026-01-07', '1 day');

7.3 日期时间 ​

sql
SELECT now(), current_date, current_timestamp, localtimestamp;
SELECT now() AT TIME ZONE 'Asia/Shanghai';
SELECT age(timestamp '2000-01-01', timestamp '1990-06-15');
SELECT extract(year FROM now()), extract(dow FROM now());
SELECT date_trunc('day', now()), date_trunc('month', now());
SELECT to_char(now(), 'YYYY-MM-DD HH24:MI:SS');
SELECT to_timestamp('2026-04-17 10:30','YYYY-MM-DD HH24:MI');
SELECT now() + interval '7 days', now() - interval '2 hours';

7.4 聚合 ​

函数说明
count(*) / count(col)非 NULL 计数
sum / avg / min / max-
array_agg(c ORDER BY x)聚合为数组
string_agg(c,',' ORDER BY x)等价 GROUP_CONCAT
json_agg / jsonb_agg聚合为 JSON 数组
jsonb_object_agg(k,v)聚合为 JSON 对象
bool_or / bool_and布尔聚合
FILTER (WHERE cond)count(*) FILTER (WHERE x>0)
sql
SELECT user_id,
       count(*)                             AS total,
       count(*) FILTER (WHERE amount>100)   AS big_orders,
       string_agg(DISTINCT status,',' ORDER BY status) AS statuses,
       jsonb_agg(to_jsonb(o.*) ORDER BY id) AS items
  FROM orders o GROUP BY user_id;

7.5 窗口函数 ​

sql
SELECT user_id, amount,
       row_number() OVER w AS rn, rank() OVER w AS rk, dense_rank() OVER w AS drk,
       lag(amount)  OVER w AS prev, lead(amount) OVER w AS next,
       first_value(amount) OVER w AS first_amt,
       sum(amount) OVER (PARTITION BY user_id ORDER BY id
                         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS roll3
  FROM orders
WINDOW w AS (PARTITION BY user_id ORDER BY id);

7.6 JSON / JSONB ​

sql
-- 构造 / 取值
SELECT jsonb_build_object('id',1,'name','Tom');
SELECT jsonb_build_array(1,'x',true,null);
SELECT data -> 'user' ->> 'name' FROM t;     -- ->> 返回 text
SELECT data #>> '{user,name}'    FROM t;

-- 包含 / 键判断（走 GIN）
SELECT * FROM t WHERE data @> '{"role":"admin"}';
SELECT * FROM t WHERE data ? 'role';
SELECT * FROM t WHERE data ?| array['a','b'];

-- 修改（jsonb 整体重写，不可局部更新）
SELECT jsonb_set(data,'{user,name}','"Alice"'::jsonb, true);
SELECT data - 'key';                            -- 删键
SELECT data #- '{user,temp}';                   -- 按路径删
SELECT data || '{"age":18}'::jsonb;             -- 合并

-- 展开 / 路径查询
SELECT jsonb_array_elements(data->'items') FROM t;
SELECT jsonb_path_query(data,'$.items[*] ? (@.price > 100)') FROM t;

7.7 数组 ​

sql
SELECT ARRAY[1,2,3];
SELECT array_length(ARRAY[1,2,3],1);        -- 3
SELECT array_position(ARRAY['a','b','c'],'b');
SELECT unnest(ARRAY[10,20,30]);
SELECT * FROM t WHERE tags @> ARRAY['hot'];  -- 包含（走 GIN）
SELECT * FROM t WHERE tags && ARRAY['x','y']; -- 有交集

8. 执行计划与诊断 ​

sql
EXPLAIN SELECT * FROM users WHERE email='a@x.com';

-- 真实跑一遍（会真的执行 INSERT/UPDATE！）
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS, WAL, TIMING)
SELECT ...;

-- 避免副作用
BEGIN;
  EXPLAIN (ANALYZE, BUFFERS) UPDATE t SET x=1 WHERE id=1;
ROLLBACK;

-- JSON 格式（供程序解析）
EXPLAIN (ANALYZE, FORMAT JSON) SELECT ...;

扫描节点含义速记：

节点含义
Seq Scan全表扫描
Index Scan索引扫 + 回表
Index Only Scan覆盖索引 / VM 可见 → 不回表
Bitmap Heap/Index Scan多条件合并或区间
Nested Loop / Hash Join / Merge Join三大 JOIN
Gather / Gather Merge并行

抓慢 SQL：

sql
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT calls, total_exec_time, mean_exec_time, rows, left(query,120)
  FROM pg_stat_statements
ORDER BY mean_exec_time DESC LIMIT 20;

自动记录慢计划（postgresql.conf）：

shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '500ms'
auto_explain.log_analyze = on

9. 系统视图速查 ​

活动会话 / 长事务：

sql
-- 活跃 + 找长事务
SELECT pid, usename, state, wait_event, now()-xact_start AS xact_age, left(query,120) q
  FROM pg_stat_activity
 WHERE state <> 'idle' AND (xact_start IS NULL OR now()-xact_start > interval '5 min')
 ORDER BY xact_start NULLS LAST;

表 / 索引统计：

sql
-- 死元组情况
SELECT schemaname, relname, n_live_tup, n_dead_tup,
       round(100.0*n_dead_tup/nullif(n_live_tup+n_dead_tup,0),2) AS dead_pct,
       last_autovacuum
  FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 20;

-- 从未用过的索引
SELECT relname, indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
  FROM pg_stat_user_indexes ORDER BY idx_scan;

-- 表 / 索引大小 TOP
SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS total,
       pg_size_pretty(pg_indexes_size(relid)) AS idx_size
  FROM pg_catalog.pg_statio_user_tables
 ORDER BY pg_total_relation_size(relid) DESC LIMIT 20;

锁：

sql
-- 谁阻塞谁
SELECT blocked.pid AS blocked, blocking.pid AS blocking,
       left(blocked.query,80) bq, left(blocking.query,80) gq
  FROM pg_stat_activity blocked
  JOIN pg_stat_activity blocking ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));

SELECT locktype, relation::regclass, mode, granted, pid FROM pg_locks
 WHERE NOT granted OR relation IS NOT NULL;

复制：

sql
-- 主库
SELECT client_addr, state, sync_state, replay_lag,
       pg_wal_lsn_diff(sent_lsn,replay_lsn) AS lag_bytes FROM pg_stat_replication;
-- 备库
SELECT pg_is_in_recovery(), pg_last_wal_replay_lsn(), pg_last_xact_replay_timestamp();

其它常用目录：

视图作用
pg_settingsGUC 所有参数、当前值、生效上下文
pg_stat_bgwriter后台写 / checkpoint 统计
pg_class / pg_index / pg_constraint / pg_attribute表/索引/约束/列元数据
pg_replication_slots复制槽
pg_available_extensions可装扩展列表
sql
SELECT name,setting,unit,context FROM pg_settings
 WHERE name IN ('shared_buffers','work_mem','max_connections');

10. 服务端控制函数 ​

sql
SELECT pg_reload_conf();                    -- 热重载配置

SELECT pg_cancel_backend(12345);            -- 取消 query（软）
SELECT pg_terminate_backend(12345);         -- 终止会话（硬）

-- 批量清 idle in transaction
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
 WHERE state='idle in transaction' AND now()-xact_start > interval '5 min';

-- 主备切换
SELECT pg_is_in_recovery(), pg_promote();   -- PG12+ promote
SELECT pg_wal_replay_pause(), pg_wal_replay_resume();

-- 复制槽
SELECT pg_create_physical_replication_slot('slot_standby1');
SELECT pg_create_logical_replication_slot('slot_cdc','pgoutput');
SELECT pg_drop_replication_slot('slot_cdc');

CHECKPOINT;                                  -- 会 I/O 抖动，慎用

11. WAL / 复制 ​

sql
-- WAL 位点
SELECT pg_current_wal_lsn(), pg_current_wal_insert_lsn(), pg_current_wal_flush_lsn();

-- 距离（字节）
SELECT pg_wal_lsn_diff('0/ABC12345','0/ABC10000');

-- LSN → 文件
SELECT pg_walfile_name(pg_current_wal_lsn());

-- 复制槽滞留大小
SELECT slot_name, active, restart_lsn,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),restart_lsn)) AS lag
  FROM pg_replication_slots;

-- 逻辑复制
CREATE PUBLICATION  pub_all  FOR ALL TABLES;
CREATE PUBLICATION  pub_some FOR TABLE users, orders;
CREATE SUBSCRIPTION sub_x
  CONNECTION 'host=10.0.0.1 dbname=learn_pg user=repl password=xx'
  PUBLICATION pub_some;

12. 备份恢复 CLI ​

bash
# 逻辑备份（定制格式 + 并行）
pg_dump -d learn_pg -F c -j 4 --no-owner --no-privileges -f learn_pg.dump
# 只备 / 排除表
pg_dump -d learn_pg -t 'public.users' -F c -f u.dump
pg_dump -d learn_pg -T 'public.logs*' -F c -f nolog.dump
# 全局对象（角色 / 表空间）
pg_dumpall -g > globals.sql

# 恢复
createdb -O postgres learn_pg_new
pg_restore -d learn_pg_new -j 4 learn_pg.dump
psql -d learn_pg_new -f learn_pg.sql          # SQL 文件直接

# 物理备份（在线）
pg_basebackup -h 127.0.0.1 -U replicator -D /backup/base \
              -F t -z -P --wal-method=stream --slot=slot_backup

# 实用工具
pg_isready -h 127.0.0.1 -p 5432
pg_controldata $PGDATA                        # LSN / timeline
pg_waldump -p /pg/wal 000000010000000000000001
vacuumdb -d learn_pg -z -j 4                  # 并行 VACUUM ANALYZE
reindexdb -d learn_pg --concurrently -t users

13. 常调参数（最常见 20 个 GUC） ​

参数典型值说明
max_connections200~500超 300 必配 PgBouncer
shared_buffers内存 25%非越大越好（双缓存）
effective_cache_size内存 50~75%规划器参考值（不分配内存）
work_mem4MB~64MB每节点可能都用一份
maintenance_work_mem256MB~2GBVACUUM / CREATE INDEX 专用
checkpoint_timeout15min两次 checkpoint 最大间隔
checkpoint_completion_target0.9I/O 平摊
max_wal_size / min_wal_size4GB / 1GBWAL 累计上限
wal_levelreplica / logical逻辑复制需 logical
max_wal_senders / max_replication_slots10 / 10-
synchronous_commiton / remote_apply同步级别
synchronous_standby_namesANY 1 (*)同步备
random_page_costSSD 1.1 / HDD 4随机读代价
effective_io_concurrencySSD 200 / HDD 2并发 IO 预取
autovacuumon（不要关）-
autovacuum_vacuum_scale_factor大表降到 0.02触发阈值
idle_in_transaction_session_timeout10min~30min必配
statement_timeout30s 或按业务单 SQL 超时
log_min_duration_statement500ms慢 SQL 阈值
timezoneAsia/Shanghai / UTC会话时区

改法：

sql
SET work_mem = '256MB';                                 -- 会话
SET LOCAL work_mem = '256MB';                           -- 仅本事务
ALTER DATABASE learn_pg SET timezone = 'Asia/Shanghai'; -- 库级
ALTER ROLE app_user SET statement_timeout = '30s';      -- 角色级
ALTER SYSTEM SET max_connections = 300;                 -- 集群级
SELECT pg_reload_conf();

14. 推荐 .psqlrc 配置 + 别名 ​

sql
-- ~/.psqlrc
\timing on
\set ON_ERROR_ROLLBACK interactive       -- 事务内错不废整事务
\x auto                                   -- 宽表竖排
\pset null '¤'
\pset linestyle unicode
\set HISTFILE ~/.psql_history- :DBNAME
\set HISTCONTROL ignoredups
\set COMP_KEYWORD_CASE upper

-- 别名：直接 :activity / :blocks / :dead
\set activity 'SELECT pid,usename,state,wait_event,left(query,80) q FROM pg_stat_activity WHERE state<>''idle'' ORDER BY xact_start NULLS LAST;'
\set blocks   'SELECT blocked.pid, blocking.pid, left(blocked.query,60), left(blocking.query,60) FROM pg_stat_activity blocked JOIN pg_stat_activity blocking ON blocking.pid=ANY(pg_blocking_pids(blocked.pid));'
\set dead     'SELECT schemaname||''.''||relname, n_live_tup, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 20;'

Shell 小工具（~/.bashrc）：

bash
alias pg='psql -h 127.0.0.1 -U postgres -d learn_pg'
alias pgbk='pg_dump -h 127.0.0.1 -U postgres -F c -j 4 --no-owner --no-privileges'

📌 用法：第一次通读 → 日常 Ctrl+F 查。搭配附录 A（PG vs MySQL）、附录 C（踩坑集）三件套一起收藏。