Problems with postgres container settings

[dark-hippo]

I’m trying to launch a postgres db via a docker container, but can’t seem to get a connection to it after it’s successfully launched.

The dockerfile itself is rather simple

FROM postgres

COPY scripts /docker-entrypoint-initdb.d/

ENV POSTGRES_USER=postgres
ENV POSTGRES_PASSWORD=<password>
ENV APP_DB_NAME=<db_name>

EXPOSE 5432

CMD ["postgres"]

My fly.toml file has gone through a few iterations, but is currently:

app = "db"
primary_region = "lhr"

[build]
  ignorefile = ".dockerignore"

[http_service]
  internal_port = 5432
  force_https = true
  auto_start_machines = true
  auto_stop_machines = false
  min_machines_running = 0
  
[[services]]
  internal_port = 5432
  protocol = "tcp"

[[services.ports]]
  port = 5432

I originally tried using the generated file from fly launch, I’ve also tried setting the connection handler to the postgres specific “pg_tls” value.

Originally, the plan was to connect to the database with an api (also a docker container deployed to fly.io), but I couldn’t get it to connect properly, so decided to try and connect to it from pg-admin on my machine (also running in a docker container), but couldn’t get that to work either.

I’ll admit, at this point I’m a little lost. My endgame is to have a nodeJS api talking to the database. Being able to connect to it with an admin tool running outside of the fly network would be nice, but more importantly, I want to understand how to correctly configure the .toml file and where I’m going wrong.

[kd1]

If you’ve successfully launched your postgres db, you’ll need Wireguard to access it from your local machine. There are example commands in the docs: Connecting with flyctl.

Could you try using fly postgres connect -a <postgres-app-name> with the toml file generated by fly launch?

When connecting to the db from another app deployed on Fly, that app will need to know the internal IPv6 address to the db. You can give the connection string to the app as described in the docs here, and there are examples for how to connect the app here: App Connection Examples.

[dark-hippo]

Great, thank you.

I thought that part of the documents was only pertaining to the Fly Postgres clusters, not docker containers running postgres, but using fly proxy worked.

For the API, for some reason, I can’t seem to get the IPv6 address to work correctly, but the internal hostname (appname.internal) seemed to work. At least, it gave me a different error message to do with missing tables in the DB, which is annoyingly accurate.

Thanks for the help, after a day of fighting with it, I was starting to lose hope.