External Connections to Fly Postgres (Update: Where's my data???)

Questions / Help
,
1

I’m totally confused, I am trying to follow the instructions outlined in External Connections · Fly Docs but I still can’t connect to my database from an external network. According to the fly.toml reference, all I need to map an internal port to an external port is correctly configured [[services.ports]] section. I think mine looks fine.

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

  [[services.ports]]
    port = 5432
    handlers = ["pg_tls"]
  [services.concurrency]
    type = "connections"
    hard_limit = 1000
    soft_limit = 1000

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

  [[services.ports]]
    port = 5433
    handlers = ["pg_tls"]
  [services.concurrency]
    type = "connections"
    hard_limit = 1000
    soft_limit = 1000

Furthermore , the fly info command doesn’t include the shared IPv4 I have assigned to my machine. This being a deprecated command, I can’t tell if this is relevant.

Services
PROTOCOL        PORTS                   FORCE HTTPS
TCP             5432 => 5432 [PG_TLS]   False
TCP             5433 => 5433 [PG_TLS]   False

IP Addresses
TYPE            ADDRESS                 REGION  CREATED AT
private_v6      fdaa:2:1de0:0:1::2      global  2023-05-05T01:46:00Z

The shared IP address does appear in the web UI and if I use the fly ips list command

image
image864×145 6.59 KB

VERSION         IP                      TYPE            REGION  CREATED AT
private_v6      fdaa:2:1de0:0:1::2      private         global  2023-05-05T01:46:00Z
v4              66.241.125.163          public (shared)
2

Well, I woke up and realized my test application doesn’t work because it can’t log in to the database after following the steps External Connections · Fly Docs

It occured to me to use fly ssh to check on the machine myself and see what’s going. Something very interesting happened. First of all I tried to use psql and was surprised with the message

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
        Is the server running locally and accepting connections on that socket?

So I immediately thought I was on the wrong machine. So I had to double check. I used fly machines list and got this:

ID              NAME                    STATE   REGION  IMAGE                           IP ADDRESS                      VOLUME                  CREATED                LAST UPDATED            APP PLATFORM    PROCESS GROUP   SIZE
e286552ec32358  winter-river-5389       started dfw     flyio/postgres:14 (v0.0.38)     fdaa:2:1de0:a7b:ed:36a6:2bad:2  vol_3q80vd3g0z1rgzy6    2023-05-05T01:46:06Z   2023-05-06T03:19:32Z    v2              app             shared-cpu-1x:256MB

So this time I made use to use fly ssh console -s --app <my bermudi app> to make sure I was connecting to the correct machine running postgres. And indeed I am, I can log in to the postgres user and the hostname matches the machine ID i got previously. So why am I getting the previous error message? Surprisingly, if I add the -h <my bermudi app>.internal I can connect just fine to the database using the postgres username and the password that was generated when I first deployed my app and I was asked if I wanted to setup a postgres database for it. And without any network related tools like ping, traceroute or even ip I can’t I still can’t tell for sure if I am connecting to postgres on my e286552ec32358 machine or to somewhere else in the internal network.

Now, for the cherry on top. I can’t see any signs of my app information anywhere in the database. All I am seeing are the default tables created by postgres. What is the point of volumes then? Both df and mount -l confirm there is an additional volume /dev/vdb mounted in /data, so where’s my data??

I know I probably have no business trying to deploy here but I am trying to pick-up and learn new things and skills after spending years away from I.T. and I am totally stuck here with a lot of questions and very few ideas on what to try next.

3

@bermudi It looks the image that you originally deployed was using the flex implementation:

registry-1.docker.io/flyio/postgres-flex:15.2

Then I see a new deploy that pushed the image:

registry-1.docker.io/flyio/postgres:14

The latter is the image that runs the Stolon implementation and a completely different major version.

The good news, is that it looks like your original postgres directory is still intact, so try and re-deploy using the correct image.

E.G. registry-1.docker.io/flyio/postgres-flex:15.2

1 Like
4

Thanks a lot for taking time to answer!! I was trying to follow the instructions and probably got confused and deployed the tag in the instructions instead of my tag. As soon as I deployed the right tag my test app came back to life!

But I still have a few questions. Or maybe a lot. First, where you able to identify and troubleshoot the issue because you are a fly.io employee? What I mean is that I tried to go through the entire flyctl reference and I couldn’t find a way to display any information about v0 with any command. For example, fly history shows the creation of secrets in the day I deployed my test app but no Release complete:

TYPE    STATUS          DESCRIPTION                             USER                            DATE
Release complete        Release                                 <redacted>      7m46s ago
Release complete        Release                                 <redacted>      23h38m ago
Release complete        Release                                 <redacted>      23h43m ago
Secret                  SSH_CERT (a257c2e9611b5273)             <redacted>      2023-05-05T01:46:02Z
Secret                  SSH_KEY (3333e3957f42499a)              <redacted>      2023-05-05T01:46:01Z
Secret                  OPERATOR_PASSWORD (779b2c154ee32571)    <redacted>      2023-05-05T01:46:01Z
Secret                  REPL_PASSWORD (7b77efd0ac094f7b)        <redacted>      2023-05-05T01:46:01Z
Secret                  SU_PASSWORD (9bf642fdec3e45c2)          <redacted>      2023-05-05T01:46:01Z
Secret                  FLY_CONSUL_URL (7053060e9cf5c0a1)       <redacted>      2023-05-05T01:46:01Z

fly app releases --image doesn’t show any signs of v0 either:

DESCRIPTION     USER                            DATE            DOCKER IMAGE
v3      complete Release         <redacted>      9m33s ago       registry-1.docker.io/flyio/postgres-flex@sha256:5b83beb33bf77ef3079bc8e8672534dd1bfe046562991b686b83dd3e9bbb2090
v2      complete Release         <redacted>      23h40m ago      registry-1.docker.io/flyio/postgres@sha256:72c4931316e747f2a9a9aeba2405017ddd1d9e71a238b5febfbc5a9a9181d8e6
v1      complete Release         <redacted>      23h44m ago      registry-1.docker.io/flyio/postgres@sha256:72c4931316e747f2a9a9aeba2405017ddd1d9e71a238b5febfbc5a9a9181d8e6

Web dashboard is the same, there is no sign of v0 anywhere

Another thing I just noticed, when I saved the information when creating my test app the generated output was this:

VERSION STATUS   Postgres cluster <appname>-db created
  Username:    postgres
  Password:    <password>
  Hostname:    <appname>-db.internal
  Flycast:     fdaa:2:1de0:0:1::2
  Proxy port:  5432
  Postgres port:  5433
  Connection string: postgres://postgres:<password>@<appname>-db.flycast:5432

I obviously edited the output to show it here but please notice that there is a discrepancy in the Hostname: and the Connection string: one has .internal and the other one has .flycast

Looks like they work interchangeably but you guys probably want to generate a consistent output

Another thing. Even though my app is back up and working, I still can’t connect to the database remotely. And I still don’t understand why if I ssh into the machine I still have to specify the host because without it I’m getting the same error:

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
        Is the server running locally and accepting connections on that socket?

I also noticed my machine has a different ipv6 address than the one provided (fdaa:2:1de0:0:1::2) and both work as valid psql -h option as well as 127.0.0.1 but that’s probably you guys doing some crazy routing over there

Sorry for being a nuisance.

5

I was really hoping for an answer to these questions I had. I was never able to connect to my database from the outside to do the testing I needed and I am not sure if I’m missing something or if the platform doesn’t provide all the necessary information for me to debug my own issues.

6

Worth mentioning that Postgres apps cannot be accessed externally via a shared IPv4, as those only support HTTP handlers. External access requires either an IPv6 or a dedicated IPv4.

2 Likes
7

This is probably intentional. Looking at the source, the .flycast address (which is a subset of .internal, see New Feature: .flycast domains) might not exist. The connection string uses it if it exists, otherwise it falls back to the .internal address; while the hostname always uses .internal (which must exist). That makes sense to me.

1 Like
8

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.