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.

Answer 1

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.

Answer 2

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