Postgres database running in docker keeps hanging

Question

I'm using the postgres docker image, and after months of using databases running in docker images, now I'm getting the behaviour where after a certain period of time, they simply just hang. I can exec with bin/bash but can't do anything with postgres at all; the commands don't return and the containers can't be brought down. Even docker kill -s SIGKILL <container_id> doesn't work; needs a reboot of the docker server to stop them.

The only smoking gun I can see is the message:

 WARNING:  could not open statistics file "pg_stat_tmp/global.stat": Operation not permitted

on all containers. Anyone has any ideas I'd be really appreciative as this is killing things at the moment.

Answer 1

This is happening due to a user permission mismatch in the docker container.

Listing the relevant files in the container:

$ docker exec <container> ls -l /var/lib/postgresql/data/pg_stat_tmp
-rw------- 1 root     root     [...] db_0.stat
-rw------- 1 root     root     [...] db_1.stat
-rw------- 1 root     root     [...] db_2.stat
-rw------- 1 postgres postgres [...] global.stat

we can see that all the db_*.stat files are owned by root:root, while global.stat is owned by postgres:postgres.

Checking the docker user gives us:

$ docker exec <container> whoami
root

So, we'd like all of these files to be owned by the postgres user. Luckily, this is quite easy! Just set user to postgres, and restart!

In a dockerfile:

USER postgres

Using docker-compose:

services:
  postgres:
    image: postgres:13
    user: postgres