Обсуждение: WARNING: could not open statistics file "pg_stat_tmp/global.stat": Operation not permitted
Вложения
Perry Smith <pedz@easesoftware.com> writes: > From within the container, files which I assume are created by > PostgreSQL are ending up being owned by root rather than Postgres. If it looks that way from *inside* the container, that's not good --- wouldn't that prevent Postgres from reading the files? > 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? pg_stat_tmp exists specifically because it holds only temporary files, cf https://www.postgresql.org/docs/devel/storage-file-layout.html It's explicitly cleared out during server start. The only reason to put it outside the data directory is to make it *less* persistent than the rest of PG's files, say by putting it on a RAM disk. You sound like you've set it up to be *more* persistent (ie outside the container not inside), which surely is exactly backwards. regards, tom lane
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
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
> On Sep 8, 2022, at 10:43 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Perry Smith <pedz@easesoftware.com> writes: >> From within the container, files which I assume are created by >> PostgreSQL are ending up being owned by root rather than Postgres. > > If it looks that way from *inside* the container, that's not good > --- wouldn't that prevent Postgres from reading the files? > >> 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? > > pg_stat_tmp exists specifically because it holds only temporary files, > cf > > https://www.postgresql.org/docs/devel/storage-file-layout.html > > It's explicitly cleared out during server start. > > The only reason to put it outside the data directory is to make it > *less* persistent than the rest of PG's files, say by putting it > on a RAM disk. You sound like you've set it up to be *more* > persistent (ie outside the container not inside), which surely is > exactly backwards. The data directory is outside so it is persistent. The pg_stat_tmp is inside the data directory. This is how whoever buildsthe “official” Postgres images set things up. If the right solution is to not make it part of the data directory, I can change that. One of the solutions explains howwhich solves the Postgres issue but still leaves open the question of how and why do some of the files get owned by root(which probably is a Docker or a Linux issue).
The data directory is outside 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.
-- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com