DEV Community

Cover image for Install and use Postgres in WSL
Sabrina Pereira
Sabrina Pereira

Posted on • Updated on

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=#
Enter fullscreen mode Exit fullscreen mode

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:~$
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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=#
Enter fullscreen mode Exit fullscreen mode

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

psql -U postgres -q mydb < <file-path/file.sql>
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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"

Top comments (7)

Collapse
 
cavein254 profile image
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

Collapse
 
sfpear profile image
Sabrina Pereira

Thank you! And good catch, fixed it 😊

Collapse
 
mtoprak53 profile image
Mehmet Toprak

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

Collapse
 
sfpear profile image
Sabrina Pereira

Glad it was useful :)

Collapse
 
noahtah profile image
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

Collapse
 
is2ac profile image
IS2aC • Edited

Great article !

Collapse
 
golamshahanoor profile image
Golam Shahanoor

Great Job, really helped me to get started 🫡