PostgreSQL 常用 SQL 与运维速查
连接与查看
psql -h 127.0.0.1 -U app -d appdb
psql -c 'SELECT version();'
psql -l
psql -d appdb -c '\dt'
psql -d appdb -c '\d+ orders'
psql -d appdb -c '\df'
psql -d appdb -c 'SHOW max_connections;'
用户与权限
CREATE ROLE app LOGIN PASSWORD 'password';
GRANT CONNECT ON DATABASE appdb TO app;
GRANT USAGE ON SCHEMA public TO app;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO app;
\du
\dp
查询与诊断
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1;
SELECT * FROM pg_stat_activity WHERE state <> 'idle';
SELECT * FROM pg_locks WHERE NOT granted;
SELECT pg_size_pretty(pg_database_size('appdb'));
SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;
备份与恢复
pg_dump -Fc -d appdb -f appdb.dump
pg_restore -d appdb appdb.dump
pg_dump -d appdb > appdb.sql
psql -d appdb -f appdb.sql
pg_basebackup -D /backup/base -Ft -z -P
场景速查
| 目的 |
命令 |
| 当前连接 |
SELECT * FROM pg_stat_activity; |
| 锁等待 |
SELECT * FROM pg_locks WHERE NOT granted; |
| 表膨胀 |
pg_stat_user_tables + pgstattuple |
| 复制状态 |
SELECT * FROM pg_stat_replication; |
| 慢查询 |
pg_stat_statements |
| 取消查询 |
SELECT pg_cancel_backend(pid); |
| 终止连接 |
SELECT pg_terminate_backend(pid); |
易错点
pg_dump -Fc 必须用 pg_restore 恢复；纯 SQL 才用 psql。- 备份版本不能用旧版
pg_dump 导新版数据库；用同版本或更新版本。
pg_stat_activity 的 idle in transaction 会阻塞 vacuum。pg_terminate_backend 会强制断连接；先确认业务影响。VACUUM FULL 会锁表；生产优先 pg_repack。- 默认标识符折叠小写；大写表名要加双引号。
- 连接数满时先看连接池和
idle 连接，不要只调 max_connections。
相关命令
psql — 完整命令页pg_dump / pg_restore — 备份恢复pg_stat_statements — 慢 SQL