快速入门PostgreSQL数据库

2026-03-30 22:17:00
丁国栋
原创 10
摘要：本文记录和整理PostgreSQL数据库的基本操作和概念。

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 用户名;

权限管理建议

  1. 最小权限原则：只授予用户完成工作所必需的权限。
  2. 使用角色分组：将权限授予角色，再将角色分配给用户，便于管理。
  3. 定期审计：通过 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 的多库环境。
发表评论
博客分类