Public URL / IP for Postgres Cluster

@vicente did you get anywhere with this? I’ve managed to set up external connections using the above approach but the tool we’re using (Mode) only allows encrypted connections, which I can’t seem to get working.

Did you manage anything without needing to resort to pgbouncer?

Hey @harg, I was able to set it up with Metabase. It’s pretty standard/lean 1-instance Postgres setup, so I’m using plain connections from the reporting tool.

OK, thanks. Yeah, we use Mode which enforces SSL for the DB connections, which is a bit of an issue.

Do you self-host Metabase? I guess that would get around the public host stuff entirely if it’s running inside the private network.

@kurt @shaun hey there, can you provide a link to the docs to connect to the hosted postgres from an admin tool? I can’t find anything other than what’s shared above and it’s not clear. Thanks!

@jgb-solutions Hey there, is the admin tool you’re looking to connect running on Fly, or is a separate service?

hey @shaun I want to connect something like Valentina Studio.

To connect from your dev machine you can setup Wireguard.

The detailed instructions are in the docs here but the tldr is:

  1. Download and install Wireguard
  2. Use fly wireguard create to create a config that you can add to wireguard
1 Like

hey @pier thanks! I’ll have a look. My guess is that people will want to connect to tools to the db and thus should have an easier way to do that.

1 Like

fly proxy 15432:5432 -a postgres-app-name will start it listening locally, and you can connect tools to localhost:15432.

1 Like

hey @kurt sounds like what I need. Thank you!

Is there an easy way to do that on Docker to be able to connect to PG from outside Fly?

(maybe adding the Fly credentials with an env var or something)

Is it possible to edit the services and ports of an automatically created postgres app (flyctl postgres create)? By default they don’t have a fly.toml and if you generate one, they don’t have an image or build. Do you have to use the github repository?

Or is there another way to edit the services/ports for the app?

@peoutebju

By default they don’t have a fly.toml and if you generate one, they don’t have an image or build.

The image bit could certainly be better. To make this work, you will need to download the configuration file by running fly config save --app <app-name> and make your changes. To apply the changes, you will need to deploy while specifying the --image argument.

Note: Be sure to be mindful of the directory you’re in when you download the configuration file.

fly deploy . --image <repository>:<tag>

To determine what image you’re currently running, you can run the following:

fly image show 

Let me know if you have any other questions!

Thanks, @shaun !

That makes sense. I guess what I’m struggling with might still be related to the automatically installed Postgres. I had already saved and edited the config, but when I run fly image show I get what I assume is the special image for automatically installed Postgres.
Registry = registry-1.docker.io
Repository = flyio/postgres

But when I deploy with fly deploy . --image flyio/postgres:latest, It says “Could not find image docker.io/flyio/postgres:latest

I assume that’s because it’s a private repository or something? If I try to use fly-apps/postgres-ha:latest it says authentication required, but I’m not exactly sure flyio/postgres is exactly the same as fly-apps/postgres-ha.

My goal is to stay on the automatic Postgres install and just make it available to directly access the DB from the internet so I can use it with PostGIS in QGIS and QField.

Edit: maybe this thread will help me?

So latest is not a valid tag in this particular image repo. If you’re using PG 14, you should specify flyio/postgres:14 or the specific tag listed when you run image show.

1 Like

Hi @shaun, I’ve created a Postgres DB following the official docs

Then I created the .toml file by doing fly config save --app theirstack-test and added the [[Services]] part from your comment. I tried both the secure and insecure versions.

My full fly.toml file looks like this:

# fly.toml file generated for theirstack-test on 2022-07-18T18:15:22+02:00

app = "theirstack-test"
kill_signal = "SIGTERM"
kill_timeout = 300
processes = []

[checks]
  [checks.pg]
    grace_period = "30s"
    headers = []
    interval = "15s"
    method = "get"
    path = "/flycheck/pg"
    port = 5500
    protocol = "http"
    restart_limit = 0
    timeout = "10s"
    tls_skip_verify = false
    type = "http"
  [checks.role]
    grace_period = "30s"
    headers = []
    interval = "15s"
    method = "get"
    path = "/flycheck/role"
    port = 5500
    protocol = "http"
    restart_limit = 0
    timeout = "10s"
    tls_skip_verify = false
    type = "http"
  [checks.vm]
    grace_period = "1s"
    headers = []
    interval = "1m"
    method = "get"
    path = "/flycheck/vm"
    port = 5500
    protocol = "http"
    restart_limit = 0
    timeout = "10s"
    tls_skip_verify = false
    type = "http"

[env]
  PRIMARY_REGION = "mad"


[build]
  image = "flyio/postgres:14.4"


[experimental]
  allowed_public_ports = []
  auto_rollback = false
  enable_consul = true
  private_network = true

[metrics]
  path = "/metrics"
  port = 9187

[[mounts]]
  destination = "/data"
  encrypted = false
  source = "pg_data"


[[services]]
  internal_port = 5432
  protocol = "tcp"


[[services.ports]]
 handlers = ["tls"]
 port = 443

I need to connect to it from Heroku and eventually from GitHub to set up actions.

Running flyctl postgres connect theirstack-test works, but when I try to connect with psql it fails. The hostname is theirstack-test.fly.dev.

My psql command looks like this:
psql postgresql://postgres:<PASSWORD>@theirstack-test.fly.dev:5432/postgres

But I keep getting the same error: psql: error: could not translate host name "theirstack-test.fly.dev" to address: nodename nor servname provided, or not known

Is there something wrong with my connection string?

2 Likes

Hello.

It’s been some time, but it might help someone else.

There are a few things to be done.

You updated the port in the fly.toml file, so that’s part of it.
For it to work externally, you’ll need an IP as well, otherwise, the host can’t be solved externally.

So use the command

flyctl ips allocate-v4 -a postgres_app

Or

flyctl ips allocate-v6 -a postgres_app

Depending on what you need.

To check the IP:

flyctl ips list -a postgres_app

Then your URL should be something like:
postgres://postgres:<password>@theirstack-test.fly.dev:443/postgres

Hope it helps!

1 Like

Now that dedicated IP addresses are a paid feature, is there any other way to access the Fly Postgres from an external server?

You can use flyctl postgre connect but this way you can use psql only. A second way is use the command fly proxy <custom-local-port>:5432 -a <pg-instance-name>. This command will establish a proxy connection from your local PC to the FlyIo private network of your DB instance. Then use some tools like PGAdmin to connect to your localhost:. Now you’re connected.

I did this an my DB app (I’m using TablePlus on my Mac) and connects fine. But I need to fly proxy. I wish I didn’t need to do this dance every time I need to connect. I have a dedicated IP address, BTW.