19.1 PostgreSQL

PostgreSQL 是一款自由的对象关系型数据库（Object-Relational Database，ORDB）。PostgreSQL 最早发布于 1989 年 6 月（最初名为 POSTGRES）。

在 FreeBSD 上，PostgreSQL 有多个版本可选。在吞吐量、源代码可读性和响应时间等方面，PostgreSQL 相较于 MySQL 有一定优势。

值得一提的是，PostgreSQL 也渊源于加州大学伯克利分校。PostgreSQL 的许可证是 PostgreSQL 许可证 备份，与 BSDL 类似。

安装 PostgreSQL

以 PostgreSQL 18 为例，其他版本安装与配置方法类似。

使用 pkg 安装：

# pkg ins postgresql18-server

或者使用 ports 安装：

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

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

# 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.
并务必理解其对性能的影响（校验会带来额外开销）。

服务管理

设置 PostgreSQL 服务开机自启：

# service postgresql enable

初始化数据库

• 初始化 PostgreSQL 数据库集群：

# service postgresql initdb

• 示例输出（我设置了 i18n，如果你的输出是英文也是正常的）：初始化 PostgreSQL 数据库实例。

# 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，排序规则为 ASCII 编码。

建议用 sysrc 设置如下：

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

设置 PostgreSQL 初始化数据库的参数：UTF-8 编码、C 排序规则、SCRAM-SHA-256 认证，并要求输入密码。

解释：

• -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。

配置实例

# 增加一个 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