第 19.1 节 PostgreSQL

PostgreSQL 是一款自由的对象关系型数据库（Object-Relational Database，ORDB）。PostgreSQL 最早发布于 1989 年 6 月。在 FreeBSD 上，有多个版本可选。在吞吐量、源代码清晰度、平均耗时等多方面均优于 MySQL。

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

安装 PostgreSQL

以 PostgreSQL 17 为例，其余版本大同小异。

# pkg install -y postgresql17-server

或者

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

查看安装后配置

# pkg info -D postgresql17-server
postgresql17-server-17.5:
On install:
安装时说明：
For procedural languages and postgresql functions, please note that  
对于过程语言（procedural languages）和 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.  
也希望定期执行 vacuum 并备份你的数据库。  
There 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  
你可能会觉得有用。你可以用它来备份并对所有  
databases nightly. Per default, it performs `vacuum analyze'. See the  
数据库每晚执行 vacuum。默认行为是执行 `vacuum analyze`。请查看  
script for instructions. For autovacuum settings, please review  
该脚本获取说明。关于自动清理设置，请查阅  
~postgres/data/postgresql.conf.  
~postgres/data/postgresql.conf。  

If you plan to access your PostgreSQL server using ODBC, please  
如果你打算通过 ODBC 访问你的 PostgreSQL 服务器，请  
consider running the SQL script /usr/local/share/postgresql/odbc.sql  
考虑运行 SQL 脚本 /usr/local/share/postgresql/odbc.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  
为了设置限制、语言环境（locale）、排序规则（collation）及其他  
things, you can set up a class in /etc/login.conf before initializing  
内容，你可以在初始化数据库前，在 /etc/login.conf 中设置一个类。  
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:  
---  
and run `cap_mkdb /etc/login.conf'.  
然后运行 `cap_mkdb /etc/login.conf`。  
Then add 'postgresql_login_class="postgres"' to /etc/rc.conf, or  
再将 'postgresql_login_class="postgres"' 添加到 /etc/rc.conf，或者  
set it as the postgres user's login class in /etc/passwd.  
将其作为 postgres 用户在 /etc/passwd 中的登录类设置。  

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

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 的 PostgreSQL Port 默认将日志记录到 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  
    希望启用数据校验和。它可以在  
    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  
    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.  
    并确保你理解它对性能的影响。

服务管理

# service postgresql enable

初始化数据库

# service postgresql initdb

示例输出（我设置了 i18n，如果你是输出是英文也是正常的）：

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/data17 ... 成功
正在创建子目录 ... 成功
正在选择动态共享内存实现 ... 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/data17 -l logfile start

登录使用

Postgresql 默认是没有 root 用户的，需要使用其创建的用户 postgres 登录。

示例输出：

root@ykla:~ # psql
psql: FATAL:  role "root" does not exist

正确用法：

#切换用户
root@ykla:~ # su - postgres

#启动服务，注意 data 后边的版本你可能和我不同
$ /usr/local/bin/pg_ctl -D /var/db/postgres/data17 -l logfile start
等待服务器进程启动 .... 完成
服务器进程已经启动

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

postgres=# ALTER USER ykla WITH ENCRYPTED PASSWORD 'password';
ALTER ROLE
postgres=#
postgres=# GRANT ALL PRIVILEGES ON DATABASE new_db TO ykla;
GRANT
# 退出数据库
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"

解释：

• -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  
# sysrc postgresql_dev_enable=YES  

说明：

• ① 把实例名放入 rc 参数名中，就是对应实例的参数名。

• ② postgresql_data、postgresql_enable 等参数还是可以使用。这时这些参数作为实例对应参数的默认值使用，在找不到实例参数时使用。但当你显式配置了实例参数后，就不会使用默认参数了。

• ③ 在管理多个实例时，建议只使用实例名相关的参数，不去使用没有实例名的参数。好处是清晰明确，控制上也灵活，不同实例可以有不同设置。

初始化实例目录

# service postgresql initdb

这会初始化两个数据库实例。

也可以在命令最后指定实例名分别进行初始化，在新增实例时使用这种方法：

# service postgresql initdb main
# service postgresql initdb dev

注意：

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

这里 main 实例使用默认的 5432 端口，不作修改；dev 实例使用 5433 端口，修改对应配置文件

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

port = 5433

启动停止和重启

# service postgresql start
# service postgresql status
# service postgresql restart
# service postgresql stop

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

# 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)
/s/usr/local/bin/postgres "-D" "/var/db/postgres/main"
===> postgresql profile: dev
status postgresql
pg_ctl: server is running (PID: 2641)
/s/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)
/s/usr/local/bin/postgres "-D" "/var/db/postgres/main"
===> postgresql profile: dev
status postgresql
pg_ctl: server is running (PID: 2701)
/s/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
# service postgresql restart dev
# service postgresql status dev
# service postgresql stop dev

输出示例如下：

# service postgresql start dev
start postgresql
# service postgresql status dev
status postgresql
pg_ctl: server is running (PID: 2807)
/s/usr/local/bin/postgres "-D" "/var/db/postgres/dev"
# service postgresql restart dev
restart postgresql
# service postgresql stop dev
stop postgresql