postgresql 为什么postgres数据库升级失败?

zbdgwd5y  于 2023-04-20  发布在  PostgreSQL
关注(0)|答案(3)|浏览(297)

我尝试从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"
整个信息是:

  1. ==> Upgrading postgresql data from 9.6 to 10...
  2. Stopping `postgresql`... (might take a while)
  3. ==> Successfully stopped `postgresql` (label: homebrew.mxcl.postgresql)
  4. ==> Moving postgresql data from /usr/local/var/postgres to /usr/local/var/postgres.o
  5. The files belonging to this database system will be owned by user "jbkimac".
  6. This user must also own the server process.
  7. The database cluster will be initialized with locale "en_US.UTF-8".
  8. The default database encoding has accordingly been set to "UTF8".
  9. The default text search configuration will be set to "english".
  10. Data page checksums are disabled.
  11. fixing permissions on existing directory /usr/local/var/postgres ... ok
  12. creating subdirectories ... ok
  13. selecting default max_connections ... 100
  14. selecting default shared_buffers ... 128MB
  15. selecting dynamic shared memory implementation ... posix
  16. creating configuration files ... ok
  17. running bootstrap script ... ok
  18. performing post-bootstrap initialization ... ok
  19. syncing data to disk ... ok
  20. WARNING: enabling "trust" authentication for local connections
  21. You can change this by editing pg_hba.conf or using the option -A, or
  22. --auth-local and --auth-host, the next time you run initdb.
  23. Success. You can now start the database server using:
  24. /usr/local/opt/postgresql/bin/pg_ctl -D /usr/local/var/postgres -l logfile start
  25. Performing Consistency Checks
  26. -----------------------------
  27. Checking cluster versions ok
  28. Checking database user is the install user ok
  29. Checking database connection settings ok
  30. Checking for prepared transactions ok
  31. Checking for reg* data types in user tables ok
  32. Checking for contrib/isn with bigint-passing mismatch ok
  33. Checking for invalid "unknown" user columns ok
  34. Creating dump of global objects ok
  35. Creating dump of database schemas
  36. ok
  37. lc_collate values for database "postgres" do not match: old "en_GB.UTF-8", new "en_US.UTF-8"
  38. Failure, exiting
  39. Error: Upgrading postgresql data from 9.6 to 10 failed!
  40. ==> Removing empty postgresql initdb database...
  41. ==> Moving postgresql data back from /usr/local/var/postgres.old to /usr/local/var/p
  42. ==> Successfully started `postgresql` (label: homebrew.mxcl.postgresql)
  43. 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"冲突问题?

bvjveswy

bvjveswy1#

  1. brew services stop postgresql
  2. brew postgresql-upgrade-database
  3. brew services start postgresql
xpszyzbs

xpszyzbs2#

我在brew postgresql-upgrade-database上遇到了同样的问题,不得不像@Thermatix提到的那样更改/usr/local/Homebrew/Library/Taps/homebrew/homebrew-core/cmd/brew-postgresql-upgrade-database.rb--lc-collate--lc-ctype都必须与当前的数据库设置相对应。在我的情况下,两者都是ch_DE.UTF8

sycxhyv7

sycxhyv73#

这个问题很老了,但如果它可以帮助某人:默认数据库可能没有使用正确的lc_ctypelc_collate进行初始化。忽略错误消息并尝试修复错误设置非常简单。
一步一步地做(不使用brew postgresql-upgrade-database):

  • 以防万一,保存当前的数据库模式(例如pg_dumpall),并将数据文件夹移动到另一个位置(默认情况下是/usr/local/var/posgres
  • 然后,使用最新版本初始化默认DB:
  1. $ initdb --lc-collate=en_GB.UTF-8 --lc-ctype=en_GB.UTF-8 -E UTF-8 /usr/local/var/postgres
  • 最后,将旧数据导入新版本的数据文件夹(此处为第13页至第14页):
  1. $ /usr/local/Cellar/postgresql/14.1_1/bin/pg_upgrade
  2. -r
  3. -b /usr/local/Cellar/postgresql@13/13.5_1/bin/
  4. -B /usr/local/Cellar/postgresql/14.1_1/bin/
  5. -d /usr/local/var/postgres.old
  6. -D /usr/local/var/postgres
  7. -j 4
展开查看全部

相关问题