question Re: Postgres 10 ambari-server upgrade and Postgres 9.4 in Archives of Support Questions (Read Only)

Postgres 10 ambari-server upgrade and Postgres 9.4

esawyja — Tue, 23 Jan 2018 19:22:28 GMT

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?

Re: Postgres 10 ambari-server upgrade and Postgres 9.4

jsensharma — Tue, 23 Jan 2018 20:15:45 GMT

@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-ambari.html

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)

Re: Postgres 10 ambari-server upgrade and Postgres 9.4

esawyja — Wed, 24 Jan 2018 17:04:54 GMT

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.

Re: Postgres 10 ambari-server upgrade and Postgres 9.4

jsensharma — Wed, 24 Jan 2018 17:30:26 GMT

@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-Postgres-CREATE.sql#L173-L180

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-Postgres-CREATE.sql

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).

Re: Postgres 10 ambari-server upgrade and Postgres 9.4

esawyja — Wed, 24 Jan 2018 17:45:43 GMT

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.

Re: Postgres 10 ambari-server upgrade and Postgres 9.4

ururu — Sun, 28 Jan 2018 18:22:27 GMT

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.html