Upgrade from 46.6.4 to 50.33 Fails with Syntax Error

Get Help FAQ
1

I am trying to upgrade my original install of 46.6.4 to 50.33.

  • Postgres version: 13
  • java.version: 11
  • Maximum memory available to JVM: 15.7 GB

As far as I can tell from the documentation, I’ve met all the minimum requirements. I’ve backed up my PostgreSQL database and jar file, shut down the services, and swapped to the new jar.

When I start up, I get this error:

INFO liquibase.changelog :: Marking ChangeSet: "migrations/001_update_migrations.yaml::v48.00-010::qnkhuat" as ran despite precondition failure due to onFail='MARK_RAN':
liquibase.yaml : DBMS Precondition failed: expected mysql,mariadb, got postgresql
INFO liquibase.changelog :: Index idx_revision_most_recent created
INFO liquibase.changelog :: ChangeSet migrations/001_update_migrations.yaml::v48.00-011::qnkhuat ran successfully in 4ms
ERROR liquibase.changelog :: ChangeSet migrations/001_update_migrations.yaml::v48.00-013::qnkhuat encountered an exception.
liquibase.exception.DatabaseException: ERROR: syntax error at or near "NOT"
Position: 17 [Failed SQL: (0) CREATE INDEX IF NOT EXISTS idx_action_made_public_by_id ON action (made_public_by_id)]

Has anyone encountered this before? I am new to Metabase and Postgres, so I am still struggling to get a handle on this.

2

Hi there,

It’s strange that it’s failing on the “NOT” part, sounds like this is some sort of non-standard postgres? Could you share more details about how this postgres is deployed, and can you send us the result of running a `select version()` query on the the database?

3

PostgreSQL 9.2.24 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)

RHEL Version: 3.10.0-1160.119.1.el7.x86_64

I installed using an Ansible script by a co-worker.

I don’t see a way to attach a file here. Here is the section of the script that deals with postgres:

tasks:

  • name: Download PostgreSQL Repository RPM
    shell: yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
  • name: Install PostgreSQL
    yum:
    name:
  • postgresql13
  • postgresql13-server
  • postgresql13-contrib
  • postgresql13-libs
  • python-psycopg2
    state: present
  • name: Initialize database cluster
    shell: /usr/pgsql-13/bin/postgresql-13-setup initdb
  • name: Start PostgreSQL service
    service:
    name: postgresql-13
    state: started
  • name: PostgreSQL initialized
    stat:
    path: "/var/lib/pgsql/13/data/pg_hba.conf"
    register: postgres_data
  • name: Enable PostgresQL service
    systemd:
    name: postgresql-13
    state: started
    enabled: yes
  • name: Update the pg_hba.conf file to allow postgress local access
    copy:
    src: /home/ansible/playbooks/metabase/files/pg_hba.conf
    dest: /var/lib/pgsql/13/data/pg_hba.conf
    owner: postgres
    group: postgres
    mode: '0600'
  • name: Restart PostgresQL service
    systemd:
    name: postgresql-13
    state: restarted
    enabled: yes
  • name: Create metabase database
    postgresql_db:
    name: metabase
    encoding: UTF-8
  • name: Create metabase database user
    postgresql_user:
    db: metabase
    name: REDACTED
    password: REDACTED
    priv: "ALL"
4

Judging by the first bit where your SELECT VERSION() returns Postgres 9.2.24, and that the NOT EXISTS syntax has been introduced in Postgres 9.5 (see this stack overflow post), I’d say you’re actually running Postgres 9.2. Could it be you’re running multiple postgres and pointing to the wrong one? I would double check the version and make sure it really is version 13.

5

Sure enough, it’s 9.2!

[root]# cat PG_VERSION
9.2
[root]# cat /var/lib/pgsql/data/base/16384/PG_VERSION
9.2
[root]# cat /var/lib/pgsql/data/base/12926/PG_VERSION
9.2
[root]# cat /var/lib/pgsql/data/base/12921/PG_VERSION
9.2
[root]# cat /var/lib/pgsql/data/base/1/PG_VERSION
9.2

It looks like I will have to move to PG 12 to be able to upgrade to Metabase 50. Or is it just recommended to install PG 13 as we will ultimately upgrade to the newest Metabase release?

6

You might as well upgrade to the latest available (17) at this point. PG12 is out of support so I wouldn’t choose that, and PG13 support will end shortly, so you’ll be upgrading again soon.

From 9.2 it’s a dump & restore to upgrade, but I would absolutely run a test upgrade first in case there are issues with getting a proper dump. Remember to use pg_dump from the target version. You’ll probably need to test your app(s) as well, if there is anything other than Metabase using that PG cluster.

2 Likes
7

We’re about to upgrade our server to RHEL 8. We’ll do that first, so we don’t have to mess with the different PG repos. Then we will upgrade to PG17. Finally, we’ll upgrade Metabase.

Thank you for your help!