PostgreSQL 命令速查 - 常用命令大全

连接与元信息

psql 连接

psql -h <host> -p 5432 -U <user> -d <db>

连接后切换数据库

c <db>

列出所有数据库

l

列出当前库所有表

dt

查看表结构

d <table>

扩展显示（列太宽时）

x

Schema 与表结构

创建 schema

CREATE SCHEMA <schema>;

查看搜索路径

SHOW search_path;

设置搜索路径

SET search_path TO <schema>, public;

创建表（自增 + UUID 可选）

CREATE TABLE <table> (id BIGSERIAL PRIMARY KEY, name TEXT NOT NULL, uid UUID DEFAULT gen_random_uuid());

修改字段类型

ALTER TABLE <table> ALTER COLUMN <col> TYPE VARCHAR(128);

分页与 UPSERT

分页查询

SELECT * FROM <table> ORDER BY id LIMIT <size> OFFSET <offset>;

UPSERT（冲突时更新）

INSERT INTO <table> (id, name) VALUES (1, 'a') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;

JSONB 字段

创建 JSONB 列

CREATE TABLE <table> (id BIGSERIAL PRIMARY KEY, attrs JSONB);

查询 JSON 字段值

SELECT attrs ->> 'name' FROM <table> WHERE id = 1;

更新 JSON 字段

UPDATE <table> SET attrs = jsonb_set(attrs, '{name}', '"新值"') WHERE id = 1;

JSONB 建 GIN 索引

CREATE INDEX idx_<table>_attrs ON <table> USING GIN (attrs);

向量检索（pgvector）

启用扩展

CREATE EXTENSION IF NOT EXISTS vector;

建带向量列的表

CREATE TABLE <table> (id BIGSERIAL PRIMARY KEY, content TEXT, embedding vector(3));

插入向量

INSERT INTO <table> (content, embedding) VALUES ('文本', '[0.1,0.2,0.3]');

余弦相似度 Top-K（<-> 为距离）

SELECT id, content, 1 - (embedding <-> '[0.1,0.2,0.3]') AS sim FROM <table> ORDER BY embedding <-> '[0.1,0.2,0.3]' LIMIT 10;

向量列建 HNSW 索引（提速）

CREATE INDEX ON <table> USING hnsw (embedding vector_cosine_ops);

锁与会话排查

查看活跃会话

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

查看锁等待

SELECT pid, locktype, mode, granted FROM pg_locks WHERE not granted;

行级锁

SELECT * FROM <table> WHERE <col> = <val> FOR UPDATE;

备份与恢复

逻辑备份（单库）

pg_dump -h <host> -U <user> -d <db> -f backup.dump

逻辑备份（全部）

pg_dumpall -h <host> -U <user> > backup.sql

恢复（SQL 格式）

psql -h <host> -U <user> -d <db> -f backup.sql

恢复（自定义格式）

pg_restore -h <host> -U <user> -d <db> backup.dump

📖 PostgreSQL 命令速查