PI 4 postgres won't start

Hi,

I’m trying to get postgres to work on a PI4. I have added the required ENV variables to my balena fleet which runs on balenaOS 2.85.2+rev3.

Docker Compose:

version: '2'
services:
    postgres:
      build: 
        context: .
        dockerfile: ./Dockerfile
      volumes:
        - database:/var/lib/postgresql/data
      ports:
        - 5432:5432
      restart: unless-stopped
      environment: 
        - POSTGRES_USER=${DB_USER}
        - POSTGRES_PASSWORD=${DB_PASSWORD}
        - POSTGRES_DB=${DB_NAME}
volumes:
  database:

Dockerfile:

FROM balenalib/aarch64-alpine:3.14-build

RUN set -eux; \
    addgroup -g 70 -S postgres; \
    adduser -u 70 -S -D -G postgres -H -h /var/lib/postgresql -s /bin/sh postgres; \
    mkdir -p /var/lib/postgresql; \
    chown -R postgres:postgres /var/lib/postgresql

RUN apk add --update \
    bash \
    postgresql \
    less \
    nano \
    net-tools \
    ifupdown \
    usbutils \
    gnupg \
    raspberrypi \
    raspberrypi-libs \
    raspberrypi-dev \
    && rm -rf /var/cache/apk/*

ENV LANG en_US.utf8

RUN mkdir /docker-entrypoint-initdb.d

ENV PG_MAJOR 14
ENV PG_VERSION 14.0
ENV PG_SHA256 ee2ad79126a7375e9102c4db77c4acae6ae6ffe3e082403b88826d96d927a122

RUN set -eux; \
    \
    wget -O postgresql.tar.bz2 "https://ftp.postgresql.org/pub/source/v$PG_VERSION/postgresql-$PG_VERSION.tar.bz2"; \
    echo "$PG_SHA256 *postgresql.tar.bz2" | sha256sum -c -; \
    mkdir -p /usr/src/postgresql; \
    tar \
    --extract \
    --file postgresql.tar.bz2 \
    --directory /usr/src/postgresql \
    --strip-components 1 \
    ; \
    rm postgresql.tar.bz2; \
    \
    apk add --no-cache --virtual .build-deps \
    bison \
    coreutils \
    dpkg-dev dpkg \
    flex \
    gcc \
    #		krb5-dev \
    libc-dev \
    libedit-dev \
    libxml2-dev \
    libxslt-dev \
    linux-headers \
    llvm11-dev clang g++ \
    make \
    #		openldap-dev \
    openssl-dev \
    # configure: error: prove not found
    perl-utils \
    # configure: error: Perl module IPC::Run is required to run TAP tests
    perl-ipc-run \
    #		perl-dev \
    #		python-dev \
    #		python3-dev \
    #		tcl-dev \
    util-linux-dev \
    zlib-dev \
    # https://www.postgresql.org/docs/10/static/release-10.html#id-1.11.6.9.5.13
    icu-dev \
    # https://www.postgresql.org/docs/14/release-14.html#id-1.11.6.5.5.3.7
    lz4-dev \
    ; \
    \
    cd /usr/src/postgresql; \
    # update "DEFAULT_PGSOCKET_DIR" to "/var/run/postgresql" (matching Debian)
    # see https://anonscm.debian.org/git/pkg-postgresql/postgresql.git/tree/debian/patches/51-default-sockets-in-var.patch?id=8b539fcb3e093a521c095e70bdfa76887217b89f
    awk '$1 == "#define" && $2 == "DEFAULT_PGSOCKET_DIR" && $3 == "\"/tmp\"" { $3 = "\"/var/run/postgresql\""; print; next } { print }' src/include/pg_config_manual.h > src/include/pg_config_manual.h.new; \
    grep '/var/run/postgresql' src/include/pg_config_manual.h.new; \
    mv src/include/pg_config_manual.h.new src/include/pg_config_manual.h; \
    gnuArch="$(dpkg-architecture --query DEB_BUILD_GNU_TYPE)"; \
    # explicitly update autoconf config.guess and config.sub so they support more arches/libcs
    wget -O config/config.guess 'https://git.savannah.gnu.org/cgit/config.git/plain/config.guess?id=7d3d27baf8107b630586c962c057e22149653deb'; \
    wget -O config/config.sub 'https://git.savannah.gnu.org/cgit/config.git/plain/config.sub?id=7d3d27baf8107b630586c962c057e22149653deb'; \
    # configure options taken from:
    # https://anonscm.debian.org/cgit/pkg-postgresql/postgresql.git/tree/debian/rules?h=9.5
    ./configure \
    --build="$gnuArch" \
    # "/usr/src/postgresql/src/backend/access/common/tupconvert.c:105: undefined reference to `libintl_gettext'"
    #		--enable-nls \
    --enable-integer-datetimes \
    --enable-thread-safety \
    --enable-tap-tests \
    # skip debugging info -- we want tiny size instead
    #		--enable-debug \
    --disable-rpath \
    --with-uuid=e2fs \
    --with-gnu-ld \
    --with-pgport=5432 \
    --with-system-tzdata=/usr/share/zoneinfo \
    --prefix=/usr/local \
    --with-includes=/usr/local/include \
    --with-libraries=/usr/local/lib \
    \
    # these make our image abnormally large (at least 100MB larger), which seems uncouth for an "Alpine" (ie, "small") variant :)
    #		--with-krb5 \
    #		--with-gssapi \
    #		--with-ldap \
    #		--with-tcl \
    #		--with-perl \
    #		--with-python \
    #		--with-pam \
    --with-openssl \
    --with-libxml \
    --with-libxslt \
    --with-icu \
    --with-llvm \
    --with-lz4 \
    ; \
    make -j "$(nproc)" world; \
    make install-world; \
    make -C contrib install; \
    \
    runDeps="$( \
    scanelf --needed --nobanner --format '%n#p' --recursive /usr/local \
    | tr ',' '\n' \
    | sort -u \
    | awk 'system("[ -e /usr/local/lib/" $1 " ]") == 0 { next } { print "so:" $1 }' \
    )"; \
    apk add --no-cache --virtual .postgresql-rundeps \
    $runDeps \
    bash \
    su-exec \
    # tzdata is optional, but only adds around 1Mb to image size and is recommended by Django documentation:
    # https://docs.djangoproject.com/en/1.10/ref/databases/#optimizing-postgresql-s-configuration
    tzdata \
    ; \
    apk del --no-network .build-deps; \
    cd /; \
    rm -rf \
    /usr/src/postgresql \
    /usr/local/share/doc \
    /usr/local/share/man \
    ; \
    \
    postgres --version

# make the sample config easier to munge (and "correct by default")
RUN set -eux; \
    cp -v /usr/local/share/postgresql/postgresql.conf.sample /usr/local/share/postgresql/postgresql.conf.sample.orig; \
    sed -ri "s!^#?(listen_addresses)\s*=\s*\S+.*!\1 = '*'!" /usr/local/share/postgresql/postgresql.conf.sample; \
    grep -F "listen_addresses = '*'" /usr/local/share/postgresql/postgresql.conf.sample

RUN mkdir -p /var/run/postgresql && chown -R postgres:postgres /var/run/postgresql && chmod 2777 /var/run/postgresql

ENV PGDATA /var/lib/postgresql/data
# this 777 will be replaced by 700 at runtime (allows semi-arbitrary "--user" values)
RUN mkdir -p "$PGDATA" && chown -R postgres:postgres "$PGDATA" && chmod 777 "$PGDATA"
VOLUME /var/lib/postgresql/data

COPY docker-entrypoint.sh /usr/local/bin/
ENTRYPOINT ["docker-entrypoint.sh"]

# We set the default STOPSIGNAL to SIGINT, which corresponds to what PostgreSQL
# calls "Fast Shutdown mode" wherein new connections are disallowed and any
# in-progress transactions are aborted, allowing PostgreSQL to stop cleanly and
# flush tables to disk, which is the best compromise available to avoid data
# corruption.
#
# Users who know their applications do not keep open long-lived idle connections
# may way to use a value of SIGTERM instead, which corresponds to "Smart
# Shutdown mode" in which any existing sessions are allowed to finish and the
# server stops when all sessions are terminated.
#
# See https://www.postgresql.org/docs/12/server-shutdown.html for more details
# about available PostgreSQL server shutdown signals.
#
# See also https://www.postgresql.org/docs/12/server-start.html for further
# justification of this as the default value, namely that the example (and
# shipped) systemd service files use the "Fast Shutdown mode" for service
# termination.
#
STOPSIGNAL SIGINT
#
# An additional setting that is recommended for all users regardless of this
# value is the runtime "--stop-timeout" (or your orchestrator/runtime's
# equivalent) for controlling how long to wait between sending the defined
# STOPSIGNAL and sending SIGKILL (which is likely to cause data corruption).
#
# The default in most runtimes (such as Docker) is 10 seconds, and the
# documentation at https://www.postgresql.org/docs/12/server-start.html notes
# that even 90 seconds may not be long enough in many instances.

EXPOSE 5432
CMD ["postgres"]

Entrypoint is copied from the official postgres docker github: postgres/docker-entrypoint.sh at master · docker-library/postgres · GitHub

It doesn’t start successfully. No errors or anything shown. Could anybody please hint me into a direction? Thank you

Hi, what are you trying to do by using your own Dockerfile to create a PostgreSQL service? Is there a feature that an image such as arm64v8/postgres:14.0-alpine could not provide?

1 Like

Nothing special at all :-). Just trying to make it complicated. Thanks for the great help. Now it works.

I cannot connect though to my database with Navicat. Always get “role doesn’t exist”. I set my ENVs in the docker-compose to:

environment: 
        - POSTGRES_USER=postgres
        - POSTGRES_PASSWORD=postgres
        - POSTGRES_DB=postgres

but they don’t seem to work. Do I have to set something if I connect from the “outside”?

Hey @razu could you please confirm that the Device Variables are properly created on the device’s Variables on balenaCloud?

Are you using the local IP address? or how are you trying to connect? If you ping the database does it respond?

Thanks for the further help.

I managed to change the pg_hba.conf to accept all connections (solution: How to configure PostgreSQL to accept all incoming connections - Stack Overflow). Now I’m able to connect.

I just need to figure out now how I can do that without manually sshing everytime into the container, changing the settings and restarting the service. I know there is a way to do that in the compose file. If anybody has a good hint or how-to I would greatly appreciate it.

BTW: It would be great if balena could offer a standard image to make this a more hassle free to setup.

Hi @razu! Maybe I am missing something, but I believe you can simply add a COPY line to your Dockerfile that copies the desired configuration file to the proper location in your image.

Regarding your suggestion, as far as I understand, you not doing anything that is specific to balena here. Thus, I’d agree with my colleague that using an image like arm64v8/postgres:14.0-alpine would be the simplest way to go.

Hey, I figured out how to do it and everything works. The only thing I couldn’t figure out is how to to copy my custom pg_hpg.conf into the image. Bind mounts are not allowed. The other alternative would be to echo the contents into the file via ssh, but this is not possible yet via balena ssh. I found an open ticket on github looking into this feature. For now I have to manually chang the file which is a bit annoying but ok for now.

Hi @razu, what if you added a COPY line in your Dockerfile to install your custom pg_hpg.conf during the build, instead of at runtime? So your Dockerfile could look something like this:

FROM arm64v8/postgres:14.0-alpine
COPY pg_hpg.conf /etc/postgresql/postgresql.conf

And your compose file would still use the build: . option and have the config file stored in the root of the project.

Hey, thanks for the help.

Shouldn’t it be:

FROM arm64v8/postgres:14.0-alpine
COPY ./pg_hba.conf /var/lib/postgresql/data/

When I edit it now manual its under the path /var/lib/postgresql/data/

@razu, yup, that looks good to me, I was working without an example in front of me!

Works like charm. Thanks for the help! Maybe you guys can write an article about deploying postgres.

1 Like