Pivotal Knowledge Base

Follow

How to connect to Ambari's PostgreSQL database

Environment

Product Version
Pivotal HD (PHD) 3.0.x
Ambari 1.7.1 / 2.1.2

Purpose

While troubleshooting Ambari issues, it may be necessary to connect to the Ambari PostgreSQL database to review the contents of the database to understand a specific error. This article will explain how this connection can be achieved.

Important Notes:

  • Ambari can be configured to use MySQL or Oracle databases in the backend, the steps below apply only to PostgreSQL but will likely be useful for connecting to a database hosted by other types of RDMS.
  • Only SELECT should be run on the database to read data and help troubleshoot issues. NO UPDATES, DELETES, or INSERTS should be run on the database. If updates are needed, these should be done via the Ambari GUI or API

Procedure

1. Log into the Ambari node as user root.

2. Determine the process ID for the Ambari postgres instance:

root@amb171hawq data]# ps -eaf | grep ambari | grep postgres | awk '{print $3}'
2855
2855
2855
2855
2855
2855
2855
2855
2855

3. Determine the port that is being used by the Ambari PostgreSQL instance by using the process ID found previously:

[root@amb171hawq data]# netstat -anp | grep 2855
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 2855/postmaster
tcp 0 0 :::5432 :::* LISTEN 2855/postmaster
udp 0 0 127.0.0.1:40899 127.0.0.1:40899 ESTABLISHED 2855/postmaster
unix 2 [ ACC ] STREAM LISTENING 24744 2855/postmaster /tmp/.s.PGSQL.5432
[root@amb171hawq data]#

From the above output, we can see that the Ambari PostgreSQL instance is using port 5432 (default port).

4. Log on to the Ambari database with the command below (default password is 'bigdata'):

[root@amb171hawq data]# psql ambari -U ambari -W -p 5432
Password for user ambari: 
psql (8.4.20)
Type "help" for help. ambari=>

5. Tables available can be listed with \dt

ambari=> \dt
List of relations
Schema | Name | Type | Owner
--------+-------------------------------+-------+----------
ambari | adminpermission | table | postgres
ambari | adminprincipal | table | postgres
ambari | adminprincipaltype | table | postgres
ambari | adminprivilege | table | postgres
ambari | adminresource | table | postgres
<...>
ambari | qrtz_paused_trigger_grps | table | postgres
ambari | qrtz_scheduler_state | table | postgres
ambari | qrtz_simple_triggers | table | postgres
ambari=>

6. An example of a query could be the following: 

ambari=> select * from metainfo;
metainfo_key | metainfo_value
--------------------------------------------------+------------------------------------------
version | 1.7.1
repo:/PHD/3.0/redhat5/PHD-UTILS-1.1.0.20:baseurl | http://amb171hawq.lab/PHD-UTILS-1.1.0.20
repo:/PHD/3.0/redhat6/PHD-UTILS-1.1.0.20:baseurl | http://amb171hawq.lab/PHD-UTILS-1.1.0.20
repo:/PHD/3.0/suse11/PHD-UTILS-1.1.0.20:baseurl | http://amb171hawq.lab/PHD-UTILS-1.1.0.20
repo:/PHD/3.0/suse11/PHD-3.0:baseurl | http://amb171hawq.lab/PHD-3.0.1.0
repo:/PHD/3.0/redhat6/PHD-3.0:baseurl | http://amb171hawq.lab/PHD-3.0.1.0
repo:/PHD/3.0/redhat5/PHD-3.0:baseurl | http://amb171hawq.lab/PHD-3.0.1.0
(7 rows)
ambari=>

 

Comments

Powered by Zendesk