PostgreSQL WAL directory consuming excessive storage and causing down time

[sunu]

Problem description

We're running a standard deployment of eoapi-k8s for IFRC Montandon with these configuration values:

    ingress:
      host: "montandon-eoapi-stage.ifrc.org"
      tls:
        enabled: false
    pgstacBootstrap:
      settings:
        envVars:
          LOAD_FIXTURES: "0"
          RUN_FOREVER: "1"
    postgrescluster:
      instances:
      - name: eoapi
        replicas: 1
        dataVolumeClaimSpec:
          accessModes:
          - "ReadWriteOnce"
          resources:
            requests:
              storage: "500Gi"
              cpu: "1024m"
              memory: "3048Mi"

We've hit a snag where the database service eventually runs out of disk space after ingesting data for a while. When this happens, it fails its health checks and becomes unresponsive, which brings down the raster service with it.
Looking into our staging instance, we found that the PostgreSQL WAL (Write-Ahead Log) directory is the culprit - it's eating up almost all of our storage. Here's what we're seeing:

bash-4.4$ df -h /pgdata/
Filesystem      Size  Used Avail Use% Mounted on
/dev/sdd        493G  493G     0 100% /pgdata
bash-4.4$ du -sh /pgdata/*
16K	/pgdata/lost+found
116M	/pgdata/pg16
492G	/pgdata/pg16_wal
12K	/pgdata/pgbackrest

The 492GB WAL directory is quite large compared to the 116MB data directory.

Expected Output

Ideally, the WAL directory should stay under certain usage limit and should not cause down time.

Environment Information

Crunchy Postgres Operator: v5.5.2
eoapi-k8s: v0.5.0

[batpad]

cc @bitner @geohacker

[bitner]

@sunu It doesn't look like you have any of the required settings for pgbackrest (

eoapi-k8s/helm-chart/postgrescluster/values.yaml

Line 173 in a673de8

# Backups / pgBackRest Settings #

) set up. Is that snippet the full configuration?

[sunu]

Thanks @bitner! I talked to @thenav56 and they're still seeing the issue with WAL directory growing even when pgbackrest is configured and successfully archiving data. Here's their config:

pgstacBootstrap:
  settings:
    envVars:
      LOAD_FIXTURES: "0"
      RUN_FOREVER: "1"

# Sub chart - https://github.com/developmentseed/eoapi-k8s/blob/main/helm-chart/postgrescluster/values.yaml
postgrescluster:

  # Instance
  instances:
    - name: eoapi
      replicas: 1
      dataVolumeClaimSpec:
        storageClassName: "longhorn"
        accessModes:
          - ReadWriteOnce
        resources:
          requests:
            cpu: "0.2"
            memory: "1Gi"
            storage: "2Gi"
      walVolumeClaimSpec:
        storageClassName: "longhorn"
        accessModes:
        - "ReadWriteOnce"
        resources:
          requests:
            storage: 1Gi

  # Backup
  pgBackRestConfig:
    # https://access.crunchydata.com/documentation/postgres-operator/latest/tutorials/backups-disaster-recovery/backup-management
    global:
      # https://pgbackrest.org/configuration.html#section-repository/option-repo-retention-full
      repo1-retention-full-type: count
      repo1-retention-full: "1"
      repo1-retention-diff: "5"
    repos:
      - name: repo1
        schedules:
          full: "0 1 * * 0"
          differential: "0 1 * * 1-6"
        volume:
          volumeClaimSpec:
            storageClassName: longhorn
            accessModes:
              - "ReadWriteMany"
            resources:
              requests:
                storage: "1Gi"

Some more information along with the storage growth pattern for the data directory, the WAL directory and the pgbackrest archive is available in @thenav56's notes: https://notes.tools.togglecorp.com/05XFdqSgQbaBzVoWjVeJnw#Storage-usages