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/16/main/

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

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

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

Start the server

Create unix_socket_directory docs

$ sudo mkdir /var/run/postgresql
$ sudo chown postgres /var/run/postgresql

The Debian way...

As of January 27, 2024, The Art of Postgresql data is in Postgres 16 so start it like this after creating the run directory that seems to get destroyed after restarting

sudo -u postgres  pg_ctlcluster --foreground 16 main start

In another terminal psql -p 5434 connects to database david. Use set search_path <schema> to explore f1db or chinook schemas.

Postgres docs on starting the server

Notes from first time setup

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

Install Deps for TAOP

1. sudo apt-get install postgresql-16-hll postgresql-16-ip4r
2. Run make as postgres user