Why my Rails Projects Couldn’t Connect to Postgresql

[insert Solid Snake meme]

Backstory

It was a while ago that I decided to start to develop a messaging app to add to my portfolio. I wanted to have a bit of use using websockets, and I thought it would be a fun side project. There was just one thorn in my side that I was tired of dealing with, which was using postgres. Using postgres instead of the rails default sqlite is an important step if you’re wanting to host your backend on heroku. My current environment is a custom PC running Windows 10 and I’m using Ubuntu with Windows Subsystem for Linux (WSL) for my cli. This is important to note because initially I thought that this was the core of my problems.

The Problem

Whenever you’re doing an initial rails setup with postgres you run…

$ rails db:setup

this will set up the test and development databases for your current user. This is where my first error showed up. It was an extra long rails error saying it could not connect to the postgres server. Which was odd to me since I had my Postgresql admin panel up, running the server just fine.

The Journey

My initial solution was to manually set a username, password, host, and port number in my rails database.yml. This worked just fine, but was possibly insecure since this yml file would be uploaded to github with my user roles password there for the world to see. Plus, it was just super annoying to have to set this up for every new project.

So I knew my error was that rails couldn’t connect to my postgres server to make the databases. My next step in figuring out what is happening is to see if I could connect to the server itself. I ran…

$ sudo -i -u postgres

to switch to the postgres account on the server, then ran…

$ psql

to try to access the postgres prompt. This got me the same error as rails did. Saying it could not connect to the server and that I should check if it’s even running.

So my next step was to see if I could start it from the terminal. I shut down the server on my postgres admin app, and I ran…

$ sudo service postgresql start

to attempt to start it manually. Leading to my third error, saying that it couldn’t start the postgresql 12 server and that its permission was denied. But wait … postgres 12!? The version of postgres that I installed with the postgres admin panel was 13! Why are there two versions?

“A jumper cable walks into a bar. The bartender says, “I’ll serve you, but DON’T START ANYTHING”

My initial thoughts were that this was something to do with it not being up to date. So I tried updating everything I could from within the terminal and reinstalling postgres, but it still gave me all the same errors. At this point I was tired and frustrated and I decided it was probably just something to do with windows not liking that something through WSL was trying to do server stuff. Then I called it a day.

The Solution

Then while writing this post, I looked back at some of these errors and thought “hang on… maybe there isn’t just one postgres installed”. I assumed that the postgres I installed through their installer was the only one on my machine. So I looked through my packages, and I found postgres 12! Which makes sense that that error was showing 12 in ubuntu, but still didn’t make sense that it couldn’t start the server with the other postgres server shut down. I thought that by not having that server running on that port that it wouldn’t make any more problems. Just to be sure I completely uninstalled the postgres 13 that I installed using the installer, and tried again. I started the server, switched to the postgres user, and tried to connect to the server prompt. This time it worked flawlessly. After making a new role for my user, I was able to do ‘rails db:setup’ and ‘rails db:migrate’ without any errors.

Conclusion

I ended up dealing with a string of errors and headaches for 6 hours of my time. I learned that I really should look at my errors more closely and not make too many assumptions about what’s going on. That would have saved me a lot more time.

TLDR: if you need to use postgres with rails on a Windows 10 Ubuntu WSL environment, don’t install it using the installer. Install it through the Ubuntu terminal. Here is a really helpful guide on doing just that here. Remember to start the server by running

$ sudo service postgresql start

I hope this helps somebody who might be struggling with rails and postgres. If not, at least just have a quick laugh at how much of a dum I was for not realizing that there were two postgres fighting each other on my system.