Can't connect to Postgres through Rails app but can through TablePlus

1

So, I’ve proxied my app to port 15432 (flyctl proxy 15432:5432 -a myapp) and then if I use ‘postgres://postgres:password@localhost:15432/db’ as a database connection string in TablePlus it connects without any problems.

However if I use a rails app using that same URL for the database, I get

ActiveRecord::ConnectionNotEstablished (could not connect to server: Connection refused
        Is the server running on host "::1" and accepting
        TCP/IP connections on port 15432?
):

What am I doing wrong?

2

Update:

Now I’m getting
Puma caught this error: bad URI(is not URI?): ‘postgresql://postgres:password@localhost:15432/db' (URI::InvalidURIError)
the first time I try to connect, and then subsequent attempts give:
#<NoMethodError: undefined method ```default_timezone=' for ActiveRecord::Base:Class

None of this happens when the DATABASE_URL is pointed to my previous Heroku deployment

3

I haven’t tried deploying postgres on localhost, I’ve only used postgres databases that are provided as an option on launch or via flyctl postgres --create, but I will say that when I do so, I see secrets being set of the form: DATABASE_URL=postgres://postgres:password@db-app.internal:5432.

Note also that you say TablePlus works with URLs of the form postgres://..., and ActiveRecord is failing with URLs of the form postgresql://. Try dropping the ql before the colon.

4

Thanks for your reply @rubys

I removed the ‘ql’ typo.

Tried with localhost but also postgres://postgres:password@my-app-name.internal:5432/db

TablePlus: could not translate host name "cja-budget-db.internal" to address: nodename nor servname provided, or not known
Rails: As above - first an invalid URI error, then complains about default_timezone

5

Ah ok…so I’ve now set up a WireGuard tunnel, and now TablePlus can connect using my-app.internal instead of localhost.

Still same behaviour within the Rails app though.

6

OK, this is a long shot, but take a look at ruby on rails - `undefined method `default_timezone=' for ActiveRecord::Base:Class` on Ubuntu 14.04 - Stack Overflow

The symptoms match. Now look at the fix:

It turns out that it was the DATABASE_URL environment variable that caused this issue. I have a password with special characters and because they weren’t url escaped, it errored like this.

Any chance that you have special characters in your password?

7

Ahh!

I’d already seen that SO post and the password does not have any special characters, but I double checked the environment config in my rails app and realised that somehow the url had a curly ‘ rather than a ' :man_facepalming:t2: No idea how that happened but now that I’ve changed that it works!

Thanks for your help @rubys :+1: