PostgreSQL 常用命令速查表

涵盖：连接管理、数据库/表操作、数据增删改查、用户权限、备份恢复、索引、事务、性能排查等。

一、连接与退出

# 连接本地数据库（默认用户、默认库 postgres）
psql
# 指定用户、主机、端口、库名
psql -U <user> -h <host> -p <port> -d <dbname>
# 远程连接并提示输入密码
psql -U postgres -h 192.168.1.10 -p 5432 -d mydb -W
# 直接执行一条 SQL 后退出
psql -U postgres -d mydb -c "SELECT version();"
# 从文件执行 SQL 脚本
psql -U postgres -d mydb -f /path/to/script.sql
# 以非交互方式导出查询结果为 CSV
psql -U postgres -d mydb -c "COPY (SELECT * FROM users) TO STDOUT WITH CSV HEADER" > users.csv

二、数据库操作

-- 创建数据库
CREATE DATABASE mydb
  WITH OWNER = postgres
       ENCODING = 'UTF8'
       LC_COLLATE = 'en_US.utf8'
       LC_CTYPE = 'en_US.utf8'
       TEMPLATE = template0
       CONNECTION LIMIT = 100;
-- 修改数据库名
ALTER DATABASE mydb RENAME TO mydb_new;
-- 修改数据库拥有者
ALTER DATABASE mydb OWNER TO new_user;
-- 设置数据库连接数限制
ALTER DATABASE mydb CONNECTION LIMIT 50;
-- 删除数据库（需先断开所有连接）
DROP DATABASE IF EXISTS mydb;
-- 强制断开所有连接后删除（先终止会话）
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'mydb';
DROP DATABASE mydb;

三、表操作

-- 创建表
CREATE TABLE users (
    id          BIGSERIAL PRIMARY KEY,
    username    VARCHAR(50)  NOT NULL UNIQUE,
    email       VARCHAR(100) NOT NULL,
    age         INT          CHECK (age >= 0 AND age <= 150),
    status      VARCHAR(20)  DEFAULT 'active',
    created_at  TIMESTAMPTZ  DEFAULT now(),
    updated_at  TIMESTAMPTZ  DEFAULT now()
);
-- 带外键的表
CREATE TABLE orders (
    id         BIGSERIAL PRIMARY KEY,
    user_id    BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    amount     NUMERIC(10,2) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now()
);
-- 查看表结构
\d users
-- 重命名表
ALTER TABLE users RENAME TO accounts;
-- 添加列
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- 删除列
ALTER TABLE users DROP COLUMN IF EXISTS phone;
-- 修改列类型
ALTER TABLE users ALTER COLUMN age TYPE BIGINT;
-- 重命名列
ALTER TABLE users RENAME COLUMN username TO login_name;
-- 设置/删除默认值
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'inactive';
ALTER TABLE users ALTER COLUMN status DROP DEFAULT;
-- 设置/删除非空约束
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
ALTER TABLE users ALTER COLUMN email DROP NOT NULL;
-- 添加注释
COMMENT ON TABLE users IS '用户表';
COMMENT ON COLUMN users.email IS '用户邮箱地址';
-- 清空表数据（保留结构，比 DELETE 快，不触发触发器）
TRUNCATE TABLE users RESTART IDENTITY;
-- 清空并级联清空外键关联表
TRUNCATE TABLE users, orders CASCADE;
-- 删除表
DROP TABLE IF EXISTS orders CASCADE;

四、数据增删改查（CRUD）

-- 插入单行
INSERT INTO users (username, email, age) VALUES ('alice', 'alice@example.com', 28);
-- 插入多行
INSERT INTO users (username, email) VALUES
    ('bob',   'bob@example.com'),
    ('carol', 'carol@example.com'),
    ('dave',  'dave@example.com');
-- 插入冲突时更新（UPSERT）
INSERT INTO users (username, email) VALUES ('alice', 'new@example.com')
ON CONFLICT (username) DO UPDATE SET email = EXCLUDED.email;
-- 插入冲突时忽略
INSERT INTO users (username, email) VALUES ('alice', 'x@example.com')
ON CONFLICT (username) DO NOTHING;
-- 插入并返回生成的值
INSERT INTO users (username) VALUES ('eve') RETURNING id, created_at;
-- 查询
SELECT id, username, email FROM users WHERE status = 'active' ORDER BY created_at DESC LIMIT 20 OFFSET 0;
-- 去重查询
SELECT DISTINCT status FROM users;
-- 更新
UPDATE users SET status = 'inactive', updated_at = now() WHERE last_login < now() - INTERVAL '90 days';
-- 基于另一张表更新
UPDATE orders o SET amount = o.amount * 1.1
FROM users u WHERE o.user_id = u.id AND u.status = 'vip';
-- 删除
DELETE FROM users WHERE status = 'inactive' AND id NOT IN (SELECT user_id FROM orders);
-- 删除并返回被删行
DELETE FROM users WHERE id = 100 RETURNING *;

常用聚合与分组

SELECT status, COUNT(*) AS cnt, AVG(age) AS avg_age, MAX(created_at) AS latest
FROM users
GROUP BY status
HAVING COUNT(*) > 10
ORDER BY cnt DESC;

JOIN 类型

-- 内连接
SELECT u.username, o.id, o.amount
FROM users u JOIN orders o ON u.id = o.user_id;
-- 左连接
SELECT u.username, o.id
FROM users u LEFT JOIN orders o ON u.id = o.user_id;
-- 全外连接
SELECT u.username, o.id
FROM users u FULL OUTER JOIN orders o ON u.id = o.user_id;
-- 自连接（如查找同邮箱用户）
SELECT a.id, b.id, a.email
FROM users a JOIN users b ON a.email = b.email AND a.id < b.id;

子查询 / CTE

-- 子查询
SELECT username FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);
-- 公用表表达式（CTE）
WITH active_users AS (
    SELECT id, username FROM users WHERE status = 'active'
),
big_orders AS (
    SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id
)
SELECT a.username, b.total
FROM active_users a JOIN big_orders b ON a.id = b.user_id
ORDER BY b.total DESC;
-- 递归 CTE：1 到 10 的序列
WITH RECURSIVE seq(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 10
)
SELECT n FROM seq;

窗口函数

-- 每个用户的订单按金额排名
SELECT
    user_id,
    id,
    amount,
    ROW_NUMBER()      OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn,
    RANK()            OVER (PARTITION BY user_id ORDER BY amount DESC) AS rnk,
    SUM(amount)       OVER (PARTITION BY user_id)                       AS user_total,
    AVG(amount)       OVER (PARTITION BY user_id)                       AS user_avg
FROM orders;

五、索引

-- 普通索引
CREATE INDEX idx_users_email ON users(email);
-- 唯一索引
CREATE UNIQUE INDEX idx_users_username ON users(username);
-- 复合索引
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
-- 部分索引（只索引满足条件的行）
CREATE INDEX idx_active_users ON users(username) WHERE status = 'active';
-- 表达式索引
CREATE INDEX idx_lower_email ON users(LOWER(email));
-- 并发创建索引（不锁表，生产推荐）
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
-- 查看表的所有索引
\d users
-- 查看索引大小
SELECT schemaname, relname, indexrelname, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes ORDER BY pg_relation_size(indexrelid) DESC LIMIT 10;
-- 删除索引
DROP INDEX IF EXISTS idx_users_email;
-- 重建索引
REINDEX INDEX idx_users_email;
REINDEX TABLE CONCURRENTLY users;

六、事务与锁

-- 标准事务
BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;   -- 或 ROLLBACK;
-- 设置保存点
BEGIN;
    INSERT INTO users (username) VALUES ('test1');
    SAVEPOINT sp1;
    INSERT INTO users (username) VALUES ('test2');
    ROLLBACK TO SAVEPOINT sp1;   -- 只回滚 test2
COMMIT;
-- 设置事务隔离级别
BEGIN ISOLATION LEVEL SERIALIZABLE;
    -- ...
COMMIT;
-- 查看当前锁等待
SELECT l.locktype, l.relation::regclass, l.pid, l.mode, l.granted,
       a.query
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted;
-- 终止某个后端连接
SELECT pg_terminate_backend(<pid>);
-- 取消某个查询（不终止连接）
SELECT pg_cancel_backend(<pid>);

七、用户与权限

-- 创建角色（可登录用户）
CREATE ROLE app_user WITH LOGIN PASSWORD 'StrongPass!23' CONNECTION LIMIT 20;
-- 创建只读角色
CREATE ROLE readonly NOLOGIN;
-- 修改密码
ALTER ROLE app_user WITH PASSWORD 'NewPass!45';
-- 重命名角色
ALTER ROLE app_user RENAME TO app_user_new;
-- 授予连接数据库权限
GRANT CONNECT ON DATABASE mydb TO app_user;
-- 授予 schema 使用权
GRANT USAGE ON SCHEMA public TO app_user;
-- 授予表权限
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
-- 只读权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
-- 授予序列使用权（INSERT 自增列需要）
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;
-- 默认权限（今后新建的表自动授权）
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
-- 将角色赋予用户（继承权限）
GRANT readonly TO app_user;
-- 撤销权限
REVOKE INSERT, UPDATE, DELETE ON users FROM app_user;
-- 查看某用户的权限
\du app_user
-- 删除角色（需先撤销其所有权限）
DROP ROLE app_user;

快速撤销某角色所有权限（删除角色前必做）

-- 批量撤销某角色在 public schema 下的所有表权限
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM app_user;
REVOKE ALL ON ALL SEQUENCES IN SCHEMA public FROM app_user;
REVOKE ALL ON SCHEMA public FROM app_user;
REVOKE CONNECT ON DATABASE mydb FROM app_user;

八、视图 / 物化视图 / 函数

-- 创建视图
CREATE OR REPLACE VIEW active_users AS
SELECT id, username, email FROM users WHERE status = 'active';
-- 查询视图
SELECT * FROM active_users;
-- 删除视图
DROP VIEW IF EXISTS active_users;
-- 创建物化视图
CREATE MATERIALIZED VIEW user_order_summary AS
SELECT u.id, u.username, COUNT(o.id) AS order_cnt, COALESCE(SUM(o.amount),0) AS total
FROM users u LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;
-- 刷新物化视图
REFRESH MATERIALIZED VIEW user_order_summary;
-- 并发刷新（需先建唯一索引）
CREATE UNIQUE INDEX ON user_order_summary(id);
REFRESH MATERIALIZED VIEW CONCURRENTLY user_order_summary;
-- 自定义函数
CREATE OR REPLACE FUNCTION add(a INT, b INT) RETURNS INT AS $$
BEGIN
    RETURN a + b;
END;
$$ LANGUAGE plpgsql;
SELECT add(3, 5);

九、备份与恢复

9.1 逻辑备份（pg_dump / pg_dumpall）

# 备份单个数据库（自定义压缩格式，推荐）
pg_dump -U postgres -Fc mydb -f mydb.dump
# 备份为纯 SQL 文本
pg_dump -U postgres -d mydb -Fp -f mydb.sql
# 仅备份表结构（不含数据）
pg_dump -U postgres -d mydb --schema-only -f mydb_schema.sql
# 仅备份数据（不含结构）
pg_dump -U postgres -d mydb --data-only -f mydb_data.sql
# 备份指定表
pg_dump -U postgres -d mydb -t users -t orders -f tables.dump
# 备份所有数据库 + 全局对象（角色、表空间）
pg_dumpall -U postgres -f alldb.sql
# 仅备份全局对象（角色/表空间，迁移时常用）
pg_dumpall -U postgres --globals-only -f globals.sql

9.2 逻辑恢复（pg_restore）

# 从自定义格式恢复
pg_restore -U postgres -d mydb -c mydb.dump     # -c 先 drop 再 create
# 仅恢复表结构
pg_restore -U postgres -d mydb --schema-only mydb.dump
# 恢复指定表
pg_restore -U postgres -d mydb -t users mydb.dump
# 从纯 SQL 文本恢复
psql -U postgres -d mydb -f mydb.sql

9.3 物理备份（pg_basebackup，用于流复制/ PITR）

# 基础物理备份（需在 postgresql.conf 设置 wal_level=replica 并配置 pg_hba.conf）
pg_basebackup -U repl_user -h primary_host -D /var/lib/pg_backup -Fp -Xs -P -R

9.4 SQL 内导出/导入（COPY）

-- 导出整张表到 CSV（服务器端文件，需 superuser）
COPY users TO '/tmp/users.csv' WITH CSV HEADER;
-- 导出查询结果
COPY (SELECT id, username FROM users WHERE status='active') TO '/tmp/active.csv' WITH CSV HEADER;
-- 从 CSV 导入
COPY users(username, email, age) FROM '/tmp/users.csv' WITH CSV HEADER;
-- 客户端文件（任意用户可用，路径相对客户端）
\copy users TO 'users.csv' WITH CSV HEADER;
\copy users(username, email) FROM 'users.csv' WITH CSV HEADER;

十、性能排查与监控

-- 查看数据库版本
SELECT version();
-- 查看当前所有连接/活动查询
SELECT pid, usename, datname, state, query_start, state_change, wait_event, query
FROM pg_stat_activity
WHERE state IS NOT NULL
ORDER BY query_start DESC;
-- 查看长时间运行查询（> 1 分钟）
SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active' AND now() - query_start > INTERVAL '1 minute'
ORDER BY duration DESC;
-- 查看数据库大小
SELECT pg_size_pretty(pg_database_size('mydb'));
-- 所有库大小排序
SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database ORDER BY pg_database_size(datname) DESC;
-- 表大小（含索引/不含索引）
SELECT
    relname,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_relation_size(relid))       AS table_size,
    pg_size_pretty(pg_indexes_size(relid))        AS index_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;
-- 死锁检测
SELECT pid, usename, application_name, state, query
FROM pg_stat_activity WHERE wait_event = 'Lock';
-- 查看慢查询统计（需启用 pg_stat_statements 扩展）
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT
    substring(query, 1, 80) AS query,
    calls,
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2)  AS mean_ms,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
-- �'EXPLAIN 分析查询计划
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT u.username, COUNT(o.id)
FROM users u JOIN orders o ON u.id = o.user_id GROUP BY u.username;
-- 查看表统计信息（用于优化器）
ANALYZE users;            -- 手动收集统计
SELECT * FROM pg_stats WHERE tablename = 'users';

十一、扩展与常用工具

-- 查看可用扩展
SELECT * FROM pg_available_extensions;
-- 查看已安装扩展
SELECT * FROM pg_extension;
-- 安装扩展
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";     -- UUID 生成
CREATE EXTENSION IF NOT EXISTS pgcrypto;        -- 加密函数
CREATE EXTENSION IF NOT EXISTS hstore;          -- 键值对类型
CREATE EXTENSION IF NOT EXISTS postgis;         -- 地理空间
CREATE EXTENSION IF NOT EXISTS pg_trgm;         -- 模糊匹配/相似度
CREATE EXTENSION IF NOT EXISTS btree_gist;      -- GiST 支持 btree 类型
-- 使用 uuid-ossp
SELECT uuid_generate_v4();
-- 使用 pg_trgm 模糊搜索（需先建 trigram 索引）
CREATE INDEX idx_users_username_trgm ON users USING gin (username gin_trgm_ops);
SELECT * FROM users WHERE username % 'alise';   -- 相似度匹配
SELECT * FROM users WHERE username ILIKE '%ali%';

十二、序列操作

-- 查看序列当前值
SELECT last_value FROM users_id_seq;
-- 手动设置序列值
SELECT setval('users_id_seq', 1000);
-- 序列前进一格
SELECT nextval('users_id_seq');
-- 将序列重置为表中 id 最大值（常用：手动导入数据后）
SELECT setval('users_id_seq', (SELECT MAX(id) FROM users));
-- 重置所有序列（导入数据后常用脚本）
DO $$
DECLARE r RECORD;
BEGIN
    FOR r IN SELECT sequencename FROM pg_sequences WHERE schemaname='public' LOOP
        EXECUTE format('SELECT setval(%L, (SELECT COALESCE(MAX(id),0) FROM %I));',
                       'public.' || r.sequencename,
                       replace(r.sequencename, '_id_seq', ''));
    END LOOP;
END $$;

十三、配置与维护

-- 查看关键配置参数
SHOW shared_buffers;
SHOW work_mem;
SHOW max_connections;
SHOW wal_level;
-- 查看所有配置参数及来源
SELECT name, setting, unit, source, context
FROM pg_settings ORDER BY name;
-- 重新加载配置文件（无需重启）
SELECT pg_reload_conf();
-- 手动触发 WAL 切换（归档）
SELECT pg_switch_wal();
-- 查看复制状态（主库）
SELECT * FROM pg_stat_replication;
-- 查看复制状态（备库）
SELECT * FROM pg_stat_wal_receiver;
-- 自动清理（autovacuum）状态
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC LIMIT 10;
-- 手动 VACUUM / ANALYZE
VACUUM (VERBOSE, ANALYZE) users;
VACUUM FULL users;          -- 回收磁盘空间（会锁表，慎用）

十四、JSON / JSONB 操作

-- 建表带 JSONB 列
CREATE TABLE events (id BIGSERIAL, data JSONB);
-- 插入
INSERT INTO events (data) VALUES ('{"user":"alice","action":"login","ip":"10.0.0.1"}');
-- 提取字段
SELECT data->'user' AS user, data->>'action' AS action FROM events;
-- 条件查询（JSONB 字段）
SELECT * FROM events WHERE data->>'action' = 'login';
SELECT * FROM events WHERE data @> '{"user":"alice"}';
-- 添加/更新键
UPDATE events SET data = jsonb_set(data, '{ip}', '"10.0.0.2"') WHERE id = 1;
-- 删除键
UPDATE events SET data = data - 'ip' WHERE id = 1;
-- 建索引用于 JSONB 查询
CREATE INDEX idx_events_data ON events USING gin (data);

十五、快捷速查