Postgres setup in WSL Fedora

step-1.md

Setting up Postgres

Ref: gbraad-fedora/fedora-wsl-genie#1, arkane-systems/genie#98

sudo dnf module reset postgresql -y
sudo dnf module enable postgresql:14
sudo dnf install postgresql-server postgresql

Before installing genie, set systemd to multi-user.target as mentioned in the docs:

sudo systemctl set-default multi-user.target

Install genie using dnf install ./pkg.rpm from Genie releases. Then install psutil using conda install psutil.

Now run genie -i. This will take quite a lot of time on first run. Once done it might show you two different errors:

  UNIT                     LOAD   ACTIVE SUB    DESCRIPTION
● auditd.service           loaded failed failed Security Auditing Service
● systemd-sysusers.service loaded failed failed Create System Users

Ignore it for now. If you run genie -i again, you'd see:

$ genie -i
genie: bottle is already established (systemd running)

This means genie -i is working. Now run:

sudo systemctl status postgresql.service
# yep we won't use `service` command but `systemctl`

You would see an error Failed to get properties: Transport endpoint is not connected. This means we have to run genie -s, wait for few secs, and <ctrl-c> to exit out of it (I believe its my old fish setup acting up and hanging commands at the end otherwise the command would've succeeded w/o any output and I didn't have to do <ctrl-c>). Now on running status command you'd see:

$ sudo systemctl status postgresql.service
○ postgresql.service - PostgreSQL database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql.service; disabled; vendor preset: disabled)
     Active: inactive (dead)

Remember those two errors appearing? Given that we've already ran genie -s and it is working, we can do:

sudo systemctl disable auditd

to remove the first error. The second one would get automatically removed the next time you run genie -i.

Now the most important thing, completely setting up postgres... if you try to start postgres it'll result in error. We need initialize db first before starting the service (and after running genie -s):

sudo postgresql-setup --initdb
sudo systemctl start postgresql.service

We have to run the first command postgresql-setup only once (right after installation).

---

Awesome, right! Now to summarize and re-do what it's needed on subsequent runs:

# ---- PS ----
wsl --shutdown
wsl
# ---- WSL ----
genie -i
genie -s 
# wait for few secs after genie -s and do ctrl-c if the program is stuck
sudo systemctl start postgresql.service
# ^ or do `status`, `stop` whatever

# 👆 use the above term for systemctl operations

# 👇 open a new Windows Terminal tab for wsl and run `kitty &` to do coding on this one

sudo -i -u postgres
psql
\q

# or simply
sudo -u postgres psql
\q

# Create a new user
sudo -u postgres createuser --interactive
# name it same as your username (zlksnk) and make it superuser
# now while logged in into zlksnk shell, do:
psql postgres
# ^ here postgres is the db, you can also do `psql -U user_name db_name` 

step-2.md

Creating a new user and connecting it with TablePlus on Windows

Let's find where config file exists and also create a pwd for newly added user zlksnk:

$ sudo -u postgres psql
psql (14.1)
Type "help" for help.

postgres=# SHOW hba_file;
            hba_file
---------------------------------
 /var/lib/pgsql/data/pg_hba.conf
(1 row)

postgres=# \password zlksnk
Enter new password:
Enter it again:

postgres=# \q

Now let's open this config file:

sudo -iu postgres
vim /var/lib/pgsql/data/pg_hba.conf

Change:

host    all             all             127.0.0.1/32            ident

to:

host    all             all             127.0.0.1/32            md5

Sidenote: We could've used password instead of md5, but supposedly MD5 is more secure than sending plain passwords.

We would've also needed to add a line listen_addresses = '*' in /var/lib/pgsql/data/postgresql.conf but we don't have to as we're only modifying localhost. You can read more about it right above where you made the ident to md5 change.

Now do:

sudo systemctl restart postgresql.service 

Open TablePlus and add a new Postgres connection — give it a name, host must be 127.0.0.1 (and not localhost), skip port (it'll take 5432 as default), put in username, password and database name. Try testing it, it should work.