# 19.1 PostgreSQL 数据库

PostgreSQL 是一款自由的对象关系型数据库管理系统（Object-Relational Database Management System，ORDBMS），在现代数据管理领域兼具重要学术价值与广泛产业应用。其学术源流可追溯至与 BSD 操作系统同源的加州大学伯克利分校的 Ingres 项目，首个版本于 1989 年 6 月发布，当时命名为 POSTGRES。

PostgreSQL 采用 [PostgreSQL 许可证](https://www.postgresql.org/about/licence/)，这是一种与 BSD 许可证类似的宽松开源许可证。

在 FreeBSD 操作系统环境下，PostgreSQL 提供了多个稳定版本供用户选择。作为严格遵循 ACID（原子性、一致性、隔离性、持久性）特性的数据库系统，PostgreSQL 在事务处理机制、复杂查询优化和数据完整性约束保障方面表现优异。

## 安装 PostgreSQL

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

使用 pkg 安装：

```sh
# pkg ins postgresql18-server
```

或者使用 ports 安装：

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

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

```sh
# pkg info -D postgresql18-server
postgresql18-server-18.r1:
软件包：postgresql18-server，版本 18.r1

On install:
安装时提示：

For procedural languages and postgresql functions, please note that
对于过程式语言与 PostgreSQL 函数，请注意：

you might have to update them when updating the server.
在升级数据库服务器时，这些扩展/函数也可能需要一并更新。

If you have many tables and many clients running, consider raising
如果表很多、并发客户端也很多，建议考虑提高系统允许打开文件的上限，

kern.maxfiles using sysctl(8), or reconfigure your kernel
可通过 sysctl(8) 调整 kern.maxfiles，或相应地重新配置内核，

appropriately.
以避免“文件描述符不足”等问题。

The port is set up to use autovacuum for new databases, but you might
该 Port 为新建数据库默认启用 autovacuum，但你仍可能需要

also want to vacuum and perhaps backup your database regularly. There
定期手动 vacuum，且定期备份数据库。系统提供了一个定时脚本，

is a periodic script, /usr/local/etc/periodic/daily/502.pgsql, that
位于 /usr/local/etc/periodic/daily/502.pgsql，

you may find useful. You can use it to backup and perform vacuum on all
你可以使用它对所有数据库进行备份并执行 vacuum，

databases nightly. Per default, it performs `vacuum analyze'. See the
默认是每晚执行一次；默认动作为 `vacuum analyze`。具体用法见脚本注释。

script for instructions. For autovacuum settings, please review
关于 autovacuum 的详细配置，请检查

~postgres/data/postgresql.conf.
~postgres/data/postgresql.conf（postgres 用户家目录下的数据目录配置文件）。

If you plan to access your PostgreSQL server using ODBC, please
如果计划通过 ODBC 访问 PostgreSQL 服务器，

consider running the SQL script /usr/local/share/postgresql/odbc.sql
请考虑运行 /usr/local/share/postgresql/odbc.sql 这个 SQL 脚本，

to get the functions required for ODBC compliance.
以创建 ODBC 兼容所需的函数。

Please note that if you use the rc script,
请注意：如果你使用 rc 脚本

 /usr/local/etc/rc.d/postgresql, to initialize the database, unicode
/usr/local/etc/rc.d/postgresql 来初始化数据库，则默认会使用 Unicode

 (UTF-8) will be used to store character data by default.  Set
（UTF-8）来存储字符数据。你可以设置

 postgresql_initdb_flags or use login.conf settings described below to
postgresql_initdb_flags，或者使用下文提到的 login.conf 设置，

 alter this behaviour. See the start rc script for more info.
来改变这一默认行为。更多信息参见该启动脚本头部说明。

To set limits, environment stuff like locale and collation and other
若要设置资源限制、区域/排序等环境相关参数，

things, you can set up a class in /etc/login.conf before initializing
可以在初始化数据库前，先在 /etc/login.conf 中定义一个 login class，

the database. Add something similar to this to /etc/login.conf:
并将类似如下的条目加入 /etc/login.conf：

---
postgres:\
	:lang=en_US.UTF-8:\
	:setenv=LC_COLLATE=C:\
	:tc=default:
---
上面这段是示例 login class：设置语言为 en_US.UTF-8，并将 LC_COLLATE 设为 C。

and run `cap_mkdb /etc/login.conf'.
随后运行 `cap_mkdb /etc/login.conf` 以生成数据库。

Then add 'postgresql_login_class="postgres"' to /etc/rc.conf, or
然后在 /etc/rc.conf 中加入 'postgresql_login_class="postgres"'，

set it as the postgres user's login class in /etc/passwd.
或直接在 /etc/passwd 里将 postgres 用户的 login class 设为 postgres。

======================================================================

To use PostgreSQL, enable it in rc.conf using
要启用 PostgreSQL，请在 rc.conf 中打开服务：

  sysrc postgresql_enable=yes

To initialize the database, run
初始化数据库：

  service postgresql initdb

You can then start PostgreSQL by running:
随后启动 PostgreSQL：

  service postgresql start

For postmaster settings, see ~postgres/data/postgresql.conf
关于 postmaster（服务进程）的设置，请查看 ~postgres/data/postgresql.conf。

NB. FreeBSD's PostgreSQL port logs to syslog by default
注意：FreeBSD 的 Port PostgreSQL 默认将日志写入 syslog，

    See ~postgres/data/postgresql.conf for more info
详情见 ~postgres/data/postgresql.conf 中的日志相关配置。

NB. If you're not using a checksumming filesystem like ZFS, you might
注意：如果你没有使用带校验的文件系统（如 ZFS），

    wish to enable data checksumming. It can be enabled during
可以考虑开启数据库的数据校验（checksumming）。该特性可在

    the initdb phase, by adding the "--data-checksums" flag to
initdb 阶段通过添加 `--data-checksums` 参数启用，

    the postgresql_initdb_flags rcvar. Otherwise you can enable it later by
也可以将该参数加入 rc 变量 postgresql_initdb_flags。若已完成初始化，

    using pg_checksums.  Check the initdb(1) manpage for more info
则可用 pg_checksums 事后开启。请参考 initdb(1) 手册获取更多信息，

    and make sure you understand the performance implications.
并务必理解其对性能的影响（校验会带来额外开销）。
```

## 相关文件结构

```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 源码端口
└── var/
    └── db/
        └── postgres/
            ├── data18/  # PostgreSQL 18 数据目录
            │   └── postgresql.conf  # PostgreSQL 配置文件
            ├── main/    # main 实例数据目录
            └── dev/     # dev 实例数据目录
```

## 服务管理

设置 PostgreSQL 服务开机自启：

```sh
# service postgresql enable
```

## 初始化数据库

数据库集群是 PostgreSQL 存储数据的目录集合，包含所有数据库文件和配置。本节介绍如何初始化 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 能在一台机器上运行多个实例，这在测试不同配置、实现环境隔离等场景中具有重要实用价值。

在 Debian/Ubuntu 系统上，管理多个数据库实例常用工具是 `pg_ctlcluster`，它封装了 `pg_ctl`。

FreeBSD 上并没有这个工具，但 FreeBSD 上 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
```

## 课后习题

1. 撰写更多 PostgreSQL 使用实例。
2. 查找 FreeBSD PostgreSQL rc 脚本源码，分析其多实例管理机制的实现方式，修改默认端口分配逻辑，实现实例名到端口号的自动映射。
3. 使用 PostgreSQL 部署一些常见服务，如 Typecho、WordPress，并完善它们对 PostgreSQL 的支持，贡献上游。