How to Check the Size/Consumption of the PostgreSQL Database in the db-noobaa-db-pg-0 PVC

Solution Verified - Updated

Environment

Red Hat OpenShift Container Platform (RCOCP) v4.x
Red Hat OpenShift Container Storage (OCS) v4.x
Red Hat OpenShift Data Foundations (ODF) v4.x
Red Hat Quay (RHQ) v3.x

Issue

In some instances, it may be necessary to observe the data consumption in regards to the noobaa-db-pg-0 PostgreSQL database.

Related Articles:
Expanding the db-noobaa-db-pg-0 PVC
Change the Multi-Cloud Object Gateway Database's Collation Locale to C
How to reduce the retention period of deleted metadata in the ODF Multicloud Gateway MCG Noobaa DB database
How to increase the frequency of deleting metadata in the ODF Multicloud Gateway MCG Noobaa DB database when the Noobaa DB is constantly growing
Performing Maintenance on a Growing PostgreSQL Database

Resolution

To View by Database Name:

  1. Log into postgres:

ODF v4.18 and below:

$ oc exec -n openshift-storage -it noobaa-db-pg-0 -- bash -c 'psql'

ODF v4.19 and above:
a. Identify Primary db:

$ oc get cluster -n openshift-storage
NAME                   AGE   INSTANCES   READY   STATUS                     PRIMARY
noobaa-db-pg-cluster   30h   2           2       Cluster in healthy state   noobaa-db-pg-cluster-1 <----

b. Login to psql using the PRIMARY cluster db:

$ oc exec -n openshift-storage -it noobaa-db-pg-cluster-1 -- bash -c 'psql'
  1. Show all databases and view the Size column of the nbcore database. The example below shows a size of 113 GB:

NOTE: The Collate column must also reflect C. If not, please see the related article in the "Issue" section.

Example Output:

postgres=# \l+

   Name    |  Owner   | Encoding | Collate |   Ctype    |   Access privileges   |  Size   | Tablespace |                Description                 
-----------+----------+----------+---------+------------+-----------------------+---------+------------+--------------------------------------------
 nbcore    | noobaa   | UTF8     | C       | en_US.utf8 |                       | 113 GB  | pg_default |  <------------------------ 
 postgres  | postgres | UTF8     | C       | en_US.utf8 |                       | 7969 kB | pg_default | default administrative connection database
 template0 | postgres | UTF8     | C       | en_US.utf8 | =c/postgres          +| 7825 kB | pg_default | unmodifiable empty database
           |          |          |         |            | postgres=CTc/postgres |         |            | 
 template1 | postgres | UTF8     | C       | en_US.utf8 | =c/postgres          +| 7825 kB | pg_default | default template for new databases
           |          |          |         |            | postgres=CTc/postgres |         |            | 
(4 rows)
  1. Quit and exit the postgres command line and return to the OpenShift bastion:
postgres=# \q

To View by Table:

  1. Log into nbcore:
    ODF v4.18 and below:
$ oc exec -n openshift-storage -it noobaa-db-pg-0 -- bash -c 'psql -d nbcore'

ODF v4.19 and above:

$ oc exec -n openshift-storage -it noobaa-db-pg-cluster-1 -- bash -c 'psql -d nbcore'
  1. List each table in nbcore to view its respective size:

Copy/Paste all contents shown below:

select
table_name,
pg_size_pretty(pg_total_relation_size(quote_ident(table_name))),
pg_total_relation_size(quote_ident(table_name))
from information_schema.tables
where table_schema = 'public'
order by 3 desc;

Example Output:

      table_name      | pg_size_pretty | pg_total_relation_size 
----------------------+----------------+------------------------
 endpointgroupreports | 1816 kB        |                1859584
 system_history       | 664 kB         |                 679936
 systems              | 168 kB         |                 172032
 nodes                | 128 kB         |                 131072
 buckets              | 96 kB          |                  98304
 clusters             | 96 kB          |                  98304
 pools                | 96 kB          |                  98304
 objectmds            | 88 kB          |                  90112
 iostats              | 72 kB          |                  73728
 tieringpolicies      | 64 kB          |                  65536
 activitylogs         | 64 kB          |                  65536
 tiers                | 64 kB          |                  65536
 objectstats          | 64 kB          |                  65536
 accounts             | 64 kB          |                  65536
 objectparts          | 56 kB          |                  57344
 datachunks           | 56 kB          |                  57344
 chunk_configs        | 48 kB          |                  49152
 master_keys          | 48 kB          |                  49152
 datablocks           | 48 kB          |                  49152
 roles                | 48 kB          |                  49152
 alertslogs           | 48 kB          |                  49152
 objectmultiparts     | 40 kB          |                  40960
 namespace_resources  | 32 kB          |                  32768
 agent_configs        | 32 kB          |                  32768
 bucketstats          | 32 kB          |                  32768
 funcs                | 32 kB          |                  32768
 usagereports         | 32 kB          |                  32768
 replicationconfigs   | 24 kB          |                  24576
(28 rows)
  1. Quit and exit the nbcore database and return to the OpenShift bastion:
postgres=# \q

Root Cause

Databases can grow, and PVCs may need to be expanded. However, in rare instances, there may be other issues, such as duplicates or other issues in the db. If nbcore database is growing at a faster rate than expected, open a case with Red Hat Support.

Diagnostic Steps

Alert Manager firing:

noobaa-db is low on disk space:
"The PersistentVolume claimed by db-noobaa-db-pg-0 in Namespace openshift-storage is only 2.431% free."

This solution is part of Red Hat’s fast-track publication program, providing a huge library of solutions that Red Hat engineers have created while supporting our customers. To give you the knowledge you need the instant it becomes available, these articles may be presented in a raw and unedited form.