# 21.2 PostgreSQL

PostgreSQL 是一款自由的对象关系型数据库管理系统（Object-Relational Database Management System，ORDBMS）。其学术源流可追溯至加州大学伯克利分校的 Ingres 项目，Michael Stonebraker 教授随后启动后继项目 POSTGRES（意为“post-Ingres”，即后 Ingres 时代），首个版本于 1989 年 6 月发布。

PostgreSQL 采用 [PostgreSQL 许可证](https://www.postgresql.org/about/licence/)，该许可证与 BSD 许可证类似，同为宽松开源许可证。

作为严格遵循 ACID（原子性、一致性、隔离性、持久性）特性的数据库系统，PostgreSQL 支持事务处理、复杂查询优化和数据完整性约束。

## 安装 PostgreSQL

PostgreSQL 提供多个稳定版供选择，本节以 PostgreSQL 18 为例，其他版本的安装与配置方法类似。

使用 pkg 安装：

```sh
# pkg install postgresql18-server
```

或者使用 Ports 安装：

```sh
# cd /usr/ports/databases/postgresql18-server/
# make install clean
```

查看 PostgreSQL 安装后的配置信息：

```sh
# pkg info -D postgresql18-server
```

## 文件结构

PostgreSQL 的文件结构如下。

```sh
/
├── etc/
│   ├── login.conf  # 登录配置文件
│   ├── rc.conf     # 系统启动配置
│   └── passwd      # 用户密码文件
├── usr/
│   ├── local/
│   │   ├── etc/
│   │   │   ├── rc.d/
│   │   │   │   └── postgresql  # PostgreSQL 启动脚本
│   │   │   └── periodic/
│   │   │       └── daily/
│   │   │           └── 502.pgsql  # PostgreSQL 定时备份脚本
│   │   ├── share/
│   │   │   └── postgresql/
│   │   │       └── odbc.sql  # ODBC 兼容 SQL 脚本
│   │   └── bin/
│   │       └── pg_ctl  # PostgreSQL 控制工具
│   └── ports/
│       └── databases/
│           └── postgresql18-server/  # PostgreSQL 18 Port
└── var/
    └── db/
        └── postgres/
            ├── data18/  # PostgreSQL 18 数据目录
            │   └── postgresql.conf  # PostgreSQL 配置文件
            ├── main/    # main 实例数据目录
            └── dev/     # dev 实例数据目录
```

## 服务管理

设置 PostgreSQL 服务开机自启：

```sh
# service postgresql enable
```

## 初始化数据库

数据库集簇（database cluster）是 PostgreSQL 存储数据的目录集合，其中包含所有数据库文件和配置，初始化方法如下。

初始化 PostgreSQL 数据库集簇：

```sh
# service postgresql initdb
```

示例输出（已设置国际化，若输出为英文亦属正常）：

```sh
# service postgresql initdb
initdb postgresql
属于此数据库系统的文件宿主为用户 "postgres".
此用户也必须为服务器进程的宿主.

数据库集簇将以该区域环境配置初始化:
  区域环境支持程序:   libc
  LC_COLLATE:  C
  LC_CTYPE:    zh_CN.UTF-8
  LC_MESSAGES: zh_CN.UTF-8
  LC_MONETARY: zh_CN.UTF-8
  LC_NUMERIC:  zh_CN.UTF-8
  LC_TIME:     zh_CN.UTF-8
initdb: 无法为区域环境 "zh_CN.UTF-8" 找到合适的文本搜索配置
默认的文本搜索配置将被设为 "simple".

允许生成数据页校验和.

正在创建目录 /var/db/postgres/data18 ... 成功
正在创建子目录 ... 成功
正在选择动态共享内存实现 ... posix
正在选择默认最大连接数 (max_connections) ... 100
正在选择默认共享缓冲区 (shared_buffers) ... 128MB
正在选择默认时区 ... Asia/Shanghai
正在创建配置文件 ... 成功
正在运行启动脚本 ... 成功
正在执行启动脚本后续初始化 ... 成功
正在同步数据到磁盘 ... 成功

initdb: 警告: 为本地连接启用 "trust" 身份验证
initdb: 提示: 可通过编辑 pg_hba.conf 或下次运行 initdb 时使用 -A 或者 --auth-local 和 --auth-host 选项更改。

成功。你现在可以用下面的命令开启数据库服务器:

    /usr/local/bin/pg_ctl -D /var/db/postgres/data18 -l logfile start

```

## 登录使用

PostgreSQL 默认没有 root 用户，需要使用安装时创建的 `postgres` 用户登录。

示例输出：

```sh
# psql
psql: 错误: 连接到套接字"/tmp/.s.PGSQL.5432"上的服务器失败:致命错误:  角色 "root" 不存在
```

正确用法：

```sh
# su - postgres	    # 切换到 postgres 用户以管理 PostgreSQL 数据库

-- 启动服务，注意 data 目录后的版本号可能与本节示例不同
$ /usr/local/bin/pg_ctl -D /var/db/postgres/data18 -l logfile start
等待服务器进程启动 .... 完成
服务器进程已经启动

# 创建新用户 ykla，并设置密码
$ createuser -sdrP ykla
为新角色输入的口令:
再输入一遍:
$
-- 创建 PostgreSQL 数据库
$ createdb new_db
-- 登录 PostgreSQL 数据库并将数据库权限赋予用户 ykla。
$ psql
psql (18rc1)
输入 "help" 来获取帮助信息.

postgres=# ALTER USER ykla WITH ENCRYPTED PASSWORD 'password';
ALTER ROLE
postgres=#
postgres=# GRANT ALL PRIVILEGES ON DATABASE new_db TO ykla;
GRANT
-- 退出 PostgreSQL 数据库
postgres=# \q
$ exit
root@ykla:~ #
```

## PostgreSQL 服务管理

### 初始化建议

初始化数据库推荐使用 `service postgresql initdb` 命令，最为简便。

以下说明如何使用 `postgresql_initdb_flags` rc 脚本参数。

`postgresql_initdb_flags` 参数默认是 `"--encoding=utf-8 --lc-collate=C"`，其含义是：指定编码为 UTF-8，排序规则为 C。

建议用 `sysrc` 设置如下：

```sh
# sysrc postgresql_initdb_flags="--encoding=utf-8 --lc-collate=C -A scram-sha-256 -W"
```

设置 PostgreSQL 初始化数据库的参数：UTF-8 编码、C 排序规则、SCRAM-SHA-256 认证（一种基于 Salted Challenge Response Authentication Mechanism 的安全认证协议），并要求输入密码。

解释：

* `-A scram-sha-256` 指定默认的认证方法，否则 `pg_hba.conf` 中默认认证方法为 `trust`，即无密码登录；
* `-W` 在初始化时要求设置 `postgres` 用户密码，无需登录后再设置。

这样可以简化部分初始化操作，也可通过 `postgresql_initdb_flags` 参数自定义编码、认证方式等初始化选项。

### 管理多个数据库实例（集簇）

PostgreSQL 能在一台机器上运行多个实例，适用于测试不同配置、环境隔离等场景。

PostgreSQL 的 rc 脚本封装了这些管理功能，同样便捷。

下面将创建两个命名实例，分别为 main 和 dev。

#### 配置实例

```sh
# 增加一个 main 实例
# sysrc postgresql_profiles+=main

# 增加一个 dev 实例
# sysrc postgresql_profiles+=dev

# 当然可以直接一次设置多个实例
# sysrc postgresql_profiles="main dev"

# 对应 main 实例数据目录 ②
# sysrc postgresql_main_data=/var/db/postgres/main

# 对应 dev 实例数据目录 ③
# sysrc postgresql_dev_data=/var/db/postgres/dev

# 必须为每个实例设置数据目录
# （见上两条）

# 为 main 实例设置初始化参数 ①
# dev 实例默认初始化，作为对比
# sysrc postgresql_main_initdb_flags="-E utf-8 -A scram-sha-256 --lc-collate C -W"

# 为每个实例设置启用或关闭
# sysrc postgresql_main_enable=YES   # 设置 PostgreSQL 服务开机自启
# sysrc postgresql_dev_enable=YES    # 设置 PostgreSQL 开发服务开机自启
```

说明：

* ① 将实例名放入 rc 参数名中，即为对应实例的参数名。
* ② `postgresql_data`、`postgresql_enable` 等参数仍然可以使用。这些参数作为实例对应参数的默认值使用，在找不到对应实例参数时使用。但显式配置实例参数后，便不再使用默认参数。
* ③ 在管理多个实例时，建议只使用带实例名的参数。如此配置的优势在于清晰明确，控制也更灵活，不同实例可以有不同设置。

#### 初始化实例目录

配置完成后，即可初始化数据库实例。

初始化 PostgreSQL 数据库集簇：

```sh
# service postgresql initdb
```

该命令会初始化两个数据库实例。

也可以在命令末尾指定实例名分别初始化，这种方法适用于新增实例。

```sh
# service postgresql initdb main   # 初始化 PostgreSQL 主服务数据库
# service postgresql initdb dev    # 初始化 PostgreSQL 开发服务数据库
```

> **注意**
>
> 两个不同的实例必须运行在不同的端口上，因此需要修改对应实例的端口号。

其中 `main` 实例使用默认端口 `5432`，无需修改；`dev` 实例使用端口 `5433`，需修改其对应的配置文件。

修改 **/var/db/postgres/dev/postgresql.conf** 文件。找到 `port` 一行，修改如下：

```ini
port = 5433
```

设置 PostgreSQL 监听端口为 5433。

#### 启动、停止和重启

实例配置完成后，即可启动或停止。

```sh
# service postgresql start     # 启动 PostgreSQL 服务
# service postgresql status    # 查看 PostgreSQL 服务状态
# service postgresql restart   # 重启 PostgreSQL 服务
# service postgresql stop      # 停止 PostgreSQL 服务
```

以上操作针对所有实例。示例如下：

```sh
# service postgresql start # 启动服务
===> postgresql profile: main
start postgresql
===> postgresql profile: dev
start postgresql
# service postgresql status # 查看状态
===> postgresql profile: main
status postgresql
pg_ctl: server is running (PID: 2624)
/usr/local/bin/postgres "-D" "/var/db/postgres/main"
===> postgresql profile: dev
status postgresql
pg_ctl: server is running (PID: 2641)
/usr/local/bin/postgres "-D" "/var/db/postgres/dev"
# service postgresql restart # 重启服务
===> postgresql profile: main
restart postgresql
===> postgresql profile: dev
restart postgresql
# service postgresql status # 查看状态
===> postgresql profile: main
status postgresql
pg_ctl: server is running (PID: 2685)
/usr/local/bin/postgres "-D" "/var/db/postgres/main"
===> postgresql profile: dev
status postgresql
pg_ctl: server is running (PID: 2701)
/usr/local/bin/postgres "-D" "/var/db/postgres/dev"
# service postgresql stop # 停止服务
===> postgresql profile: main
stop postgresql
===> postgresql profile: dev
stop postgresql
# service postgresql status # 查看状态
===> postgresql profile: main
status postgresql
pg_ctl: no server running
===> postgresql profile: dev
status postgresql
pg_ctl: no server running
```

也可以指定实例名称，操作单个实例：

```sh
# service postgresql start dev     # 启动 PostgreSQL 开发服务
# service postgresql restart dev   # 重启 PostgreSQL 开发服务
# service postgresql status dev    # 查看 PostgreSQL 开发服务状态
# service postgresql stop dev      # 停止 PostgreSQL 开发服务
```

输出示例如下：

```sh
# service postgresql start dev     # 启动 PostgreSQL 开发服务
start postgresql
# service postgresql status dev    # 查看 PostgreSQL 开发服务状态
status postgresql
pg_ctl: server is running (PID: 2807)
/usr/local/bin/postgres "-D" "/var/db/postgres/dev"
# service postgresql restart dev   # 重启 PostgreSQL 开发服务
restart postgresql
# service postgresql stop dev      # 停止 PostgreSQL 开发服务
stop postgresql
```

## 参考文献

* The PostgreSQL Global Development Group. A Brief History of PostgreSQL\[EB/OL]. \[2026-04-17]. <https://www.postgresql.org/docs/current/history.html>. 记载了 POSTGRES 项目从 Ingres 后续发展到 PostgreSQL 的完整历程，包括 1989 年 6 月发布 Version 1 等关键时间节点。


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://book.bsdcn.org/di-21-zhang-shu-ju-ku-guan-li/di-21.2-jie-postgresql-shu-ju-ku.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.