第八章数据库管理基础

前面七章聚焦于"怎么用 SQL 操作数据"，这一章转向运维视角——用户权限、备份恢复、日常监控。即使你不是 DBA，了解这些基础也非常必要。

8.1 用户与角色

PostgreSQL 使用角色（Role） 来管理访问权限。角色分为两种：

登录角色（用户）：可以连接到数据库的角色
组角色：不能登录，只用来组织权限，方便批量授权

在 PostgreSQL 中，CREATE USER 等同于 CREATE ROLE ... WITH LOGIN。二者只是写法不同。

创建用户

sql

-- 创建一个可以登录的用户
CREATE USER app_user WITH PASSWORD 'secure_password_123';

-- 等价写法
CREATE ROLE app_user WITH LOGIN PASSWORD 'secure_password_123';

常用选项

sql

CREATE USER analyst WITH
    PASSWORD 'analyst_pass'
    CREATEDB             -- 允许创建数据库
    VALID UNTIL '2026-12-31';  -- 账户过期时间

修改用户

sql

-- 修改密码
ALTER USER app_user WITH PASSWORD 'new_password';

-- 赋予创建数据库的权限
ALTER USER app_user CREATEDB;

删除用户

sql

DROP USER app_user;
DROP USER IF EXISTS app_user;

查看所有用户

sql

\du

输出类似：

                             List of roles
  Role name  |                   Attributes
-------------+------------------------------------------------
 analyst     | Create DB                                     
 app_user    |
 postgres    | Superuser, Create role, Create DB, Replication

8.2 权限管理（GRANT / REVOKE）

权限控制的核心是两个命令：GRANT（授予）和 REVOKE（撤销）。

数据库级权限

sql

-- 允许 app_user 连接到 school 数据库
GRANT CONNECT ON DATABASE school TO app_user;

-- 撤销连接权限
REVOKE CONNECT ON DATABASE school FROM app_user;

表级权限

sql

-- 授予查询权限
GRANT SELECT ON students TO app_user;

-- 授予增删改查全部权限
GRANT SELECT, INSERT, UPDATE, DELETE ON students TO app_user;

-- 授予某个模式下所有表的查询权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_user;

-- 撤销权限
REVOKE INSERT, UPDATE, DELETE ON students FROM app_user;

常用权限列表

权限	含义
`SELECT`	查询数据
`INSERT`	插入数据
`UPDATE`	更新数据
`DELETE`	删除数据
`TRUNCATE`	清空表
`ALL PRIVILEGES`	以上全部

用组角色批量管理

sql

-- 创建一个组角色（不能登录）
CREATE ROLE readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

-- 把组角色授予具体用户
GRANT readonly TO analyst;
GRANT readonly TO app_user;

-- 这样 analyst 和 app_user 都自动获得了 readonly 的所有权限

实际权限设计建议

超级管理员（postgres）  → 仅用于运维，不给应用使用
应用账号（app_user）    → 只有 SELECT + INSERT + UPDATE + DELETE
只读账号（readonly）    → 只有 SELECT，给数据分析师用

8.3 备份与恢复

数据丢失是最严重的事故。定期备份是数据库管理的铁律。

pg_dump — 逻辑备份

pg_dump 把数据库导出为 SQL 脚本或自定义格式。

bash

# 导出为 SQL 脚本（最简单）
pg_dump -U postgres school > school_backup.sql

# 导出为自定义格式（支持并行恢复、更灵活）
pg_dump -U postgres -Fc school > school_backup.dump

# 只导出表结构（不含数据）
pg_dump -U postgres --schema-only school > school_schema.sql

# 只导出数据（不含表结构）
pg_dump -U postgres --data-only school > school_data.sql

# 只导出指定表
pg_dump -U postgres -t students school > students_backup.sql

恢复备份

bash

# 从 SQL 脚本恢复
psql -U postgres school < school_backup.sql

# 从自定义格式恢复
pg_restore -U postgres -d school school_backup.dump

# 恢复时先清空再导入（覆盖式恢复）
pg_restore -U postgres -d school --clean school_backup.dump

pg_dumpall — 备份所有数据库

bash

# 导出整个 PostgreSQL 实例（所有数据库 + 用户 + 权限）
pg_dumpall -U postgres > full_backup.sql

# 恢复
psql -U postgres < full_backup.sql

备份策略建议

场景	建议
开发环境	不定期手动 pg_dump 就行
小型生产环境	每天定时 pg_dump，保留最近 7 天
大型生产环境	pg_dump + WAL 归档（支持时间点恢复）

简单的定时备份脚本（Linux）：

bash

#!/bin/bash
BACKUP_DIR="/backups/postgres"
DATE=$(date +%Y%m%d_%H%M%S)
pg_dump -U postgres -Fc school > "$BACKUP_DIR/school_$DATE.dump"
# 删除 7 天前的备份
find "$BACKUP_DIR" -name "*.dump" -mtime +7 -delete

8.4 常用监控与运维命令

数据库信息

sql

-- 查看 PostgreSQL 版本
SELECT version();

-- 查看所有数据库及其大小
SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

-- 查看当前数据库大小
SELECT pg_size_pretty(pg_database_size(current_database()));

表信息

sql

-- 查看所有表（psql）
\dt

-- 查看表结构
\d students

-- 查看各表大小
SELECT
    tablename,
    pg_size_pretty(pg_total_relation_size(tablename::regclass)) AS total_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(tablename::regclass) DESC;

连接信息

sql

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

-- 查看所有连接数
SELECT count(*) FROM pg_stat_activity;

-- 终止一个连接（谨慎使用）
SELECT pg_terminate_backend(pid);

慢查询排查

sql

-- 查看当前正在执行的长时间查询（超过 5 秒）
SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active' AND now() - query_start > interval '5 seconds';

常用 psql 快捷命令汇总

命令	作用
`\l`	列出所有数据库
`\c dbname`	切换数据库
`\dt`	列出当前 schema 所有表
`\d tablename`	查看表结构（列、类型、约束）
`\di`	列出所有索引
`\dv`	列出所有视图
`\df`	列出所有函数
`\du`	列出所有用户 / 角色
`\dn`	列出所有 schema
`\timing`	开启 / 关闭查询计时
`\x`	切换扩展显示模式（竖排显示）
`\e`	用外部编辑器编写 SQL
`\i filename`	执行 SQL 文件
`\q`	退出 psql

8.5 PostgreSQL 配置文件（了解）

PostgreSQL 的核心配置文件有两个：

文件	作用
`postgresql.conf`	数据库核心配置（内存、连接数、日志等）
`pg_hba.conf`	客户端认证配置（谁能从哪里用什么方式连接）

查看配置文件位置

sql

SHOW config_file;
-- 例如：/etc/postgresql/16/main/postgresql.conf

SHOW hba_file;
-- 例如：/etc/postgresql/16/main/pg_hba.conf

几个常调整的配置项

# postgresql.conf

# 最大连接数（默认 100）
max_connections = 200

# 共享缓冲区（建议设为物理内存的 25%）
shared_buffers = 1GB

# 工作内存（排序、JOIN 等操作使用）
work_mem = 64MB

# 日志记录慢查询（超过 1 秒的查询）
log_min_duration_statement = 1000

修改配置后需要重启或重新加载：

bash

# 重新加载配置（不中断连接）
sudo systemctl reload postgresql

# 或在 psql 中
SELECT pg_reload_conf();

本章小结

你学到了	要点
用户管理	`CREATE USER`, `ALTER USER`, `DROP USER`
权限控制	`GRANT` 授权, `REVOKE` 撤销, 组角色批量管理
备份恢复	`pg_dump` 导出, `psql` / `pg_restore` 恢复
日常监控	数据库大小、活跃连接、慢查询排查
psql 命令	`\l`, `\dt`, `\d`, `\du` 等
配置文件	`postgresql.conf`（核心配置）, `pg_hba.conf`（认证配置）

下一部分：附录

第八章 数据库管理基础 ​

8.1 用户与角色 ​

创建用户 ​

常用选项 ​

修改用户 ​

删除用户 ​

查看所有用户 ​

8.2 权限管理（GRANT / REVOKE） ​

数据库级权限 ​

表级权限 ​

常用权限列表 ​

用组角色批量管理 ​

实际权限设计建议 ​

8.3 备份与恢复 ​

pg_dump — 逻辑备份 ​

恢复备份 ​

pg_dumpall — 备份所有数据库 ​

备份策略建议 ​

8.4 常用监控与运维命令 ​

数据库信息 ​

表信息 ​

连接信息 ​

慢查询排查 ​

常用 psql 快捷命令汇总 ​

8.5 PostgreSQL 配置文件（了解） ​

查看配置文件位置 ​

几个常调整的配置项 ​

本章小结 ​

第八章数据库管理基础

8.1 用户与角色

创建用户

常用选项

修改用户

删除用户

查看所有用户

8.2 权限管理（GRANT / REVOKE）

数据库级权限

表级权限

常用权限列表

用组角色批量管理

实际权限设计建议

8.3 备份与恢复

pg_dump — 逻辑备份

恢复备份

pg_dumpall — 备份所有数据库

备份策略建议

8.4 常用监控与运维命令

数据库信息

表信息

连接信息

慢查询排查

常用 psql 快捷命令汇总

8.5 PostgreSQL 配置文件（了解）

查看配置文件位置

几个常调整的配置项

本章小结