附录：附录：PostgreSQL 速查手册

zjc 于 2026-02-03 发布

这是《PostgreSQL 零基础实战指南》的独立章节版。本章从概念、实操和生产排查三个视角展开，代码块保留了原书可直接运行的版本。

连接与查看

psql -h 127.0.0.1 -U postgres -d shop
psql -h pg.internal -U app_user -d app -f schema.sql

SELECT version();
SELECT current_database(), current_user;
SHOW data_directory;
SHOW shared_buffers;
SHOW work_mem;

psql

\l        数据库列表
\c db     切换数据库
\dt       表列表
\d table  表结构
\di       索引
\du       用户角色
\x        扩展显示
\timing   计时
\q        退出

建表模板

CREATE TABLE orders (
    id bigint GENERATED ALWAYS AS IDENTITY,
    order_no text NOT NULL,
    user_id bigint NOT NULL,
    status text NOT NULL DEFAULT 'CREATED',
    amount numeric(12,2) NOT NULL CHECK (amount >= 0),
    created_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT pk_orders PRIMARY KEY (id),
    CONSTRAINT uq_orders_order_no UNIQUE (order_no)
);

常用索引

CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at DESC);

CREATE INDEX idx_orders_open
ON orders(created_at)
WHERE status IN ('CREATED', 'PAID');

CREATE INDEX idx_events_payload
ON events USING GIN (payload jsonb_path_ops);

CREATE INDEX idx_events_created_brin
ON events USING BRIN (created_at);

执行计划

EXPLAIN ANALYZE
SELECT ...
;

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...
;

重点：

扫描节点
rows vs actual rows
loops
Sort / Hash 内存
Shared Hit / Read
Rows Removed by Filter

SQL 常用

INSERT INTO users(name, city)
VALUES ('Alice', 'Shanghai')
ON CONFLICT (email) DO NOTHING;

UPDATE orders
SET status = 'PAID', paid_at = now()
WHERE order_no = 'O001'
RETURNING id, amount;

SELECT DISTINCT ON (user_id)
    user_id, id, amount, created_at
FROM orders
ORDER BY user_id, created_at DESC;

事务

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

超时：

SET statement_timeout = '30s';
SET lock_timeout = '3s';
SET idle_in_transaction_session_timeout = '10min';

活动与锁

SELECT pid, state, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE state <> 'idle';

SELECT blocked.pid AS blocked_pid,
       blocked.query AS blocked_query,
       blocking.pid AS blocking_pid,
       blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid AND NOT bl.granted
JOIN pg_locks ul ON ul.granted
 AND ul.locktype = bl.locktype
 AND ul.database IS NOT DISTINCT FROM bl.database
 AND ul.relation IS NOT DISTINCT FROM bl.relation
JOIN pg_stat_activity blocking ON blocking.pid = ul.pid
WHERE blocked.wait_event_type = 'Lock';

VACUUM 与统计

ANALYZE orders;
VACUUM ANALYZE orders;

SELECT relname, n_live_tup, n_dead_tup,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

复制与 WAL

SELECT client_addr, state, sync_state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_bytes
FROM pg_stat_replication;

SELECT slot_name, slot_type, active, restart_lsn
FROM pg_replication_slots;

SELECT archived_count, failed_count
FROM pg_stat_archiver;

备份恢复

pg_dump -Fc -d shop -f shop.dump
pg_restore -d shop_restore shop.dump
pg_basebackup -h primary -U replicator -D standby -Fp -Xs -P -R

常见错误

错误	方向
duplicate key	唯一约束冲突
deadlock detected	加锁顺序或重试
too many connections	连接池
could not serialize access	串行化重试
canceling statement due to timeout	statement_timeout
terminating connection due to administrator command	管理操作
database is not accepting commands	事务 ID 回卷保护

本章小结

本速查手册汇总 PostgreSQL 连接、建表、索引、执行计划、事务、锁、清理、复制和备份常用命令。生产使用时以当前版本文档为准。