快速入门PostgreSQL数据库 - Database

PostgreSQL 常用命令

连接与登录

操作	命令
登录本地数据库	`psql -U postgres`
登录远程数据库	`PGPASSWORD=$POSTGRES_PASSWORD psql -h postgres -U postgres`
指定连接参数	`psql -h 主机名 -p 端口 -U 用户名 -d 数据库名`

数据库操作

操作	命令
列出所有数据库	`\l` 或者 `SELECT datname FROM pg_database;`
切换到某个数据库	`\c DB_NAME`
查看当前数据库	`SELECT current_database();`

表与模式操作

操作	命令
列出所有表	`\d`
查看表及大小	`\dt+`
列出表的所有字段名	`\d TABLE_NAME`
查看所有用户	`\du`
查看所有模式	`\dn`

配置与权限

操作	命令
查询配置	`SELECT name, setting, sourcefile FROM pg_settings;`
创建用户	`CREATE USER 用户名 WITH PASSWORD '密码';`
授予权限	`GRANT 权限 ON 表名 TO 用户名;`
撤销权限	`REVOKE 权限 ON 表名 FROM 用户名;`
开启/关闭执行时间显示	`\timing`

备份与恢复

操作	命令
备份数据库	`pg_dump -U 用户名数据库名 > 备份文件.sql`
恢复数据库	`psql -U 用户名数据库名 < 备份文件.sql`

用户的权限有哪些？

在 PostgreSQL 中，权限（Privileges）分为多个层级和类型，可以通过 GRANT 和 REVOKE 命令管理。以下是主要的权限类别和说明：

1. 数据库层级权限

这些权限作用于整个数据库。

权限	说明
CONNECT	允许用户连接到该数据库。
CREATE	允许用户在该数据库中创建新模式。
TEMPORARY 或 TEMP	允许用户在该数据库中创建临时表。

示例：

GRANT CONNECT, CREATE ON DATABASE 数据库名 TO 用户名;

2. 模式层级权限

模式（Schema）是数据库中的命名空间，用于组织表、视图等对象。

权限	说明
USAGE	允许用户访问模式中的对象。
CREATE	允许用户在模式中创建新对象（如表、视图等）。

示例：

GRANT USAGE, CREATE ON SCHEMA 模式名 TO 用户名;

3. 表/视图层级权限

这些是最常用的权限，控制对表或视图的访问。

权限	说明
SELECT	允许查询表中的数据。
INSERT	允许向表中插入新行。
UPDATE	允许修改表中的数据。
DELETE	允许删除表中的数据。
TRUNCATE	允许清空表（快速删除所有行）。
REFERENCES	允许创建外键约束引用该表。
TRIGGER	允许在表上创建触发器。
ALL PRIVILEGES	授予所有可用权限（除了 `TRUNCATE` 在某些版本中可能独立管理）。

示例：

-- 授予特定权限
GRANT SELECT, INSERT ON 表名 TO 用户名;
-- 授予所有权限
GRANT ALL PRIVILEGES ON 表名 TO 用户名;

4. 列层级权限

可以针对特定列授予 SELECT、UPDATE 或 REFERENCES 权限。

示例：

-- 只允许用户更新特定列
GRANT UPDATE (列1, 列2) ON 表名 TO 用户名;

5. 序列权限

序列（Sequence）用于生成自增数字，通常用于主键。

权限	说明
USAGE	允许使用 `nextval()` 和 `currval()` 函数操作序列。
SELECT	允许查看序列的当前值。
UPDATE	允许修改序列的值（通过 `setval()`）。

示例：

GRANT USAGE ON SEQUENCE 序列名 TO 用户名;

6. 函数权限

控制执行函数的权限。

权限	说明
EXECUTE	允许调用函数或存储过程。

示例：

GRANT EXECUTE ON FUNCTION 函数名(参数类型) TO 用户名;

7. 默认权限

可以为将来创建的对象设置默认权限，避免每次手动授权。

示例：

-- 为指定用户在指定模式中未来创建的所有表自动授予 SELECT 权限
ALTER DEFAULT PRIVILEGES IN SCHEMA 模式名
GRANT SELECT ON TABLES TO 用户名;

8. 角色与权限继承

角色（Role）：可以是用户或用户组。

角色可以继承其他角色的权限：

GRANT 角色名 TO 用户名;  -- 将角色的权限授予用户

9. 查看现有权限

-- 查看表的权限
\z 表名
-- 或使用 SQL
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = '表名';

10. 回收权限

使用 REVOKE 命令移除权限：

REVOKE 权限 ON 表名 FROM 用户名;

权限管理建议

最小权限原则：只授予用户完成工作所必需的权限。
使用角色分组：将权限授予角色，再将角色分配给用户，便于管理。
定期审计：通过 information_schema 或 pg_catalog 视图检查权限分配。

与MySQL对比，在PostgreSQL中数据库、模式分别是什么含义？

在 PostgreSQL 中，“数据库”和“模式”是两层不同的命名空间，这与 MySQL 的“单库多表”逻辑有本质区别。理解它们的层级关系，是掌握 PostgreSQL 权限体系的关键。

一、PostgreSQL 的层级划分

PostgreSQL 采用 “实例 → 数据库 → 模式 → 对象” 的严格三级结构。

层级	含义	类比
数据库 (Database)	独立的数据容器。每个数据库拥有独立的用户权限、连接隔离和配置。	相当于 MySQL 中的一个独立实例（但共享进程）。
模式 (Schema)	数据库内部的命名空间。用于逻辑分组表、视图、函数等对象。	相当于 MySQL 中的 Database（库）。

1. 数据库 (Database)

作用：最高级别的隔离单位。连接 PostgreSQL 时，必须指定连接到哪个数据库（\c dbname）。
特性：
- 不同数据库之间的数据默认不可跨库直接访问（除非使用 dblink 或 FDW）。
- 拥有独立的权限体系（GRANT CONNECT ON DATABASE ...）。

2. 模式 (Schema)

作用：在同一个数据库内，通过模式将表进行逻辑分组（例如 public 模式、hr 模式、finance 模式）。
特性：
- 默认存在一个 public 模式。
- 访问表时需指定 schema.table_name（如 SELECT * FROM hr.employees;）。
- 权限控制粒度更细（GRANT SELECT ON SCHEMA ...）。

二、MySQL 的对应关系

MySQL 的层级结构相对扁平：“实例 → 数据库 → 对象”。

PostgreSQL	MySQL 对应物	关键差异
Database	Instance (实例)	MySQL 的“数据库”实际上是 PostgreSQL 的“模式”。
Schema	Database (数据库)	MySQL 的 `USE db` 相当于 PostgreSQL 的 `SET search_path TO schema;`。

核心区别：

在 MySQL 中，CREATE DATABASE db1; 创建的 db1，在逻辑层级上等同于 PostgreSQL 中的 CREATE SCHEMA db1;。
PostgreSQL 在“数据库”之上多了一层更严格的物理/逻辑隔离。

三、操作对比手册

1. 创建与查看

操作	PostgreSQL	MySQL
创建“库”	`CREATE DATABASE myapp;`	`CREATE DATABASE myapp;`
创建“模式”	`CREATE SCHEMA myschema;`	(无直接对应，等同于创建库)
查看列表	`\l` (数据库) `\dn` (模式)	`SHOW DATABASES;`
切换/使用	`\c myapp` (切换数据库) `SET search_path TO myschema;` (切换模式)	`USE myapp;`

2. 访问对象

场景	PostgreSQL	MySQL
默认访问	`SELECT * FROM table;` (默认在 `public` 模式)	`SELECT * FROM table;` (默认在 `USE` 的库)
跨模式/库访问	`SELECT * FROM hr.employees;`	`SELECT * FROM hr.employees;` (语法一致)
跨数据库访问	无法直接访问，需使用 FDW 或 dblink	`SELECT * FROM db2.table;` (可直接跨库)

四、权限管理的差异

这是两者在权限控制上最大的不同点。

控制点	PostgreSQL	MySQL
连接权限	必须授予 `CONNECT ON DATABASE` 权限	用户 `user@host` 默认拥有全局或库级权限
对象权限	模式级 (`GRANT USAGE ON SCHEMA`) 表级 (`GRANT SELECT ON TABLE`)	主要基于 Database 层级授权 (`GRANT ON db.*`)
隔离性	强隔离。不同数据库如同独立沙盒。	弱隔离。同一实例下的数据库可被用户跨库访问。

五、选型与设计建议

1. 何时使用 PostgreSQL 的“数据库”？

场景：多租户 SaaS 应用（每个租户数据完全物理隔离）、微服务架构（每个服务独占一个数据库）。
优势：备份恢复独立、权限隔离彻底、连接池可独立配置。

2. 何时使用 PostgreSQL 的“模式”？

场景：单应用内的模块划分（如 auth、order、report 模块）、逻辑分组。
优势：避免表名冲突（如每个模块都可以有 config 表）、便于权限细分。

3. 从 MySQL 迁移的注意事项

如果你习惯了 MySQL 的 USE db; 逻辑，在 PostgreSQL 中应：

将 MySQL 的每个 Database 映射为 PostgreSQL 的一个 Schema。
在 PostgreSQL 中通常只创建一个 Database（如 myapp），然后在其中创建多个 Schema 来模拟 MySQL 的多库环境。