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.

Who is using postgres (I guess you have some kind of backend connecting to it...). What kind of data do you handle. Do you have any large or complex queries that might render postgres without memory or the server without space? — Marc Sances
– Marc Sances, Commented Oct 20, 2020 at 13:07
This is actually on my laptop. No large or complex queries. Not much data. There's plenty of memory left. — David Boshton
– David Boshton, Commented Oct 20, 2020 at 13:19
I'm having the same issue on my dev environment since upgrading to the latest version of docker — craigp
– craigp, Commented Nov 5, 2020 at 5:43
I've downgraded docker from 2.5 to 2.4 and so far it's going better.. — craigp
– craigp, Commented Nov 5, 2020 at 6:15
FWIW I get these same WARNINGs frequently in my Postgres logs on docker, but it doesn't seem to cause any hangs in my case. PostgreSQL 9.6.15, docker engine 19.03.13, macOS High Sierra. — Josh Kupershmidt
– Josh Kupershmidt, Commented Dec 8, 2020 at 15:11

Dharman · Accepted Answer · 2021-01-27 08:02:35Z

13

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

edited Jan 27, 2021 at 8:02

Dharman♦

34k27 gold badges106 silver badges158 bronze badges

answered Jan 27, 2021 at 7:56

Norling

1,1812 gold badges10 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

吳約南 Over a year ago

Not working for me. All files or dirs under Postgres data directory are belong to postgres:postgres, but the logs by docker logs -f postgres still pop ``` WARNING: could not open statistics file "pg_stat_tmp/global.stat": Operation not permitted ```

Fide · Accepted Answer · 2021-12-17 11:05:06Z

0

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
    .
    .
    .

answered Dec 17, 2021 at 11:05

Fide

1,1978 silver badges9 bronze badges

Collectives™ on Stack Overflow

Postgres database running in docker keeps hanging

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related