Install and use Postgres in WSL

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:

1. Enter the command: sudo passwd postgres
2. You will get a prompt to enter your new password.
3. Close and reopen your terminal.

You can access psql 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.

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 and the sources listed below.

Sources

WSL Documentation: Install PostgreSQL
Postgres documentation: 1.3. Creating a Database
StackOverflow: PostgreSQL: Why psql can't connect to server?
StackExchange: How do I list all databases and tables using psql?
StackOverflow: fatal role "root" does not exist
POSTGRESQL ON WSL2 FOR WINDOWS: INSTALL AND SETUP
StackOverflow: password authentication failed for user "postgres"

Comments

[John Muthua]

@sfpear great article. You have a typo on sudo -u postrges psql that generates an error. It should be sudo -u postgres psql

[Sabrina Pereira]

Thank you! And good catch, fixed it 😊

[Mehmet Toprak]

Thanks, that's what I needed. Clear explanation.

[Sabrina Pereira]

Glad it was useful :)

[Noah Tah]

thanks for helping me get set up! thankfully i was learning a bit of vim keybinds previous to trying this or I would be even more lost when we opened vi

[IS2aC]

Great article !

[Golam Shahanoor]

Great Job, really helped me to get started 🫡