PostgreSQL一些概念特性

一、Schema（模式）

什么是 Schema？
在 PostgreSQL 中，Schema 是数据库对象（如表、视图、函数等）的逻辑容器，类似于命名空间的概念。默认情况下，每个数据库包含一个名为 public 的 Schema。

Schema 的作用

• 避免命名冲突：不同团队或模块可以使用相同的表名而互不影响。
• 实现逻辑隔离：支持多租户或多模块架构。
• 权限控制更细粒度：可以针对不同 Schema 授权。

常见操作

查看当前数据库中的所有 schema 及其所有者

SELECT schema_name, schema_owner
FROM information_schema.schemata

创建 Schema

-- 创建 Schema
CREATE SCHEMA my_app_schema;
-- 创建 schema 并指定所有者为 postgres
CREATE SCHEMA AUTHORIZATION postgres;

在指定 Schema 中创建表

CREATE TABLE my_app_schema.users
(
    id         SERIAL,
    name       VARCHAR(50),
    created_at TIMESTAMP DEFAULT now()
);

指定搜索路径（search_path）

-- 查看当前搜索路径
SHOW search_path; -- 默认通常是 "$user", public

-- 设置搜索路径（仅在当前会话生效）
SET search_path TO my_app_schema, public;

-- 为用户永久设置搜索路径
ALTER USER postgres SET search_path TO my_app_schema, public;

如果不指定 Schema，对象会默认创建在 public Schema 中。

二、Template

当你执行 CREATE DATABASE 时，PostgreSQL 并非从零开始。它实际上是克隆一个现有的模板数据库。

内置模板

• template1：默认的模板数据库。你可以在此数据库中创建一些所有新数据库都需要的基础对象（如扩展、函数），后续创建的数据库都会包含它们。
• template0：一个最原始的“干净”模板。永远不要修改它。当 template1 损坏或你需要一个不受自定义更改影响的纯净数据库时，就用它来创建。

操作示例

基于模板创建数据库

-- 默认使用 template1
CREATE DATABASE my_new_db;

-- 显式指定使用 template0
CREATE DATABASE my_clean_db TEMPLATE template0;

将现有数据库设为模板

-- 标记为模板（防止被意外连接）
UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'db_template';

-- 现在可以基于它创建新数据库了
CREATE DATABASE app_dev WITH TEMPLATE db_template;

三、权限（Privileges）

PostgreSQL 提供基于角色（Role）的权限系统，支持对数据库、Schema、表、列等层级授权。

• 用户（User）：可登录数据库的角色；
• 角色（Role）：可被授予权限，但不一定能登录。

常见操作

创建角色、用户

CREATE ROLE read_only_role; -- 组角色
CREATE USER app_user WITH PASSWORD 'abc12345'; -- 登录用户

授予权限

-- 授予连接数据库权限
GRANT CONNECT ON DATABASE postgres TO read_only_role;

-- Schema 级别：使用和查看对象权限
GRANT USAGE ON SCHEMA my_app_schema TO read_only_role;
GRANT SELECT ON ALL TABLES IN SCHEMA my_app_schema TO read_only_role;

-- 对象级别：表的具体操作权限
GRANT SELECT, INSERT, UPDATE ON my_app_schema.users TO app_user;

-- 将角色授予用户（使用户拥有角色的所有权限）
GRANT read_only_role TO app_user;

权限类型

常见的权限包括：

权限	说明
CONNECT	连接数据库
CREATE	创建对象
SELECT / INSERT / UPDATE / DELETE	数据操作权限
USAGE	对 Schema 或序列的使用权限
EXECUTE	执行函数权限

四、插件（Extensions）

PostgreSQL 的扩展机制是其最大优势之一。通过插件（Extension），用户可以添加数据类型、函数、索引方法甚至外部数据源支持。

查看已安装插件

SELECT * FROM pg_extension;

安装可用插件

首先，确保插件包在服务器上已安装

-- 启用 PostGIS（地理信息系统）
CREATE EXTENSION IF NOT EXISTS postgis;

-- 启用 uuid-ossp 以生成 UUID
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

卸载插件

DROP EXTENSION postgis;

常见的一些插件

插件名	功能说明
hstore	键值存储类型
uuid-ossp	UUID 生成函数
postgis	地理空间数据支持
pg_stat_statements	SQL 性能分析
citext	大小写不敏感文本类型
dblink	跨库访问
pgcrypto	加密支持