Install and use Postgres in WSL
Tags: postgres, wsl, tutorial, database
Published: 2023-02-24 23:42:52

To keep this short and sweet, I'll assume you know your way around bash, Postgres and already have WSL installed.

## Install Postgres
To install Postgres and run it in WSL, all you have to do is the following:

1. Open your WSL terminal
2. Update your Ubuntu packages: `sudo apt update`
3. Once the packages have updated, install PostgreSQL (and the -contrib package which has some helpful utilities) with: `sudo apt install postgresql postgresql-contrib`
4. Confirm installation and get the version number: `psql --version`

## Set a password 
The default admin user, `postgres`, needs a password assigned in order to connect to a database. To set a password:

5. Enter the command: `sudo passwd postgres`
6. You will get a prompt to enter your new password.
7. Close and reopen your terminal.

You can access [psql](https://www.postgresql.org/docs/current/app-psql.html) directly using `sudo -u postgres psql`. You should see your prompt change to:

```
postgres=#
```
To change databases just use `\c mydb`.

You can also use `su - postgres` to go into the postgres user. Here you use the password you set up above. Your prompt should change to:
```
postgres@mycomputername:~$
```
From here, you can use psql to login into any database.


## Creating a database

To create a database, just use the following command:

```
createdb mydb
```
You can change `mydb` to whatever name you want to give your database. To access it, just enter `psql mydb` in the command line. Now your prompt should look like this:

```
mydb=#
```

To create tables in a database from a file, use the following command:

```
psql -U postgres -q mydb < <file-path/file.sql>
```

## Useful commands 

- `\l` lists all databases. Works from any database.
- `\dt` lists all tables in the current database.
- `\c <db name>` switch to a different database.

## Use `psql` without sudo
Create a Postgres user with the same name as your Ubuntu username using the following command. And when it asks, make the new role a superuser.
```
$ sudo -u postgres createuser --interactive
Enter name of role to add: sabrina
Shall the new role be a superuser? (y/n) y
```

Then you have to change the `pg_hba.conf` file. It will be under `/etc/postgresql/<postgres-version>/main`. You will need sudo to edit this file.

```
sudo vi pg_hba.conf
```
Scroll to the bottom of the files, now change where it says `peer`, to `trust`, like so:

```
# Database administrative login by Unix domain socket
local   all             postgres                                trust

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
```

## Accessing your database from Windows
1. You have to change the file `postgresql.conf`. Just uncomment the line for `listen_address` and change it to `listen_address = '*'`.
2. Set up a password for postgres admin user `sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'postgres';"`. This will change the password to postgres and this is what you use when connecting to a database. The password you set during install is for the postgres Ubuntu user.

Now you should be able to connect to your database from Windows using software such as [TablePlus](https://tableplus.com/windows).


## Need more?
This is just a quick overview of what to do to get up and running, for a more in-depth tutorial see [this](https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-16-04) and the sources listed below.

## Sources
[WSL Documentation: Install PostgreSQL](https://learn.microsoft.com/en-us/windows/wsl/tutorials/wsl-database#install-postgresql)
[Postgres documentation: 1.3. Creating a Database](https://www.postgresql.org/docs/current/tutorial-createdb.html#id-1.4.3.4.10.4)
[StackOverflow: PostgreSQL: Why psql can't connect to server?](https://stackoverflow.com/a/73066114)
[StackExchange: How do I list all databases and tables using psql?](https://dba.stackexchange.com/a/1288)
[StackOverflow: fatal role "root" does not exist](https://stackoverflow.com/a/46143801)
[POSTGRESQL ON WSL2 FOR WINDOWS: INSTALL AND SETUP](https://www.cybertec-postgresql.com/en/postgresql-on-wsl2-for-windows-install-and-setup/)
[StackOverflow: password authentication failed for user "postgres"](https://stackoverflow.com/a/13796578)