PostgreSQL 基础概念与操作指南

✅ PostgreSQL 基础概念与操作指南

---

🎯 学习目标

• 理解 数据库实例 vs 数据库 的区别
• 掌握使用 psql 命令行工具连接和操作数据库
• 熟悉常用 psql 元命令
• 理解 用户与权限体系
• 完成实践任务：创建新用户 + 新数据库

---

一、基础概念详解

---

✅ 1. 数据库实例（Database Cluster / Instance） vs 数据库（Database）

概念	说明
数据库实例 （Database Cluster）	一个 PostgreSQL 服务进程管理的“数据库集合”，包含多个数据库、用户、配置、日志等。由 initdb 初始化，由 pg_ctl 控制启停。对应一个数据目录（如 /var/lib/postgresql/16/main）。一个服务器通常运行一个实例。
数据库 （Database）	实例中的一个独立逻辑单元，包含表、视图、函数等对象。一个实例可包含多个数据库（如 postgres, myapp, test）。用户连接时必须指定数据库。

📌 类比理解：

🏢 实例 = 一栋大楼（PostgreSQL 服务）
🚪 数据库 = 楼里的一个个房间（每个房间独立，存放不同项目的数据）
👤 用户 = 进楼的人（有不同权限，有的能进所有房间，有的只能进特定房间）

---

✅ 2. 模式（Schema）

• 模式（Schema） 是数据库内的命名空间，用于组织对象（表、函数、视图等）。
• 默认模式是 public。
• 一个数据库可以有多个模式 → 避免命名冲突，实现逻辑分组。

CREATE SCHEMA hr;
CREATE TABLE hr.employees (...);

💡 类似“文件夹”概念：数据库.hr.employees

---

二、psql 命令行工具使用

---

✅ 1. 连接数据库

# 标准格式
psql -U 用户名 -d 数据库名 -h 主机名 -p 端口

# 本地连接 postgres 用户和 postgres 数据库（默认）
psql -U postgres

# 连接指定数据库
psql -U postgres -d mydb

# 如果当前系统用户与数据库用户同名，可省略 -U
psql mydb

💡 Windows/macOS/Linux 安装后默认创建 postgres 超级用户和 postgres 数据库。

---

✅ 2. 常用 psql 元命令（以 \ 开头）

命令	作用说明	示例
\?	显示所有 psql 元命令帮助	\?
\h	显示 SQL 命令帮助（如 \h CREATE TABLE）	\h SELECT
\l	列出所有数据库	\l
\c	切换连接数据库	\c mydb
\d	列出当前数据库中的表、视图等	\d 或 \d tablename
\dn	列出所有模式（schema）	\dn
\du	列出所有用户/角色	\du
\dt	列出所有表（table）	\dt
\dv	列出所有视图（view）	\dv
\df	列出所有函数	\df
\x	切换扩展显示模式（行转列）	\x → 再 \x 关闭
\q	退出 psql	\q

📌 注意： 元命令是 psql 客户端特有，不是 SQL 语句，结尾不加分号。

---

三、用户与权限体系

---

✅ 1. 超级用户（postgres） vs 普通用户

类型	权限说明
超级用户 （如 postgres）	拥有所有权限：创建/删除数据库、创建用户、修改配置、访问所有数据等。安装时默认创建。
普通用户	权限受限，需由超级用户授权。通常用于应用程序连接，遵循最小权限原则。

🔐 安全建议：生产环境不要用超级用户连接应用！应创建专用用户并授权。

---

✅ 2. 用户 = 角色（Role）

PostgreSQL 中，用户和角色是同一个概念，使用 CREATE ROLE 或 CREATE USER（后者是前者的别名，带 LOGIN 权限）。

-- 创建普通用户（可登录）
CREATE USER appuser WITH PASSWORD 'secure123';

-- 创建角色（不可登录，用于权限组）
CREATE ROLE readonly;

-- 授权角色给用户
GRANT readonly TO appuser;

---

四、📝 实践任务：使用 psql 创建用户和数据库

---

✅ 任务步骤：

1. 以 postgres 用户身份连接数据库

psql -U postgres

输入安装时设置的密码（Windows）或直接进入（Linux/macOS 本地信任认证）

---

2. 查看当前有哪些数据库

\l

你会看到默认数据库如 postgres, template0, template1

---

3. 创建新用户（例如：myuser）

CREATE USER myuser WITH PASSWORD 'mypassword';

✅ 可选：赋予创建数据库权限（用于开发）

ALTER USER myuser CREATEDB;

---

4. 创建新数据库（例如：myappdb），并指定所有者

CREATE DATABASE myappdb OWNER myuser;

---

5. 验证是否创建成功

\l  -- 应看到 myappdb
\du -- 应看到 myuser

---

6. 切换到新数据库并连接新用户（可选测试）

\c myappdb myuser

如果提示密码错误或认证失败，需修改 pg_hba.conf 设置为 md5 并重启服务（见上一章“常见问题”）。

---

7. 退出 psql

\q

---

✅ 最终成果验证

你可以在终端重新连接新数据库测试：

psql -U myuser -d myappdb

成功进入 → ✅ 任务完成！

---

🧠 知识扩展（选学）

✅ 默认模板数据库

• template0：原始模板，不可修改
• template1：默认模板，新建数据库时从此复制（可自定义）
• postgres：默认管理数据库

-- 从自定义模板创建数据库
CREATE DATABASE mydb TEMPLATE mytemplate;

---

✅ 权限控制基础

-- 授予用户在数据库上的连接权限
GRANT CONNECT ON DATABASE myappdb TO myuser;

-- 授予在 public 模式下创建表的权限
GRANT CREATE ON SCHEMA public TO myuser;

-- 授予对某表的 SELECT 权限
GRANT SELECT ON mytable TO myuser;

-- 撤销权限
REVOKE SELECT ON mytable FROM myuser;

---

🎉 总结回顾

你已掌握	说明
数据库实例 vs 数据库	实例是服务容器，数据库是其中的独立单元
psql 连接与元命令	\l, \d, \c, \du, \q 等
用户与权限体系	超级用户权限大，普通用户需授权
创建用户和数据库	CREATE USER ..., CREATE DATABASE ...
实践操作能力	成功在本地完成用户与数据库创建