Postgres 10 ambari-server upgrade and Postgres 9.4

[#1]

Hi all,

I've upgraded Postgres 9.5 to Postgres 10.1 on one of my lab servers, Ambari works fine with Postgres 10, but when I do the upgrade from Version 2.5.0.3 to Version 2.6.0.0

yum info ambari-server
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
Installed Packages
Name : ambari-server
Arch : x86_64
Version : 2.5.0.3
Release : 7
Size : 722 M
Repo : installed
Summary : Ambari Server
URL : http://www.apache.org
License : (c) Apache Software Foundation
Description : Maven Recipe: RPM Package.

Available Packages
Name : ambari-server
Arch : x86_64
Version : 2.6.0.0
Release : 267
Size : 712 M
Repo : ambari-ambari-2.6.0.0
Summary : Ambari Server
URL : http://www.apache.org
License : (c) Apache Software Foundation
Description : Maven Recipe: RPM Package.

But when I try and do the upgrade I get Column t1.tgconstrname does not exist error, seem like it might be a Postgres 10 problem?

https://liquibase.jira.com/browse/CORE-3135

See the logfile below

23 Jan 2018 12:35:00,848 INFO [main] DBAccessorImpl:874 - Executing query: DELETE FROM upgrade_group
23 Jan 2018 12:35:00,851 INFO [main] DBAccessorImpl:874 - Executing query: DELETE FROM upgrade
23 Jan 2018 12:35:00,855 INFO [main] DBAccessorImpl:874 - Executing query: ALTER TABLE upgrade DROP COLUMN to_version
23 Jan 2018 12:35:00,863 INFO [main] DBAccessorImpl:874 - Executing query: ALTER TABLE upgrade DROP COLUMN from_version
23 Jan 2018 12:35:00,872 INFO [main] DBAccessorImpl:874 - Executing query: ALTER TABLE upgrade ADD from_repo_version_id BIGINT NOT NULL
23 Jan 2018 12:35:00,888 ERROR [main] SchemaUpgradeHelper:202 - Upgrade failed.
org.postgresql.util.PSQLException: ERROR: column t1.tgconstrname does not exist
Position: 113
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:559)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:403)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:283)
at org.postgresql.jdbc2.AbstractJdbc2DatabaseMetaData.getImportedExportedKeys(AbstractJdbc2DatabaseMetaData.java:3580)
at org.postgresql.jdbc2.AbstractJdbc2DatabaseMetaData.getCrossReference(AbstractJdbc2DatabaseMetaData.java:3894)
at org.apache.ambari.server.orm.DBAccessorImpl.tableHasForeignKey(DBAccessorImpl.java:404)
at org.apache.ambari.server.orm.DBAccessorImpl.addFKConstraint(DBAccessorImpl.java:509)
at org.apache.ambari.server.orm.DBAccessorImpl.addFKConstraint(DBAccessorImpl.java:482)
at org.apache.ambari.server.upgrade.UpgradeCatalog252.addRepositoryColumnsToUpgradeTable(UpgradeCatalog252.java:181)
at org.apache.ambari.server.upgrade.UpgradeCatalog252.executeDDLUpdates(UpgradeCatalog252.java:122)
at org.apache.ambari.server.upgrade.AbstractUpgradeCatalog.upgradeSchema(AbstractUpgradeCatalog.java:923)
at org.apache.ambari.server.upgrade.SchemaUpgradeHelper.executeUpgrade(SchemaUpgradeHelper.java:200)
at org.apache.ambari.server.upgrade.SchemaUpgradeHelper.main(SchemaUpgradeHelper.java:418)
23 Jan 2018 12:35:00,893 ERROR [main] SchemaUpgradeHelper:437 - Exception occurred during upgrade, failed
org.apache.ambari.server.AmbariException: ERROR: column t1.tgconstrname does not exist
Position: 113
at org.apache.ambari.server.upgrade.SchemaUpgradeHelper.executeUpgrade(SchemaUpgradeHelper.java:203)
at org.apache.ambari.server.upgrade.SchemaUpgradeHelper.main(SchemaUpgradeHelper.java:418)
Caused by: org.postgresql.util.PSQLException: ERROR: column t1.tgconstrname does not exist
Position: 113
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:559)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:403)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:283)
at org.postgresql.jdbc2.AbstractJdbc2DatabaseMetaData.getImportedExportedKeys(AbstractJdbc2DatabaseMetaData.java:3580)
at org.postgresql.jdbc2.AbstractJdbc2DatabaseMetaData.getCrossReference(AbstractJdbc2DatabaseMetaData.java:3894)
at org.apache.ambari.server.orm.DBAccessorImpl.tableHasForeignKey(DBAccessorImpl.java:404)
at org.apache.ambari.server.orm.DBAccessorImpl.addFKConstraint(DBAccessorImpl.java:509)
at org.apache.ambari.server.orm.DBAccessorImpl.addFKConstraint(DBAccessorImpl.java:482)

Please I need some help, any suggestions?

[#2 · Accepted solution]

@Wynand J van Vuuren

If you are using Ambari 2.6.0 then please use Postgres 9.4 or lower.

Please refer to the tested and certified configuration of ambari with different Databases:

https://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.6.3/bk_support-matrices/content/ch_matrices-amb...

.

From Postgres 9.x to 10.x is a major upgrade of the DB and there may be many advanced changes that can cause issues, hence it is better to be on the supported version (atleast Postgres 9.x)

[#3]

I've now installed Postgres 9.4 with Ambari 2.5.0.3, upgraded Ambari to 2.6.0.0 but when I do the upgrade, I get another Postgres issue

java.lang.IllegalArgumentException: cluster_version table does not contain repo_version_id column

ambari-server upgrade
Using python /usr/bin/python
Upgrading ambari-server
INFO: Upgrade Ambari Server
INFO: Updating Ambari Server properties in ambari.properties ...
INFO: Updating Ambari Server properties in ambari-env.sh ...
WARNING: Original file ambari-env.sh kept
INFO: Fixing database objects owner
Ambari Server configured for Postgres. Confirm you have made a backup of the Ambari Server database [y/n] (y)? y
INFO: Upgrading database schema
INFO: Return code from schema upgrade command, retcode = 1
ERROR: Error executing schema upgrade, please check the server logs.
ERROR: Error output from schema upgrade command:
ERROR: Exception in thread "main" org.apache.ambari.server.AmbariException: cluster_version table does not contain repo_version_id column
at org.apache.ambari.server.upgrade.SchemaUpgradeHelper.executeUpgrade(SchemaUpgradeHelper.java:203)
at org.apache.ambari.server.upgrade.SchemaUpgradeHelper.main(SchemaUpgradeHelper.java:418)
Caused by: java.lang.IllegalArgumentException: cluster_version table does not contain repo_version_id column
at org.apache.ambari.server.orm.DBAccessorImpl.getIntColumnValues(DBAccessorImpl.java:1536)
at org.apache.ambari.server.upgrade.UpgradeCatalog260.getCurrentVersionID(UpgradeCatalog260.java:507)
at org.apache.ambari.server.upgrade.UpgradeCatalog260.executeDDLUpdates(UpgradeCatalog260.java:194)
at org.apache.ambari.server.upgrade.AbstractUpgradeCatalog.upgradeSchema(AbstractUpgradeCatalog.java:923)
at org.apache.ambari.server.upgrade.SchemaUpgradeHelper.executeUpgrade(SchemaUpgradeHelper.java:200)
... 1 more


ERROR: Ambari server upgrade failed. Please look at /var/log/ambari-server/ambari-server.log, for more details.
ERROR: Exiting with exit code 11.
REASON: Schema upgrade failed.

[#4]

@Wynand J van Vuuren

Looks like you might be hitting the issue reported here: https://issues.apache.org/jira/browse/AMBARI-22421

So please try the below Workaround:

As "cluster_version" table was present in Ambari 2.5.x however from 2.6.x this table is gone. So as a workaround you will need to first create the following tale inside your ambari DB and then perform the "ambari-server upgrade"

Login to Postgress DB:

# psql -u ambari ambari
Enter password: bigdata

Create Dummy Table:

CREATE TABLE cluster_version (
  id BIGINT NOT NULL,
  repo_version_id BIGINT NOT NULL,
  cluster_id BIGINT NOT NULL,
  state VARCHAR(32) NOT NULL,
  start_time BIGINT NOT NULL,
  end_time BIGINT,
  user_name VARCHAR(32));<br>

.

The perform ambari server upgrade:

# ambari-server upgrade

Ambari 2.5.0

https://github.com/apache/ambari/blob/release-2.5.0/ambari-server/src/main/resources/Ambari-DDL-Post...

Ambari 2.6.0 (does not have cluster_version)

https://github.com/apache/ambari/blob/release-2.6.0/ambari-server/src/main/resources/Ambari-DDL-Post...

.

In your case this table is missing because looks like you might have run the upgrade command twice (earlier it might have not executed completely so the table got deleted).

[#5]

Thanks so much, I did that and then hit the next problem, this is like hitting your head against a solid brick wall...

ambari=# CREATE TABLE cluster_version (
ambari(# id BIGINT NOT NULL,
ambari(# repo_version_id BIGINT NOT NULL,
ambari(# cluster_id BIGINT NOT NULL,
ambari(# state VARCHAR(32) NOT NULL,
ambari(# start_time BIGINT NOT NULL,
ambari(# end_time BIGINT,
ambari(# user_name VARCHAR(32));
CREATE TABLE
ambari=#

ambari-server upgrade
Using python /usr/bin/python
Upgrading ambari-server
INFO: Upgrade Ambari Server
INFO: Updating Ambari Server properties in ambari.properties ...
WARNING: Can not find ambari.properties.rpmsave file from previous version, skipping import of settings
INFO: Updating Ambari Server properties in ambari-env.sh ...
INFO: Can not find ambari-env.sh.rpmsave file from previous version, skipping restore of environment settings. ambari-env.sh may not include any user customization.
INFO: Fixing database objects owner
Ambari Server configured for Postgres. Confirm you have made a backup of the Ambari Server database [y/n] (y)? y
INFO: Upgrading database schema
INFO: Return code from schema upgrade command, retcode = 1
ERROR: Error executing schema upgrade, please check the server logs.
ERROR: Error output from schema upgrade command:
ERROR: Exception in thread "main" java.lang.Exception: Unexpected error, upgrade failed
at org.apache.ambari.server.upgrade.SchemaUpgradeHelper.main(SchemaUpgradeHelper.java:441)
Caused by: java.lang.RuntimeException: Unable to read database version
at org.apache.ambari.server.upgrade.SchemaUpgradeHelper.readSourceVersion(SchemaUpgradeHelper.java:97)
at org.apache.ambari.server.upgrade.SchemaUpgradeHelper.main(SchemaUpgradeHelper.java:402)
Caused by: org.postgresql.util.PSQLException: ERROR: relation "metainfo" does not exist
Position: 30
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:559)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:403)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:283)
at org.apache.ambari.server.upgrade.SchemaUpgradeHelper.readSourceVersion(SchemaUpgradeHelper.java:90)
... 1 more


ERROR: Ambari server upgrade failed. Please look at /var/log/ambari-server/ambari-server.log, for more details.
ERROR: Exiting with exit code 11.
REASON: Schema upgrade failed.

[#6]

Hi, @Jay Kumar SenSharma

How can i use Ambari 2.6.0 with HDF 3.0.2, if supported version is different?

Ambari is need Postgres 9.4, but HDF is need Postgres 9.5.

https://docs.hortonworks.com/HDPDocuments/HDF3/HDF-3.0.2/bk_support-matrices/content/ch_matrices-hdf...