PostgreSQL

PostgreSQL 是一个开源、社区驱动且符合标准的开源对象关系数据库系统。

安装

警告在升级 PostgreSQL 软件包到新版本之前，请参阅 #升级 PostgreSQL 获取必要的步骤。

安装 postgresql 软件包。安装程序还会创建一个名为 postgres 的系统用户。

现在，您可以使用特权提升程序切换到 postgres 用户。

初始配置

在 PostgreSQL 正常工作之前，必须初始化数据库集群。

[postgres]$ initdb -D /var/lib/postgres/data

其中 -D 是数据库集群必须存储的默认位置（如果您想使用其他位置，请参阅 #更改默认数据目录）。initdb 接受许多额外的参数。

本文章或章节需要扩充。

原因： PostgreSQL 也支持 ICU 区域设置。[1]（在 Talk:PostgreSQL 中讨论）

默认情况下，数据库集群的区域设置和编码来源于您当前的系统环境（使用 $LANG 值）。如果这不是您想要的，可以使用 --locale=locale（locale 需从系统可用的区域设置中选择）和 --encoding=encoding（必须与所选区域设置匹配）来覆盖默认值。（数据库启动后，可以使用 [postgres]$ psql -l 检查使用了哪些值。）
注意使用 C.UTF-8、C、POSIX 或 ucs_basic 以外的区域设置可能会导致排序规则版本不匹配，如果提供区域设置的库（glibc 或 icu）更新，则需要重新建立索引。
如果您的数据目录位于没有数据校验和的文件系统上，您可能希望启用 PostgreSQL 内置的校验和以获得更高的完整性保证 —— 为此请添加 --data-checksums 参数。阅读 #启用数据校验和获取更多信息。（数据库启动后，可以使用 [postgres]$ psql --tuples-only -c "SHOW data_checksums" 检查它是否已启用。）

注意 /var/lib/postgres/data/ 目录设置了 C (No_COW) 文件属性 [2]。这在 Btrfs 中禁用了校验和。

默认使用 trust 身份验证方法，这意味着主机上的任何人都可以以任何数据库用户的身份连接。您可以使用 --auth-local=peer --auth-host=scram-sha-256 来获得更安全的身份验证方法。
-c/--set 选项可用于设置任何 postgresql.conf 参数，无需手动编辑 postgresql.conf。
有关更多选项，请参阅 initdb --help 和官方文档。

示例

[postgres]$ initdb --locale=C.UTF-8 --encoding=UTF8 -D /var/lib/postgres/data --data-checksums

此时屏幕上应该会出现许多行，其中几行以 ... ok 结尾。

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "C.UTF-8".
The default text search configuration will be set to "english".

Data page checksums are enabled.

creating directory /var/lib/postgres/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /var/lib/postgres/data -l logfile start

如果您看到了这些行，则说明该过程已成功。使用 exit 返回常规用户。

警告

要了解关于此 initdb 警告的更多信息，请参阅 #默认限制数据库超级用户的访问权限。
如果数据库位于 Btrfs 文件系统上，则在创建任何数据库之前，您应该考虑对该目录禁用写时复制 (CoW)。
如果数据库位于 ZFS 文件系统上，在创建任何数据库之前，您应咨询 ZFS#Databases。

提示如果您将根目录更改为 /var/lib/postgres 以外的目录，则必须编辑服务文件。如果根目录在 home 下，请确保将 ProtectHome 设置为 false。

最后，启动并启用 postgresql.service。

创建您的第一个数据库/用户

提示如果您创建的 PostgreSQL 角色/用户与您的 Linux 用户名相同，那么您无需指定登录用户即可访问 PostgreSQL 数据库 shell（这非常方便）。

切换到 postgres 用户。使用 createuser 命令添加一个新的数据库角色/用户：

[postgres]$ createuser --interactive

使用 createdb 命令创建一个新数据库，上述用户在该数据库上拥有读/写权限（如果数据库用户与您的 Linux 用户同名，请从您的登录 shell 执行此命令，否则在以下命令中添加 -O database-username）：

$ createdb myDatabaseName

提示如果您没有授予新用户创建数据库的权限，请在上一个命令中添加 -U postgres。

熟悉 PostgreSQL

访问数据库 shell

切换到 postgres 用户。启动主要数据库 shell psql，您可以在其中进行所有数据库/表的创建、删除、权限设置，并运行原始 SQL 命令。使用 -d 选项连接到您创建的数据库（如果不指定数据库，psql 将尝试访问与您的用户名匹配的数据库）。

[postgres]$ psql -d myDatabaseName

一些有用的命令

获取帮助

=> \help

列出所有数据库

=> \l

连接到特定数据库

=> \c database

列出所有用户及其权限级别

=> \du

显示当前数据库中所有表的摘要信息

=> \dt

退出/关闭 psql shell

=> \q

或按 Ctrl+d。

当然还有许多其他元命令，但这些应该足以让您入门。要查看所有元命令，请运行：

=> \?

可选配置

PostgreSQL 数据库服务器配置文件是 postgresql.conf。该文件位于服务器的数据目录中，通常是 /var/lib/postgres/data。该文件夹还存放其他主要配置文件，包括定义本地用户和其他主机用户身份验证设置的 pg_hba.conf。

注意默认情况下，普通用户无法浏览或搜索此文件夹。这就是 find 和 locate 找不到配置文件的原因。

默认限制数据库超级用户的访问权限

默认的 pg_hba.conf 允许任何本地用户以任何数据库用户的身份连接，包括数据库超级用户。这可能不是您想要的，因此为了限制对 postgres 用户的全局访问，请更改以下行：

/var/lib/postgres/data/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust

改为：

/var/lib/postgres/data/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             postgres                                peer

根据您的需要或软件需求，以后可以添加额外的行。

登录时要求密码

编辑 /var/lib/postgres/data/pg_hba.conf 并将每个用户（或 all 以影响所有用户）的身份验证方法设置为 scram-sha-256：

/var/lib/postgres/data/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             user                                    scram-sha-256

注意更改 pg_hba.conf 中的身份验证方法不会更新存储在数据库中的哈希密码 [3]。要从 md5 迁移到 scram-sha-256，您需要为每个数据库用户设置一个新密码。

重启 postgresql.service，然后使用 ALTER USER user WITH ENCRYPTED PASSWORD 'password'; 重新添加每个用户的密码。

配置 PostgreSQL 仅通过 UNIX 套接字访问

在初始创建集群时，将 -c listen_addresses='' 附加到 initdb 命令。

对于现有集群，编辑 postgresql.conf，并在连接和身份验证部分设置：

/var/lib/postgres/data/postgresql.conf

listen_addresses = ''

这将完全禁用网络监听。之后，您应该重启 postgresql.service 以使更改生效。

配置 PostgreSQL 以支持从远程主机访问

在连接和身份验证部分，根据需要设置 listen_addresses 行：

/var/lib/postgres/data/postgresql.conf

listen_addresses = 'localhost,my_local_ip_address'

您可以使用 '*' 监听所有可用地址。

注意 PostgreSQL 默认使用 TCP 端口 5432 进行远程连接。请确保此端口在您的防火墙中打开并能够接收传入连接。您也可以在 listen_addresses 下方的配置文件中更改它。

然后在身份验证配置中添加如下行：

/var/lib/postgres/data/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# IPv4 local connections:
host    all             all             ip_address/32           scram-sha-256

其中 ip_address 是远程客户端的 IP 地址。

请参阅 pg_hba.conf 的文档。

注

本文或本章节的准确性存在争议。

原因： 官方文档称 md5 使用挑战-响应身份验证，“防止密码嗅探”。也许它不应该被认为像明文发送密码那样不安全。（在 Talk:PostgreSQL 中讨论）

如果不通过 TLS 安全连接，通过 Internet 发送明文密码或 md5 哈希都是不安全的。请参阅 Secure TCP/IP Connections with SSL 了解如何配置带有 TLS 的 PostgreSQL。

之后，您应该重启 postgresql.service 以使更改生效。

如需故障排除，请查看服务器日志文件。

# journalctl -u postgresql.service

配置 PostgreSQL 使用 PAM 进行身份验证

PostgreSQL 提供了多种身份验证方法。如果您希望允许用户使用其系统密码进行身份验证，则需要执行额外步骤。首先，您需要为连接启用 PAM。

例如，与上面相同的配置，但启用了 PAM：

/var/lib/postgres/data/pg_hba.conf

# IPv4 local connections:
host   all   all   my_remote_client_ip_address/32   pam

然而，PostgreSQL 服务器在没有 root 权限的情况下运行，将无法访问 /etc/shadow。我们可以通过允许 postgres 组访问此文件来解决这个问题：

# setfacl -m g:postgres:r /etc/shadow

更改默认数据目录

存储所有新创建数据库的默认目录是 /var/lib/postgres/data。要更改此设置，请执行以下步骤：

创建新目录并使 postgres 用户成为其所有者：

# mkdir -p /pathto/pgroot/data
# chown -R postgres:postgres /pathto/pgroot

切换到 postgres 用户，并初始化新集群：

[postgres]$ initdb -D /pathto/pgroot/data

编辑 postgresql.service 以创建覆盖文件 (drop-in file)，并覆盖 Environment 和 PIDFile 设置。例如：

/etc/systemd/system/postgresql.service.d/PGROOT.conf

[Service]
Environment=PGROOT=/pathto/pgroot
PIDFile=/pathto/pgroot/data/postmaster.pid

如果您想将 /home 目录用作默认目录或表空间，请在此文件中再添加一行：

ProtectHome=false

将新数据库的默认编码更改为 UTF-8

注意如果您在运行 initdb 时使用了 -E UTF8 或使用了 UTF-8 区域设置，则不需要这些步骤。

当创建新数据库（例如使用 createdb blog）时，PostgreSQL 实际上会复制一个模板数据库。有两个预定义的模板：template0 是原始模板，而 template1 是由管理员更改的现场模板，并默认使用。为了更改新数据库的编码，一种选择是更改现场的 template1。为此，请登录 PostgreSQL shell (psql) 并执行以下操作：

首先，我们需要删除 template1。模板无法直接删除，所以我们先将其修改为普通数据库：

UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template1';

现在我们可以删除它了：

DROP DATABASE template1;

下一步是从 template0 创建一个具有新默认编码的新数据库：

CREATE DATABASE template1 WITH TEMPLATE = template0 ENCODING = 'UNICODE';

现在修改 template1，使其成为一个模板：

UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1';

（可选）如果您不希望任何人连接到此模板，请将 datallowconn 设置为 FALSE：

UPDATE pg_database SET datallowconn = FALSE WHERE datname = 'template1';

注意最后一步可能会在通过 pg_upgrade 升级时产生问题。

现在您可以创建一个新数据库了。

[postgres]$ createdb blog

如果您重新登录 psql 并检查数据库，应该可以看到新数据库的正确编码：

\l

                              List of databases
  Name    |  Owner   | Encoding  | Collation | Ctype |   Access privileges
-----------+----------+-----------+-----------+-------+----------------------
blog      | postgres | UTF8      | C         | C     |
postgres  | postgres | SQL_ASCII | C         | C     |
template0 | postgres | SQL_ASCII | C         | C     | =c/postgres
                                                     : postgres=CTc/postgres
template1 | postgres | UTF8      | C         | C     |

启用数据校验和

本文或本章节已过时。

原因： 自 PostgreSQL 18 起，数据校验和默认启用。在运行 initdb 时，可以使用 --no-data-checksums 选项禁用数据校验和。（在 Talk:PostgreSQL 中讨论）

如果您的数据库文件位于没有校验和的文件系统上，其数据容易受到位衰减和硬件故障导致的静默数据损坏的影响。虽然这种情况很少见，但如果您关心数据完整性，可能需要启用 PostgreSQL 内置的数据校验和。此功能必须在集群级别启用，而不是在每个数据库或每个表级别。

注意此功能有几个注意事项：

它有最小的性能影响，尤其是在从磁盘读取大型数据集时。内存中操作不受影响。
PostgreSQL 无法修复损坏的数据——它只会中止读取损坏页面的事务，以防止进一步损坏或无效的执行结果。
校验和仅覆盖磁盘上的数据（行）页面，不覆盖元数据或控制结构。内存中的页面不会进行校验和。纠错存储和 ECC 内存仍然有益。

要在集群创建期间启用校验和，请将 --data-checksums 参数添加到 initdb。
要验证是否启用了校验和，请运行 [postgres]$ psql --tuples-only -c "SHOW data_checksums"（应该打印 off 或 on）。
要在现有集群上切换校验和：

停止 postgresql.service。
运行 [postgres]$ pg_checksums --pgdata /var/lib/postgres/data --enable（如果不再需要校验和，则使用 --disable）。启用校验和将重写所有数据库页面，这对于大型数据库实例需要一些时间。
启动 postgresql.service。

图形工具

phpPgAdmin — 基于 Web 的 PostgreSQL 管理工具。

https://github.com/phppgadmin/phppgadmin || phppgadmin^AUR

pgAdmin — PostgreSQL 的综合设计和管理 GUI。

https://www.pgadmin.org/ || Web 界面: pgadmin4-web^AUR，桌面应用程序（基于 Electron）: pgadmin4-desktop^AUR

pgModeler — PostgreSQL 的图形化模式设计器。

https://pgmodeler.io/ || pgmodeler^AUR

Postbird — 跨平台 PostgreSQL GUI 客户端，使用 JavaScript 编写，运行在 Electron 上。

https://github.com/paxa/postbird || postbird-bin^AUR

rainfrog — Postgres 的数据库管理 TUI。

https://github.com/achristmascarl/rainfrog || rainfrog

pgweb — 用于 PostgreSQL 数据库的跨平台 Web 客户端。

https://sosedoff.github.io/pgweb || pgweb-bin^AUR

对于支持多种 DBMS 的工具，请参阅 List of applications/Documents#Database tools。

设置备份

建议为包含有价值数据的数据库设置备份。请参阅 PostgreSQL 文档中的备份和恢复一章。PostgreSQL wiki 中还有一个备份工具列表，尽管它可能不是最新或完整的。请记住，除非您不时执行测试恢复，否则备份系统是不可信的！

升级 PostgreSQL

本文章或章节需要扩充。

原因： 使用第三方扩展时如何升级？（在 Talk:PostgreSQL#pg_upgrade problem if extensions (like postgis) are used 中讨论）

升级 PostgreSQL 大版本（例如 14.x 到 15.y）需要一些额外的维护。

注意应遵循官方 PostgreSQL 升级文档。

警告以下说明可能会导致数据丢失。不要盲目执行以下命令，而不了解其作用。请先进行备份。

通过以下命令获取当前使用的数据库版本：

# cat /var/lib/postgres/data/PG_VERSION

为确保您不会意外地将数据库升级到不兼容的版本，建议跳过更新 PostgreSQL 软件包。

小版本升级是安全的。但是，如果您意外升级到不同的大版本，可能无法访问任何数据。请务必查看 PostgreSQL 主页，以确定每次升级所需的步骤。关于为什么会这样，请参阅版本控制策略。

注意如果您使用扩展，请检查 #使用扩展时，PostgreSQL 数据库在软件包更新后无法启动和 #升级并使用扩展时，无法使用旧版本数据库启动 PostgreSQL 服务器。

升级 PostgreSQL 数据库主要有两种方式。详情请阅读官方文档。

pg_upgrade

pg_upgrade 实用程序尝试在集群之间复制尽可能多的兼容数据，并升级其他所有内容。它通常是升级大多数实例的最快方法，尽管它需要访问源 PostgreSQL 版本和目标 PostgreSQL 版本的二进制文件。阅读 pg_upgrade(1) 手册页以了解它执行的操作。对于非平凡的实例（例如具有流复制或日志传送的实例），请先阅读上游文档。

对于希望使用 pg_upgrade 的用户，可以使用 postgresql-old-upgrade 软件包，它将始终运行在实际 PostgreSQL 软件包之后的一个大版本。它可以与新版本的 PostgreSQL 并行安装。要从旧版本的 PostgreSQL 升级，有 AUR 软件包可用，例如 postgresql-12-upgrade^AUR。（您必须使用随您要升级到的 PostgreSQL 版本打包的 pg_upgrade 版本）。如果使用 PostGIS，请安装 postgis-old-upgrade^AUR。

请注意，数据库集群目录在版本之间不会发生变化，因此在运行 pg_upgrade 之前，有必要重命名现有数据目录并迁移到新目录中。新数据库集群必须使用与旧集群相同的参数进行初始化。

当您准备开始升级时：

在旧数据库集群仍然在线时，收集用于创建它的 initdb 参数。有关更多信息，请参阅 #初始配置。
停止 postgresql.service。检查单元状态，确保 PostgresSQL 已正确停止。如果失败，pg_upgrade 将会报错 The source cluster was not shut down cleanly。
升级 postgresql、postgresql-libs 和 postgresql-old-upgrade。
确保 /var/lib/postgres/olddata 不存在。如果您在上次升级后没有删除它，请立即删除。

重命名旧集群目录，然后创建一个新集群和临时工作目录：

# mv /var/lib/postgres/data /var/lib/postgres/olddata
# mkdir /var/lib/postgres/data /var/lib/postgres/tmp
# chown postgres:postgres /var/lib/postgres/data /var/lib/postgres/tmp
[postgres]$ cd /var/lib/postgres/tmp

使用与旧集群相同的 initdb 参数初始化新集群：

[postgres]$ initdb -D /var/lib/postgres/data --locale=C.UTF-8 --encoding=UTF8 --data-checksums

升级集群，将下面的 PG_VERSION 替换为旧的 PostgreSQL 版本号（例如 15）：
```
[postgres]$ pg_upgrade -b /opt/pgsql-PG_VERSION/bin -B /usr/bin -d /var/lib/postgres/olddata -D /var/lib/postgres/data
```
提示在支持 reflinks 的文件系统（例如 Btrfs 和 XFS）上，附加 --clone 选项可以加速文件复制。
如有必要，调整新集群的配置文件（例如 pg_hba.conf 和 postgresql.conf）以匹配旧集群。
启动 postgresql.service。
可选： 运行 [postgres]$ vacuumdb --all --analyze-in-stages 重新计算查询分析器统计信息，这应该会提高查询性能。（添加 --jobs=NUMBER_OF_CPU_CORES 参数可能会提高此命令的性能。）
可选： 备份 /var/lib/postgres/olddata 目录，以防需要恢复到以前的 PostgreSQL 版本。
删除包含旧集群数据的 /var/lib/postgres/olddata 目录。
删除 /var/lib/postgres/tmp 目录。
如果您使用 pgbackrest，请运行 stanza-upgrade 命令。

手动导出与导入

您也可以执行类似这样的操作（在升级和安装 postgresql-old-upgrade 之后）。

注

以下是从 PostgreSQL 14 升级的命令。只要安装了匹配版本的 postgresql-old-upgrade 软件包，您就可以在 /opt/ 中找到适用于您 PostgreSQL 集群版本的类似命令。
如果您自定义了 pg_hba.conf 文件，则可能需要暂时修改它以允许从本地系统完全访问旧数据库集群。升级完成后，将您的自定义设置应用到新数据库集群，并重启 postgresql.service。

停止 postgresql.service

# mv /var/lib/postgres/data /var/lib/postgres/olddata
# mkdir /var/lib/postgres/data
# chown postgres:postgres /var/lib/postgres/data
[postgres]$ initdb -D /var/lib/postgres/data --locale=C.UTF-8 --encoding=UTF8 --data-checksums
[postgres]$ /opt/pgsql-14/bin/pg_ctl -D /var/lib/postgres/olddata/ start
# cp /usr/lib/postgresql/postgis-3.so /opt/pgsql-14/lib/ # Only if postgis installed
[postgres]$ pg_dumpall -h /tmp -f /tmp/old_backup.sql
[postgres]$ /opt/pgsql-14/bin/pg_ctl -D /var/lib/postgres/olddata/ stop

启动 postgresql.service

[postgres]$ psql -f /tmp/old_backup.sql postgres

故障排除

提高小型事务的性能

如果您在本地开发机器上使用 PostgreSQL 并且感觉很慢，可以尝试在配置中关闭 synchronous_commit off。不过要注意注意事项。

/var/lib/postgres/data/postgresql.conf

synchronous_commit = off

使用扩展时，PostgreSQL 数据库在软件包更新后无法启动

这种情况的原因通常是现有的软件包不是为新版本编译的（即使它是最新的），解决方法是手动重新构建软件包，或等待扩展软件包的更新。

升级并使用扩展时，无法使用旧版本数据库启动 PostgreSQL 服务器

这是因为来自 postgresql-old-upgrade 软件包的旧版本 postgres 在其 lib 目录中没有所需的扩展（.so 文件）。当前的解决方案比较繁琐，可能会导致很多问题，因此请务必保留数据库备份以防万一。基本上是将 /usr/lib/postgresql/ 或单个 .so 文件备份到单独的临时位置，升级 postgresql-old-upgrade、postgresql 等，然后将之前备份的文件恢复到 /opt/pgsql-XX/lib/ 中（记得将 XX 替换为 postgresql-old-upgrade 的大版本号）。

例如，对于 vectorchord：

# mkdir /tmp/pgsql_update
# cp -a /usr/lib/postgresql/vchord.so*.so /tmp/pgsql_update
# pacman -Syu
# cp -a /tmp/pgsql_update/*.so /opt/pgsql-17/lib/

警告虽然复制 .so 文件对我来说足够了，但可能需要将更多文件复制到 /opt/pgsql-XX/ 下的正确目录中。

要了解要复制的确切文件，请使用以下命令检查扩展软件包的内容：

$ pacman -Ql package_name

警告这是一个非常肮脏的解决方案，可能会损坏数据库或导致数据丢失，所以请保留备份。

警告：数据库 "postgres" 的排序规则版本不匹配

您可能会看到类似以下内容：

WARNING:  database "postgres" has a collation version mismatch
DETAIL:  The database was created using collation version X.YY, but the operating system provides version X.ZZ.
HINT:  Rebuild all objects in this database that use the default collation and run ALTER DATABASE postgres REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.

这意味着排序规则提供程序库（glibc 或 icu）已更新，这可能导致某些索引无效。因此需要重新索引这些数据库。

您可以使用以下命令执行此操作：

[postgres]$ psql -c 'REINDEX DATABASE' postgres
[postgres]$ psql -c 'ALTER DATABASE postgres REFRESH COLLATION VERSION'

通过将 postgres 替换为相应的数据库名称，对所有其他数据库重复上述操作。

提示使用 C.UTF-8、C、POSIX 或 ucs_basic 区域设置进行数据库集群可以避免此问题。