Postgre SQL Metastore creation error


Hadoop Eco System Forums Hadoop Discussion Forum Postgre SQL Metastore creation error

This topic contains 1 reply, has 2 voices, and was last updated by Profile photo of Siva Siva 4 years, 10 months ago.

Viewing 2 posts - 1 through 2 (of 2 total)
  • Author
    Posts
  • #1652 Reply
    Profile photo of ram
    ram
    Participant

    Hi, I am receiving below error message while trying to install cloudera manager on Amazon EC2 instances:

    <pre class=”lang:default decode:true ” >- Error in connecting to db with user 'hive' and jdbcUrl "jdbc:postgresql://ip-10-172-163-220.ap-southeast-1.compute.internal:7432/hive

    Please provide suggestion on how to resolve this error message

    #1660 Reply
    Profile photo of Siva
    Siva
    Keymaster

    Hi ,

    This might be due to stopping the instance and starting it again. It will change the private and public IP address pairs in Amazon AWS. So it is better to follow below steps to manually create the metastore tables in PostgreSQL.

    You can find the database host, port, database name, username, password in /var/run/cloudera-scm-agent/process/25-hive-metastore-create-tables/hive-site.xml

    The “25” in the path will probably be different for you. You’ll need to be root or “hive” to access this directory.

    When running the psql tool to run the sql scripts, it is important that you specify the host name as the fully qualified host name, and specify the correct port, along with all the other information.

    After doing this, then individually run all of the steps that were supposed to run after the create tables command, as discussed above.

    Look in the file “/var/lib/cloudera-scm-server-db/data/generated_password.txt” to get the admin login info for your postgres, then use the “psql” command line to connect with that user to the database (must be ssh’d into the host where the cm server and db is hosted). The admin must log in using localhost.
    psql –host=localhost –port=7432 –username=cloudera-scm –dbname=postgres

    It will prompt you for the password, which is located in /var/lib/cloudera-scm-server-db/data/generated_password.txt.

    Once logged in, you should find that there is a hive user (select usename from pg_user;) and database (\list), since that’s what the CM create database command said.

    Then try to log in using your hive info in hive-site.xml, remembering to replace host below with normal host name (must not be localhost):

    psql –host=ip-xx-xxx-xx-x.ec2.internal –port=7432 –username=hive –dbname=hive

    .sql file needed for creation of tables for postgresql will be present in, /opt/cloudera/parcels/CDH/lib/hive/scripts/metastore/upgrade/postgres/

    After creating tables perform below actions:

    1. In the Cloudera Manager interface, click the “hive” service.
    2. Click Configuration / View and Edit.
    3. Expand “Service-Wide” and click “Hive Metastore Database”.
    4. Check the “Hive Metastore Database Host” setting – it is probably pointing at an old address you don’t have control of anymore.
    5. Replace that with the Manager instance’s current private DNS name, obtained from the EC2 console.

Viewing 2 posts - 1 through 2 (of 2 total)
Reply To: Postgre SQL Metastore creation error
Your information: