CODEWITHSUNDEEP
postgresqldocker
David Boshton
David Boshton

Reputation: 2775

Postgres database running in docker keeps hanging

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.

Upvotes: 11

Views: 5700

Answers (2)

Fide
Fide

Reputation: 1197

As Norling and Dharman's answer did not work for me, I've tried another way: leaving the temporary file in container - and that worked. I've changed with inline command the Postgresql config:

postgresdb: 
    image: 'postgres'
    command: postgres -c stats_temp_directory=/tmp
    .
    .
    .

Upvotes: 0

Norling
Norling

Reputation: 1171

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

Upvotes: 13

Related Questions