postgresql学习记录1

以下是 PostgreSQL 常用命令及操作指南，涵盖用户管理、表空间、权限、备份、SQL 优化等关键场景：

一、用户与权限管理

1. 创建用户

-- 创建用户（默认无登录权限）
CREATE ROLE username WITH LOGIN PASSWORD 'password';

-- 允许用户登录并设置有效期
CREATE USER username WITH LOGIN PASSWORD 'password' VALID UNTIL '2024-12-31';

2. 赋权

-- 授予数据库所有权限
GRANT ALL PRIVILEGES ON DATABASE dbname TO username;

-- 授予表读写权限
GRANT SELECT, INSERT, UPDATE, DELETE ON tablename TO username;

-- 授予模式（Schema）下所有表权限
GRANT USAGE ON SCHEMA schemaname TO username;
GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA schemaname TO username;

-- 授予超级用户权限（谨慎操作）
ALTER USER username WITH SUPERUSER;

3. 撤销权限

REVOKE ALL PRIVILEGES ON DATABASE dbname FROM username;

二、表空间管理

1. 创建表空间

-- 创建表空间（需提前在操作系统创建目录并赋予权限）
CREATE TABLESPACE tspace_name LOCATION '/path/to/directory';

2. 指定表空间创建表

CREATE TABLE tablename (id INT) TABLESPACE tspace_name;

3. 查看表空间

\db+  -- 列出所有表空间及路径

三、备份与恢复

1. 逻辑备份

# 全库备份（格式为 SQL）
pg_dump -U username -d dbname -f backup.sql

# 单表备份
pg_dump -U username -d dbname -t tablename -f table.sql

# 压缩备份（推荐）
pg_dump -U username -d dbname | gzip > backup.sql.gz

# 并行备份（加快速度）
pg_dump -U username -d dbname -j 4 -Fd -f backup_dir/

2. 逻辑恢复

# 恢复 SQL 文件
psql -U username -d dbname -f backup.sql

# 恢复压缩备份
gunzip -c backup.sql.gz | psql -U username -d dbname

3. 物理备份（基于文件系统）

# 使用 pg_basebackup 进行基础备份
pg_basebackup -U username -D /path/to/backup_dir -Ft -Xs -P

四、SQL 优化与性能分析

1. 查看执行计划

-- 显示查询计划（不执行）
EXPLAIN SELECT * FROM tablename WHERE id = 1;

-- 显示实际执行时间（执行查询）
EXPLAIN ANALYZE SELECT * FROM tablename WHERE id = 1;

-- 格式化输出（JSON/文本）
EXPLAIN (FORMAT JSON, ANALYZE) SELECT * FROM tablename;

2. 开启慢查询日志

修改 postgresql.conf：

log_min_duration_statement = 1000  -- 记录超过 1 秒的查询
log_statement = 'all'              -- 可选：记录所有 SQL

重启 PostgreSQL 生效。

3. 查看活跃查询与锁

-- 查看当前活跃查询
SELECT * FROM pg_stat_activity;

-- 查看锁信息
SELECT * FROM pg_locks;

4. 分析高频/耗时 SQL

启用 pg_stat_statements 扩展：

-- 修改 postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

-- 创建扩展（需超级用户）
CREATE EXTENSION pg_stat_statements;

-- 查看耗时 TOP 10 的 SQL
SELECT query, calls, total_time, rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

五、索引优化

1. 创建索引

-- 单列索引
CREATE INDEX idx_name ON tablename (columnname);

-- 复合索引
CREATE INDEX idx_name ON tablename (col1, col2);

-- 唯一索引
CREATE UNIQUE INDEX idx_name ON tablename (columnname);

2. 索引优化建议

• 避免在小表或频繁更新的列上过度索引。
• 使用 EXPLAIN 分析是否命中索引。
• 定期重建索引（REINDEX INDEX idx_name）。

六、实用工具与命令

1. 命令行工具（psql）

\c dbname           -- 切换数据库
\dt                 -- 列出所有表
\di                 -- 列出所有索引
\d+ tablename       -- 查看表结构
\du                 -- 列出所有用户
\l                  -- 列出所有数据库
\timing             -- 开启执行时间统计

2. 查看版本与配置

SELECT version();                   -- 查看版本
SHOW config_file;                   -- 显示配置文件路径
SHOW shared_buffers;                -- 查看缓存配置

七、常见问题处理

1. 连接数满

-- 查看最大连接数
SHOW max_connections;

-- 终止指定进程
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle';

2. 表膨胀清理

VACUUM (VERBOSE, ANALYZE) tablename;  -- 常规清理
VACUUM FULL tablename;                -- 彻底清理（锁表）

总结

• 权限管理：遵循最小权限原则，按需分配。
• 备份策略：逻辑备份用于数据迁移，物理备份用于全量恢复。
• 性能优化：优先分析执行计划，针对性创建索引，避免全表扫描。
• 监控工具：定期检查 pg_stat_activity 和 pg_stat_statements。

根据实际场景调整参数（如 work_mem、shared_buffers），并结合 pgBadger 或 pgAdmin 等工具进行深度分析。