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
