Postgres: Cannot connect to 9.4 server once it is running

[BertHartm]

about 6 hours ago my automated tests started failing because we could no longer connect to the database once the docker container came up and reported that it was accepting connections.

Further testing revealed that it was because we were pulling a new version of the official postgres:9.4 image.

A known good image we have is ad2fc7b9d681

The failing image seems to be 25add771b79e

I can switch back and forth between the 2 images and reproduce the bug consistently.

We do run some initialization scripts, but they're all sql commands to create our users and build our schemas and tables, not anything touching hba or similar.

[BertHartm]

I've noticed that bringing the image itself up lets me connect as the postgres user, but building the following Dockerfile breaks it.

FROM postgres:9.4

RUN LC_ALL=C DEBIAN_FRONTEND=noninteractive apt-get update && apt-get install -y \
    postgresql-plperl-9.4

[snopoke]

After much digging I discovered that the issue is that apt upgrades postgresql to the latest version and overrides the postgresql.conf file with the default. To prevent this from happening add the --no-upgrade flag to the apt-get command:

FROM postgres:9.4

RUN LC_ALL=C DEBIAN_FRONTEND=noninteractive apt-get update && apt-get install --no-upgrade -y \
    postgresql-plperl-9.4

[BertHartm]

Thanks for the digging, but for whatever reason that doesn't seem to have fixed our issue.

[snopoke]

Connect to the tuning container check to see what the postgresql.conf looks
like. The listen_address should be '*'

[BertHartm]

Thanks @snopoke . I didn't find the time to debug it further along your suggestions, but apparently whatever broke is fixed in the most recent image. Closing

[BertHartm]

Re-opening, as the issue appears to be back

[BertHartm]

I'm trying to look at the configuration, after applying the same dockerfile as above (with --no-upgrade), here's what I see so far.

postgresql.conf:

root@992bac22b33c:/# cat /var/lib/postgresql/data/postgresql.conf | grep listen
#listen_addresses = 'localhost'         # what IP address(es) to listen on;

pg_hba.conf:

...
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                trust
#host    replication     postgres        127.0.0.1/32            trust
#host    replication     postgres        ::1/128                 trust

host all all 0.0.0.0/0 md5

I'll try to compare these to the base image

[BertHartm]

What's surprising is that I changed the Dockerfile to

FROM postgres:9.4

RUN LC_ALL=C DEBIAN_FRONTEND=noninteractive apt-get update && apt-get install --no-upgrade -y \
    postgresql-plperl-9.4
RUN echo "listen_addresses = '*'" >> /var/lib/postgresql/data/postgresql.conf

so that listen_addresses would be re-set correctly if the apt-get changed anything, but when I look at the file in the container, the added line isn't there.

[BertHartm]

after looking at the changes in #127 a bit closer, I found that /usr/share/postgresql/postgresql.conf.sample was being ignored because /usr/share/postgresql/9.4/postgresql.conf.sample was being overwritten by the postgresql-plperl-9.4 install.

The following Dockerfile demonstrates a functional workaround:

FROM postgres:9.4

RUN LC_ALL=C DEBIAN_FRONTEND=noninteractive apt-get update && apt-get install --no-upgrade -y \
    postgresql-plperl-9.4
RUN rm /usr/share/postgresql/9.4/postgresql.conf.sample
RUN ln -sv /usr/share/postgresql/postgresql.conf.sample /usr/share/postgresql/9.4/

I'm not sure if this project is wrong for making the symlink, or if postgres-plperl is wrong for overwriting it.

[arthurlogilab]

got hit by this too. Has anyone found a workaround that could be in the original dockerfile ?

[tianon]

Sorry for the delay -- I'm inclined to think that it's very odd for postgres-plperl to overwrite postgresql.conf.sample outright instead of modifying it in-place or something similar, especially since postgresql.conf.sample is _the_ source material for postgresql.conf when it generates the initial database contents.

If this is still an issue, I'd recommend checking with upstream to see if they have any suggestions as to what's going on here. If they've got a better method for us to adjust postgresql.conf.sample, a PR (or even just a pointer to their recommendations) would be grand. :+1:

Since there's not really anything we can actionably do here right now, I'm going to close.