suddenly unable to connect to postgres/docker

[Kevin Burkett]

i've been using sqitch with docker for some time now, and all of a sudden i cannot connect:

$ sqitch deploy --target=app-roles

could not connect to server: Connection refused

Is the server running on host "0.0.0.0" and accepting

TCP/IP connections on port 5432?


i check with:

$ psql -U postgres -h 0.0.0.0

Null display is "[NULL]".

Expanded display is used automatically.

psql (11.1, server 9.6.10)

Type "help" for help.

postgres=# \q

same with pg10 - i've downloaded the latest version of sqitch docker at sqitch/sqitch

i can't figure out how to get more information to troubleshoot this.

[Kevin Burkett]

looks like a couple subsequent replies didn't get captured here:

How are you running Posgres? Is it on your local host, or in a Docker image? You need to be sure that you can connect from the Sqitch Docker image to whatever host Postgres is running on, which might require mapping some ports.

HTH,

David

I'm running postgres 10 with docker.   I previously didn't need to map any ports...

i've tried pulling a new sqitch image and regenerating the execustion script.  no luck.

i can psql into the db on my machine but sqitch cannot connect.

my docker run statement looks like this:

docker run --name thedb -v /Users/buckfactor/pg10data:/var/lib/postgresql/data -e POSTGRES_PASSWORD=1234 -p 5432:5432 -d postgres:10-alpine

i'm not sure what else to try

 

[Kevin Burkett]

a bit more info, just to show the ip and port are active:

$ docker ps

CONTAINER ID        IMAGE                COMMAND                  CREATED              STATUS              PORTS                    NAMES

567799de6e98        postgres:10-alpine   "docker-entrypoint.s…"   About a minute ago   Up About a minute   0.0.0.0:5432->5432/tcp   postgresqldb

$ sqitch deploy --target db:pg://postgres:12...@0.0.0.0/phile_starter --plan-file sqitch.plan

could not connect to server: Connection refused

Is the server running on host "0.0.0.0" and accepting

TCP/IP connections on port 5432?

$ psql -h 0.0.0.0 -U postgres

Null display is "[NULL]".

Expanded display is used automatically.

psql (11.1, server 10.7)

Type "help" for help.

postgres=#

[David E. Wheeler]

On May 25, 2019, at 19:32, Kevin Burkett <stlb...@gmail.com> wrote:

> a bit more info, just to show the ip and port are active:
>
> $ docker ps
> CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
> 567799de6e98 postgres:10-alpine "docker-entrypoint.s…" About a minute ago Up About a minute 0.0.0.0:5432->5432/tcp postgresqldb
>
> $ sqitch deploy --target db:pg://postgres:12...@0.0.0.0/phile_starter --plan-file sqitch.plan
> could not connect to server: Connection refused
> Is the server running on host "0.0.0.0" and accepting
> TCP/IP connections on port 5432?

Odd. Here’s what I get on macOS:

$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES

d7b768710c49 postgres:10-alpine "docker-entrypoint.s…" 9 minutes ago Up 9 minutes 0.0.0.0:5432->5432/tcp serene_hertz

$ curl -L https://git.io/fAX6Z -o sqitch && chmod +x sqitch
$ ./sqitch status db:pg://postgres:1234@localhost/postgres
# On database db:pg://postgres:@localhost/postgres
Database db:pg://postgres:@localhost/postgres has not been initialized for Sqitch

Which means it connected and found that the database isn’t initialized. Deploy works, too, so both the Sqitch database connection and the execution of psql work:

$ ./sqitch deploy db:pg://postgres:12...@0.0.0.0/postgres
Adding registry tables to db:pg://postgres:@0.0.0.0/postgres
Deploying changes to db:pg://postgres:@0.0.0.0/postgres
+ foo ..... ok
+ blah .... ok
+ sup ..... ok
+ blargh .. ok
+ yipee ... ok

Are you on macOS? I don’t have quick access to a Linux instance to test (long story), but it’s possible that the Sqitch container doesn’t have direct access to port 5432 on the Docker host, or maybe can’t route through the docker host to the Postgres container. Another thing to try is to explicitly tell the Sqitch container to link to the Postgres container’s network. Since your Postgres container is named postgresqldb, you can link by adding this to the docker-sqitch.sh script:

--link postgresqldb:pg

Then use the host name “pg” instead of 0.0.0.0.

If that works, then great. Mystery to me as to why. But I learned when I went to look up the docs just now that --link is deprecated! Kind of annoying.

https://docs.docker.com/network/links/

Time to learn something new, I guess.

HTH,

David

[David E. Wheeler]

On May 26, 2019, at 14:01, David E. Wheeler <da...@justatheory.com> wrote:

> If that works, then great. Mystery to me as to why. But I learned when I went to look up the docs just now that --link is deprecated! Kind of annoying.
>
> https://docs.docker.com/network/links/
>
> Time to learn something new, I guess.

Oh, this is interesting. From https://docs.docker.com/network/bridge/:

“Containers on the default bridge network can only access each other by IP addresses, unless you use the --link option, which is considered legacy. On a user-defined bridge network, containers can resolve each other by name or alias.”

So maybe just try to use “postgresqldb" as the host name instead of 0.0.0.0. Uh, reading on, though, that probably won’t work unless you’re using a named network, because:

"If you run the same application stack on the default bridge network, you need to manually create links between the containers (using the legacy --link flag). These links need to be created in both directions, so you can see this gets complex with more than two containers which need to communicate. Alternatively, you can manipulate the /etc/hosts files within the containers, but this creates problems that are difficult to debug.”

So I guess user-defined bridges might be more desirable in the long run.

Best,

David

[Kevin Burkett]

yes i'm on macOS.   link option is not recognized and throws an error

[Kevin Burkett]

ok - i'll explore this path and let you know.   this is new territory for me, but like you say, time to learn something new.   a good activity for memorial weekend?

[Kevin Burkett]

bingo!

docker rm -f $(docker ps -aq)

docker run \

--name postgresqldb \

-v /Users/buckfactor/pg10data:/var/lib/postgresql/data \

-e POSTGRES_PASSWORD=1234 \

-p 5432:5432 \

--network phile-net \

-d postgres:10-alpine

and 

docker run -it --rm --network phile-net \

--mount "type=bind,src=$(pwd),dst=/repo" \

--mount "type=bind,src=$HOME,dst=$homedst" \

"${passopt[@]}" "$SQITCH_IMAGE" "$@"

sqitch deploy --target db:pg://postgres:1234@postgresqldb/phile_starter --plan-file sqitch.plan --verbose

Adding registry tables to db:pg://postgres:@postgresqldb/phile_starter

Deploying changes to db:pg://postgres:@postgresqldb/phile_starter

  + 0010-roles .. ok

[Kevin Burkett]

so i'm just curious if you'll be changing the generated script that executes docker now to take a network parameter?

[David E. Wheeler]

On May 26, 2019, at 15:22, Kevin Burkett <stlb...@gmail.com> wrote:

> bingo!
>
> docker rm -f $(docker ps -aq)
> docker run \
> --name postgresqldb \
> -v /Users/buckfactor/pg10data:/var/lib/postgresql/data \
> -e POSTGRES_PASSWORD=1234 \
> -p 5432:5432 \
> --network phile-net \
> -d postgres:10-alpine

Where did phile-net come from? Did you create it in a pervious command and run the Postgres container with it?

> and
>
> docker run -it --rm --network phile-net \
> --mount "type=bind,src=$(pwd),dst=/repo" \
> --mount "type=bind,src=$HOME,dst=$homedst" \
> "${passopt[@]}" "$SQITCH_IMAGE" "$@"
>
> sqitch deploy --target db:pg://postgres:1234@postgresqldb/phile_starter --plan-file sqitch.plan --verbose
> Adding registry tables to db:pg://postgres:@postgresqldb/phile_starter
> Deploying changes to db:pg://postgres:@postgresqldb/phile_starter
> + 0010-roles .. ok

Yay!

so i'm just curious if you'll be changing the generated script that executes docker now to take a network parameter?

On May 26, 2019, at 15:25, Kevin Burkett <stlb...@gmail.com> wrote:

> so i'm just curious if you'll be changing the generated script that executes docker now to take a network parameter?

Might make sense to add an environment variable, but I’m a bit resistant, just because there is no reasonable default value.

Did you by chance try using the host name host.docker.internal? That’s the host name to use when connecting to Postgres (or another database) running on your main host; maybe it would also connect to a container with its port forwarded.

IOW, remove the phile-net stuff, go back to the way it was before, and try “host.docker.internal” instead of “0.0.0.0”. Curious if that would also fix your issue. If so, I think that’s going to be the way to go in general, since host.docker.internal will soon be supported on Linux, too (it’s macOS and Windows-only at the moment).

Best,

David

[Kevin Burkett]

in my original code, i did not have --network host when i ran postgres image.   if i have this line in, everything works fine on 0.0.0.0.  if i take it out, i get the error...

also, if i leave it in but do what you ask above, error:

$ sqitch deploy --target db:pg://postgres:12...@host.docker.internal/phile_starter --plan-file sqitch.plan

could not connect to server: Connection refused

Is the server running on host "host.docker.internal" (192.168.65.2) and accepting

TCP/IP connections on port 5432?

--

peace, etc.

bucket

[David E. Wheeler]

On May 26, 2019, at 17:06, Kevin Burkett <stlb...@gmail.com> wrote:

> in my original code, i did not have --network host when i ran postgres image. if i have this line in, everything works fine on 0.0.0.0. if i take it out, i get the error…

Well that’s weird, since `--host` is documented to do nothing on macOS or Windows:

"The host networking driver only works on Linux hosts, and is not supported on Docker Desktop for Mac, Docker Desktop for Windows, or Docker EE for Windows Server."

https://docs.docker.com/network/host/

I wonder if that has changed and the docs have fallen behind.

> also, if i leave it in but do what you ask above, error:
>
> $ sqitch deploy --target db:pg://postgres:12...@host.docker.internal/phile_starter --plan-file sqitch.plan
> could not connect to server: Connection refused
> Is the server running on host "host.docker.internal" (192.168.65.2) and accepting
> TCP/IP connections on port 5432?

What happens if you don’t pass `--network host` when you run the Postgres container, but leave it in the Sqitch container?

Honestly, this stuff needs to be easier to explain…

Best,

David

[Kevin Burkett]

another interesting thing:

with the 

  --network host 

argument, sqitch works but psql directly does not.   w/0 that arg, the opposite.

i think i need to make some sort of matrix with settings and and results for each - this thread is a little confusing at this point. lol.    i'll try and summarize everything in a subsequent response.  not much time right now tho.

 

--

peace, etc.

bucket

[Kevin Burkett]

this is a bummer

--

peace, etc.

bucket

[Kevin Burkett]

reading the docs it seems that bridge on both containers should work

--

peace, etc.

bucket

[David E. Wheeler]

On May 27, 2019, at 14:55, Kevin Burkett <stlb...@gmail.com> wrote:

> reading the docs it seems that bridge on both containers should work

What version of Docker are you running?

David

[Kevin Burkett]

Docker version 19.03.0-beta3, build c55e026

sqitch (App::Sqitch) 0.9999

--

peace, etc.

bucket

[David E. Wheeler]

On May 27, 2019, at 11:15 PM, Kevin Burkett <stlb...@gmail.com> wrote:

Docker version 19.03.0-beta3, build c55e026

sqitch (App::Sqitch) 0.9999

Hrm. I have 18.09.2. Wonder if something has changed.

Seems as though that’s the latest stable version:

  https://docs.docker.com/docker-for-mac/release-notes/

Quite a few changers in the interim:

  https://github.com/docker/docker-ce/releases

D

[Kevin Burkett]

that was the case - i reverted to 18.09.2 and it works as expected

--

peace, etc.

bucket

[David E. Wheeler]

On May 28, 2019, at 19:07, Kevin Burkett <stlb...@gmail.com> wrote:

> that was the case - i reverted to 18.09.2 and it works as expected

Thank you! I took the liberty to copy your tables into a bug report:

https://github.com/docker/for-mac/issues/3692

Hope they can help us understand what’s going on!

Best,

David

[David E. Wheeler]

On May 29, 2019, at 19:38, David E. Wheeler <da...@justatheory.com> wrote:

> Thank you! I took the liberty to copy your tables into a bug report:
>
> https://github.com/docker/for-mac/issues/3692
>
> Hope they can help us understand what’s going on!

David Scott was kind enough to reply:

https://github.com/docker/for-mac/issues/3692#issuecomment-498300770

Sounds like the behavior has reverted to its previous pattern, though not released yet. But he also says that the host.docker.internal host name should work reliably. Didn’t you try that, Kevin?

Best,

David

[Kevin Burkett]

Yes, I did.

--

peace, etc.

bucket

[David E. Wheeler]

On Jun 3, 2019, at 20:37, Kevin Burkett <stlb...@gmail.com> wrote:

> Yes, I did.


Would you be able to comment on that ticket?

https://github.com/docker/for-mac/issues/3692

Thanks,

David