ERROR in Ambari-server setup when configuring ambari database (PostgreSQL)

[#1]

Hello Everyone!

I need your help because I'm having an issue when running ambari-server setup (Ambari 2.5). The error is as follows:

-------------------------------------------------------------------------------------------------------------------------------------------------------------------

Configuring ambari database...

Checking PostgreSQL...

Configuring local database...

Configuring PostgreSQL...

Backup for pg_hba found, reconfiguration not required

Creating schema and user... done.

Creating tables...

ERROR: Failed to execute command:['ambari-sudo.sh', 'bash', '-c', 'export PGPASSWORD=hola && psql -U awv_user -f /var/lib/ambari-server/resources/Ambari-DDL-Postgres-CREATE.sql']

ERROR: stderr:psql: FATAL: database "awv_user" does not exist

ERROR: stdout: failed to execute queries ...retrying (1)

Creating tables... ERROR: Failed to execute command:['ambari-sudo.sh', 'bash', '-c', 'export PGPASSWORD=hola && psql -U awv_user -f /var/lib/ambari-server/resources/Ambari-DDL-Postgres-CREATE.sql']

ERROR: stderr:psql: FATAL: database "awv_user" does not exist

ERROR: stdout: failed to execute queries ...retrying (2) Creating tables... ERROR: Exiting with exit code 2. REASON: Running database init script failed. Exiting.

---------------------------------------------------------------------------------------------------------------------------------------------------------------

Other data:

Database admin user: postgres

Database name: awv

Postgres schema: ambari

Username: awv_user

Sorry for my English and thank you for your attention!

[#2]

@Alexander Carreño

I assume you referenced this official document

The database creation script has to run as user awv_user and this user doesn't exist !!

Did you run this step

CREATE USER awv_user WITH PASSWORD 'xxxx'; 

Can you check that the permission is correct for the script in

/var/lib/ambari-server/resources/Ambari-DDL-Postgres-CREATE.sql

Please create the above user following the document above and retry.

[#3]

It works! Thank you! But I'm having another error when running "ambari-server start":

-----------------------------------------------------------------------------------------------------------------------------------------------------------------

Local Exception Stack:

Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.2.v20151217-774c696): org.eclipse.persistence.exceptions.DatabaseExc$

Internal Exception: org.postgresql.util.PSQLException:

ERROR: relation "metainfo" does not exist

Position: 46

Error Code: 0 Call:

SELECT "metainfo_key", "metainfo_value" FROM metainfo WHERE ("metainfo_key" = ?) bind => [1 parameter bound] ...

------------------------------------------------------------------------------------------------------------------------------------------------------------------

[#4]

@Alexander Carreño

Check metainfo data in the databases

psql -U ambari -d ambari; 
Password for user ambari 
psql (8.4.20) Type "help" for help. 
ambari=> SELECT "metainfo_key", "metainfo_value" FROM metainfo; 

If you get 0 rows then continue with the below

That's because the postgres table/db corruption.Please run the below command to reset postgres ambari DB

# ambari-server reset 
Using python /usr/bin/python Resetting ambari-server **** WARNING **** You are about to reset and clear the Ambari Server database. 
This will remove all cluster host and configuration information from the database. 
You will be required to re-configure the Ambari server and re-run the cluster wizard. 
Are you SURE you want to perform the reset [yes/no] (no)? yes 
Confirm server reset [yes/no](no)? yes 
Resetting the Server database... Connecting to local database...done. 
WARNING: Non critical error in DDL, use --verbose for more information Ambari Server 'reset' completed with warnings. 

Restart Ambari

#ambari-server start 

All should run successfully and you should be able to log on.

Please revert

[#5]

When I check metainfo data in the databases, It shows this:

awv=> SELECT "metainfo_key", "metainfo_value" FROM metainfo;
ERROR:  relation "metainfo" does not exist
LÍNEA 1: SELECT "metainfo_key", "metainfo_value" FROM metainfo;

Then I run "ambari-server -reset" and It shows this:

Using python  /usr/bin/python
Resetting ambari-server
**** WARNING **** You are about to reset and clear the Ambari Server database. This will remove all cluster host and configuration information from the database. You will be required to re-configure the Ambari server and re-run the cluster wizard. 
Are you SURE you want to perform the reset [yes/no] (no)? yes
ERROR: Exiting with exit code 1. 
REASON: Ambari doesn't support resetting exernal DB automatically. To reset Ambari Server schema you must first drop and then create it using DDL scripts from "/var/lib/ambari-server/resources/"

[#6]

@@Alexander Carreño

How did you create your Postgres database? what was your choice Select n at Enter advanced database configuration?

That shows it isn't the embedded but create manually right?

There are 4 scripts in /var/lib/ambari-server/resources/

Ambari-DDL-Postgres-CREATE.sql
Ambari-DDL-Postgres-DROP.sql
Ambari-DDL-Postgres-EMBEDDED-CREATE.sql
Ambari-DDL-Postgres-EMBEDDED-DROP.sql

So you have 2 choices Ambari-DDL-Postgres-EMBEDDED-DROP.sql and Ambari-DDL-Postgres-DROP.sql to use evidently, you can see the difference and run a new ambari setup..

Below is a silent option setup for embedded database option.

ambari-server setup-s
--database=embedded|oracle|mysql|postgres
--databasehost=Hostname of database server
--databaseport=Database port
--databasename=Database/Schema/Service name or ServiceID
--databaseusername=Database user login
--databasepassword=Database user password

Please drop and recreate the ambari database to enable you to continue. If you prefer Mysql or MariaDB follow the below steps

################################################
# Install MySQL or MariaDB database for Ambari on Centos/RHEL 7
###############################################
# yum install -y ambari-server or yum install -y mariadb-server
# chkconfig mysqld --level 345 on
# yum install -y mysql-connector-java
# service mysqld start  
# service mysqld status  
# ls /usr/share/java/mysql-connector-java.jar
################################################
# Startup at server bootup for MySQL or MariaDB database for Ambari on Centos/RHEL 7
###############################################
sudo systemctl enable mariadb
sudo systemctl start mariadb
sudo ambari-server setup --jdbc-db=mysql --jdbc-driver=/usr/share/java/mysql-connector-java.jar
#############################################
# Install MySQL database for Ambari
##############################################
# mysql -u root
UPDATE mysql.user SET Password=PASSWORD('welcome1') WHERE User='root';
flush privileges;
##############################
#  Using Ambari user for MySQL
###################################
mysql -u root -pwelcome1
CREATE USER 'ambari'@'%' IDENTIFIED BY 'ambari';
GRANT ALL PRIVILEGES ON *.* TO 'ambari'@'%';
CREATE USER 'ambari'@'localhost' IDENTIFIED BY 'ambari';
GRANT ALL PRIVILEGES ON *.* TO 'ambari'@'localhost';
grant all privileges on *.* to 'ambari'@'localhost' identified by 'ambari';
CREATE USER 'ambari'@'bombo.uganda.com' IDENTIFIED BY 'ambari';
GRANT ALL PRIVILEGES ON *.* TO 'ambari'@'FQDN_for_Ambari';
FLUSH PRIVILEGES;
##################################################3
# Create the Ambari DB as user Ambari
###################################################
mysql -u ambari -pambari
CREATE DATABASE ambari;
USE ambari;
SOURCE /var/lib/ambari-server/resources/Ambari-DDL-MySQL-CREATE.sql;

Now you can proceed with the ambari setup

##################################################
# Ambari setup
##################################################
# ambari-server setup --jdbc-db=mysql --jdbc-driver=/usr/share/java/mysql-connector-java.jar
##################################################
# Setup ambari-server  for Mysql database.
##################################################
# ambari-server setup
Using python  /usr/bin/python
Setup ambari-server
Checking SELinux...
SELinux status is 'enabled'
SELinux mode is 'permissive'
WARNING: SELinux is set to 'permissive' mode and temporarily disabled.
OK to continue [y/n] (y)? y
Customize user account for ambari-server daemon [y/n] (n)?
Adjusting ambari-server permissions and ownership...
Checking firewall status...
Checking JDK...
[1] Oracle JDK 1.8 + Java Cryptography Extension (JCE) Policy Files 8
[2] Oracle JDK 1.7 + Java Cryptography Extension (JCE) Policy Files 7
[3] Custom JDK
==============================================================================
Enter choice (1):
To download the Oracle JDK and the Java Cryptography Extension (JCE) Policy Files you must accept the license terms found at http://www.oracle.com/technetwork/java/javase/terms/license/index.html and not accepting will cancel the Ambari Server setup and you must install the JDK and JCE files manually.
Do you accept the Oracle Binary Code License Agreement [y/n] (y)?
Downloading JDK from http://public-repo-1.hortonworks.com/ARTIFACTS/jdk-8u77-linux-x64.tar.gz to /var/lib/ambari-server/resources/jdk-8u77-linux-x64.tar.gz
jdk-8u77-linux-x64.tar.gz... 100% (173.0 MB of 173.0 MB)
Successfully downloaded JDK distribution to /var/lib/ambari-server/resources/jdk-8u77-linux-x64.tar.gz
Installing JDK to /usr/jdk64/
Successfully installed JDK to /usr/jdk64/
Downloading JCE Policy archive from http://public-repo-1.hortonworks.com/ARTIFACTS/jce_policy-8.zip to /var/lib/ambari-server/resources/jce_policy-8.zip
Successfully downloaded JCE Policy archive to /var/lib/ambari-server/resources/jce_policy-8.zip
Installing JCE policy...
Completing setup...
Configuring database...
Enter advanced database configuration [y/n] (n)? y
Configuring database...
==============================================================================
Choose one of the following options:
[1] - PostgreSQL (Embedded)
[2] - Oracle
[3] - MySQL / MariaDB
[4] - PostgreSQL
[5] - Microsoft SQL Server (Tech Preview)
[6] - SQL Anywhere
[7] - BDB
==============================================================================
Enter choice (1): 3
Hostname (localhost):
Port (3306):
Database name (ambari):
Username (ambari):
Enter Database Password (bigdata):
Re-enter password:
Configuring ambari database...
Copying JDBC drivers to server resources...
Configuring remote database connection properties...
WARNING: Before starting Ambari Server, you must run the following DDL against the database to create the schema: /var/lib/ambari-server/resources/Ambari-DDL-MySQL-CREATE.sql
Proceed with configuring remote database connection properties [y/n] (y)?
Extracting system views...
ambari-admin-2.4.1.0.22.jar
...........
Adjusting ambari-server permissions and ownership...
Ambari Server 'setup' completed successfully.

Hope that helps

[#7]

My friend! I solved the issue. Let me tell you how:

I tried your instructions but the error remained.

I uninstalled and installed ambari several times, following this instructions:

How to Completely Clean, Remove or Uninstall Ambari for Fresh Install

Completely uninstall HDP and Ambari

But the error remained again.

Finally I tried install Ambari 2.4, run "ambari-server setup" and It worked no issue! I don't know what happend. I don't know if the issue was Ambari 2.5 with CentOS 6 or another thing. This issue was so terrible for me.

Thank you for helping me!

Please can you help me with another thing?

I'm installing Hortonworks and I'm in the the part of customizing the services. I have a 1.9 TB disk and I want Datanodes storage data in this disk. How do I select this disk in this part? I tried put the /home (1.9 TB) directory in DataNode directories but It shows this alert: Can't start with "home(s)".

Disks and Customize Services

[#8]

@Alexander Carreño

Nice to know you recreated the ambari databases and successfully launched the cluster installation. Depending on your cluster user production,test or trainging,its recommended you learn the good practice from day one. Never use embedded database but create custom database I have a preference for MariaDB or Mysql easy management and open source.

You will have to create in advance databases for hive,oozie and ranger but its optional as you could do it during the configuration of the components.

The disk partitioning should follow the same logic of in-built HDFS of storing 3 copies,the default replication factor of file blocks distribution in DataNodes. Having said that a good practice is to have 3 separate disk partition and different mount points.

I see you are using ubuntu, you should have used custom partition eg

mkdir -p /grid/0 
mkdir -p /grid/1 
mkdir -p /grid/2 
mkdir -p /grid/x 

or another classic layout

mkdir /u01 
mkdir /u02
mkdir /u03
mkdir /u0x 

Please see this official cluster planning recommendation

Taking into account that you have no component installed I would advice you to start afresh with a good structure.

Re-install ubuntu partiion the disk accordingly see above document 3 partition of 300GB could do. 
Prepare the environment, firewall,ntp,passwordless etc 
Install MariaDB/Mysql Create the ambari database required 
Create databases for oozie,hive and ranger optional can be setup later. 
Chose the correct partition /grid/0 --/grid/x or /u01--/u0x

The installation should be successful

Please let me know if you need any info