Setting up Postgres on WSL

CodeLemur -

So for a recent project, I've been setting up Postgres on my laptop (Ubuntu 18.04) and desktop (Windows running Windows Subsystem Linux 16.04), making sure to be able to develop on both.

I followed a few different guides, but this is what ended up working for me:

  • First I installed postgres  I did this following postgres' guide for linux installations In short I updated my /etc/apt/sources.list.d/pgdg.list file (I needed to use sudo, not sure if that was right)
  • added the deb URL added the key
  • ran sudo apt-get update
  • ran sudo apt-get install postgresql-12 (change the number at the end if you're installing a different version of postgres.  
  • Created a cluster using pg_createcluster 12 codelemur --start and pg_lsclusters to verify the cluster was up.
Ver Cluster Port Status Owner    Data directory              Log file
12  main    5432 online postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log
  • I can't totally remember, but I think I had to create a new db user from my postgres user account. I think it was something like:
  • sudo -i postgres and createuser codelemur in order to create my default user tied to my ubuntu username. Then I probably had to grant permissions with ALTER ROLE codelemur SUPERUSER;. But again, can't totally remember this part. The docs may help
  • I then ran createdb choreo (a project I'm working on) to make the new db I wanted
  • I ran psql -d choreo with my current user

At this point, I had logged into choreo with my default username, and was able to create tables and whatnot, so we're good from the command line! Great!

That being said, next I wanted to figure out how to run pgAdmin 4, a GUI for postgres db management.

This took a little more work, as I was getting strange errors like so:

Unable to connect to server:

could not connect to server: Connection refused (0x0000274D/10061)
Is the server running on host "localhost" (::1) and acceptingTCP/IP connections on port 5432?
FATAL: password authentication failed for user "codelemur"
FATAL: password authentication failed for user "codelemur"

This persisted despite the fact that I could see the cluster running, and had repeatedly run \password on the user I was working with :/. I got it to work by using 127.0.0.1 in place of localhost, so that'll maybe be a story for another day!

And that's all, folks! I was set up and good to go again.

Hope this helps in case anyone happens to search for these exact error messages, but otherwise, thanks for checking it out.