在Fedora29升级至Fedora30的过程中，解决了无法运行PostgreSQL的问题

2 年 ago

韵, 科

3 minutes

当我从Fedora 29升级到Fedora 30并打开网页http://heavymetalhardrock.no-ip.info时，遇到以下错误。

ERROR: SQLSTATE[08006] [7] could not connect to server: Connection refused Is the server running on host "localhost" (::1) and accepting TCP/IP connections on port 5432? could not connect to server: Connection refused Is the server running on host "localhost" (127.0.0.1) and accepting TCP/IP connections on port 5432?

看起来 PostgreSQL 没有启动。

我会确认。

# systemctl status postgresql
（表示）
● postgresql.service - PostgreSQL database server
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
Active: failed (Result: exit-code) since xxxxxxxx
Process: 810 ExecStartPre=/usr/libexec/postgresql-check-db-dir postgresql (code=exited, status=1/FAILURE)

May 10 01:15:07 xxxx systemd[1]: Starting PostgreSQL database server...
May 10 01:15:07 xxxx postgresql-check-db-dir[810]: An old version of the database format was found.
May 10 01:15:07 xxxx postgresql-check-db-dir[810]: Use 'postgresql-setup --upgrade' to upgrade to version '11'
May 10 01:15:07 xxxx postgresql-check-db-dir[810]: See /usr/share/doc/postgresql/README.rpm-dist for more information.
May 10 01:15:07 xxxx systemd[1]: postgresql.service: Control process exited, code=exited, status=1/FAILURE
May 10 01:15:07 xxxx systemd[1]: postgresql.service: Failed with result 'exit-code'.
May 10 01:15:07 xxxx systemd[1]: Failed to start PostgreSQL database server.

看起来，PostgreSQL 的主要版本已经从 10 升级到 11。
我执行了以下命令。

# postgresql-setup --upgrade
（表示）
* Upgrading database.
* Upgraded OK.
WARNING: The configuration files were replaced by default configuration.
WARNING: The previous configuration and data are stored in folder
WARNING: /var/lib/pgsql/data-old.
WARNING: If you've just upgraded your database from a previous major version of
Fedora or RHEL, please run reindexdb against your databases. Core library
collation data may have changed and this will invalidate database indexes. For
example, in Fedora 28 and RHEL 8 there have been extensive changes in glibc
collations to support ISO 14651:2016 (Unicode 9.0.0 data) and your indexes may
be affected: https://sourceware.org/ml/libc-announce/2018/msg00002.html
* See /var/lib/pgsql/upgrade_postgresql.log for details.

听说以前的数据库区域已经迁移到 /var/lib/psql/data-old。我们要检查一下是否已经启动了。

# systemctl status postgresql
● postgresql.service - PostgreSQL database server
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
Active: active (running) since xxxxxxxx
Process: 1479 ExecStartPre=/usr/libexec/postgresql-check-db-dir postgresql (code=exited, status=0/SUCCESS)
Main PID: 1481 (postmaster)
Tasks: 8 (limit: 1141)
Memory: 16.2M
CGroup: /system.slice/postgresql.service
├─1481 /usr/bin/postmaster -D /var/lib/pgsql/data
├─1482 postgres: logger
├─1484 postgres: checkpointer
├─1485 postgres: background writer
├─1486 postgres: walwriter
├─1487 postgres: autovacuum launcher
├─1488 postgres: stats collector
└─1489 postgres: logical replication launcher

May 10 01:21:47 xxxxxxxx systemd[1]: Starting PostgreSQL database server...
May 10 01:21:47 xxxxxxxx postmaster[1481]: 2019-05-10 01:21:47.355 JST [1481] LOG: listening on IPv6 address "::1", port >
May 10 01:21:47 xxxxxxxx postmaster[1481]: 2019-05-10 01:21:47.356 JST [1481] LOG: listening on IPv4 address "127.0.0.1",>
May 10 01:21:47 xxxxxxxx postmaster[1481]: 2019-05-10 01:21:47.357 JST [1481] LOG: listening on Unix socket "/var/run/pos>
May 10 01:21:47 xxxxxxxx postmaster[1481]: 2019-05-10 01:21:47.361 JST [1481] LOG: listening on Unix socket "/tmp/.s.PGSQ>
May 10 01:21:47 xxxxxxxx postmaster[1481]: 2019-05-10 01:21:47.372 JST [1481] LOG: redirecting log output to logging coll>
May 10 01:21:47 xxxxxxxx postmaster[1481]: 2019-05-10 01:21:47.372 JST [1481] HINT: Future log output will appear in dire>
May 10 01:21:47 xxxxxxxx systemd[1]: Started PostgreSQL database server.

似乎一切正常运行着。

我已经确认了版本。

# postmaster --version
postgres (PostgreSQL) 11.2

目前的日期是11月2日。

当您重新加载Web页面时，现在显示如下内容。

错误：SQLSTATE[08006] [7] 致命错误：用户“postgres”的身份验证失败。

那个备份了的 pg_hba.conf 文件最后是怎么样的呢？

# diff -ruNp data/pg_hba.conf data-old/pg_hba.conf
--- data/pg_hba.conf 2019-05-10 01:20:27.400566913 +0900
+++ data-old/pg_hba.conf 2018-05-22 09:11:24.777453980 +0900
@@ -77,11 +77,14 @@
# TYPE DATABASE USER ADDRESS METHOD

# "local" is for Unix domain socket connections only
-local all all peer
+#local all all peer
+local all all trust
# IPv4 local connections:
-host all all 127.0.0.1/32 ident
+#host all all 127.0.0.1/32 ident
+host all all 127.0.0.1/32 trust
# IPv6 local connections:
-host all all ::1/128 ident
+#host all all ::1/128 ident
+host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer

我希望尽可能进行最小程度的修正。

具体而言，打开 /var/lib/pgsql/data/pg_hba.conf 文件，将 IPv6 行的 METHOD 栏目修改为 trust，然后重新启动 PostgreSQL，问题就解决了。

在这个服务器上，由于启用了IPv6，似乎优先使用IPv6。