Troubleshooting Quay Database

Updated 24 Jul 2023

Introduction

The PostgreSQL database used in Red Hat Quay stores various types of information related to container images and their management.
Some of the key pieces of information that the PostgreSQL database keeps include:
- Image Metadata: The database stores metadata associated with container images, such as image names, versions, creation timestamps, and the user or organization that owns the image. This information allows for easy identification and organization of container images within the registry.
- Image Tags: Quay allows users to assign tags to container images, enabling convenient labeling and versioning. The PostgreSQL database maintains the mapping between image tags and their corresponding image manifests, allowing users to retrieve specific versions of container images based on the provided tags.
- Image Layers: Container images are composed of multiple layers, which are stored as individual objects. The database records information about these layers, including their order, checksums, and sizes. This data is crucial for efficient storage and retrieval of container images.
- User and Organization Data: Red Hat Quay supports user and organization management, allowing users to authenticate and manage access to container images. The PostgreSQL database stores user and organization information, including usernames, email addresses, authentication tokens, and access permissions.
- Repository Information: Quay organizes container images into repositories, which act as logical units for grouping related images. The database maintains repository data, including names, descriptions, visibility settings, and access control information, enabling users to manage and share their repositories effectively.
- Event Logs: Quay tracks various events and activities related to image management and repository operations. These event logs, including image pushes, pulls, deletions, and repository modifications, are stored in the PostgreSQL database, providing an audit trail and allowing administrators to monitor and analyze system activities.

Troubleshooting Steps

Check the type of Deployment for the database. Whether it is deployed as a container on VM or deployed on OpenShift as a pod.
Check Container Status

$ oc get pods

$ podman ps -a

Examine database container/pod logs

Quay database container/pod has no verbose logs, use the below commands. Make sure the desired dir exists on the host.

 $ oc logs pod-name --previous 
 $ oc logs pod-name --previous -c <container_name>
 $ oc cp pod-name:/var/lib/pgsql/data/userdata/log/* /path/to/desired-directory-on-host

 $ podman logs container-name --previous
 $ podman logs container-name --previous -c <container_name>
 $ podman cp database-container:/var/lib/pgsql/data/userdata/log/* /path/to/desired-directory-on host

For when container/pod is in a crashloopbackoff state

    $ oc scale deployment/quay-operator.v3.8.z  --replicas=0
     deployment.apps/quay-operator.v3.8.z scale

    $ oc scale deployment/quay-database --replicas=0
    deployment.apps/quay-database scaled

    $ oc edit deployment quay-database 
    ...
     template:
       metadata:
         creationTimestamp: null
         labels:
           quay-component: quay-database
           quay-operator/quayregistry: quay-operator.v3.8.z
       spec:
         containers:
         - env:
           - name: POSTGRESQL_USER
             value: postgres
           - name: POSTGRESQL_DATABASE
             value: postgres
           - name: POSTGRESQL_PASSWORD
             value: postgres
           - name: POSTGRESQL_ADMIN_PASSWORD
             value: postgres
           - name: POSTGRESQL_MAX_CONNECTIONS
             value: "1000"
           image: registry.redhat.io/rhel8/postgresql-10@sha256:a52ad402458ec8ef3f275972c6ebed05ad64398f884404b9bb8e3010c5c95291
           imagePullPolicy: IfNotPresent
           name: postgres
           command: ["/bin/bash", "-c", "sleep 86400"].   << add this line in the same indentation 
    ...
    deployment.apps/quay-database edited
 
    $ oc exec -it <quay-database> -- cat /var/lib/pgsql/data/userdata/postgresql/logs/* /path/to/desired-directory-on-host

Connectivity and Networking

Check connectivity between Quay and database pod (Given they are in running state).

  $ oc exec -it quay-pod-name -- curl -v telnet://database-pod-name:5432

  $ podman exec -it quay-container-name curl -v telnet://database-container-name:5432

Check Database Configuration

On OpenShift and container level

  $ oc exec -it quay-pod-name -- cat /conf/stack/config.yaml

  $ podman exec -it quay-pod-name cat /conf/stack/config.yaml

On Postgres level

  $ oc exec -it <database-pod> -- cat /var/lib/pgsql/data/userdata/postgresql.conf    

  $ podman exec -it <database-container> cat /var/lib/pgsql/data/userdata/postgresql.conf

When container/pod is in crashloopbackoff/error state

    $ oc get secret -n <quay-project> <quay-config-secret> -o "jsonpath={$.data['config\.yaml']}" | base64 -d  &> quay-config.yaml

    $ cat $QUAY/conf/config.yaml

Check Resource Allocation
- List running containers
```
$ oc get pods

$ podman ps
```
- Monitor disk usage
```
    $ oc exec -it quay-database-pod-name -- df -ah 

    $ podman exec -it quay-database-conatiner-name df -ah 
```
- Monitor other resource usage:
```
    $ podman pod stats pod-name
    CONTAINER ID  NAME          CPU %  MEM USAGE / LIMIT   MEM %    NET I/O           BLOCK I/O      PIDS

    83015f9a31f0  container-1   0.12%  12.3MiB / 1GiB      1.23%    9.18kB / 0B       11.5kB / 0B    7
    20d847fc9e1f  container-2   0.08%  8.2MiB / 1GiB       0.82%    5.76kB / 0B       7.52kB / 0B    4

    $ podman stats container-name
  
    CONTAINER ID   NAME      CPU %   MEM USAGE / LIMIT     MEM %     NET I/O         BLOCK I/O       PIDS

    d987d2a9f1bf   quay_app  0.05%   300.4MiB / 7.795GiB   3.77%     8.23kB / 648B   8.19kB / 648B   27
```
- CPU %: The percentage of CPU usage by the container since the last measurement. This value represents the container's share of the available CPU resources.
- MEM USAGE / LIMIT: The current memory usage of the container followed by its memory limit. The values are displayed in the format current_usage / memory_limit. For example, 300.4MiB / 7.795GiB indicates that the container is currently using 300.4 megabytes of memory out of a limit of 7.795 gigabytes.
- MEM %: The percentage of memory usage by the container in relation to its memory limit.
- NET I/O: The network I/O (input/output) statistics of the container. It displays the amount of data transmitted and received by the container over the network. The values are displayed in the format: transmitted_bytes / received_bytes.
- BLOCK I/O: The block I/O (input/output) statistics of the container. It represents the amount of data read from and written to the block devices (e.g., disks) used by the container. The values are displayed in the format read_bytes / written_bytes.

Interact with database

Locate the quay container/pod and use the psql command-line tool to interact with the database.

$ oc exec -it quay-database-pod -- psql
postgres=# \l+    # this will show all databases stored and their respective sizes
postgres=# \c "quay-database-name";
      
$ podman exec -it quay-database-container psql
postgres=# \l+    # this will show all databases stored and their respective sizes
postgres=# \c "quay-database-name";