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
