AIOps - Postgres Queries

Products

• DX Operational Intelligence
• DX Application Performance Management
• CA App Experience Analytics

Issue/Introduction

The following is a high list of queries to employ when troubleshooting performance, display and configuration issues related to Postgres,

Environment

DX Platform 2x

Resolution

Table of Contents

• Connect to postgres pod
• Connects to a database under a specific user
• Switch connection to a new database
• List available databases
• List database size
• List available tables  
• Describe a table such as a column, type, modifiers of columns, etc. 
• Display command history 
• Know all available psql commands
• Exit psql shell  
•  

• Connect to postgres pod

a) obtain the postgres pod name

kubectl get pods -n<namespace> | grep post

Example:

kubectl get pods -ndxi | grep post
postgresql-77c878cc47-76hwm                          1/1       Running       0          26s


b) login to pod

kubectl exec -it <postgresql-pod> -n<namespace> bash

Example:

kubectl exec -it postgresql-77c878cc47-76hwm -ndxi bash

 

• Connects to a database under a specific user

psql -d database -U user -W

Example:

psql -U aopuser -d aoplatform

 

• Switch connection to a new database

\c dbname

Example:

\c aoplatform aopuser

 

• List available databases

\l

• List database size

\l+

 

• List available tables  

\d+

 

• Describe a table such as a column, type, modifiers of columns, etc. 

\d table_name

for example:

\d aradminbasicauthuser

 

• Display command history 

\s

 

• Know all available psql commands

\?

 

• Exit psql shell  

\q

Additional Information

https://knowledge.broadcom.com/external/article/190815/aiops-troubleshooting-common-issues-and.html