数据库管理

创建数据库

数据库用 SQL 命令CREATE DATABASE创建：

CREATE DATABASE name;

其中name遵循SQL标识符的一般规则。当前角色自动成为该新数据库的拥有者。以后删除这个数据库也是该拥有者的特权（同时还会删除其中的所有对象，即使那些对象有不同的拥有者）。

为了方便，你还可以用shell来创建新数据库： createdb。

createdb dbname

它连接到postgres数据库并且发出CREATE DATABASE命令。注意不带任何参数的createdb将创建一个使用当前用户名的数据库。

有时候你想为其他人创建一个数据库，并且使其成为新数据库的拥有者， 这样他们就可以自己配置和管理这个数据库。要实现这个目标，使用下列命令之一：

• 用于 SQL 环境的

CREATE DATABASE dbname OWNER rolename;

• 或者用于 shell 的

createdb -O rolename dbname

只有root用户才被允许为其他人创建一个数据库。

模板数据库

使用\l命令时可以看到此时有postgres,tmplate1和template0这三个额外的数据库。
第一个数据库总是在初始化数据存储区域时创建的。这个数据库被称为postgres。因此要创建第一个“普通”数据库时，你可以连接到postgres。

在集簇初始化期间还会创建两个额外的数据库， template1 和 template0。

每当在集簇中创建新数据库时，实际上是克隆了template1。 这意味着在template1中所做的任何更改都会传播到随后创建的所有数据库中。所以该数据库是创建新数据库的“模板”。 如果你为template1数据库增加对象，这些对象将被拷贝到后续创建的用户数据库中。 这种行为允许对数据库中标准对象集合的站点本地修改。例如，如果你把过程语言PL/Perl安装到 template1中，那么你在创建用户数据库后不需要额外的操作就可以使用该语言。 因此，除非您希望这些更改传播到每个新创建的数据库中，否则应避免在template1中创建对象。

系统里还有名为template0的第二个标准系统数据库。 这个数据库包含和template1初始内容一样的数据，也就是说，只包含你的PostgreSQL版本预定义的标准对象。 在数据库集簇被初始化之后，不应该对template0做任何修改。 通过指示CREATE DATABASE使用template0取代template1进行拷贝， 你可以创建一个“原始的”用户数据库,它不会包含任何template1中的站点本地附加物。
要通过拷贝template0来创建一个数据库，使用：SQL 环境中的

CREATE DATABASE dbname TEMPLATE template0;

或者 shell 中的

createdb -T template0 dbname

除了系统模板，你也可以让 PostgreSQL 把任意数据库当作“模板”来复制：

CREATE DATABASE newdb TEMPLATE olddb;

这条命令会完整克隆 olddb 的：所有表结构；所有数据；索引；函数；视图；权限。
在 PostgreSQL 中可以通过以下命令查看所有会话连接：

SELECT pid, datname, usename, client_addr, state
FROM pg_stat_activity;

---

删除数据库

数据库用DROP DATABASE命令删除：

DROP DATABASE name;

只有数据库的拥有者或者超级用户才可以删除数据库。删除数据库会移除其中包括的所有对象。数据库的删除不能被撤销。

你不能在与目标数据库连接时执行DROP DATABASE命令。不过，你可以连接到任何其它数据库，包括 template1数据库。template1也是你删除一个给定集簇中最后一个用户数据库的唯一选项。

为了方便，有一个在 shell 程序可以删除数据库，dropdb：

dropdb dbname

（和createdb不同，删除当前用户名的数据库不是默认动作）。

---

表空间

在 PostgreSQL 中，表空间（tablespace）是数据库对象在文件系统中的存储位置。
换句话说，它定义了 数据库文件（表、索引等）在磁盘上保存的路径。

PostgreSQL 默认会把所有数据放在一个目录下（例如 /usr/local/pgsql/data/base/），
但通过表空间，你可以让不同的表或索引放到不同的磁盘上。

默认表空间

PostgreSQL 安装后自动有两个默认表空间：

表空间名	路径	说明
pg_default	$PGDATA/base	普通用户表和索引的默认存储位置
pg_global	$PGDATA/global	存储系统级共享数据（如用户信息、事务日志等）

所有未指定表空间的对象，默认都会放在 pg_default 下。

自定义表空间

通过使用表空间，管理员可以控制一个PostgreSQL安装的磁盘布局。 这么做至少有两个用处。首先，如果初始化集簇所在的分区或者卷用光了空间，而又不能在逻辑上扩展或者做别的什么操作，那么表空间可以被创建在一个不同的分区上，直到系统可以被重新配置。

其次，表空间允许管理员根据数据库对象的使用模式来优化性能。例如，一个很频繁使用的索引可以被放在非常快并且非常可靠的磁盘上，如一种非常贵的固态设备。同时，一个很少使用的或者对性能要求不高的存储归档数据的表可以存储在一个便宜但比较慢的磁盘系统上。

这个位置必须是一个已有的空目录，并且属于PostgreSQL操作系统用户。 所有后续在该表空间中创建的对象都将被存放在这个目录下的文件中。该位置不能放在可移动 或者瞬时存储上，因为如果表空间丢失会导致集簇无法工作。

1. 在文件系统中创建目录

mkdir /mnt/ssd/pg_tablespace
chown wqstage:wqstage /mnt/ssd/pg_tablespace

2. 在 PostgreSQL 中创建表空间

CREATE TABLESPACE fastspace LOCATION '/mnt/ssd/pg_tablespace';

在表空间中创建数据库或表

1. 创建数据库时指定表空间

CREATE DATABASE mydb TABLESPACE fastspace;

2. 创建表或索引时指定表空间

CREATE TABLE mytable (
    id SERIAL PRIMARY KEY,
    data TEXT
) TABLESPACE fastspace;

CREATE INDEX myindex ON mytable (data) TABLESPACE fastspace;

查看表空间信息

psql程序的\db元命令也可以用来列出现有的表空间。

或者使用SELECT 来查询:

SELECT spcname, pg_tablespace_location(oid)
FROM pg_tablespace;

---

模式

在 PostgreSQL 中，模式（Schema） 是数据库中的一种命名空间（namespace），它用于组织数据库对象（例如表、视图、函数、类型、索引等）。类似于操作系统中的目录

模式的作用是对数据库对象进行逻辑分组，防止命名冲突，并方便管理。

查看所有模式

\dn

有关模式的相关操作

要创建一个模式，可使用CREATE SCHEMA命令，并且给出选择的模式名称。例如：

CREATE SCHEMA myschema;

在模式中创建表:

CREATE TABLE myschema.mytable (
 ...
);

要删除一个为空的模式（其中的所有对象已经被删除），可用：

DROP SCHEMA myschema;

要删除一个模式以及其中包含的所有对象，可用：

DROP SCHEMA myschema CASCADE;

创建一个由其他人所拥有的模式（因为这是将用户动作限制在良定义的名字空间中的方法之一）。其语法是：

CREATE SCHEMA schema_name AUTHORIZATION user_name;

搜索路径

限定名写起来很冗长(myschema.mytable)，因此，表名通常被使用非限定名来引用，它只由表名构成。系统将沿着一条搜索路径来决定该名称指的是哪个表。
搜索路径是一个进行查看的模式列表。 搜索路径中第一个匹配的表将被认为是所需要的。如果在搜索路径中没有任何匹配，即使在数据库的其他模式中存在匹配的表名也将会报告一个错误。

要显示当前搜索路径，使用下面的命令：

SHOW search_path;

在默认设置下这将返回：

search_path

"$user", public

第一个元素说明一个和当前用户同名的模式会被搜索。如果不存在这个模式，该项将被忽略。第二个元素是public模式(默认的模式)，public模式没有什么特别之处，它只是默认存在而已，它也可以被删除。

搜索路径中的第一个模式是创建新对象的默认存储位置。这就是默认情况下对象会被创建在公共模式中的原因。当对象在任何其他没有模式限定的环境中被引用（表修改、数据修改或查询命令）时，搜索路径将被遍历直到一个匹配对象被找到。因此，在默认配置中，任何非限定访问将只能指向public模式。

要把新模式放在搜索路径中，我们可以使用：

SET search_path TO myschema,public;

（这里省略了$user，因为我们并不立即需要它）

模式和权限

模式本身也是一个数据库对象，因此可以通过 GRANT 和 REVOKE 命令来控制访问。

权限类型	说明
CREATE	允许在该模式下创建新对象（如表、视图、函数等）
USAGE	允许访问该模式中的对象（但不能修改或创建）

授予用户权限

GRANT USAGE ON SCHEMA sales TO analyst;
GRANT CREATE ON SCHEMA sales TO developer;

撤销权限

REVOKE CREATE ON SCHEMA sales FROM developer;
REVOKE USAGE ON SCHEMA sales FROM analyst;

---

清理(VACUUM)

清理是 PostgreSQL 维护任务中不可或缺的组成部分。通过回收存储、优化性能并保持数据库精简，清理有助于维护 PostgreSQL 系统的健康。

PostgreSQL 采用MVCC（多版本并发控制） 机制：
每次执行 UPDATE 或 DELETE 时，旧行不会立即删除，而是被标记为过期（dead tuple），并在表文件中保留。表中会存在许多“死行版本（dead tuples）”，
这些死行会：占用磁盘空间；影响全表扫描性能；干扰索引可见性。因此 PostgreSQL 需要“清理。
VACUUM可以标记无效元组为可重用空间（但不立即收缩文件），更新可见性信息（visibility map）：哪些页没有死元组，可供只读查询直接跳过，以及清理索引对应的无效项。

PostgreSQL 的清理主要依赖三种机制：

类型	执行方式	主要目的
手动 VACUUM	DBA 执行	清除死元组、更新统计信息
自动清理 Autovacuum	后台守护进程自动执行	定期清理、冻结事务
冻结 Vacuum Freeze	自动或手动	防止事务 ID 回绕溢出（XID wraparound）

VACUUM 的执行过程

执行：

VACUUM [VERBOSE] table_name;

PostgreSQL 会：

1. 扫描表
   遍历所有页，识别死元组（根据 xmin/xmax 事务ID可见性判断）。

2. 回收空间
   将死元组标记为空闲（可供新行重用）；
   但不会立即缩小文件大小（除非用 VACUUM FULL）。

3. 更新可见性映射 (Visibility Map)
   标记哪些页面全可见，以优化未来的 VACUUM。

4. 更新统计信息
   用于 ANALYZE、优化器决策。

注意：VACUUM FULL（彻底清理）会锁表,耗时长,通常只在表严重膨胀时使用。

Autovacuum（自动清理）

默认启用（autovacuum=on）。
后台有一个 autovacuum launcher 进程，周期性检查哪些表需要清理。每个表由一个独立的 worker 执行，根据统计信息判断是否触发。

触发条件：
dead_tuples > vacuum_threshold + vacuum_scale_factor * reltuples

冻结（Freeze）与事务ID回绕

PostgreSQL 使用 32位事务ID（XID），会循环。
如果不“冻结”，旧事务可能看起来“还没提交”，从而导致数据不可见。
清理过程中的 冻结（freezing）：将旧版本的 xmin 改为一个特殊常量 FrozenXID，表示此行“永远可见”，无需再比较事务ID。

当数据库接近溢出风险时，PostgreSQL 会强制执行“防回绕清理（anti-wraparound vacuum）”。

常用组合命令：VACUUM ANALYZE

等价于顺序执行两步：

VACUUM;
ANALYZE;

但它更常用，因为先清理再统计，防止统计包含过时数据,是大型数据导入后或更新大量行后的标准维护命令。

---

主从同步(复制)

主从同步/复制（Replication） 是指：
把一个数据库实例（称为 主节点 或 Primary）中的数据变化，实时或准实时地复制到另一个实例（称为 从节点 或 Standby）。
简单说，就是让多个数据库节点保持数据一致或接近一致。

目的	说明
高可用（High Availability）	主库宕机时，从库可接管服务，保证业务不中断。
灾备（Disaster Recovery）	在异地保留一份副本，主库损坏或数据丢失时可恢复。
读扩展（Read Scaling）	从库可以承担只读查询，分担主库压力。
备份验证（Backup Testing）	可以在从库上做备份、分析、报表等任务，不影响主库。

复制的分类：

PostgreSQL 复制
├── 物理复制（Physical Replication）
│   └── 流复制（Streaming Replication）
│       ├── 异步复制（Asynchronous）
│       ├── 同步复制（Synchronous）
│       └── 半同步复制 / 仲裁复制（Quorum-based）
└── 逻辑复制（Logical Replication）

流复制是 物理复制 的一种实现：主库生成 WAL 日志，从库实时接收（streaming）这些 WAL，从库将 WAL “重放（replay）”到自己的数据文件，从而保持和主库完全一致的物理数据。

类型	同步方式	数据一致性	延迟	是否会阻塞主库提交
异步复制（Asynchronous）	主库写 WAL 后立即提交	主从可能有延迟	低	否
同步复制（Synchronous）	主库等待从库确认接收 WAL 后才提交	主从强一致	极低	是
半同步复制（Quorum-based）	主库等待部分从库确认	高一致性 + 可用性平衡	可调	部分阻塞

---

备份恢复(Backup & Recovery)和高可用(HA)

PostgreSQL 的高可用与备份机制要解决三类问题：

目标	说明	对应机制
可靠性 (Durability)	数据不会因崩溃或断电丢失	WAL日志、崩溃恢复
灾难恢复 (Disaster Recovery)	可以恢复到某个时间点	归档 + Base backup + PITR
高可用性 (High Availability)	主库宕机时服务不中断	流复制 + 自动故障切换

WAL预写日志

WAL（Write-Ahead Logging）预写日志 是 PostgreSQL 所有恢复与复制机制的基础。
基本原则：所有数据变更，必须先写入 WAL，再写入数据页。
WAL 日志是顺序写，性能非常高；
即使数据库崩溃，只要 WAL 在，就能重放恢复一致状态。
具体wal介绍见： PostgreSQL数据库配置#预写式日志wal

备份类型

逻辑备份

pg_dump 是 PostgreSQL 提供的逻辑备份工具，用于导出数据库中的结构（DDL）和数据（DML），可以方便地在相同或不同 PostgreSQL 实例间迁移数据、做版本升级迁移、或者进行部分（单库、单表）备份。

pg_dump 不会直接复制数据库文件（那是物理备份的范畴，如 pg_basebackup），而是生成 SQL 或自定义格式文件。
使用 SQL 层导出数据。

基本写法：

pg_dump [选项] 数据库名 > dumpfile.sql

pg_dump -U postgres mydb > mydb.sql
pg_dumpall > all_databases.sql

可跨版本恢复、易读，但是速度慢，不适合超大数据库。适合的场景是迁移、版本升级。

此外还有一个命令：pg_dumpall 是 PostgreSQL 里用于导出整个集群（cluster）所有数据库与全局对象的逻辑备份命令。

pg_restore 是配合 pg_dump 使用的，它属于逻辑层备份 / 恢复工具。

物理备份

pg_basebackup直接复制数据库的数据文件、控制文件、WAL 日志等底层文件，生成一个完整的 数据库集群物理镜像 base backup,通常与 WAL 归档结合使用。
流程：pg_basebackup 连接主数据库，请求 PostgreSQL 通过内部复制协议发送所有数据文件内容，同时拷贝 WAL（事务日志），保证备份一致性， 生成一个完整的可启动数据库副本。

这意味着用 pg_basebackup 备份出来的目录，可以直接作为 PostgreSQL 实例启动。

pg_basebackup -D /backup/base -Ft -z -P -X stream

二者对比

项目	pg_dump / pg_dumpall	pg_basebackup
备份类型	逻辑备份（SQL层）	物理备份（文件层）
备份内容	数据库对象（表、索引、视图、数据等）	数据目录（数据文件、WAL、配置等）
恢复方式	重新执行 SQL 脚本或 pg_restore	拷贝数据目录 + 启动数据库
可移植性	高（可跨版本）	低（仅限相同版本）
体积	小（仅结构+数据）	大（整个实例）
耗时	慢（逻辑导入导出）	快（文件级复制）
典型用途	单库迁移 / 数据导出 / 部分备份	实例全备 / 物理高可用 / 流复制主备搭建
是否支持PITR（时间点恢复）	否	是（配合WAL归档）

归档(ARCHIVE)与时间点恢复（PITR）

时间点恢复 Point-In-Time Recovery，该机制基于物理备份 + WAL 日志重放可以恢复到某一时间点的状态，实现了灾难恢复。例如误删了一张表，希望回到删表前的时间，就可以用PITR
时间线：
原理流程图：

────────●────────●────────●────────●────────▶
        A        B        C        D
      Base      WAL      WAL      Crash
     Backup     #1       #2

恢复时：从 A 点的基础备份（Base Backup）开始，顺序回放归档的 WAL 日志，当时间到达指定的恢复点（例如“C点”）就停止回放。

当需要时间点恢复时，可参考如下步骤：
1. 首先要提前开启wal归档
在 postgresql.conf 开启 WAL 归档，这样 PostgreSQL 每生成一个 WAL 文件，就会自动复制到 /archive 目录。

wal_level = replica
archive_mode = on
archive_command = 'cp %p /usr/local/src/archive/%f'

还要手动创建一个/usr/local/src/archive目录并修改权限，因为PostgreSQL不会自动创建。

sudo mkdir /usr/local/src/archive
sudo chown wqstage:wqstage /usr/local/src/archive
sudo chmod 700 /usr/local/src/archive

2. 定期做基础备份
得到一个完整的数据快照（不含 WAL）。

pg_basebackup -D /backup/base -Ft -z -P -X none

3. 出现灾难后恢复步骤

# 1. 停掉数据库
pg_ctl stop -D 

# 2. 删除旧数据，换成备份
rm -rf /var/lib/postgresql/data/*
tar -xzf /backup/base/base.tar.gz -C /var/lib/postgresql/data

# 3. 编辑 recovery 配置
echo "restore_command = 'cp /archive/%f %p'" >> postgresql.conf
echo "recovery_target_time = '2025-10-14 15:59:59'" >> postgresql.conf

然后重启数据库：

pg_ctl start -D /var/lib/postgresql/data

系统就会从 Base Backup 起步，顺序重放 /archive 中的 WAL，一旦回放到 指定的恢复点停止。

高可用（HA）

即保证让数据库在主节点宕机时，业务不中断。
PostgreSQL 官方自带的 HA 机制是 **主从同步(流复制)**。

原理：主库写 WAL → 从库实时接收并重放 WAL → 保持同步。

          WAL流
Primary  ─────────▶  Standby

详细流程就是主库产生日志（WAL sender），从库通过网络实时接收（WAL receiver），从库写入自身 WAL 并“回放”，数据页更新后，保持与主库一致。

可以参考 主从同步/流复制

---