DOCKER container with postgres, WARNING: could not open statistics file "pg_stat_tmp/global.stat": Operation not permitted

Question

I have a DOCKER container built from a few different images using a .yml, Dockerfile(s), etc. Everything builds and runs fine so far, except for this one issue that I am seeing mentioned in the title:

index-db_1   | 2021-02-22 23:18:33.388 UTC [31] WARNING:  could not open statistics file "pg_stat_tmp/global.stat": Operation not permitted

That Database Index is mapped to a Folder on the host in the root of the Docker Package, and everything else seems to work fine as far as the database is concerned. I am using a Mac, but if I list permission from CLI for the DB folder I get:

-rw-------@  1 sscotti  staff      3 Feb 22 11:01 PG_VERSION
drwx------@  6 sscotti  staff    192 Feb 22 11:54 base
drwx------@ 60 sscotti  staff   1920 Feb 22 16:00 global
drwx------@  2 sscotti  staff     64 Feb 22 11:01 pg_commit_ts
drwx------@  2 sscotti  staff     64 Feb 22 11:01 pg_dynshmem
-rw-------@  1 sscotti  staff   4782 Feb 22 11:02 pg_hba.conf
-rw-------@  1 sscotti  staff   1636 Feb 22 11:01 pg_ident.conf
drwx------@  5 sscotti  staff    160 Feb 22 17:46 pg_logical
drwx------@  4 sscotti  staff    128 Feb 22 11:01 pg_multixact
drwx------@  2 sscotti  staff     64 Feb 22 11:01 pg_notify
drwx------@  2 sscotti  staff     64 Feb 22 11:01 pg_replslot
drwx------@  2 sscotti  staff     64 Feb 22 11:01 pg_serial
drwx------@  2 sscotti  staff     64 Feb 22 11:01 pg_snapshots
drwx------@  2 sscotti  staff     64 Feb 22 16:00 pg_stat
drwx------@  5 sscotti  staff    160 Feb 22 17:50 pg_stat_tmp
drwx------@  3 sscotti  staff     96 Feb 22 11:01 pg_subtrans
drwx------@  2 sscotti  staff     64 Feb 22 11:01 pg_tblspc
drwx------@  2 sscotti  staff     64 Feb 22 11:01 pg_twophase
drwx------@  4 sscotti  staff    128 Feb 22 11:01 pg_wal
drwx------@  3 sscotti  staff     96 Feb 22 11:01 pg_xact
-rw-------@  1 sscotti  staff     88 Feb 22 11:01 postgresql.auto.conf
-rw-------@  1 sscotti  staff  28073 Feb 22 11:01 postgresql.conf
-rw-------@  1 sscotti  staff     36 Feb 22 16:00 postmaster.opts
-rw-------   1 sscotti  staff     94 Feb 22 16:00 postmaster.pid

pg_stat folder is actually empty.

and pg_stat_temp has:

-rw-------  1 sscotti  staff   1952 Feb 22 17:54 db_0.stat
-rw-------  1 sscotti  staff  20360 Feb 22 17:54 db_13395.stat
-rw-------  1 sscotti  staff   1151 Feb 22 17:54 global.stat

The .yml file has this:

  index-db:
      image: postgres
      restart: unless-stopped
      volumes:
          - ./OrthancIndex:/var/lib/postgresql/data

Is that something that can just be ignored given that it is a Docker container.

Adding a comment about the same setup on UBUNTU.

Database Folder:

drwx------ 19 systemd-coredump root 4096 Jun 30 13:12 OrthancIndex

Database:

drwx------ 6 systemd-coredump systemd-coredump  4096 Jun 11 13:00 base
drwx------ 2 systemd-coredump systemd-coredump  4096 Jun 30 13:12 global
drwx------ 2 systemd-coredump systemd-coredump  4096 Mar 12 16:12 pg_commit_ts
drwx------ 2 systemd-coredump systemd-coredump  4096 Mar 12 16:12 pg_dynshmem
-rw------- 1 systemd-coredump systemd-coredump  4782 Mar 12 16:12 pg_hba.conf
-rw------- 1 systemd-coredump systemd-coredump  1636 Mar 12 16:12 pg_ident.conf
drwx------ 4 systemd-coredump systemd-coredump  4096 Jul  1 13:27 pg_logical
drwx------ 4 systemd-coredump systemd-coredump  4096 Mar 12 16:12 pg_multixact
drwx------ 2 systemd-coredump systemd-coredump  4096 Mar 12 16:12 pg_notify
drwx------ 2 systemd-coredump systemd-coredump  4096 Mar 12 16:12 pg_replslot
drwx------ 2 systemd-coredump systemd-coredump  4096 Mar 12 16:12 pg_serial
drwx------ 2 systemd-coredump systemd-coredump  4096 Mar 12 16:12 pg_snapshots
drwx------ 2 systemd-coredump systemd-coredump  4096 Jun 30 13:12 pg_stat
drwx------ 2 systemd-coredump systemd-coredump  4096 Jul  1 13:29 pg_stat_tmp
drwx------ 2 systemd-coredump systemd-coredump  4096 Jun 24 21:04 pg_subtrans
drwx------ 2 systemd-coredump systemd-coredump  4096 Mar 12 16:12 pg_tblspc
drwx------ 2 systemd-coredump systemd-coredump  4096 Mar 12 16:12 pg_twophase
-rw------- 1 systemd-coredump systemd-coredump     3 Mar 12 16:12 PG_VERSION
drwx------ 3 systemd-coredump systemd-coredump  4096 Jul  1 12:37 pg_wal
drwx------ 2 systemd-coredump systemd-coredump  4096 Mar 12 16:12 pg_xact
-rw------- 1 systemd-coredump systemd-coredump    88 Mar 12 16:12 postgresql.auto.conf
-rw------- 1 systemd-coredump systemd-coredump 28073 Mar 12 16:12 postgresql.conf
-rw------- 1 systemd-coredump systemd-coredump    36 Jun 30 13:12 postmaster.opts
-rw------- 1 systemd-coredump systemd-coredump    94 Jun 30 13:12 postmaster.pid

pg_stat_temp

-rw------- 1 systemd-coredump systemd-coredump  2660 Jul  1 13:30 db_0.stat
-rw------- 1 systemd-coredump systemd-coredump 31157 Jul  1 13:30 db_13395.stat
-rw------- 1 systemd-coredump systemd-coredump  1151 Jul  1 13:30 global.stat

I actually get the same error on UBUNTU:

postgres_index-db_1   | 2021-07-01 18:06:45.140 UTC [266] WARNING:  could not open statistics file "pg_stat_tmp/global.stat": Operation not permitted
postgres_index-db_1   | 2021-07-01 18:13:45.583 UTC [273] WARNING:  could not open statistics file "pg_stat_tmp/global.stat": Operation not permitted
postgres_index-db2_1  | 2021-07-01 18:19:43.716 UTC [282] WARNING:  could not open statistics file "pg_stat_tmp/global.stat": Operation not permitted
postgres_index-db2_1  | 2021-07-01 18:21:43.749 UTC [284] WARNING:  could not open statistics file "pg_stat_tmp/global.stat": Operation not permitted

Although here the user and group are systemd-coredump.

Einstein

I'm having the exact same issue, did not find any solution yet – Lacylad

I am having the exact same issue. I have the files mounted to my local file system and get this error. One point of interest is that when I use a docker volume, the message does not appear. This leads me to think that it's some mismatch between the OSX permissions and docker. – Enjambement

I agree with that. Catalina and Big Sur implemented that new stuff with permissions. Good thing is that it doesn't really seem to affect performance. I have not really tried to fix it at this point. – Einstein

@Enjambement How do you make that change in the .yaml file. Mounting as a docker volume instead of binding to a folder on the host ? I think that makes a difference in term of performance and permissions on OS X, but may be much difference with a LINUX host. I do want to easily access the data from the host still. – Einstein

@Enjambement Also want it to be persistent. – Einstein

Same problem for me. This is a very interesting response to a similar question. https://mcmap.net/q/391106/-postgresql-statistics-issue-could-not-rename-temporary-statistics-file – Kahaleel

Can you access those files while the container is running? docker-compose exec index-db ls -l /var/lib/postgresql/data/pg_stat_tmp or head -c 5 /var/lib/postgresql/data/pg_stat_tmp/global.stat – Reeducate

It's odd that is happens in both – Reeducate

docker exec -it orthanc_docker_ris_postgres_index-db_1 ls -l /var/lib/postgresql/data/pg_stat_tmp gives: -rw------- 1 root root 1421 Jul 19 16:07 db_0.stat -rw------- 1 root root 10271 Jul 19 16:07 db_13395.stat -rw------- 1 postgres postgres 1151 Jul 19 16:07 global.stat, maybe has something to do with postgres vs. root permissions there ? – Einstein

Bummer that you cannot use line breaks in comments ? – Einstein

Answer 1

Writing this down as much for my future self as to answer the question: looking over at the documentation and taking @Fide's solution above, the following seems to work on MacOSX Monterey running a PostGIS image (which is just built on top of the Postgres images)...

Note that this is taken from a shell script to launch a Postgres+PostGIS image with a persistent local store on the host system:

WORK_DIR="$HOME/Documents/git/project"
DOCKER_NM="postgis"
DOCKER_IMG="postgis/postgis"
POSTGRES_PWD="XXX"
PORT_NO="5432"
docker run --name $DOCKER_NM \
    -e POSTGRES_PASSWORD=$POSTGRES_PWD \
    -e PGDATA=/var/lib/postgresql/data/pgdata \
    -v "$WORK_DIR/data/postgres":/var/lib/postgresql/data \
    -p "$PORT_NO:5432" -d $DOCKER_IMG \
    postgres -c stats_temp_directory=/tmp

If you were running from the Terminal directly (i.e. not via zsh/bash) then you'd need to export each of the parameters instead of just specifying them.

With this the global.stat error seems to have gone away... though I should note that I've only just figured this out and have been running the container for less than 60 minutes. The alternative would be to follow the instructions on that page for creating and using a persistent custom conf file (extending @SScotti's comment above) as mounted from the local FS:

-v "$PWD/my-postgres.conf":/etc/postgresql/postgresql.conf

Tauten

@Absolutely stats_timestamp 2022-03-28 12:44:10.89023-05 is later than collector's time 2022-03-28 12:44:10.865062-05 for database 13445 I tried something like that recently and periodically get this error. Not sure that will be a problem. – Einstein

Answer 2

Simple and dumb

I've tried another way: leaving the temporary file in container, where you need not deal with user permissions - and that worked. I've changed with inline command the Postgresql config in docker-compose.yml:

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

This is quick and simple solution, but it does not set multiple postgres config parameters. Through the time I found another solution.

Advanced (added on 22/01/14 09:00PM CET)

The advanced solution consists in these steps:

1. Prepare your desired postgresql.conf - here you can set whichever postgres server parameters you need. The necessary one for solving problem in this thread is:

stats_temp_directory = '/tmp/stat_temporary'

2. Create bash file, that you will start using docker-entrypoint-initdb.d in docker-compose.yml with which you will copy your configuration to postgres config directory in docker and create your temporary folder.

If you setup the docker-compose.yml this way:

postgres-db:
    image: postgres
    container_name: 'dbcontainer'
    volumes:
      # point to your postgres init scripts (folder or file)
      - $PWD/db_init:/docker-entrypoint-initdb.d
      # if you need persistent data, point data to local folder
      - $PWD/database:/var/lib/postgresql/data
      # if you want some data restore, point to your backup
      - $PWD/db_backup:/db_backup
      # point docker to directory with your config
      - $PWD/db_config:/db_config

Then database will init with scripts in db_init directory. (Or you can point directly to one sql or bash file. If you point to directory, init script will run all files in alphabetical order. Your file with init bash script, that you will place in your db_init folder can look like this and will be started inside docker:

echo starting script...
# create temporary directory for postgres in docker
mkdir /tmp/stat_temporary 

# copy your postgresql.conf to postgresql config location in docker
cp /db_config/postgresql.conf var/lib/postgresql/data/postgresql.conf

# alternatively you can run any pg script you need, here I restore backup to new database
pg_restore -U postgres -v -Fc -d analysis /db_backup/analysis_backup.bak
echo finished

After this script is run, the database service on docker is restarted and loads with new parameters you set up.

Absolutely

See my last comment also. – Einstein

The advanced solution was very simple.... Just create the bash file in $PWD dir, add the volumes entry, restart container - no more warnings! Thank you! – Kurr

Answer 3

Not an answer to the original question, but hence the lack of reputation points I can't add my two cents as a comment. So here is a working docker-compose.yml file using the tmpfs solution as an answer (for @SScotti and anyone else, who wants to try this without cluttering the default command or config):

Environment file (.env):

POSTGRES_VERSION=13.6
DATABASE_NAME=dbname
DATABASE_USER=dbuser
DATABASE_PASS=dbpass

Compose file (docker-compose.yml):

version: '3.6'
services:
  postgres:
    image: postgres:${POSTGRES_VERSION}-alpine
    restart: always
    container_name: postgres
    environment:
      POSTGRES_DB: ${DATABASE_NAME}
      POSTGRES_USER: ${DATABASE_USER}
      POSTGRES_PASSWORD: ${DATABASE_PASS}
    volumes:
      - /etc/timezone:/etc/timezone:ro
      - .volumes/pgdata:/var/lib/postgresql/data
      - type: tmpfs
        target: /var/lib/postgresql/data/pg_stat_tmp
        tmpfs:
          # 256 Mb - beware the default, it's infinity(!)
          size: 268435456

Edit: Note on first run
On first run, postgres tries to initialise the database and fails, because of non empty data directory (the existing/mounted tmpfs pg_stats_tmp in the data directory).

To avoid that, you have to run the service at first without the tmpfs section in the compose file, and when postgres finished initialising take the service down and enable the tmpfs section in the compose file before bringen up the service again.

Talie

I might try that when I have time. Seems to be a popular question and solutions. – Einstein

Trying that, seems like it is working OK. BTW, if I am using Docker Desktop and/or the Docker CLI, what is the best way to gracefully shut down postgres and mysql ? Sometimes they seem to shut down just fine, and then other times the SIGKILL fails and I have to restart docker. – Einstein

@Einstein - Best way to stop a running container is docker stop ${ID_OR_NAME}, see: docker stop. Well craftet Images will react on SIGTERM und shuts down properly. With docker-compose you have to stop the service with docker-compose stop ${SERVICE_NAME}, omitting the service name, will stop the complete stack (aka all services) defined in the compose file. Replacing stop with downwill also remove containers and networks. – Talie

Answer 4

Mounting with a local volume requires user mapping within the postgres container. There is a section in the https://hub.docker.com/_/postgres README titled Arbitrary --user Notes which describes solutions to the problem.

One recommendation listed is to use a docker volume, initialize the contents of volume mounted, shutdown the container image, run a recursive ownership change and then restart the container normally.

$ docker volume create pgdata
$ docker run -it --rm -v pgdata:/var/lib/postgresql/data -e POSTGRES_PASSWORD=mysecretpassword postgres
The files belonging to this database system will be owned by user "postgres".
...
( once it's finished initializing successfully and is waiting for connections, stop it )
$ docker run -it --rm -v pgdata:/var/lib/postgresql/data bash chown -R 1000:1000 /var/lib/postgresql/data
$ docker run -it --rm --user 1000:1000 -v pgdata:/var/lib/postgresql/data postgres

Brotherson

Have not tried this, but it might be worth setting: stats_temp_directory to a directory in the Docker Container. See: postgresql.org/docs/10/runtime-config-statistics.html. Not sure exactly how to do that from Docker. The default is: #stats_temp_directory = 'pg_stat_tmp' – Einstein

I tried that with one of my existing containers by just shutting down, editing postgresql.conf to have: stats_temp_directory = '/tmp', and then restarting. Might actually fix the problem for now. If that works, just probably need to figure out how to configure that from Docker on restart or initialization. – Einstein

Answer 5

I ran into this same problem - Looked into it in detail and finally found a simple solution.

First some basic background - your pg_stat_temp_directory location is a subdirectory under your data_directory.

show data_directory ;

If it returns the default value for Unix based systems like

/var/lib/postgresql/data

And your command to show stats_temp

show stats_temp_directory ;

Also returns the default value

pg_stat_tmp

Then your full path to stats_temp_directory is /var/lib/postgresql/data/pg_stat_tmp

I was using bind mapping to map the PG container data files to my host so that I can keep the data even after the container is stopped.

docker run -d --name gutlo-postgres -p 5434:5432 -v /data/postgres-docker/14:/var/lib/postgresql/data -e POSTGRES_PASSWORD=<PASSWORD>  postgres:latest

Now everytime I start the container, it automatically creates files under stats_temp directory and once that it has a problem with is called global.stat

ls -la global.stat 

-rw------- 1 ssengupta admin 3648 May 13 16:24 global.stat

 ls -la . | head -2 | tail -1

drwxr-xr-x@ 14 ssengupta admin 448 May 13 16:26 .

• Now you see, where is the problem. The container is able to create the file global.stat ( its directory file "." has open rwx permissions. But the file itself is created without open permission hence my container cannot access its own created file.

From docker manual, there is a concept of temp mount. So simply create a temp mount point for the stats_temp directory and the docker will use tempfs / RAM - that eliminates permission problem.

--mount type=tmpfs,destination=/var/lib/postgresql/data/pg_stat_tmp

In addition it would improve performance too although you may not detect it.

Therm

Might try that, although some of the other solutions 'work'. How would that work in a docker-compose file, using tmpfs: /var/lib/postgresql/data/pg_stat_tmp ? – Einstein

Answer 6

My attempts...

Attach to the db container:

docker compose run --no-deps -it db bash

and do ls on the data directory:

root@dfc6399e2981:/var/lib/postgresql/data# ls -l
total 60
drwx------  7 root     root       224 Sep  8 23:38 base
drwx------ 60 root     root      1920 Sep  8 23:38 global
drwx------  2 postgres postgres    64 Sep  8 23:35 pg_commit_ts
drwx------  2 postgres postgres    64 Sep  8 23:35 pg_dynshmem
-rw-------  1 postgres postgres  4821 Sep  8 23:35 pg_hba.conf
-rw-------  1 postgres postgres  1636 Sep  8 23:35 pg_ident.conf
drwx------  5 root     root       160 Sep  8 23:43 pg_logical
drwx------  4 postgres postgres   128 Sep  8 23:35 pg_multixact
drwx------  2 postgres postgres    64 Sep  8 23:35 pg_notify
drwx------  2 postgres postgres    64 Sep  8 23:35 pg_replslot
drwx------  2 postgres postgres    64 Sep  8 23:35 pg_serial
drwx------  2 postgres postgres    64 Sep  8 23:35 pg_snapshots
drwx------  2 root     root        64 Sep  8 23:35 pg_stat
drwx------  6 root     root       192 Sep  9 00:20 pg_stat_tmp
drwx------  3 postgres postgres    96 Sep  8 23:35 pg_subtrans
drwx------  2 postgres postgres    64 Sep  8 23:35 pg_tblspc
drwx------  2 postgres postgres    64 Sep  8 23:35 pg_twophase
-rw-------  1 postgres postgres     3 Sep  8 23:35 PG_VERSION
drwx------  4 postgres postgres   128 Sep  8 23:35 pg_wal
drwx------  3 postgres postgres    96 Sep  8 23:35 pg_xact
-rw-------  1 postgres postgres    88 Sep  8 23:35 postgresql.auto.conf
-rw-------  1 postgres postgres 28835 Sep  8 23:35 postgresql.conf
-rw-------  1 root     root        36 Sep  8 23:35 postmaster.opts
-rw-------  1 postgres postgres    94 Sep  8 23:35 postmaster.pid

Obviously, note that some things are owned by root. Also, compare and contrast, from outside the container I see this:

% ls -l db/data
total 120
-rw-------@  1 pedz  staff      3 Sep  8 18:35 PG_VERSION
drwx------@  7 pedz  staff    224 Sep  8 18:38 base
drwx------@ 60 pedz  staff   1920 Sep  8 18:38 global
drwx------@  2 pedz  staff     64 Sep  8 18:35 pg_commit_ts
drwx------@  2 pedz  staff     64 Sep  8 18:35 pg_dynshmem
-rw-------@  1 pedz  staff   4821 Sep  8 18:35 pg_hba.conf
-rw-------@  1 pedz  staff   1636 Sep  8 18:35 pg_ident.conf
drwx------@  5 pedz  staff    160 Sep  8 18:43 pg_logical
drwx------@  4 pedz  staff    128 Sep  8 18:35 pg_multixact
drwx------@  2 pedz  staff     64 Sep  8 18:35 pg_notify
drwx------@  2 pedz  staff     64 Sep  8 18:35 pg_replslot
drwx------@  2 pedz  staff     64 Sep  8 18:35 pg_serial
drwx------@  2 pedz  staff     64 Sep  8 18:35 pg_snapshots
drwx------@  2 pedz  staff     64 Sep  8 18:35 pg_stat
drwx------@  6 pedz  staff    192 Sep  8 19:27 pg_stat_tmp
drwx------@  3 pedz  staff     96 Sep  8 18:35 pg_subtrans
drwx------@  2 pedz  staff     64 Sep  8 18:35 pg_tblspc
drwx------@  2 pedz  staff     64 Sep  8 18:35 pg_twophase
drwx------@  4 pedz  staff    128 Sep  8 18:35 pg_wal
drwx------@  3 pedz  staff     96 Sep  8 18:35 pg_xact
-rw-------@  1 pedz  staff     88 Sep  8 18:35 postgresql.auto.conf
-rw-------@  1 pedz  staff  28835 Sep  8 18:35 postgresql.conf
-rw-------@  1 pedz  staff     36 Sep  8 18:35 postmaster.opts
-rw-------@  1 pedz  staff     94 Sep  8 18:35 postmaster.pid

So, while in the db container I did:

chown -R postgres:postgres /var/lib/postgresql/data

I can now see updates into pg_stat_tmp from outside of the container and it has been up for 30 minutes with no Warning messages so I think its "fixed" although I don't know enough about Docker's images to know for sure that the issue won't come back later. Perhaps someone with more Docker knowledge can chime in.

Update: this does NOT fix the issue. Rather than delete this answer I think I will leave it here. It is odd that some files are owned by root. And even after I did chown -R there are still some files owned by root:

root@ec24992481d1:/var/lib/postgresql# find . -user 0 -exec ls -ld {} +
-rw------- 1 root root    8 Sep  9 00:34 ./data/pg_logical/replorigin_checkpoint
-rw------- 1 root root 2225 Sep  9 01:12 ./data/pg_stat_tmp/db_0.stat
-rw------- 1 root root 6665 Sep  9 01:12 ./data/pg_stat_tmp/db_13757.stat
-rw------- 1 root root 7035 Sep  9 01:12 ./data/pg_stat_tmp/db_16384.stat
-rw------- 1 root root   94 Sep  9 00:29 ./data/postmaster.pid
root@ec24992481d1:/var/lib/postgresql# 

This might be a clue for others more savvy with Docker.

Donniedonnish