Set up PostgreSQL with PostGIS for RoR on WSL

To use Postgres with your wsl rails application, you'll have to download Postgres for windows and connect to it through linux: https://www.postgresql.org/download/windows/

Remember the username(default is Postgres) and password you chose during the Postgres installation process as you'll use them to connect your rails app to the database.

After installation, in wsl:

sudo apt update

sudo apt install build-essential patch liblzma-dev libpq-dev
# liblzma-dev is used by the nokogiri gem, while libpq-dev is used for the pg gem

sudo apt install libpq-dev

These are some other dependencies you might need:

sudo apt-get install git-core curl zlib1g-dev build-essential libssl-dev libreadline-dev libyaml-dev libsqlite3-dev sqlite3 libxml2-dev libxslt1-dev libcurl4-openssl-dev python-software-properties libffi-dev postgresql-client-common postgresql-client

The terminal might ask you for permission to install some dependencies, just type Y and hit Enter.

Assuming you chose the default port 5432, otherwise use the port you specified and run this command in wsl terminal to try to connect to your Postgres:

psql -p 5432 -h localhost -U Postgres

It will ask for the password for the user Postgres and if it enters successfully, everything should be working fine. To test connection from rails:

rails new rails_postgres_app --database=postgresql

cd rails_postgres_app

Then open the database.yml file in your IDE/text editor of choice and modify accordingly. This illustration assumes username of postgres, password of password and database hostname of localhost:

default: &default
  adapter: postgresql
  encoding: unicode
  host: localhost
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
development:
  <<: *default
  database: rails_postgres_app_development
  username: postgres
  password: password
test:
  <<: *default
  database: rails_postgres_app_test
  username: postgres
  password: password
production:
  <<: *default
  database: rails_postgres_app_production
  username:  rails_postgres_app
  password: <%= ENV['POSTGRES_PASSWORD'] %>

Then in your terminal, run:

rake db:create

NOTE: You should use environmental variables to prevent your password from being exposed in code files for the production application, this can be done with the figaro gem or some host platform-specific method.

To use PostGIS: Install postGIS: 1) https://postgis.net/install/ 2) Depending on your Postgres installer, it might be possible to install it along with your Postgres

Then change the adapter in your database.yml file to PostGIS:

default: &default
  adapter: postgis
  encoding: unicode
  host: localhost
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
development:
  <<: *default
  database: rails_postgres_app_development
  username: postgres
  password: password
test:
  <<: *default
  database: rails_postgres_app_test
  username: postgres
  password: password
production:
  <<: *default
  database: rails_postgres_app_production
  username:  rails_postgres_app
  password: <%= ENV['POSTGRES_PASSWORD'] %>