Fly.io + Remix + Postgres

Anyone can reproduce steps to make single postgres cluster and remix app on fly.io

I have no problems with connecting app to cluster but steps are coming when trying to config my dev enviorement to be able to connect to postgres with prisma. Followed steps from fly.io docs. Generated good connection url for postgres used it on prisma but didn’t get any results.

Anyone can help with that? Im on windows10 so propably there are some problems with tunnels on windows but im not sure.

Are you using WSL?

One potential issue here is DNS. The quickest thing to try is:

  1. Run flyctl ips private to get a list of VM IP addresses
  2. Try connecting to the IP directly, you can use a connection string like this: postgres://<user>:<password>@[fdaa:0:1:a7b:6b:0:1e7a:2]:5432/databasename

Other things to check:

  • Is the wireguard connection up and healthy?
  • Try pinging one of your private IPs: ping -6 fdaa:0:1:a7b:6b:0:1e7a:2

Feel free to post all the output from these commands here and we can help you interpret what it means.

Hey Kurt!

On my end:

  1. Wireguard is up
  2. Successfully pinging the private IP

Directly using the IP in the Database_url still returns

Can't reach database server at `[fdaa:0:3988:a7b:f0f:0:57c5:2]`:`5432`

I am using Prisma, but that shouldn’t change anything

If it’s easier for everyone i’m happy to do a call off discord and share screen… probably faster, it might be something super obvious.

Cheers
Tom

I looked at your DB and it was not in a good state. Did you happen to deploy with postgres:14 by chance? It looks like you added a [services] block, is it possible you used version 14 when you deployed?

Your DB is actually postgres v13 (this is our default for a new cluster), so it was stuck in a state where it couldn’t boot one of the nodes. fly logs was showing errors like this:

[18180] FATAL:  database files are incompatible with server
[18180] DETAIL:  The data directory was initialized by PostgreSQL version 13, which is not compatible with this version 14.0 (Debian 14.0-1.pgdg110+1).

I reverted the DB and it’s running healthily now. Would you see if that helped?

Hmmm still hitting the same error… but that will explain at least one thing that’s gone wrong.

I might spin them all down and try again from scratch taking notice of the version.

Following up, a complete refresh of everything results in the same scenario.

Every way I attempt to connect is rejected by prisma / psql / pg admin, even with a healthy postgres cluster

I’m in +10 timezone (Brisbane, Aus) btw again, happy to just talk it through on a call :slight_smile:

When you run psql, is that from your development machine after connecting to the Wireguard VPN? One way to check exactly where the connectivity problem is would be to ssh into the DB server itself, using fly ssh console -s to choose it, and trying to run psql there — should confirm that the DB is running correctly.

Then from inside the application server (can be reached with fly ssh console -s again) using the private IP address, to check if the app servers can reach the DB ones.

If both these work okay we can try switching back to the app.internal address.

@sudhir.j was trying this now i can connect with my pgAdmin4 client but fly ssh console -s and running psql there giving me # psql 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?
any ideas?

Still same stuff on prisma studio getting : `Message: Error in Prisma Client request:

Can’t reach database server at pf-backend.internal:5432

Please make sure your database server is running at pf-backend.internal:5432.`

try with psql -U postgres -h localhost after ssh’ing to the app

The pgAdmin4 problem seems like a misconfiguration somewhere, @Programistafrontend — the databases managed by Fly are listing on a DATABASE_URL the looks a bit like postgresql://username:password@db-app.internal:5432/dbname. The app seems to think Postgres is running locally on the same machine and trying to connect over the default file socket.

@tullo

after command seeing this:

# psql -U postgres -h localhost
Password for user postgres: 
psql (13.4 (Debian 13.4-4.pgdg110+1))
Type "help" for help.

postgres=#

seems to be fine from fly.io site but why my prisma client cant connect with db?

You should be able to get results with dig -t aaaa pf-backend.internal after your wireguard tunnel is up.

@tullo getting this:

; <<>> DiG 9.16.23 <<>> -t aaaa pf-backend.internal
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NXDOMAIN, id: 60937
;; flags: qr rd ra ad; QUERY: 1, ANSWER: 0, AUTHORITY: 1, ADDITIONAL: 1

;; OPT PSEUDOSECTION:
; EDNS: version: 0, flags:; udp: 512
;; QUESTION SECTION:
;pf-backend.internal.           IN      AAAA

;; AUTHORITY SECTION:
.                       3600    IN      SOA     a.root-servers.net. nstld.verisign-grs.com. 2021111802 1800 900 604800 86400

;; Query time: 13 msec
;; SERVER: 192.168.2.1#53(192.168.2.1)
;; WHEN: Thu Nov 18 22:36:26 W. Europe Standard Time 2021
;; MSG SIZE  rcvd: 123

I think its something wrong im getting no answer according to dig.

@Programistafrontend did you create a wireguard VPN connection already? I just checked and I don’t see any record of one. Can you take a screenshot of the wireguard status for your system?

Once you’ve ensure the wireguard connection is up and running, will you run this command and show us the output?

dig aaaa pf-backend.internal @fdaa:0:39d3::3

Also, just to make sure we understand, you’re still having problems connecting to your database from your windows machine, right?

yes im trying to connect from windows machine
feedback from dig :


and wireguard tunel:

from prisma studio getting still :

That dig output shows DNS working, but only when you specify our DNS server. The problem here is that the DNS server windows uses by default doesn’t know how to handle requests for .internal. So when you use the hostname with prisma, it can’t find the IP.

Will you try connecting Prisma to [fdaa:0:39d3:a7b:23c3:0:58de:2]:5432 and see if it works?

i think with this adress is working im getting other error on prisma studio but is not anymore related to missing db in pf-backend.internal

1 Like