Upgrade fails on old version of Postgres database

Installation
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: https://github.com/discourse/discourse/blob/208005f9c9662773b436c4ffa14272ac0888bb04/db/migrate/20181128140547_migrate_facebook_user_info.rb

2 Likes
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 Likes
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 Like
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 Likes
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

I am not completely against adding version checks for cases like yours but would prefer to see this pop up in the wild a few more times before doing so.

If you are experiencing the same issue as the OP, flag to reopen

3 Likes