33.2 PostgreSQL

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

PostgreSQL 采用 PostgreSQL 许可证，该许可证与 BSD 许可证类似，同为宽松开源许可证。

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

安装 PostgreSQL

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

使用 pkg 安装：

# pkg install postgresql18-server

或者使用 Ports 安装：

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

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

# pkg info -D postgresql18-server

文件结构

PostgreSQL 的文件结构如下。

/
├── 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 服务开机自启：

# service postgresql enable

初始化数据库

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

初始化 PostgreSQL 数据库集簇：

# service postgresql initdb

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

# 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 用户登录。

示例输出：

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

正确用法：

# 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 设置如下：

# 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 脚本封装了这些管理功能，同样便捷。

FreeBSD 主机
   │
   ├── main 实例
   │   ├── 数据目录：/var/db/postgres/main
   │   ├── 端口：5432
   │   └── 配置：postgresql.conf
   │
   └── dev 实例
       ├── 数据目录：/var/db/postgres/dev
       ├── 端口：5433
       └── 配置：postgresql.conf

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

配置实例

# 增加一个 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 数据库集簇：

# service postgresql initdb

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

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

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

注意

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

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

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

port = 5433

设置 PostgreSQL 监听端口为 5433。

启动、停止和重启

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

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

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

# 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

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

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

输出示例如下：

# 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 等关键时间节点。