DX O2 Postgres Queries

Products

• DX Operational Observability

Issue/Introduction

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

Environment

DX O2 2* OnPremise

Resolution

Table of Contents

• Connect to postgres pod
• List Databases
• Connects to a database under a specific user
• Switch connection to a new database
• 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
dxi-postgres-7bc9cff448-zhg9f                          1/1     Running            0                178m
dxi-postgres-schema-5ffcbbf746-z4dvq                   1/1     Running            0                177m


b) login to pod

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

Example:

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

 

• List Databases

psql -l

Example:

                                                     List of databases
     Name      |  Owner   | Encoding | Locale Provider | Collate |  Ctype  | ICU Locale | ICU Rules |   Access privileges
---------------+----------+----------+-----------------+---------+---------+------------+-----------+-----------------------
 aoplatform_db | aopuser  | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =Tc/aopuser          +
               |          |          |                 |         |         |            |           | aopuser=CTc/aopuser
 doi_db        | aopuser  | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =Tc/aopuser          +
               |          |          |                 |         |         |            |           | aopuser=CTc/aopuser
 grafana_db    | aopuser  | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =Tc/aopuser          +
               |          |          |                 |         |         |            |           | aopuser=CTc/aopuser
 postgres      | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           |
 template0     | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =c/postgres          +
               |          |          |                 |         |         |            |           | postgres=CTc/postgres

 

• Connects to a database under a specific user

psql -d database -U user -W

Example:

psql -U aopuser -d aoplatform_db

 

• Switch connection to a new database

\c dbname

Example:

\c aoplatform_db aopuser

 

• 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