How to connect to Ambari's PostgreSQL database

Products

Services Suite

Issue/Introduction

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.

Environment

Resolution

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=>