postgresql - brew upgrade postgresql 损坏的 psql

Question

太长请勿阅读

我想通过 HomeBrew 升级一些东西，但它似乎破坏了我的 Postgres。

我在使用 MacOS。我需要能够再次运行我的 Postgres。在没有备份的情况下删除不是什么大问题:这是本地开发设置。

升级和调试的长操作序列

我跑了:

• brew 更新
• brew 升级

哪个输出:

==> Upgrading 10 outdated packages:
postgresql 13.3 -> 14.1_1

==> Upgrading postgresql
  13.3 -> 14.1_1 

==> Pouring postgresql--14.1_1.monterey.bottle.tar.gz
==> Caveats
To migrate existing data from a previous major version of PostgreSQL run:
  brew postgresql-upgrade-database

This formula has created a default database cluster with:
  initdb --locale=C -E UTF-8 /usr/local/var/postgres
For more details, read:
  https://www.postgresql.org/docs/14/app-initdb.html

To restart postgresql after an upgrade:
  brew services restart postgresql
Or, if you don't want/need a background service you can just run:
  /usr/local/opt/postgresql/bin/postgres -D /usr/local/var/postgres
==> Summary
🍺  /usr/local/Cellar/postgresql/14.1_1: 3,304 files, 43.9MB
==> Running `brew cleanup postgresql`...
Removing: /usr/local/Cellar/postgresql/13.3... (3,230 files, 42.7MB)
==> Casks with 'auto_updates true' or 'version :latest' will not be upgraded; pass `--greedy` to upgrade them.
==> Upgrading 2 outdated packages:
pgadmin4 5.2 -> 6.2
visualvm 2.0.7 -> 2.1.1
==> Upgrading pgadmin4
==> Downloading https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v6.2/macos/pgadmin4-6.2.dmg
######################################################################## 100.0%
==> Backing App 'pgAdmin 4.app' up to '/usr/local/Caskroom/pgadmin4/5.2/pgAdmin 4.app'
==> Removing App '/Applications/pgAdmin 4.app'
==> Moving App 'pgAdmin 4.app' to '/Applications/pgAdmin 4.app'
==> Purging files for version 5.2 of Cask pgadmin4
🍺  pgadmin4 was successfully upgraded!
==> Upgrading visualvm
==> Caveats
visualvm requires Java. You can install the latest version with:
  brew install --cask temurin

==> Downloading https://github.com/oracle/visualvm/releases/download/2.1.1/VisualVM_211.dmg
==> Downloading from https://objects.githubusercontent.com/github-production-release-asset-2e65be/68017978/68ae1458-d0e0-40f3-b429-1dec7eb5b703?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIAIWNJYAX4CSVEH53A%2F20211208%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20211208T200243Z&X-Amz-Expires=300&X-Amz-Signature=e33c4cfaaba1afeb72a08a34daed39b82de9ebc342
######################################################################## 100.0%
==> Backing App 'VisualVM.app' up to '/usr/local/Caskroom/visualvm/2.0.7/VisualVM.app'
==> Removing App '/Applications/VisualVM.app'
==> Moving App 'VisualVM.app' to '/Applications/VisualVM.app'
==> Purging files for version 2.0.7 of Cask visualvm
🍺  visualvm was successfully upgraded!
==> Caveats
==> postgresql
To migrate existing data from a previous major version of PostgreSQL run:
  brew postgresql-upgrade-database

This formula has created a default database cluster with:
  initdb --locale=C -E UTF-8 /usr/local/var/postgres
For more details, read:
  https://www.postgresql.org/docs/14/app-initdb.html

To restart postgresql after an upgrade:
  brew services restart postgresql
Or, if you don't want/need a background service you can just run:
  /usr/local/opt/postgresql/bin/postgres -D /usr/local/var/postgres
==> visualvm
visualvm requires Java. You can install the latest version with:
  brew install --cask temurin

然后当我尝试用通常的方式运行我的 Postgres 服务器时

pg_ctl -D /usr/local/var/postgres start

我会得到

waiting for server to start....2021-12-08 23:44:24.182 EST [17879] FATAL:  database files are incompatible with server
2021-12-08 23:44:24.182 EST [17879] DETAIL:  The data directory was initialized by PostgreSQL version 13, which is not compatible with this version 14.1.
 stopped waiting
pg_ctl: could not start server
Examine the log output.

然后使用

tail -n 10 /usr/local/var/log/postgres.log

我明白了

2021-12-08 23:30:30.897 EST [15687] FATAL:  database files are incompatible with server
2021-12-08 23:30:30.897 EST [15687] DETAIL:  The data directory was initialized by PostgreSQL version 13, which is not compatible with this version 14.1.
2021-12-08 23:30:40.982 EST [15949] FATAL:  database files are incompatible with server
2021-12-08 23:30:40.982 EST [15949] DETAIL:  The data directory was initialized by PostgreSQL version 13, which is not compatible with this version 14.1.
2021-12-08 23:30:51.066 EST [15954] FATAL:  database files are incompatible with server
2021-12-08 23:30:51.066 EST [15954] DETAIL:  The data directory was initialized by PostgreSQL version 13, which is not compatible with this version 14.1.
2021-12-08 23:31:01.154 EST [15960] FATAL:  database files are incompatible with server
2021-12-08 23:31:01.154 EST [15960] DETAIL:  The data directory was initialized by PostgreSQL version 13, which is not compatible with this version 14.1.
2021-12-08 23:31:11.227 EST [16453] FATAL:  lock file "postmaster.pid" already exists
2021-12-08 23:31:11.227 EST [16453] HINT:  Is another postgres (PID 16451) running in data directory "/usr/local/var/postgres"?

如您所见，时间戳不匹配。那实际上是因为我也一直在尝试用

启动服务

brew services start postgres

这就是一直在那里输出日志的进程。

如果我查看 /usr/local/Cellar，我会看到:

drwxr-xr-x   3 i548042  admin    96  8 Dec 23:15 postgresql
drwxr-xr-x   3 i548042  admin    96  8 Dec 23:29 postgresql@13

看来哪里不对。

psql --version

是否输出

psql (PostgreSQL) 14.1

如果我运行 HomeBrew 的建议

brew postgresql-upgrade-database

我得到:

==> Upgrading postgresql data from 13 to 14...
waiting for server to start....2021-12-08 23:48:02.918 EST [18573] LOG:  starting PostgreSQL 13.5 on x86_64-apple-darwin21.1.0, compiled by Apple clang version 13.0.0 (clang-1300.0.29.3), 64-bit
2021-12-08 23:48:02.920 EST [18573] LOG:  listening on IPv6 address "::1", port 5432
2021-12-08 23:48:02.920 EST [18573] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2021-12-08 23:48:02.921 EST [18573] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2021-12-08 23:48:02.933 EST [18574] LOG:  database system was shut down at 2021-12-08 23:31:28 EST
2021-12-08 23:48:02.939 EST [18573] LOG:  database system is ready to accept connections
 done
server started
waiting for server to shut down...2021-12-08 23:48:03.275 EST [18573] LOG:  received fast shutdown request
.2021-12-08 23:48:03.275 EST [18573] LOG:  aborting any active transactions
2021-12-08 23:48:03.276 EST [18573] LOG:  background worker "logical replication launcher" (PID 18580) exited with exit code 1
2021-12-08 23:48:03.276 EST [18575] LOG:  shutting down
2021-12-08 23:48:03.285 EST [18573] LOG:  database system is shut down
 done
server stopped
==> Moving postgresql data from /usr/local/var/postgres to /usr/local/var/postgres.old...
==> Creating database...
The files belonging to this database system will be owned by user "i548042".
This user must also own the server process.

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

Data page checksums are disabled.

fixing permissions on existing directory /usr/local/var/postgres ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... America/Toronto
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
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:

    /usr/local/opt/postgresql/bin/pg_ctl -D /usr/local/var/postgres -l logfile start

==> Migrating and upgrading data...
Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for system-defined composite types in user tables  ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for user-defined encoding conversions              ok
Checking for user-defined postfix operators                 ok
Creating dump of global objects                             ok
Creating dump of database schemas
                                                            ok
Checking for presence of required libraries                 fatal

Your installation references loadable libraries that are missing from the
new installation.  You can add these libraries to the new installation,
or remove the functions using them from the old installation.  A list of
problem libraries is in the file:
    loadable_libraries.txt

Failure, exiting
Error: Upgrading postgresql data from 13 to 14 failed!
==> Removing empty postgresql initdb database...
==> Moving postgresql data back from /usr/local/var/postgres.old to /usr/local/var/postgres...
Error: Failure while executing; `/usr/local/opt/postgresql/bin/pg_upgrade -r -b /usr/local/Cellar/postgresql@13/13.5_1/bin -B /usr/local/opt/postgresql/bin -d /usr/local/var/postgres.old -D /usr/local/var/postgres -j 16` exited with 1.

按照建议去做

/usr/local/opt/postgresql/bin/pg_ctl -D /usr/local/var/postgres -l logfile start

给我

waiting for server to start.... stopped waiting
pg_ctl: could not start server
Examine the log output.

所以我再次使用命令查看日志。同样的事情:

2021-12-08 23:49:45.779 EST [18758] FATAL:  database files are incompatible with server
2021-12-08 23:49:45.779 EST [18758] DETAIL:  The data directory was initialized by PostgreSQL version 13, which is not compatible with this version 14.1.

我还尝试了许多不同的其他解决方案，例如 brew uninstall 和 brew install 的组合，用于 postgres 和 postgresql@13，并且还在网上寻找一种在版本 13 上手动安装 psql 的方法，因为这似乎是这里的罪魁祸首。

然后我继续尝试通过删除并重新创建它来弄乱 ~/Library/LaunchAgents 中的符号链接(symbolic link)(我很可能在那个时候也弄乱了一些东西，但事情是事先已经坏了)。

---

目标

无论如何，所有这一切都在说:这非常烦人，我不明白我能做些什么才能最终再次启动 Postgres。

我不关心版本，也不关心丢失数据库上的数据。如果我需要删除文件夹，我会的。我已经有相关信息的转储，无论如何这是一个本地开发环境，而不是生产环境。

Answer 1

这帮助了我:https://blog.testdouble.com/posts/2021-01-28-how-to-completely-uninstall-homebrew-postgres/

$ brew uninstall postgres
$ rm -rf /usr/local/var/postgres
$ rm /usr/local/var/log/postgres.log
$ rm -f ~/.psqlrc ~/.psql_history

它正确地卸载了 postgres，然后您可以使用 brew install postgres 重新安装它。

注意:这会破坏您所有的 Postgres 数据。