DX APM - Unable to login to tenant - corrupted postgres database

Products

• CA Application Performance Management Agent (APM / Wily / Introscope)
• CA Application Performance Management (APM / Wily / Introscope)
• INTROSCOPE
• DX Application Performance Management

Issue/Introduction

Unable to login to APM 11 login UI, apmservices-gateway, dxportal, tenant:

“Failed to login. Verify that user ID and password are correct”

Cause

Postgres "dsp_db" database is corrupted,

In this case, the file-system of the NFS server ran out of disk space damaging some data files

It is not possible to recover the data files. 

You can confirm this condition from the postgres log, there is a message indicating that dsp_db is corrupted:

cd /nfs/ca/dxi/axaservices/pg-data/userdata/pg_log
tail -f postgresql-<day>.log


You can try to recreate the initial state of postgres databases but data created after installation will be lost.
Restoring the "aoplatform" database should allow you to login to the existing tenants as it contains primary information related to the tenants configuration. 


NOTE: to list all the databases in postgres:

a) obtain the postgres pod name

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

b) login to pod

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

c) list databsaes

psql -U postgres -d postgres

postgres=# \l
                                 List of databases
    Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
------------+----------+----------+------------+------------+-----------------------
 aoplatform | aopuser  | UTF8     | en_US.utf8 | en_US.utf8 | =Tc/aopuser          +
            |          |          |            |            | aopuser=CTc/aopuser
 apmpe      | apmpe    | UTF8     | en_US.utf8 | en_US.utf8 |
 cpa        | aopuser  | UTF8     | en_US.utf8 | en_US.utf8 | =Tc/aopuser          +
            |          |          |            |            | aopuser=CTc/aopuser
 doi        | aopuser  | UTF8     | en_US.utf8 | en_US.utf8 | =Tc/aopuser          +
            |          |          |            |            | aopuser=CTc/aopuser
 dsp_db     | aopuser  | UTF8     | en_US.utf8 | en_US.utf8 | =Tc/aopuser          +
            |          |          |            |            | aopuser=CTc/aopuser
 dxi        | dxi      | UTF8     | en_US.utf8 | en_US.utf8 |
 postgres   | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
            |          |          |            |            | postgres=CTc/postgres
 template1  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
            |          |          |            |            | postgres=CTc/postgres
(9 rows)

postgres=# \q

Environment

APM 11.x, APM 19.x

Resolution

1. Backup db

a) obtain the postgres pod name

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

b) connect to posgres pod

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

c) backup aoplatform

pg_dump -U aopuser -F p aoplatform > aoplatform.sql

 

d) exit from pod

e) copy aoplatform.sql  from pod to local system for example to /

Syntax: kubectl cp <some-namespace>/<some-pod>:/tmp/foo /tmp/bar

kubectl cp dxi/postgresql-77c878cc47-76hwm:/opt/app-root/src/aoplatform.sql /

 

NOTE: if you see "tar: Removing leading `/' from member names" message, you can ignore it.


2. Scale down to zero the postgresql deployment

kubectl scale deploy postgresql --replicas=0 -ndxi

Or using Kubernetes Console




3. Backup Postgres pg_data 

In this example, we assume APM NFS folder = /nfs/ca/dxi/

mv /nfs/ca/dxi/axaservices/pg-data/  /nfs/ca/dxi/axaservices/pg-data.bck


4. Created new folder /nfs/ca/dxi/axaservices/pg-data and grant 26:26 permission

mkdir /nfs/ca/dxi/axaservices/pg-data
chown -R 26:26 /nfs/ca/dxi/axaservices/pg-data
 

5. Scale up the postgresql deployment using kubectl or kubernetes console:

kubectl scale deploy postgresql --replicas=1 -ndxi

deployment.extensions "postgresql" scaled


6. Delete the newly created aoplatform database 

dropdb aoplatform;


NOTE: you might not be able to delete the database because sessions are in use, in this case you can proceed as below:

   a) obtain the postgres pod name

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

   b) connect to posgres pod

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

   c) Identify open session

   psql -U postgres -d postgres

   SELECT * FROM pg_stat_activity;

   d) \q

   e) run:

   kill <all the sessions> 



7. Restore aoplatform.sql
 
a) connect to posgres pod

kubectl exec -it postgresql-77c878cc47-hvst8 -ndxi bash

b) copy aoplatform.sql from local system to new pod

Syntax: kubectl cp /tmp/foo <some-namespace>/<some-pod>:/tmp/bar

kubectl cp /aoplatform.sql dxi/postgresql-77c878cc47-hvst8:/opt/app-root/src/

c) restore aoplatform

createdb aoplatform;
psql -U aopuser -d aoplatform -f aoplatform.sql


8. (Optional) Backup up the aoplatform and dsp_db database using pg_dump as indicated in step#3 for future usage and restore.


NOTE: If for some reason something goes wrong you can always:
- scale down the postgres deployment
- restore the postgres pg-data fodler backup, in this example: /nfs/ca/dxi/axaservices/pg-data.bck
- scale up postgres to revert back all above changes.
 

Additional Information

DX - Unable to login to tenant or masteradmin console :
https://knowledge.broadcom.com/external/article/185182