PSQL + Kubernetes - Where are my tables?

8/25/2018

I have a Scala App running on a Service, using a PostgreSql Service, Tables are being created by the database management plugin (Slick)

however, need to edit a particular table and I cant find it!

postgres service:

apiVersion: v1
kind: Service
metadata:
  name: core-db
spec:
  ports:
    - port: 5432
  selector:
    app: core-db
---
apiVersion: extensions/v1beta1
kind: Deployment
metadata:
  name: core-db
spec:
  template:
    metadata:
      labels:
        app: core-db
    spec:
      containers:
        - image: "image"
          name: core-db
          imagePullPolicy: IfNotPresent
          env:
            - name: POSTGRES_USER
              value: postgres
            - name: POSTGRES_PASSWORD
              value: postgres
          ports:
            - containerPort: 5432
              name: core-db
          volumeMounts:
            - name: core-storage
              mountPath: /var/lib/postgresql/db-data
      volumes:
          - name: core-storage
            persistentVolumeClaim:
                claimName: core-pv-claim
      imagePullSecrets:
        - name: regcred
---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: core-pv-claim
spec:
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 100Mi

I get in the pod:

kubectl exec POD -it -- psql -U postgres

but \d shows no relations

What I am doing wrong, where is my data after all ?

EDIT

\l
postgres  
app   
template0 
template1 

\c postgres
\dt
No relations found.

\c app
\dt
No relations found.
-- tiefizzy
gcloud
kubernetes
postgresql

2 Answers

8/26/2018

Steps I would do to check where the problem are:

  1. the app is working so, if it's persisting data, these data should be somewhere. Try to kubectl delete deploy core-db. Is the app still working and saving data?

    I expected no as answer, and so we can go to the following points.

    If the answer is yes, so the problem may be to:

    • db connection string inside the app

    • the service, how many EndPoints do you have? kubectl get ep core-db

  2. exec psql pod and connect to postgres db \c postgres and then \dt. Are tables here?

  3. exec psql pod and connect to app db \c app and then \dt. Are tables here?

  4. exec psql pod and connect to postgres and then SELECT * FROM pg_stat_activity WHERE datname = 'app';

-- Nicola Ben
Source: StackOverflow

8/28/2018

Ok so, it happens that I just lacked some brain activity.

All my tables are stored into Schemas, and that's the reason I can see them when I use \dt on any of the databases

Connecting to the 'app' database and using \dn will list the schemas and thus, we can find the tables.

Select "schema".table is how it works...

Thanks to @NicolaBen, those steps helped me.

-- tiefizzy
Source: StackOverflow