为什么postgres数据库升级失败？
我正在尝试从Postgresql 9.6升级到10，但失败了。
我成功地运行了brew upgrade postgresql，然后运行了brew postgresql-upgrade-database并显示了失败消息。问题似乎是这样的：
lc_collate values for database "postgres" do not match: old "en_GB.UTF-8", new "en_US.UTF-8"
整个信息是：
==> Upgrading postgresql data from 9.6 to 10...
Stopping `postgresql`... (might take a while)
==> Successfully stopped `postgresql` (label: homebrew.mxcl.postgresql)
==> Moving postgresql data from /usr/local/var/postgres to /usr/local/var/postgres.o
The files belonging to this database system will be owned by user "jbkimac".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
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 default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
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
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 reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for invalid "unknown" user columns ok
Creating dump of global objects ok
Creating dump of database schemas
ok
lc_collate values for database "postgres" do not match: old "en_GB.UTF-8", new "en_US.UTF-8"
Failure, exiting
Error: Upgrading postgresql data from 9.6 to 10 failed!
==> Removing empty postgresql initdb database...
==> Moving postgresql data back from /usr/local/var/postgres.old to /usr/local/var/p
==> Successfully started `postgresql` (label: homebrew.mxcl.postgresql)
Error: Failure while executing; `/usr/local/opt/postgresql/bin/pg_upgrade -r -b /usr/local/Cellar/postgresql/9.6.1/bin -B /usr/local/opt/postgresql/bin -d /usr/local/var/postgres.old -D /usr/local/var/postgres -j 8` exited with 1.有没有人可以建议我如何解决这个"en_GB.UTF-8", new "en_US.UTF-8"冲突问题？
回答 3
Stack Overflow用户
发布于 2021-10-30 07:56:14
brew services stop postgresql
brew postgresql-upgrade-database
brew services start postgresqlStack Overflow用户
发布于 2019-02-27 17:53:27
正如@ brew postgresql-upgrade-database提到的那样，我也遇到了同样的问题，不得不更改/usr/local/Homebrew/Library/Taps/homebrew/homebrew-core/cmd/brew-postgresql-upgrade-database.rb。--lc-collate和--lc-ctype都必须与当前数据库设置相对应。在我的例子中，两者都是ch_DE.UTF8。
Stack Overflow用户
发布于 2022-01-04 17:34:52
这个问题很老了，但只是为了以防万一:默认数据库可能没有使用正确的lc_ctype 和 lc_collate进行初始化。忽略错误消息并尝试修复错误设置真的很简单。
要逐步完成此操作(不使用brew postgresql-upgrade-database)，请执行以下操作：
为了以防万一，请保存当前的数据库方案(例如，使用/usr/local/var/posgres) )并将数据文件夹移动到其他位置(默认情况下为
- )(默认位置为
pg_dumpall
- 然后，使用最新版本初始化默认数据库：
$ initdb --lc-collate=en_GB.UTF-8 --lc-ctype=en_GB.UTF-8 -E UTF-8 initdb
- 最终将旧数据导入到新版本的数据文件夹中(此处从第13到14页)：
$ /usr/local/Cellar/postgresql/14.1_1/bin/pg_upgrade -r -b /usr/local/Cellar/postgres.old -d /usr/local/var/postgres.old -D /usr/local/var/postgres.old -j /usr/local/var/postgres 4
https://stackoverflow.com/questions/52539100
复制相似问题
腾讯云开发者
