Hive metastore upgrade from 2.0.0 to 2.1.0 failed Postgres HDP 2.3.2 to 2.5.3 rolling upgrade

[#1]

Hive metastore schema upgrade from 2.0.0 to 2.1.0 is failing complaining about creation of key_constraints table at "NOT".

etastore connection URL:	 jdbc:postgresql://dbhost:5432/hive
Metastore Connection Driver :	 org.postgresql.Driver
Metastore connection User:	 hive
Starting upgrade metastore schema from version 1.2.0 to 2.1.0
Upgrade script upgrade-1.2.0-to-1.2.1000.postgres.sql
Completed upgrade-1.2.0-to-1.2.1000.postgres.sql
Upgrade script upgrade-1.2.1000-to-2.0.0.postgres.sql
Completed upgrade-1.2.1000-to-2.0.0.postgres.sql
Upgrade script upgrade-2.0.0-to-2.1.0.postgres.sql
Error: ERROR: syntax error at or near "NOT"
  Position: 17 (state=42601,code=0)

[#2]

Figured out the issue. We are running postgres on 8.4.

The create table if not exists "key constraints" was failing:

reason: create table if not exists is not supported in postgresql 8.4

need to change the 033-HIVE-13076.postgres.sql should do the trick.

waiting for the confirmation.

[#3]

Did you end up opening a bug with support on this? I ran into the same issue. Removing the "IF NOT EXISTS" got me past it, but I want to make sure it's reported for future versions. RHEL 6 still comes with 8.4.

[#4 · Accepted solution]

@David Halik

This is not a Bug, basically the 'Releases notes for HDP 2.5.3' does information for the supported metastore databases.

Release_MR

[#5]

Ah, good to know. Thanks.