Not able to upgrade hive metastore In postgres

Question

I am upgrading Hive from 2.3.2 to 3.1.3 And during startup of hive metastore service I got version related error. That metastore is in 2.3.2 version and it requires 3.1.3

So I try to upgrade it using schemaTool but it failed with the error that table already present. As metastore was present with version 2.3.2 i think that table will be present only.

Still on two to three try when i was not able to run it successfully i tried to manually delete that table but when i tried it after deleting it again failed with another table already present.

If i will drop table one by one like this then metadata will go away for hive tables. I can recreate table but partition recreation is difficult for me so is there any way to deal with this problem.

Hive metastore may be in inconsistent state as i have done many things which may lead to this, how to repair hive metastore. How to Take backup and reinitialize backed metastore??

schematool -validate -dbType postgres
Output>>
Starting metastore validation

Validating schema version
Metastore schema version is not compatible. Hive Version: 3.1.0, Database Schema Version: 2.3.0
Failed in schema version validation.
[FAIL]

Validating sequence number for SEQUENCE_TABLE
Succeeded in sequence number validation for SEQUENCE_TABLE.
[SUCCESS]

Validating metastore schema tables
Succeeded in schema table validation.
[SUCCESS]

Validating DFS locations
Succeeded in DFS location validation.
[SUCCESS]

Validating columns for incorrect NULL values.
Succeeded in column validation for incorrect NULL values.
[SUCCESS]

Done with metastore validation: [FAIL]

schematool -upgradeSchema -dbType postgres -verbose
Output>>
Metastore connection URL:        jdbc:postgresql://PostgreSQL-V1.hadoop.com/hive?useSSL=
Metastore Connection Driver :    org.postgresql.Driver
Metastore connection User:       hivesrv
Starting upgrade metastore schema from version 2.3.0 to 3.1.0
Upgrade script upgrade-2.3.0-to-3.0.0.postgres.sql
Connecting to jdbc:postgresql://PostgreSQL-V1.hadoop.com/hive?useSSL=
Connected to: PostgreSQL (version 13.9)
Driver: PostgreSQL Native Driver (version PostgreSQL 9.4.1208.jre7)
Transaction isolation: TRANSACTION_READ_COMMITTED
0: jdbc:postgresql://PostgreSQL-V1> !autocommit on
Autocommit status: true
0: jdbc:postgresql://PostgreSQL-V1> SELECT 'Upgrading MetaStore schema from 2.3.0 to 3.0.0'
+-------------------------------------------------+
|                    ?column?                     |
+-------------------------------------------------+
| Upgrading MetaStore schema from 2.3.0 to 3.0.0  |
+-------------------------------------------------+
1 row selected (0.042 seconds)
0: jdbc:postgresql://PostgreSQL-V1> CREATE TABLE "METASTORE_DB_PROPERTIES" ( "PROPERTY_KEY" VARCHAR(255) NOT NULL, "PROPERTY_VALUE" VARCHAR(1000) NOT NULL, "DESCRIPTION" VARCHAR(1000) )
Error: ERROR: relation "METASTORE_DB_PROPERTIES" already exists (state=42P07,code=0)
Closing: 0: jdbc:postgresql://PostgreSQL-V1.hadoop.com/hive?useSSL=
org.apache.hadoop.hive.metastore.HiveMetaException: Upgrade FAILED! Metastore state would be inconsistent !!
Underlying cause: java.io.IOException : Schema script failed, errorcode 2
org.apache.hadoop.hive.metastore.HiveMetaException: Upgrade FAILED! Metastore state would be inconsistent !!
        at org.apache.hive.beeline.HiveSchemaTool.doUpgrade(HiveSchemaTool.java:553)
        at org.apache.hive.beeline.HiveSchemaTool.doUpgrade(HiveSchemaTool.java:517)
        at org.apache.hive.beeline.HiveSchemaTool.main(HiveSchemaTool.java:1512)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.apache.hadoop.util.RunJar.run(RunJar.java:323)
        at org.apache.hadoop.util.RunJar.main(RunJar.java:236)
Caused by: java.io.IOException: Schema script failed, errorcode 2
        at org.apache.hive.beeline.HiveSchemaTool.runBeeLine(HiveSchemaTool.java:1226)
        at org.apache.hive.beeline.HiveSchemaTool.runBeeLine(HiveSchemaTool.java:1204)
        at org.apache.hive.beeline.HiveSchemaTool.doUpgrade(HiveSchemaTool.java:548)
        ... 8 more
*** schemaTool failed ***

Answer 1

Hi Replying to my Own question for future readers. So after spending 2 to 3 days i didn't get any known or standard solution for the same. The solutions which i applied needs to be verified by some expert from community, plz... provide your view as it will be helpfull to me also.

So i went to /hive/scripts/metastore/upgrade/postgres path and opened the upgrade-2.3.0-to-3.0.0.postgres.sql file and ran queries below CREATE TABLE "METASTORE_DB_PROPERTIES" ( "PROPERTY_KEY" VARCHAR(255) NOT NULL, "PROPERTY_VALUE" VARCHAR(1000) NOT NULL, "DESCRIPTION" VARCHAR(1000) ) (which was giving error in my case) directly inside Postgres DB. It again fail 2 to 3 times with another query, similarly I skipped that query also and ran queries below them.

After this i ran upgrade-3.0.0-to-3.1.0.postgres.sql, similar to above script i skip the queries which were failing.

Point to be noted was, every single query which was failing was DROP queries and it was failing because it was trying to drop either table or constraint which doesn't exists.(So theoretically skipping the drop queries for tables and constraints which never exists actually should not affect metastore schema and data inside it).

Either it the issue where "IF EXIST" needs to be added in those DROP statements or It might be due to patch upgrades which added those tables and constraint which i never upgraded in my case.

In the end i checked in hive, partitions data and all were present at least for my case. So again this is not standard way to do this, so handle it carefully.