PostgreSQL 数据库管理

概述

PostgreSQL 数据库管理、扩展使用、查询优化等技能。

连接管理

# 本地连接
psql -U postgres
psql -U username -d database

# 远程连接
psql -h hostname -p 5432 -U username -d database

# 执行 SQL 文件
psql -U username -d database -f script.sql

# 执行单条命令
psql -U username -d database -c "SELECT version();"

psql 常用命令

\l              -- 列出数据库
\c dbname       -- 切换数据库
\dt             -- 列出表
\d tablename    -- 表结构
\du             -- 列出用户
\dn             -- 列出 schema
\df             -- 列出函数
\di             -- 列出索引
\q              -- 退出
\?              -- 帮助
\timing         -- 显示执行时间
\x              -- 扩展显示模式

用户与权限

-- 创建用户
CREATE USER username WITH PASSWORD 'password';
CREATE ROLE username WITH LOGIN PASSWORD 'password';

-- 创建超级用户
CREATE USER admin WITH SUPERUSER PASSWORD 'password';

-- 授权
GRANT ALL PRIVILEGES ON DATABASE dbname TO username;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO username;
GRANT USAGE ON SCHEMA schema_name TO username;

-- 设置默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT ON TABLES TO readonly_user;

-- 查看权限
\du username
SELECT * FROM information_schema.role_table_grants WHERE grantee = 'username';

-- 修改密码
ALTER USER username WITH PASSWORD 'newpassword';

数据库操作

-- 创建数据库
CREATE DATABASE dbname;
CREATE DATABASE dbname OWNER username ENCODING 'UTF8';

-- 删除数据库
DROP DATABASE dbname;

-- 查看数据库大小
SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) 
FROM pg_database ORDER BY pg_database_size(pg_database.datname) DESC;

-- 查看表大小
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;

备份与恢复

pg_dump

# 备份单个数据库
pg_dump -U username dbname > backup.sql
pg_dump -U username -Fc dbname > backup.dump    # 自定义格式

# 备份所有数据库
pg_dumpall -U postgres > all_backup.sql

# 只备份结构
pg_dump -U username --schema-only dbname > schema.sql

# 只备份数据
pg_dump -U username --data-only dbname > data.sql

# 备份特定表
pg_dump -U username -t tablename dbname > table.sql

# 并行备份（大数据库）
pg_dump -U username -Fd -j 4 dbname -f backup_dir/

恢复

# 恢复 SQL 格式
psql -U username -d dbname < backup.sql

# 恢复自定义格式
pg_restore -U username -d dbname backup.dump

# 并行恢复
pg_restore -U username -d dbname -j 4 backup_dir/

# 恢复到新数据库
createdb -U postgres newdb
pg_restore -U postgres -d newdb backup.dump

性能监控

-- 当前连接
SELECT * FROM pg_stat_activity;
SELECT pid, usename, application_name, state, query 
FROM pg_stat_activity WHERE state != 'idle';

-- 终止连接
SELECT pg_terminate_backend(pid);

-- 锁信息
SELECT * FROM pg_locks WHERE NOT granted;

-- 查看锁等待
SELECT blocked_locks.pid AS blocked_pid,
       blocking_locks.pid AS blocking_pid,
       blocked_activity.usename AS blocked_user,
       blocking_activity.usename AS blocking_user,
       blocked_activity.query AS blocked_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

-- 表统计
SELECT relname, seq_scan, idx_scan, n_tup_ins, n_tup_upd, n_tup_del
FROM pg_stat_user_tables;

-- 索引使用情况
SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes;

查询优化

-- 执行计划
EXPLAIN SELECT * FROM table WHERE condition;
EXPLAIN ANALYZE SELECT * FROM table WHERE condition;
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT * FROM table;

-- 更新统计信息
ANALYZE tablename;
ANALYZE;

-- 重建索引
REINDEX TABLE tablename;
REINDEX DATABASE dbname;

-- VACUUM
VACUUM tablename;
VACUUM FULL tablename;              -- 回收空间
VACUUM ANALYZE tablename;           -- 同时更新统计

常见场景

场景 1：主从复制状态

-- 主库
SELECT * FROM pg_stat_replication;

-- 从库
SELECT * FROM pg_stat_wal_receiver;

-- 复制延迟
SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::INT AS lag_seconds;

场景 2：慢查询分析

-- 启用 pg_stat_statements
CREATE EXTENSION pg_stat_statements;

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

-- 重置统计
SELECT pg_stat_statements_reset();

场景 3：表维护

-- 查看表膨胀
SELECT schemaname, relname, n_dead_tup, n_live_tup,
       round(n_dead_tup * 100.0 / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

-- 清理膨胀
VACUUM FULL tablename;

故障排查

pg_stat_activity

EXPLAIN ANALYZE

pg_locks

pg_stat_activity

pg_stat_replication