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

Surfing around the net, this issue has been mentioned on this forum here before as well as a couple of Stack Exchange questions. I may be beating a dead horse.

My compose file at this point looks like this:

services:
  db:
    image: postgres:${POSTGRES_VERSION}
    volumes:
      - ./db/data:/var/lib/postgresql/data
    environment:
      POSTGRES_PASSWORD: password
  web:
    build:
      context: .
      dockerfile: docker/Dockerfile
      # These come from .env
      args:
        - COMPOSE_PROJECT_NAME
        - DOCKER_REPOSITORY
        - POSTGRES_VERSION
        - PROJECT_VERSION
        - RUBY_IMAGE
        - RUBY_VERSION
    hostname: ${COMPOSE_PROJECT_NAME}
    image: ${DOCKER_REPOSITORY}/${COMPOSE_PROJECT_NAME}:${PROJECT_VERSION}
    volumes:
      - .:/${COMPOSE_PROJECT_NAME}
    ports:
      - "3000:3000"
    depends_on:
      - db

What I’ve discovered that no one else seems to have looked at is that some files inside the container (from the container’s perspective) are owned by root.

docker compose --project-directory . --file docker/compose.yml run --no-deps -it db bash
root@3d1eff959317:/# find /var/lib/postgresql/data -user 0 -exec ls -l {} +
-rw------- 1 root root    8 Sep  9 12:58 /var/lib/postgresql/data/pg_logical/replorigin_checkpoint
-rw------- 1 root root 2225 Sep  9 13:09 /var/lib/postgresql/data/pg_stat_tmp/db_0.stat
-rw------- 1 root root 6665 Sep  9 13:09 /var/lib/postgresql/data/pg_stat_tmp/db_13757.stat
-rw------- 1 root root 7035 Sep  9 13:08 /var/lib/postgresql/data/pg_stat_tmp/db_16384.stat
-rw------- 1 root root   94 Sep  9 12:53 /var/lib/postgresql/data/postmaster.pid

My suspicion is that this is what is causing the errors.

I asked on the Postgres mailing list and I got this reply which I’m too inexperienced to fully understand:

| The data directory is outside the container so it is persistent. The pg_stat_tmp is inside the data directory.
Ah, that’s the reason. Docker daemon runs as root so if you do binding mount, files will be owned by root. You may want to use normal Docker volume and not an external directory.

To clarify a bit, Postgres has a data directory which is /var/lib/postgresql/data inside the container which I have mounted to ./db/data on my laptop. The pg_stat_tmp directory is inside the data directory. Everything within the data directory should only be written by a Postgres daemon which has UID:GID of 999:999. Let, somehow, a few files are becoming owned by root.

The reply above makes me think that this is somehow due to the Docker daemon inside the container but I don’t understand really why the daemon would be creating files within the container and if this is a problem, why is it occurring in only very few cases?

There are various solutions suggested but they seem more like work arounds to me instead of addressing the real root issue – but I could easily be mistaken.

It seems to me you might have not one, but two different problems.

Mounts

You mounted ./db/data directly to /var/lib/postgresql/data therefore postgresql in the container will not be able to set the proper folder permission, because you can’t change the permissions of the mount point.

If you read the description of the postgres image (https://hub.docker.com/_/postgres), it mentions:

PGDATA

This optional variable can be used to define another location - like a subdirectory - for the database files. The default is /var/lib/postgresql/data. If the data volume you’re using is a filesystem mountpoint (like with GCE persistent disks) or remote folder that cannot be chowned to the postgres user (like some NFS mounts), Postgres initdb recommends a subdirectory be created to contain the data.

So you need to set the PGDATA variable and use a subfolder in “data” which can be changed by postgres in the container so your files will not be owned by root. Actually I find it surprising that the files could be created. Did you created the files in an other Potgres instance before you mounted it in the container?

This issue is why you got the answer that you quoted, however that answer was just partially correct. Docker Daemon will not run in your postgres container, but this is not what they meant. They meant that Docker daemon on the host is running as root, so if it creates a folder, it will create it as root. If you did not create the folder before you run the containers, then it explains why it was created as root, but even if you set the permissions on the host, as far as I know, postgres will still try to set it again and fail.

Using a “normal volume” was actually a good advice, but I don’t like using volumes for production databases either, because it is so easy to delete accidentally so I use volumes for less important data. It is completely fine during development.

Operation not permitted on the stat file

In the topic you found the OP used a volume, not a “bind mount”. Using a volume means Docker can create the folder with the right permissions so postgres will have what it needs. This is why I thought it was not about incorrect permissions. When I tried to use the same data folder from multiple postgres instance, I got the '“Operation not permitted” error message. So if you did that or you somehow copied the database files from an existing postgres instance to use it in a containr, those files may be corrupted.

My suggestion is that you move your ./db/data fodler to ./db/data/pgdata and keep the current mount as it is, but set the PGDATA environment variable to /var/lib/postgresql/data/pgdata.

Since some the files are already created, when you run docker compose up -d again, postgres could still fail. If it happens, try to change the owner of the files and the group to 999 like:

chown -R 999:999 ./db/data/pgdata

If it still doesn’t work, then the db files are probably corrupted or something is still using those files. Make sure no other postgres instances are using those files. Not in a container and not on your host.

If you just started this database from scratch, and you don’t mind losing it, you can run docker compose down, remove the pgdata folder and start the containers again.

Yep. One of those posts on StackOverflow was started by me, and it is a very popular question with quite a few answers, which, your are right, seem like mostly workarounds. 12K visits with 32 up votes for the question. There are some answers in there that I have not tried that might actually work well.

My Docker setup actually maps / mounths the data directory to a folder on my host, but it sounds like you have the similar issue when using a container ?

It is an annoying issue, and really seems like sort of a bug that either Docker or Postgres could publish or come up with a ‘standard’ solution to.

Ah yes. Scotti. I remember your name on the Stack Exchange thread.

My setup is basically just like the Docker docs Quickstart which adds to your statement that someone should clean something up somewhere.

The compose has two containers and each has a mount. The db container mounts Postgres’ data directory /var/lib/postgresql/data (from inside the container) over ./db/data on the laptop. The very first time, ./db/data doesn’t exist so something in the compose bring up creates the folder. But I think @rimelek might have misunderstood. The problem is not outside the container but inside AND what is even weirder is I can chown everything inside the container to be owned by postgres:postgres and later, someone somewhere somehow creates more temp files owned by root inside the container and owned by me outside on my laptop,

I’ve opened an issue here in the docker-library/doc repository but that probably isn’t the right place.

I read more about Docker’s volumes and it convinced me that using a Docker volume rather than a “binding mount” is a better approach. What is a little odd to me is it seems like the recipe on the Docker site (above) would adopt that as well. It is easy, demonstrates another feature of Docker, and is more aligned with best practices.

But this is definitely a bug somewhere. It is just too whacky and inconsistent not to be. There is a comment somewhere and the guy said he saw a timing hole in Postgres but… when I looked inside the container, all the Postgres processes are UID Postgres so they can’t possibly be at fault.

I wonder if I can create a simpler test case that demonstrates the issue and then open a bug report somewhere in Docker.

A note to @rimelek: the files that are owned by root (inside the container) are in fact in a sub-directory pg_stat_tmp of the data directory and is owned by Postgres. Thank you @rimelek for all your help here and in two or three other posts I’ve made over the past week or so.

Here is another one, probably related, if not yours:

There is more information in the postgres docs here as well:

The statistics collector transmits the collected information to other PostgreSQL processes through temporary files. These files are stored in the directory named by the [stats_temp_directory] parameter, pg_stat_tmp by default. For better performance, stats_temp_directory can be pointed at a RAM-based file system, decreasing physical I/O requirements. When the server shuts down cleanly, a permanent copy of the statistics data is stored in the pg_stat subdirectory, so that statistics can be retained across server restarts. When recovery is performed at server start (e.g., after immediate shutdown, server crash, and point-in-time recovery), all statistics counters are reset.

Let’s share with everyone what we are talking about :slight_smile: postgresql - DOCKER container with postgres, WARNING: could not open statistics file "pg_stat_tmp/global.stat": Operation not permitted - Stack Overflow

In that tpic however one answer was accepted which uses the same variable that I recommended, PGDATA and mentions Docker Desktop.

I have to admit that I remembered wrong and thought this error would happen on every platform, but I realized it does not happen on Linux in case of installing Docker CE on the host, since then the mount is just a bind mount and not a remote network fileshare.

What platform are you workong on? Is it Docker Desktop or just Docker CE on Linux? If it is Docker Desktop then Linux, Windows or macOS?

You say you can change the owner inside the container. Can you show me the list of your files inside and also outside the container? Those owners cannot be different unless you use Docker Desktop and in that case the files on your host are not the same as the files in the virtual machine of Docker Desktop that your container actually mounts. I also remember a case when “sed” didn’t work inside a container on Docker Desktop. I think the reason was that files inside the container seemed to have right permissions, but it could not change on the host so it just threw an error. Mounts are different on each platform so it is important to know what you are using.

The fact that your files are in a subfolder is not a big difference, because if postgresql tries to change the owner recursively from the mount point, that will still fail.

Have you tried the suggested solution using the PGDATA environment variable?

I’m on macOS 12.5.1 with Docker version 20.10.17, build 100c701

I need to re-read everyone’s replies. I had forgotten that there is another virtual machine involved. Is it possible to log into that virtual machine and look around?

Note, I’ve moved on and changed things so I need to revert some things back like they were. I’m first going to make sure I can recreate the issue and then take your suggestion about PGDATA.

Some links for that:

It has been an interesting but unproductive day. In brief, the suggestion didn’t work. Here is my compose file:

version: "3.9"
services:
  db:
    image: postgres
    volumes:
      - ./tmp/db:/var/lib/postgresql/data
    environment:
      PGDATA: /var/lib/postgresql/data/pgdata
      POSTGRES_PASSWORD: password
  web:
    build: .
    command: bash -c "rm -f tmp/pids/server.pid && bundle exec rails s -p 3000 -b '0.0.0.0'"
    volumes:
      - .:/myapp
    ports:
      - "3000:3000"
    depends_on:
      - db

I think that is what you were suggesting. On the laptop, I have:

pedz@Peace:s002 From-Docker-Run % ls -l !$
ls -l tmp/db
total 0
drwx------@ 26 pedz  staff  832 Sep 11 19:02 pgdata
pedz@Peace:s002 From-Docker-Run % ls -l tmp/db/pgdata 
total 120
-rw-------   1 pedz  staff      3 Sep 11 19:02 PG_VERSION
drwx------   7 pedz  staff    224 Sep 11 19:06 base
drwx------  60 pedz  staff   1920 Sep 11 19:07 global
drwx------   2 pedz  staff     64 Sep 11 19:02 pg_commit_ts
drwx------   2 pedz  staff     64 Sep 11 19:02 pg_dynshmem
-rw-------   1 pedz  staff   4821 Sep 11 19:02 pg_hba.conf
-rw-------   1 pedz  staff   1636 Sep 11 19:02 pg_ident.conf
drwx------   5 pedz  staff    160 Sep 11 19:11 pg_logical
drwx------   4 pedz  staff    128 Sep 11 19:02 pg_multixact
drwx------   2 pedz  staff     64 Sep 11 19:02 pg_notify
drwx------   2 pedz  staff     64 Sep 11 19:02 pg_replslot
drwx------   2 pedz  staff     64 Sep 11 19:02 pg_serial
drwx------   2 pedz  staff     64 Sep 11 19:02 pg_snapshots
drwx------   2 pedz  staff     64 Sep 11 19:02 pg_stat
drwx------   6 pedz  staff    192 Sep 11 19:16 pg_stat_tmp
drwx------   3 pedz  staff     96 Sep 11 19:02 pg_subtrans
drwx------   2 pedz  staff     64 Sep 11 19:02 pg_tblspc
drwx------   2 pedz  staff     64 Sep 11 19:02 pg_twophase
drwx------   4 pedz  staff    128 Sep 11 19:02 pg_wal
drwx------   3 pedz  staff     96 Sep 11 19:02 pg_xact
-rw-------   1 pedz  staff     88 Sep 11 19:02 postgresql.auto.conf
-rw-------   1 pedz  staff  28835 Sep 11 19:02 postgresql.conf
-rw-------   1 pedz  staff     36 Sep 11 19:02 postmaster.opts
-rw-------   1 pedz  staff    101 Sep 11 19:02 postmaster.pid

I may not be doing this correctly but on the VM, this what I see:

/var/lib/docker/overlay2/c4da07c5fbc5f6dee6d1cab4095f749404316abfe00ae09eee36f4cc8d097ace # ls -ld merged/var/lib/postgresql/data
drwxrwxrwx    2 999      ping          4096 Aug 23 16:48 merged/var/lib/postgresql/data

and data appears to the VM to be just an empty directory but it is owned by 999:999.

You asked a question about who creates the directory and the only thing I can tell you is that it isn’t me. It may be that /var/lib/postgresql/data is created when the image is built. On the laptop, tmp is created before hand but tmp/data was not.

I just noticed something else.

docker compose exec db bash
 ...
root@486fdcdc49fb:/var/lib/postgresql# find . -user 999 -print | wc
    988     988   29252
root@486fdcdc49fb:/var/lib/postgresql# find . ! -user 999 -print | wc
    589     589   18130

Now, roughly half the files are owned by root and half by postgres. BUT, the file that the message is complaining about consistently is pg_stat_tmp/global.stat. So, still in the database container, I go back to the root directory, su as postgres and see if I can go through the path:

root@486fdcdc49fb:/var/lib/postgresql# cd /
root@486fdcdc49fb:/# su postgres
postgres@486fdcdc49fb:/$ id
uid=999(postgres) gid=999(postgres) groups=999(postgres),101(ssl-cert)
postgres@486fdcdc49fb:/$ cd /var/lib/postgresql/data/pgdata/pg_stat_tmp
postgres@486fdcdc49fb:~/data/pgdata/pg_stat_tmp$ cat global.stat | wc
      1       4    1592
postgres@486fdcdc49fb:~/data/pgdata/pg_stat_tmp$ ls -l global.stat
-rw------- 1 postgres postgres 1592 Sep 12 00:28 global.stat
postgres@486fdcdc49fb:~/data/pgdata/pg_stat_tmp$ wc global.stat
   1    4 1592 global.stat
postgres@486fdcdc49fb:~/data/pgdata/pg_stat_tmp$ 

Yes. The entire path is readable to postgres (and it says “w” so I assume it is writable too.

The Postgres guys say it doesn’t make much difference. One option, if a bind mount is desired, is to change the stats temp directory in postgres.conf to be a Docker tmp file.

The tests today I followed the Docker sample exactly so if you want and have time, you can probably recreate it. Just wait 10 to 30 minutes after the web is up and running and the database will start complaining.

Here are the steps in a script:

docker compose run --no-deps web rails new . --force --database=postgresql
docker compose build
cat > config/database.yml <<EOF
default: &default
  adapter: postgresql
  encoding: unicode
  host: db
  username: postgres
  password: password
  pool: 5

development:
  <<: *default
  database: myapp_development


test:
  <<: *default
  database: myapp_test
EOF
docker compose up

Trying. In the meantime: Have you enabled the Virtualization framework in the Experimental features? If you have, do you have also “VirtioFS” enabled?

update:

  • I tried without Virtualization framework, everything seemed working. Docker Desktop could change the permissions inside the container without changing it locally.
  • I enabled Virtualization Framework and virtioFS, ran docker compose down, recreated everything, but postgresql could not change the permissions
  • Ran docker compose down again, moved the db folder to db/pgdata, used PGDATA variable, everything worked again, but strangely, files in the pgdata folder were owned by root.
  • Tried to check pg_stat_tmp, then base, and I saw
root@e48e188acbfd:/var/lib/postgresql/data/pgdata# ls -lahs
total 60K
   0 drwx------ 26 root root  832 Sep 12 17:10 .
   0 drwxr-xr-x  3 root root   96 Sep 12 17:06 ..
   0 drwx------  7 root root  224 Sep 12 17:12 base
   0 drwx------ 60 root root 1.9K Sep 12 17:12 global
 root@e48e188acbfd:/var/lib/postgresql/data/pgdata# ls -lahs base/
total 0
0 drwx------   7 postgres postgres  224 Sep 12 17:12 .
0 drwx------  26 root     root      832 Sep 12 17:10 ..
0 drwx------ 296 root     root     9.3K Sep 12 17:02 1
0 drwx------ 296 root     root     9.3K Sep 12 17:02 13756
0 drwx------ 297 root     root     9.3K Sep 12 17:12 13757
0 drwx------ 297 root     root     9.3K Sep 12 17:12 16384
0 drwx------ 296 root     root     9.3K Sep 12 17:12 16385
root@e48e188acbfd:/var/lib/postgresql/data/pgdata# ls -lahs base/
root@e48e188acbfd:/var/lib/postgresql/data/pgdata# ls -lahs base/
total 0
0 drwx------   7 root root  224 Sep 12 17:12 .
0 drwx------  26 root root  832 Sep 12 17:10 ..
0 drwx------ 296 root root 9.3K Sep 12 17:02 1
0 drwx------ 296 root root 9.3K Sep 12 17:02 13756
0 drwx------ 297 root root 9.3K Sep 12 17:12 13757
0 drwx------ 297 root root 9.3K Sep 12 17:12 16384
0 drwx------ 296 root root 9.3K Sep 12 17:12 16385

So “base” was sometimes owned by “root”, then “postgres”, then root again (not quoted)

I don’t have more time to test now, but I don’t understand either wh the files are owned by root and if they are owned by root, why there is no error message. When you install Docker CE on Linux, the owners and permissions on the host are the same as in the container (not if you use rootless containers), so this behavior might be caused by the experimental virtualization framework and virtioFS. I always enable these options.

I assume I do not. How do I check to be sure? I basically downloaded Docker Desktop for macOS and started playing so I should have 100% default settings.

Update: Everything in Preferences => Experimental features is off:

Then I am really out of ideas. I hate to say that “It works on my machine” but this is the case. Can you try with the first and second features enabled? Just to see if any of the settings can help or change the error message.

Note: If you try to enable those features, you may need to “Quit Docker Desktop” if “Apply and Restart” does not work. Using the latest version I always had to do that.

Thank you for trying. I will try your suggestions tomorrow.

Did you let the application sit idle for a half hour or so. The errors are extremely intermittent and random sometimes taking quite some time to pop out.

Good news?!?!?

As you suggested, I checked the first two entries of the experimental features. Curiously, the button that says “Apply and Restart” never completed. I eventually hit cancel and then stopped and restarted Docker Desktop by hand. When it came back up, the two boxes had been checked. I stopped and restarted it one more time just for good measure.

I then pruned all my images and containers and redid the experiment following the exact same steps. According to the log messages, that was at 2022-09-14 01:31:08.286 UTC. It is now Sep 14 12:33:04 UTC and I do not have any of the old messages. I do have one weird message:

from-docker-run-db-1   | 2022-09-14 04:10:41.966 UTC [66] LOG:  stats_timestamp 2022-09-14 04:10:41.975373+00 is later than collector's time 2022-09-14 04:10:41.966405+00 for database 0

It is curious that it is a time stamp complaint.

So… you probably know better what this implies. I don’t know if this is a “fix” or what.

When you say you could not recreate it, did you have those two experimental features enabled?

No. I thought you wrote 10 to 30 seconds and now I see it was 10 to 30 minutes.

That is “just” a postgres warning. It can happen when your system clock sync is wrong.

Why it happens in this case, I don’t know.