Unable to access postgres-postgis with .env file values

[alexc-tti]

Description:

Running docker-compose up -d postgres-postgis does not create a database with the variables set in the .env file (DB: default, user: default, password: secret). Attempting to access the DB using any role (root, postgres, etc) fails saying the role does not exist.

Manually specifying the role and changing the data path does work, e.g.

### PostgreSQL PostGis ###################################
    postgres-postgis:
      build: ./postgres-postgis
      volumes:
        - ${DATA_PATH_HOST}/postgres:/var/lib/postgresql-11/data # Note change of path here
      ports:
        - "${POSTGRES_PORT}:5432"
      environment:
        - POSTGRES_DB=default # Manually specified
        - POSTGRES_USER=postgres # Manually specified
        - POSTGRES_PASSWORD=postgres # Manually specified
      networks:
        - backend

Connecting to 127.0.0.1 using postgres/postgres shows the databases correctly and allows psql to work through bash.

Expected Behavior:

Creation of role "default" with password "secret" for access to database "default", as specified in .env.

Context information:

Output of git rev-parse HEAD

05a45ed0c86f30cce339f1b693a07d6cd169aa7f

Output of docker version

Client: Docker Engine - Community
 Version:           20.10.1
 API version:       1.41
 Go version:        go1.13.15
 Git commit:        831ebea
 Built:             Tue Dec 15 04:34:59 2020
 OS/Arch:           linux/amd64
 Context:           default
 Experimental:      true

Server: Docker Engine - Community
 Engine:
  Version:          20.10.1
  API version:      1.41 (minimum version 1.12)
  Go version:       go1.13.15
  Git commit:       f001486
  Built:            Tue Dec 15 04:32:40 2020
  OS/Arch:          linux/amd64
  Experimental:     false
 containerd:
  Version:          1.4.3
  GitCommit:        269548fa27e0089a8b8278fc4fc781d7f65a939b
 runc:
  Version:          1.0.0-rc92
  GitCommit:        ff819c7e9184c13b7c2607fe6c30ae19403a7aff
 docker-init:
  Version:          0.19.0
  GitCommit:        de40ad0

Output of docker-compose version

docker-compose version 1.25.0, build unknown
docker-py version: 4.1.0
CPython version: 3.8.5
OpenSSL version: OpenSSL 1.1.1i  8 Dec 2020

System info: Mac, Windows or Linux. Include which disto/version

20.04.2 LTS (Focal Fossa)

Steps to reproduce the issue:

1. docker-compose up -d postgres-postgis with .env vars set to defaults
2. Attempt to connect to 127.0.0.1 with any PG client --> FAILS
3. Set vars manual and rebuild
4. Attempt to connect to 127.0.0.1 with any PG client --> Succeeds

[bestlong]

try upgrade docker-compose

[cjlaborde]

I am having this exact issue in
Ubuntu 20.04.2 LTS (Focal Fossa)

▶ docker-compose up postgres-postgis        
Starting laradock_postgres-postgis_1 ... done
Attaching to laradock_postgres-postgis_1
postgres-postgis_1     | 2021-06-03 01:17:40.596 UTC [1] FATAL:  database files are incompatible with server
postgres-postgis_1     | 2021-06-03 01:17:40.596 UTC [1] DETAIL:  The data directory was initialized by PostgreSQL version 13, which is not compatible with this version 11.2 (Debian 11.2-1.pgdg90+1).
laradock_postgres-postgis_1 exited with code 1

This seems to be what is causing the issue.
postgres-postgis
Is using Postgres version 11 instead of Version 13.

While postgres container is using version 13.

▶ docker-compose up postgres   
Creating laradock_postgres_1 ... done
Attaching to laradock_postgres_1
postgres_1             | 
postgres_1             | PostgreSQL Database directory appears to contain a database; Skipping initialization
postgres_1             | 
postgres_1             | 2021-06-03 01:21:05.557 UTC [1] LOG:  starting PostgreSQL 13.1 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.2.1_pre1) 10.2.1 20201203, 64-bit
postgres_1             | 2021-06-03 01:21:05.557 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
postgres_1             | 2021-06-03 01:21:05.557 UTC [1] LOG:  listening on IPv6 address "::", port 5432
postgres_1             | 2021-06-03 01:21:05.558 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
postgres_1             | 2021-06-03 01:21:05.561 UTC [23] LOG:  database system was shut down at 2021-06-02 13:33:39 UTC
postgres_1             | 2021-06-03 01:21:05.564 UTC [1] LOG:  database system is ready to accept connections

[cjlaborde]

@alexc-tti Did you resolve this issue?
Here is the problem and solution.
Problem: It was using mdillon/postgis which was not updated for 2 years.

FROM mdillon/postgis:latest

LABEL maintainer="Mahmoud Zalt <mahmoud@zalt.me>"

CMD ["postgres"]

EXPOSE 5432

Solution is change FROM to postgis/postgis:latest
laradock/postgres-postgis/Dokerfile

FROM postgis/postgis:latest

LABEL maintainer="Mahmoud Zalt <mahmoud@zalt.me>"

CMD ["postgres"]

EXPOSE 5432

But changing this file is not enough you have to fully upgrade laradock to latest version
Then you either have to rebuild or do a full reset and install everything from scratch.

Now in your .env file in laravel project.

DB_CONNECTION=pgsql
DB_HOST=postgres-postgis
DB_PORT=5432
DB_USERNAME=default
DB_PASSWORD=secret

@bestlong You can probably close this issue now.

[bestlong]

#2925