PostgreSQL on Debian in WSL

postgres_wsl.md

PostgreSQL on Debian buster in WSL

Install

First install Windows Terminal from the Microsoft Store on Windows 10 https://docs.microsoft.com/en-us/windows/terminal/

Then use that to install postgres in Debian WSL

sudo apt-get install postgres

Debian docs for postgresql

• https://wiki.debian.org/PostgreSql

• Link contains location of config files, binaries, and data files

• Debian has replaced pg_ctl with their own perl script pg_ctlcluster

• configs: /etc/postgresql/11/main/

• binaries: /usr/lib/postgresql/11/bin # not on the PATH, contains pg_ctl

• data: /var/lib/postgresql/11/main/ # owned by user postgres

• logs: /var/log/postgresql/postgresql-11-main.log # tail as user david seems to work

Start the server

The Debian way...

sudo -u postgres  pg_ctlcluster 11 main start

Postgres docs on starting the server https://www.postgresql.org/docs/11/server-start.html

Create a user

$ sudo su - postgres
$ createuser --pwprompt david
$ createdb -O david david_db
$ exit

Login

psql -d david_db -h localhost -U david

Login without a password

echo 'localhost:5432:david_db:david:password' >> ~/.pgpass
chmod 600 ~/.pgpass

Now connect without entering a password

psql -d david_db -h localhost -U david

Allow COPY command for user David

https://www.postgresql.org/docs/11/role-membership.html

david@DAVID-PC:/mnt/d/a-curious-moon/curious_data$ sudo su postgres
postgres@DAVID-PC:/mnt/d/a-curious-moon/curious_data$ psql
psql (11.7 (Debian 11.7-0+deb10u1))
Type "help" for help.

postgres=# grant pg_read_server_files to david;
GRANT ROLE
postgres=# \q
postgres@DAVID-PC:/mnt/d/a-curious-moon/curious_data$ exit