旧版 Postgres 数据库升级失败

支持 安装
1

我正在从 v2.0.0.beta5 +110 升级到 2.2.0.beta5
Postgres 版本：9.4

错误信息：

原因：
ActiveRecord::StatementInvalid: PG::DatatypeMismatch: 错误：列 "info" 的类型为 jsonb，但表达式的类型为 json
第 13 行：   json_build_object('email', email, 'first_name', first_name...
           ^
提示：您需要重写或转换该表达式。
: INSERT INTO user_associated_accounts (
  provider_name,
  provider_uid,
  user_id,
  info,
  last_used,
  created_at,
  updated_at
) SELECT
  'facebook',
  facebook_user_id,
  user_id,
  json_build_object('email', email, 'first_name', first_name, 'last_name', last_name, 'name', name),
  updated_at,
  created_at,
  updated_at
FROM facebook_user_infos
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/rack-mini-profiler-1.0.0/lib/patches/db/pg.rb:92:in `async_exec'
/var/www/discourse/vendor/bundle/ruby/2.5.0/gems/rack-mini-profiler-1.0.0/lib/patches/db/pg.rb:92:in `async_exec'

看起来这与 @david 的这个新提交有关：discourse/db/migrate/20181128140547_migrate_facebook_user_info.rb at 208005f9c9662773b436c4ffa14272ac0888bb04 · discourse/discourse · GitHub

2 个赞
2

Discourse requires Postgres version 10, so you will need to upgrade. If you are using our standard installation then this should have happened automatically many months ago.

4 个赞
3

Updating postgres solved the problem, however now I’m wondering if I should consider my discourse database as corrupted, since most of db migrate tasks were executed(successfully?) on 9.4, and only the last two on 9.5.

Would it be possible to detect incompatible postgres version before upgrade, in order to block admins from doing stupid things like what I just did? :slight_smile:

1 个赞
4

Are you running postgres outside the container?

5

That shouldn’t matter, there is no need to worry about that.
The Rails migration system and the Postgres update procedure are both robust enough to handle this.

3 个赞
6

Yes, I’m running Postgres outside the container, do you recommend to not have such setup?

7

Discourse has required Postgres 10 since October of last year, in a standard install it is able to manage the Postgres upgrades automatically, unless the database has been spun out as you’ve done.

8

Would it make sense to implement a preupgrade check in Discourse running SELECT version() query and failing the upgrade in case incompatible db version is detected?
Upgrading db was not a problem, unlike the unexpectedly increased downtime. :slight_smile:

9

我并不是完全反对针对像您这样的情况添加版本检查，但希望在真正遇到更多类似案例后再实施。如果您遇到与原始发帖人相同的问题，请标记以重新开启。

3 个赞