Postgresql Setup on Ubuntu (WSL) for a Rails app

I’ve had a few students run into issues in their Ubuntu WSL environment with Postgres not working properly. Part of this seems to be that there’s an older version of postgres installed but not active.

Making sure the service is running

sudo service postgresql status

If you see a message saying online, you can move on here as things are already working properly. If not, keep going through this section.

So, if it’s not running, let’s start it up:

sudo service postgresql start

If you’re getting a message saying the port is down and want to upgrade, we’ll first want to remove any previous installations.

sudo apt-get --purge remove postgresql\*

This is a nuclear option, so don’t do this if you want to keep previous versions installed or you have databases you don’t want potentially cleared out. There will be a prompt asking if you if you want to remove postgres database directories. If you’ve never successfully configured postgres on wsl and used it in previous projects, you can go ahead and do that at this point. If not, you can skip it.

Installation

After you’ve removed previous installations, you can install the latest version.

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt update
sudo apt install postgresql postgresql-contrib

Now, you’ll want to restart the postgresql service so that the database will be running every time you boot Ubuntu.

sudo service postgresql restart

And check that it actually started by running

sudo service postgresql status

You should see something like this:

13/main (port 5433): online

This is normal since vs 11 of Postgres. Once you get a message saying it’s online you can move on. Note that the port is imporant here so keep an eye on what port your service is running on. Rails will assume the DB port is 5432, so you’ll need to update the database configuration later on to get a successful database connection.

Creating a Role

sudo -u postgres createuser --interactive

Then enter your ubuntu username and say y for making your account a superuser.

Creating the Database

To get this working properly, you’ll need to make a change to the config/database.yml file in rails. Go to the default configuration and add a key for port and set it to 5433. It should look like this when you’re done.

default: &default
  adapter: postgresql
  encoming: unicode
  port: 5433

  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>

rails db:create

Should now work to generate the developement and test database.

To test it you can check:

sudo -u postgres psql

Then in the psql prompt:

\l

You should see your databases there and they should be owned by your ubuntu username.