<p>This particular service is Dockerized and its infraestructure runs on Kubernetes.</p>

<p>Five days ago, Postgres complained about no space left in a volume of 2GB. It was about the logs not having enough space, so instead of trying to solve that problem, we just upgraded the volume to 10GB. In five days all that space has been used. Now we have the green light to investigate.</p>

<p>There are only 10 records in the whole database, so clearly this is not a common issue, nor it is related to data.</p>

<p>The specific error is the following:</p>

<pre><code>DETAIL:  Could not write to file "pg_clog/0000" at offset 32768: No space left on device.                                                                                                
ERROR:  could not access status of transaction 0                                                                                                                                        
</code></pre>

<p>It doesn't relate specifically to logs like the old error, but it has to be related.</p>

<p>Another important point: the same K8S definitions are used with several other projects and even other architectures, but this is the first issue like this I've encountered.</p>

<p>Any insight is appreciated. For more information, files or anything else, I can supply'em. I'm not sure what else I can add right now.</p>

<p>Thanks!</p>

<p>Kubernetes definition files are the following:</p>

<pre><code>
apiVersion: v1
kind: Service
metadata:
  name: postgres-users-service
spec:
  type: ClusterIP
  selector:
    app: postgres-users-app
  ports:
    - port: 5432

---

apiVersion: apps/v1
kind: Deployment
metadata:
  name: postgres-users-deployment
spec:
  replicas: 1
  revisionHistoryLimit: 2
  selector:
    matchLabels:
      app: postgres-users-app
  template:
    metadata:
      labels:
        app: postgres-users-app
    spec:
      containers:
        - name: postgres-users-container
          image: postgres:9.6

          envFrom:
            - secretRef:
                name: users-secrets

          ports:
            - containerPort: 5432

          volumeMounts:
            - name: pvc
              mountPath: /var/lib/postgresql/data
              subPath: postgres-users-app
      volumes:
        - name: pvc
          persistentVolumeClaim:
            claimName: postgres-users-pvc
      restartPolicy: Always
      imagePullSecrets:
        - name: gitlab-registry

---

apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: postgres-users-pvc
spec:
  accessModes:
  - ReadWriteOnce
  resources:
    requests:
      storage: 10Gi
---

</code></pre>

<p><strong>UPDATE</strong></p>

<ul>
<li>About <code>wal_keep_segments</code>, this is what shows up in <code>postgresql.conf</code>.</li>
</ul>

<pre><code>#wal_keep_segments = 0      # in logfile segments, 16MB each; 0 disables
</code></pre>

<ul>
<li><p>No complex operations are being performed. Creation of records (which are 10, like 10 users), some simple update of these records, and that's it.</p></li>
<li><p>"abandoned replication slots": Have no idea what this is. At least, we haven't configured anything like this, ever.</p></li>
</ul>

<p><strong>UPDATE</strong></p>

<ul>
<li>About long running transactions: All elements in <code>pg_stat_activity</code> show the column <code>state</code> as <code>idle</code> (<em>except for the query asking for records of this table</em>). Also the column <code>xact_start</code> has no value for all records.</li>
</ul>

<p><strong>UPDATE</strong></p>

<p>I've found a weird record in the users table. Here's the GIF showing the strange behaviour.
<a href="https://i.stack.imgur.com/c6GYV.gif" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/c6GYV.gif" alt="weird record"></a></p>

<p><strong>UPDATE</strong></p>

<p><code>ls pg_wal | wc -l</code></p>

<pre><code>root@postgres-users-deployment-58bb54d59d-96dnw:/var/lib/postgresql/data# ls pg_wal | wc -l
ls: cannot access 'pg_wal': No such file or directory
0
</code></pre>

<p><code>ls pg_xlog | wc -l</code></p>

<pre><code>root@postgres-users-deployment-58bb54d59d-96dnw:/var/lib/postgresql/data# ls pg_xlog | wc -l
61
</code></pre>

<p><strong>UPDATE</strong></p>

<p><code>users=# SELECT pg_size_pretty(pg_relation_size('users'));</code></p>

<pre><code> pg_size_pretty 
----------------
 48 kB
</code></pre>

<p><code>root@postgres-users-deployment-58bb54d59d-96dnw:/var/lib/postgresql/data# du --max-depth=2 -h $PGDATA</code></p>

<pre><code>12K /var/lib/postgresql/data/pg_multixact/members
12K /var/lib/postgresql/data/pg_multixact/offsets
28K /var/lib/postgresql/data/pg_multixact
8.3G    /var/lib/postgresql/data/base/16384
7.0M    /var/lib/postgresql/data/base/12407
6.9M    /var/lib/postgresql/data/base/1
6.9M    /var/lib/postgresql/data/base/12406
8.3G    /var/lib/postgresql/data/base
12K /var/lib/postgresql/data/pg_notify
36K /var/lib/postgresql/data/pg_clog
32K /var/lib/postgresql/data/pg_stat_tmp
4.0K    /var/lib/postgresql/data/pg_replslot
4.0K    /var/lib/postgresql/data/pg_xlog/archive_status
961M    /var/lib/postgresql/data/pg_xlog
4.0K    /var/lib/postgresql/data/pg_commit_ts
4.0K    /var/lib/postgresql/data/pg_snapshots
4.0K    /var/lib/postgresql/data/pg_dynshmem
4.0K    /var/lib/postgresql/data/pg_logical/snapshots
4.0K    /var/lib/postgresql/data/pg_logical/mappings
12K /var/lib/postgresql/data/pg_logical
4.0K    /var/lib/postgresql/data/pg_tblspc
4.0K    /var/lib/postgresql/data/pg_stat
508K    /var/lib/postgresql/data/global
4.0K    /var/lib/postgresql/data/pg_serial
176K    /var/lib/postgresql/data/pg_subtrans
4.0K    /var/lib/postgresql/data/pg_twophase
9.2G    /var/lib/postgresql/data

</code></pre>

<p>The solution to this was pretty straightforward.</p>
<p>Turns out the database was being interacted to a lot. There was a <strong>scraper</strong> process entering data once every 5 seconds (the master user was being updated). The key was to follow the gif I uploaded: there was an array column that was being uploaded with a new element every 5 seconds. The total amount of elements was around 100k and I presume that was the reason <code>psql</code> wasn't showing the selected element.</p>
