Public URL / IP for Postgres Cluster

Hey there,

@kurt suggested me to post this Question here.
We are currently looking into Fly.io for our next migration of our Production Platform. Currently we are already testing apps for GraphQL and long-lived docker containers. It looks very promising for now.

We are also looking into the PostgreSQL Cluster solution, a main blocker for us ist, that we do need to use it with additional Servers and Tools outside of Fly.io, which do not have any possibility to open an Wireguard Tunnel.

As far as I could tell, the documentation only shows internal connection parameters, however we would need a public external URL or Anycast IP to connect to the PostgreSQL Cluster. It should still have the advantages of connected to the nearest read instance.

Is this even possible, planned to come or do you not suggest to use Fly’s PostgreSQL Cluster if it needs to be used with Servers outside of Fly.

Cheers
Alex

1 Like

This is doable! One thing to keep in mind is that our postgres clusters expose two ports:

  1. Port 5432 is a proxy that always connects you to the primary writable node
  2. Port 5433 is direct to postgres, and will work for read replicas

You can expose both of these publicly a couple of ways. What kind of apps are you connecting from? If it’s something like a FaaS you’ll want to do this with pgbouncer or pgpool, if it’s just external admin tools there might be a simpler way.

2 Likes

We do have FaaS Providers and IDE/Admin Tools running on Client Devices, but also regular VMs in the Cloud, needing to connect to the Cluster.

That said, if connecting to the read replicas on 5433, we do need to connect on the publicly always to the nearest one, without knowing the region of it.
So that must be taken in consideration for a way.

1 Like

I hope you guys don’t mind me chiming in.

I am facing a similar scenario, where I’d like to expose a postgres database server externally to a third party reporting tool (Redash.io).

I think setting up pgpool or pgbouncer might be overkill for my use case. I wonder @kurt if you could elaborate a bit more on those simpler ways of doing it. It pretty fits the external admin tool scenario.

For context, I am helping one of my clients automating their business operations. Even though we are not going global or anything like that, it’d be just great if I didn’t had to manage all the server side configs with ansible and a VPS. :slight_smile:

I wonder whether I should add the following section to the fly.toml in my postgres-ha clone.

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

Hey, @vicente,

I apologize for the delayed response!

So there’s two things you will need to do.

  1. Specify your internal port and protocol.
[[services]]
  internal_port = 5432
  protocol = "tcp"
  1. Specify the external port and handler.
# For secure connections
[[services.ports]]
 handlers = ["tls"]
 port = 443

# For insecure connections. 
[[services.ports]]
 handlers = []
 port = 10000

Here’s a link to our configuration docs for more information:
https://fly.io/docs/reference/configuration/#the-services-sections

I hope that helps!

1 Like

Very appreciated, @shaun. Thank you for the snippet! :slight_smile:

@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?