PostgreSQL 中数据库、用户、对象关系、表、连接及管理概述 - 详解

文章目录

• 1. 数据库连接逻辑
• 2. 核心对象关系
• • 2.1 数据库集群 (Database Cluster)：
  • 2.2 数据库 (Database)：
  • 2.3 模式 (Schema)：
  • 2.4 表 (Table) 及其他对象：
  • 2.5 用户
  • 2.6 角色（Role）
  • 2.7 表空间（Tablespace）
• 3. 对象关系详解
• • 3.1 逻辑层级（从高到低）
  • 3.2 物理关联
• 4. 操作示例
• • 4.1. 数据库操作
  • 4.2. 表空间操作
  • 4.3. 角色与用户操作
  • 4.4. 模式操作
  • 4.5. 表操作
  • 4.6 权限管理

1. 数据库连接逻辑

在 PostgreSQL 中，一个客户端连接在同一个会话里只能接入一个数据库。这主要是为了维护数据的隔离性和一致性。

• 当您启动一个连接（例如通过 psql 或其他客户端），您需要指定一个目标数据库。连接建立后，您默认只能访问这个数据库中的对象（如表、视图等）。您不能在同一连接里直接执行跨数据库的查询，比如 SELECT * FROM 其他数据库.表名; 这样的语法在 PostgreSQL 中并不可行。

• 但这并不意味着数据完全被隔绝。PostgreSQL 提供了扩展来帮助您访问其他数据库或集群中的数据，例如 dblink 模块 或 Foreign Data Wrappers (FDW)，如 postgres_fdw。它们允许在一个数据库中创建代理来访问其他数据库或外部数据源的对象。

2. 核心对象关系

PostgreSQL 的架构遵循清晰的层级关系，理解这一点对有效管理至关重要：

2.1 数据库集群 (Database Cluster)：

• 这是 PostgreSQL 管理的最高层级，一个集群包含多个数据库。
• 在初始化数据库存储区域（通常通过 initdb 命令）时创建。请注意，这里的"集群"指的是单个 PostgreSQL 服务实例管理的所有数据库的集合，并非多台服务器的集群。

2.2 数据库 (Database)：

• 集群内可以创建多个数据库。数据库之间是相互隔离的。
• 当客户端连接到 PostgreSQL 服务器时，必须在连接请求中指定要连接的数据库。

2.3 模式 (Schema)：

• 每个数据库内部可以包含一个或多个模式，模式是数据库对象（如表、视图、函数等）的命名空间。
• 这种结构非常有助于：
  • 逻辑分组：将相关的表、视图等组织在一起，使数据库结构更清晰。
  • 多租户隔离：在一个数据库内，为不同用户或应用创建独立的模式，是实现简单多租户的一种常见方式。
• 每个数据库默认都有一个 public 模式。

2.4 表 (Table) 及其他对象：

• 表必须存在于某个模式中。要唯一确定一张表，需要使用 模式名.表名 的格式。
• 除了表，函数、数据类型等也属于其他数据库对象。

2.5 用户

• 用户是可以登录 PostgreSQL 实例的账号，本质上是带有 LOGIN 属性的角色（Role）。
• 特性：用户可以被赋予权限，用于控制对数据库对象的访问。
• 作用：标识访问者身份，是权限控制的起点。

2.6 角色（Role）

• 角色是权限的集合，可以被赋予给其他角色（包括用户），实现权限的批量管理。
• 特性：角色默认没有登录权限（需显式添加 LOGIN 属性才成为用户）；支持 “角色继承”（一个角色可继承另一个角色的权限）。
• 作用：简化权限管理（例如创建 “管理员角色” 包含所有权限，再赋予给具体用户）。
• 总结：
  • 在 PostgreSQL 中，"用户"和"角色"概念相似。主要区别在于：用户 (User) 通常默认具有登录权限，而角色 (Role) 更侧重于权限分组，默认无登录权限，但可以将登录权限授予角色。
  • 用户/角色是访问控制的核心。数据库对象（如模式、表）都有所有者（通常是最初创建它的用户/角色），所有者默认拥有该对象的所有权限。
  • 权限可以层层授予：可以对角色授权，然后将角色授予用户，从而简化权限管理。

2.7 表空间（Tablespace）

• 表空间是数据的物理存储位置，关联到文件系统的一个目录。
• 特性：表空间属于实例（而非单个数据库），多个数据库或表可以共用一个表空间；通过表空间可将数据分布到不同存储介质（如 SSD 存高频访问表，HDD 存归档数据）。
• 作用：管理数据的物理存储，优化 I/O 性能。

3. 对象关系详解

各对象的关系可分为逻辑层级和物理关联两类，整体结构如下：

3.1 逻辑层级（从高到低）

实例（Instance）
├─ 数据库（Database）       （1个实例可包含多个数据库，相互隔离）
│  └─ 模式（Schema）        （1个数据库可包含多个模式，逻辑分组）
│     └─ 表（Table）        （1个模式可包含多个表，数据载体）
└─ 角色（Role）             （1个实例可包含多个角色，含用户）└─ 权限映射             （角色通过权限关联到数据库/模式/表等对象）

3.2 物理关联

实例（Instance）
└─ 表空间（Tablespace）     （1个实例可包含多个表空间，对应文件系统目录）├─ 数据库文件           （数据库的数据文件存储在指定表空间）└─ 表/索引文件          （表或索引可单独指定表空间）

核心关系总结：

• 实例与数据库：实例是数据库的容器，一个实例可创建多个数据库。
• 数据库与模式：模式是数据库的子命名空间，数据库包含多个模式。
• 模式与表：表必须属于某个模式，模式是表的直接容器。
• 角色与用户：用户是带 LOGIN 属性的角色，角色是用户的超集。
• 角色与对象：角色通过 “权限” 访问数据库、模式、表等对象（权限需显式赋予）。
• 表空间与对象：表空间是物理存储，数据库、表、索引可指定表空间（默认使用 pg_default）。

4. 操作示例

以下是常用对象的创建、授权及管理操作（基于 psql 客户端）。

4.1. 数据库操作

sql
-- 创建数据库（指定表空间）
CREATE DATABASE sales_db TABLESPACE ts_sales;
-- 查看所有数据库
\l  -- psql 命令
-- 或 SQL 命令：SELECT datname FROM pg_database;
-- 连接数据库（psql）
\c sales_db
-- 删除数据库（需先断开所有连接）
DROP DATABASE IF EXISTS sales_db;

4.2. 表空间操作

sql
-- 创建表空间（需先在文件系统创建目录，且PostgreSQL用户有读写权限）
CREATE TABLESPACE ts_sales LOCATION '/data/postgres/ts_sales';
-- 查看所有表空间
\db  -- psql 命令
-- 或 SQL 命令：SELECT spcname FROM pg_tablespace;
-- 删除表空间（需先移除关联的数据库/表）
DROP TABLESPACE IF EXISTS ts_sales;

4.3. 角色与用户操作

sql
-- 创建角色（无登录权限）
CREATE ROLE sales_role;
-- 创建用户（带登录权限的角色）
CREATE USER sales_user WITH PASSWORD 'sales@123' LOGIN;
-- 将角色赋予用户（用户继承角色权限）
GRANT sales_role TO sales_user;
-- 查看所有角色/用户
\du  -- psql 命令
-- 或 SQL 命令：SELECT rolname FROM pg_roles;
-- 删除角色/用户（需先移除权限关联）
DROP ROLE IF EXISTS sales_role;
DROP USER IF EXISTS sales_user;

4.4. 模式操作

sql
-- 切换到目标数据库
\c sales_db
-- 创建模式（指定所有者）
CREATE SCHEMA sales_schema AUTHORIZATION sales_user;
-- 查看当前数据库的模式
\dn  -- psql 命令
-- 或 SQL 命令：SELECT nspname FROM pg_namespace;
-- 删除模式（级联删除模式下的所有对象）
DROP SCHEMA IF EXISTS sales_schema CASCADE;

4.5. 表操作

sql
-- 在指定模式和表空间创建表
CREATE TABLE sales_schema.orders (id SERIAL PRIMARY KEY,order_no VARCHAR(50) NOT NULL,amount NUMERIC(10,2)
) TABLESPACE ts_sales;
-- 查看当前模式下的表
\dt  -- psql 命令（默认模式）
\dt sales_schema.*  -- 查看指定模式的表
-- 删除表
DROP TABLE IF EXISTS sales_schema.orders;

4.6 权限管理

sql
-- 赋予用户访问数据库的权限
GRANT CONNECT ON DATABASE sales_db TO sales_user;
-- 赋予用户使用模式的权限
GRANT USAGE ON SCHEMA sales_schema TO sales_user;
-- 赋予用户操作表的权限（查询、插入、更新、删除）
GRANT SELECT, INSERT, UPDATE, DELETE ON sales_schema.orders TO sales_user;
-- 查看对象的权限
\dp sales_schema.orders  -- psql 命令

• PostgreSQL 的对象体系通过 “实例 - 数据库 - 模式 - 表” 的逻辑层级实现数据隔离与组织，通过 “角色 - 权限” 控制访问，通过 “表空间” 管理物理存储，形成了灵活且严谨的架构：
• 数据库是最高级的逻辑隔离单位，一个连接只能操作一个数据库（跨库需特殊配置）。
• 模式用于同一数据库内的对象分组，比多数据库更轻量。
• 角色与用户统一权限管理，用户是可登录的角色。
• 表空间实现物理存储的灵活分配，优化性能。