Re: WARNING: could not open statistics file "pg_stat_tmp/global.stat": Operation not permitted

On 9/8/22 23:05, Perry Smith wrote:
> This is an issue when PostgreSQL is running inside a container.  In my
> quest to find an answer, I’ve discovered three instances that it has
> come up and various people have talked about fixes but no one seemed
> to notice what I found.
>
> I opened an issue here[1].
>
> From within the container, files which I assume are created by
> PostgreSQL are ending up being owned by root rather than Postgres.
>  Thus, to me, it appears to NOT be an issue of mapping internal UIDs
> and GIDs to external IDs since there should not be anything outside
> the PostgreSQL container creating files inside Postgres’ data directory.
>
> The reason I’m sending this note to the general list is to ask how bad
> is this error?  Some “solutions” are to make the pg_stat_tmp directory
> internal to the image and that somehow resolves the issue but I don’t
> think anyone really understands why and things like that bother me.
>  But I’m also curious if that appears to be a viable solution.  The
> result will be that when the Postgres is stopped and the container
> exited, the next time Postgres starts back up, the pg_stat_tmp
> directory will be gone.  Is that ok?  Does Postgres store anything
> that that needs to survive a restart?
>
> Thank you for your help,
> Perry
> [1] https://github.com/docker-library/docs/issues/2188#issue-1367170047
>
Hi Perry,

You probably need to fix your Dockerfile because your postgres seems to
be running as root. Here is my Dockerfile:

FROM oraclelinux:8
LABEL Description="This install PostgreSQL 14 on top of Oracle Linux 8"
LABEL maintainer="Mladen Gogala"
RUN dnf -y update
COPY RPMS/pgdg-redhat-repo-latest.noarch.rpm /tmp
COPY RPMS/pg_hint_plan14-1.4-1.el8.x86_64.rpm /tmp/
COPY RPMS/pg_hint_plan14-llvmjit-1.4-1.el8.x86_64.rpm /tmp/
# Install PostgreSQL software
RUN dnf localinstall -y /tmp/pgdg-redhat-repo-latest.noarch.rpm
RUN dnf -qy module disable postgresql
RUN dnf -y install postgresql14
RUN dnf -y install postgresql14-libs
RUN dnf -y install postgresql14-server
RUN dnf -y install postgresql14-llvmjit
RUN dnf -y install postgresql14-contrib
# Install pg_hint_plan 1.4
RUN cd /tmp;dnf -y localinstall `ls *.rpm`
# Cleanup
RUN rm -f /tmp/*.rpm
RUN dnf clean all

# Copy postgresql.auto.conf (modified parameters) and run initdb
USER postgres
ARG PGPASSWD="qwerty"
ENV PGDATA=/var/lib/pgsql/14/data
ENV PATH=/usr/pgsql-14/bin:/usr/bin:/usr/local/bin
RUN echo "$PGPASSWD">/var/lib/pgsql/14/pgcluster.pwd
RUN initdb -A password --pwfile=/var/lib/pgsql/14/pgcluster.pwd
RUN echo "host all all 0.0.0.0/0 md5">>$PGDATA/pg_hba.conf
COPY --chown=postgres:postgres RPMS/postgresql.auto.conf
/var/lib/pgsql/14/data
# Finish
EXPOSE 5432/tcp
ENTRYPOINT ["postgres"]

Please note that I switch users in the file and that owners are
switched. I don't have any issues with the container built this way. I
have another container which is built by untaring backup of my sample
database. I use ADD command to untar it to $PGDATA. Also, my trick with
putting the modified parameters into postgresql.auto.conf goes contrary
to the Postgres recommendations. You may want to avoid doing that. I
haven't pushed the image to the Docker hub because I'm still working on it.

--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com