PostgreSQL 开发配置速查表

连接管理 (pg-connect)

``bash # 连接本地数据库 psql -d mydb

# 连接远程数据库 psql -h host.example.com -p 5432 -U user -d mydb

# 连接后执行单个 SQL 并退出 psql -d mydb -c "SELECT version();"

# 列出所有数据库 psql -l

# 从文件执行 SQL psql -d mydb -f script.sql

# 设置环境变量免密连接 export PGPASSWORD=secret psql -h localhost -U user -d mydb

# 查看当前连接信息 \conninfo `

表结构操作 DDL (pg-ddl)

`sql -- 创建表 CREATE TABLE users ( id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(255) UNIQUE NOT NULL, created_at TIMESTAMP DEFAULT NOW() );

-- 修改表：添加列 ALTER TABLE users ADD COLUMN age INTEGER DEFAULT 0;

-- 修改表：删除列 ALTER TABLE users DROP COLUMN age;

-- 修改表：修改列类型 ALTER TABLE users ALTER COLUMN name TYPE TEXT;

-- 修改表：添加默认值 ALTER TABLE users ALTER COLUMN created_at SET DEFAULT NOW();

-- 修改表：添加非空约束 ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- 删除表 DROP TABLE users;

-- 删除表（如果存在） DROP TABLE IF EXISTS users;

-- 重命名表 ALTER TABLE users RENAME TO customers;

-- 查看表结构 \d+ users

-- 创建索引 CREATE INDEX idx_users_email ON users(email);

-- 创建唯一索引 CREATE UNIQUE INDEX idx_users_username ON users(username); `

数据操作 DML (pg-dml)

`sql -- 插入单行 INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

-- 插入多行 INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com'), ('Charlie', 'charlie@example.com');

-- 插入并返回生成 ID INSERT INTO users (name, email) VALUES ('Dave', 'dave@example.com') RETURNING id;

-- 更新数据 UPDATE users SET name = 'Alice Updated' WHERE id = 1;

-- 更新并返回 UPDATE users SET name = 'Alice New' WHERE id = 1 RETURNING *;

-- 删除数据 DELETE FROM users WHERE id = 5;

-- 删除所有数据（保留表结构） TRUNCATE TABLE users;

-- 清空表并重启自增序列 TRUNCATE TABLE users RESTART IDENTITY;

-- 删除并返回 DELETE FROM users WHERE id = 5 RETURNING *; `

查询与 JOIN (pg-query)

`sql -- 基本查询 SELECT * FROM users; SELECT id, name, email FROM users;

-- 条件查询 SELECT * FROM users WHERE age > 18;

-- 排序 SELECT * FROM users ORDER BY created_at DESC;

-- 分页 SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;

-- 模糊查询 SELECT * FROM users WHERE name LIKE '%Ali%';

-- IN 查询 SELECT * FROM users WHERE id IN (1, 2, 3);

-- 范围查询 SELECT * FROM users WHERE created_at BETWEEN '2026-01-01' AND '2026-12-31';

-- INNER JOIN SELECT u.name, o.amount FROM users u JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN SELECT u.name, o.amount FROM users u LEFT JOIN orders o ON u.id = o.user_id;

-- 多表 JOIN SELECT u.name, o.amount, p.product_name FROM users u JOIN orders o ON u.id = o.user_id JOIN products p ON o.product_id = p.id;

-- 子查询 SELECT name FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100); `

聚合与分组 (pg-query)

`sql -- 计数 SELECT COUNT(*) FROM users;

-- 去重计数 SELECT COUNT(DISTINCT city) FROM users;

-- 聚合函数 SELECT AVG(age), MAX(age), MIN(age), SUM(salary) FROM employees;

-- 分组统计 SELECT city, COUNT(*) as user_count FROM users GROUP BY city ORDER BY user_count DESC;

-- HAVING 过滤分组 SELECT city, COUNT(*) as user_count FROM users GROUP BY city HAVING COUNT(*) > 5;

-- GROUP BY + 多聚合 SELECT department, AVG(salary), MAX(salary), COUNT(*) FROM employees GROUP BY department;

-- DISTINCT SELECT DISTINCT city FROM users; `

索引管理 (pg-index)

`sql -- 创建普通索引 CREATE INDEX idx_users_name ON users(name);

-- 创建唯一索引 CREATE UNIQUE INDEX idx_users_email ON users(email);

-- 创建复合索引 CREATE INDEX idx_users_city_age ON users(city, age);

-- 创建部分索引 CREATE INDEX idx_users_active ON users(created_at) WHERE status = 'active';

-- 查看所有索引 SELECT * FROM pg_indexes WHERE tablename = 'users';

-- 查看索引详情 \d+ idx_users_name

-- 删除索引 DROP INDEX idx_users_name;

-- 分析表（更新统计信息） ANALYZE users;

-- 重建索引 REINDEX INDEX idx_users_email; `

备份与恢复 (pg-backup)

`bash # 备份单个数据库（自定义格式） pg_dump -Fc mydb > mydb.dump

# 备份单个数据库（纯 SQL） pg_dump -Fp mydb > mydb.sql

# 压缩备份 pg_dump -Fc mydb | gzip > mydb.dump.gz

# 备份特定表 pg_dump -t users -t orders mydb > tables.sql

# 只备份结构（无数据） pg_dump -s mydb > schema.sql

# 只备份数据（无结构） pg_dump -a mydb > data.sql

# 远程备份 pg_dump -h remote_host -U user -d mydb > backup.sql

# 恢复备份（自定义格式） pg_restore -d mydb mydb.dump

# 恢复备份（SQL 格式） psql -d mydb < mydb.sql

# 并行恢复（利用多核） pg_restore -d mydb -j 4 mydb.dump

# 备份整个集群 pg_dumpall > full_cluster.sql `

性能调优 (pg-perf)

`sql -- 查看查询执行计划 EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';

-- 查看实际执行计划（含耗时） EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';

-- 查看慢查询 SELECT query, calls, total_time, mean_time FROM pg_stat_statements ORDER BY mean_time DESC LIMIT 10;

-- VACUUM（回收存储空间） VACUUM users;

-- VACUUM FULL（彻底回收，需排他锁） VACUUM FULL users;

-- 分析表（更新统计信息） ANALYZE users;

-- 自动清理设置 SHOW autovacuum;

-- 查看活跃连接 SELECT pid, usename, application_name, state, query FROM pg_stat_activity WHERE state = 'active';

-- 终止连接 SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid = 12345;

-- 查看数据库大小 SELECT pg_database_size('mydb');

-- 查看表大小 SELECT pg_total_relation_size('users');

-- 友好显示大小 SELECT pg_size_pretty(pg_database_size('mydb'));

-- 设置事务隔离级别 BEGIN; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- ... queries ... COMMIT; `

权限管理 (pg-perf)

``sql -- 创建用户 CREATE USER app_user WITH PASSWORD 'secure_password';

-- 创建数据库并授权 CREATE DATABASE mydb OWNER app_user;

-- 授予所有权限 GRANT ALL PRIVILEGES ON DATABASE mydb TO app_user;

-- 授予表权限 GRANT SELECT, INSERT, UPDATE ON users TO app_user;

-- 授予模式权限 GRANT USAGE ON SCHEMA public TO app_user;

-- 撤销权限 REVOKE DELETE ON users FROM app_user;

-- 查看用户权限 \du

-- 查看表权限 \z users