Getting PostgreSQL To Work On A Rails App Using Ubuntu And WSL

We all know that getting projects hosted is important but it may not seem worth it because many hosting services require Postgres and getting Postgres to work locally can be very troublesome. I understand this because I hosted on Heroku for the first time for my latest project and I spent way too long getting Postgres to work on my machine. Even though I realized later that getting Postgres to work locally isn't absolutely necessary to deploy your app I'm still happy I was able to get Postgres working. Here are the steps I took to get Postgres working for my rails app using Ubuntu and Windows Subsystem for Linux (WSL).

Step 1.

Start by opening your Ubuntu terminal and following the steps from
https://docs.microsoft.com/en-us/windows/wsl/tutorials/wsl-database
Update apt

sudo apt update

Download Postgres

sudo apt install postgresql postgresql-contrib

Create a password for the default user 'postgres'

sudo passwd postgres

Start the postgresql service

sudo service postgresql start

Now switch over to already created default role 'postgres'

sudo -i -u postgres

and type 'psql' and hit enter to enter the postgres shell.

You should now see...

postgres=#

before everything you type.

Step 2.

Create a new user

CREATE ROLE anyusername WITH LOGIN PASSWORD 'anypassword';

Note: syntax is important. Make sure your username is not in any quotations and the password is in SINGLE quotes. The semi-colon at the end is necessary. If you did it right you should see CREATE ROLE outputted to the terminal.

Give your user a database

CREATE DATABASE your_application_name OWNER anyusername;

Step 3.

Open your rails app and add the gem 'pg' to your gemfile. Make sure to 'bundle install' afterwards as well.

Next, go to config/database.yml and change it to match this:

default: &default
  adapter: postgresql
  host: localhost
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  timeout: 5000

development:
  <<: *default
  database: your_application_name
  username: anyusername
  password: anypassword

Note that the test and production databases wont work with the way we are setting this up. Unfortunately, this was the only way I was able to do it.

Lastly, migrate your database by entering:

rake db:migrate

If you don't get any errors than you did it! It took me hours of googling and trying out different things in my terminal to get Postgres to work. Many of the guides that I read about using Postgres with rails were leaving me stuck with different errors so I figured it might be helpful to someone to see what I did. The second answer from the stackoverflow link below was what helped me the most. Lastly, I want to mention to anyone who was stuck like I was that you don't absolutely need to get Postgres to work locally to be able to host on a platform like Heroku. You just have to switch the 'sqlite3' gem to 'pg' and change the database.yml so that it works on Heroku's end. There's a good reason Rails uses sqlite3 for testing by default and it's because it so much more convenient. Still, although it may not be necessary, it's important to be able to test that the code for the database works locally before you push it to a live server and more than that, it just feels nice to say you managed to conquer a database management system like Postgres.(maybe I'm exaggerating a bit) At the very least, I hope you enjoyed reading this post.

sources:
https://stackoverflow.com/questions/9987171/rails-fatal-peer-authentication-failed-for-user-pgerror

https://docs.microsoft.com/en-us/windows/wsl/tutorials/wsl-database

Comments

[Paul John Butad]

If you don't want to manually create a database from postgres shell (you want to use "rake db:create" command)
You'll get this error
FATAL: password authentication failed for user "postgres"

Make sure you have set the password first after you type "psql".
Just type the command below and you'll get a prompt to enter your password

postgress=# \password

[Nathan Rymarz]

Thanks for the advice. I wasn't aware that you could or needed to re enter your password for the default postgres user. Being able to create the db from rake db:create would definitely save time. I'll keep this in mind for the future!