Postgres の古いバージョンでのアップグレードが失敗

サポート インストール
1

I’m upgrading from v2.0.0.beta5 +110 to 2.2.0.beta5
Postgres version: 9.4

Error message:

Caused by:
ActiveRecord::StatementInvalid: PG::DatatypeMismatch: ERROR:  column "info" is of type jsonb but expression is of type json
LINE 13:   json_build_object('email', email, 'first_name', first_name...
       ^
HINT:  You will need to rewrite or cast the expression.
: 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'

Looks like it’s related to this new commit by @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