How to increase the disk size for a postgres database in a Docker container?

Question

I'm running a Postgres 15 database in a docker container, set up with docker compose. This is the part in my docker-compose.yaml:

services:
  db:
    image: postgres
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - ./data:/usr/src/app/migrations
    environment:
      - POSTGRES_DB=postgres
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
volumes:
  postgres_data:

My database is currently failing. To debug the issue, I'm going into the db container:

docker compose exec db sh

and try to connect with psql there:

psql -U postgres -d postgres

That fails with the following error message:

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  the database system is in recovery mode

I'm not sure what have caused this. Looking at logs would be useful, however, I'm not sure where they are. Info online suggests me to run SHOW data_directory; on the database but, I can't connect to the database in the first place. The operating system of this main postgres image is Debian, but that did not help me in locating the logs.

Other resources suggest me to look at the disk space, by running df -h. The output of that says:

Filesystem      Size  Used Avail Use% Mounted on
overlay         9.7G  9.5G     0 100% /
tmpfs            64M     0   64M   0% /dev
shm              64M   28K   64M   1% /dev/shm
/dev/sda1       9.7G  9.5G     0 100% /etc/hosts
tmpfs           488M     0  488M   0% /proc/acpi
tmpfs           488M     0  488M   0% /sys/firmware

I guess this means that my overlay partition is indeed full. But what can I do about that? Can I increase the size of it? Can I remove some data in there to free up space?

Final question: my data is in a docker volume. Is this still ok? Maybe I can just stop the db container and start a new one with the same volume and everything will be ok? Or do I risk losing data when doing so?

Looking at the `df` output, your `overlay` partition is probably located on `/dev/sda1`. Unless you have more space on that device currently reserved or used by another disposable partition, then you are out of luck. If you can add another device then you can create a new PG `tablespace` on the new partition and move some tables over there. — Patrick, Jun 05 '23 at 14:14

score -1 · Answer 1 · answered Jun 07 '23 at 12:05

Here's what I did:

Stopped my running service:

docker compose down

Remove dangling docker images:

docker system prune -a

Remove some logs from containers as described here.

That gave me enough room to restart my db service:

docker compose up db

Take a backup:

docker compose exec db pg_dump -U postgres postgres > backup.dump

And then, since my VM is running on GCP, stop the VM, increase the disk size as described in the GCP docs here and restart it.

The only problem is that the external IP address changed so additional tweaking is required in my DNS.

How to increase the disk size for a postgres database in a Docker container?

1 Answers1