Cannot connect to PostGreSQL inside Sail configured Docker container

I've mostly used Homestead for Laravel development in the past, and just recently started trying out using Laravel Sail + Docker. I got most of the stuff working, but when trying to connect to the pgsql database via TabelPlus I keep getting errors when trying to connect.

Following is the relevant configuration for pgsql in my docker-compose.yml:

pgsql:
        image: 'postgres:13'
        ports:
            - '${FORWARD_DB_PORT:-5432}:5432'
        environment:
            PGPASSWORD: '${DB_PASSWORD:-secret}'
            POSTGRES_DB: '${DB_DATABASE}'
            POSTGRES_USER: '${DB_USERNAME}'
            POSTGRES_PASSWORD: '${DB_PASSWORD:-secret}'
        volumes:
            - 'sail-pgsql:/var/lib/postgresql/data'
            - './vendor/laravel/sail/database/pgsql/create-testing-database.sql:/docker-entrypoint-initdb.d/10-create-testing-database.sql'
        networks:
            - sail
        healthcheck:
            test:
                - CMD
                - pg_isready
                - '-q'
                - '-d'
                - '${DB_DATABASE}'
                - '-U'
                - '${DB_USERNAME}'
            retries: 3
            timeout: 5s

In Docker Desktop I can see that my pgsql container is running, and I see the attempts to connect.

For the environment variables in the yml config, does Sail pull values for relevant variables (e.g. DB_USERNAME) from the project's .env file, or are these environment variables in the config just for specifying prior to running the sail up command?

Essentially I'm just not sure what I'm supposed to input into the TablePlus GUI to connect to the database in the Docker container. I've tried a few things:

for the "port" the field set as 5432
for the "host" field, I've tried: localhost or 127.0.0.1 or pgsql
for the "user" field, I've tried: leaving it empty or sail (value from project .env)
for the "password" field, I've tried: leaving it empty or "password" or "secret"

Nothing seems to work; I might also just be using the wrong combination of things 🤷‍♂️

Not sure where I'm going wrong, but any guidance would be appreciated!

LaryAI

2 years ago

Level 58

The environment variables in your docker-compose.yml file are indeed pulled from your project's .env file. So, you should ensure that the values in your .env file match the ones you're trying to use to connect to your PostgreSQL database.

Here's what you should input into the TablePlus GUI to connect to the database in the Docker container:

Host: localhost or 127.0.0.1
Port: 5432 (or whatever value you have set for FORWARD_DB_PORT in your .env file)
User: The value of DB_USERNAME in your .env file
Password: The value of DB_PASSWORD in your .env file
Database: The value of DB_DATABASE in your .env file

If you're still having trouble connecting, you might want to check the logs of your PostgreSQL container to see if there are any error messages that might help you diagnose the problem. You can do this by running the following command in your terminal:

docker logs <container_id>

Replace <container_id> with the ID of your PostgreSQL container. You can get the ID by running docker ps and looking for the container that's running the postgres:13 image.

Also, ensure that the PostgreSQL service is running inside the Docker container. You can do this by running the following command:

docker exec -it <container_id> pg_isready

This command will tell you if the PostgreSQL server inside the container is ready to accept connections. If it's not, you might need to troubleshoot why the server isn't starting up correctly.

1 like

weerd

2 years ago

Best Answer

Level 8

Alright, @laryai's anwser was helpful with some things but didn't resolve anything.

I did check through the logs for the pgsql image in Docker and could see that while connection attempts from TablePlus were happening, it kept saying that the "'sail' role did not exist" which explains why it was refusing the connection. I'm not sure why the role didn't exist (why not created during docker build process), but after doing a bit more googling, I found an answer on Laracasts by @neilstee for a different question but the solution also helped for my issue.

Essentially something likely got borked during the build and running the following commands as indicated in other post by @neilstee, to remove all volumes and rebuild, etc solved the issue:

sail down -v
sail build --no-cache
sail up -d

I can now properly connect to pqsql in the container from TablePlus!

Thanks for the unknowing help @neilstee 🙌

4 likes

Please or to participate in this conversation.