How to configure PostgreSQL on the Windows Subsystem for Linux (WSL) on VS Code

I’ve grappled more than once with trying to use PostgreSQL. For one, I wanted to deploy my Ruby on Rails project, but this was not possible with the database I was forced to use, SQLite3.

In this respect, Mac users have the luxury of Homebrew which removes all the heartache of configuring Postgres correctly. Windows users, on the other hand, are left in the dark about the correct approach. Are we supposed to install Postgres like normal, fiddle with pgAdmin 4 and hope for the best?

Hours upon hours were spent trying to unravel this mystery, and the number of errors I hopelessly Googled are indicative of that. So, I solved it — what’s the solution?

The Visual Studio Code PostgreSQL extension, by Microsoft

1. Update Ubuntu: sudo apt update
2. Get the PostgreSQL extension (by Microsoft) in the VSCode Marketplace.
3. Install Postgres: sudo apt install postgresql postgresql-contrib
4. Verify version: psql --version

Once you get a version number, you’re already very far through. The next thing to do is to be able to have control over whether the database is running or not, using sudo service postgresql [start/stop] to either start or stop the server. To check the status, type status at the end instead.

The next thing you’ll need to do, however, is set a default password for the default user that’s set up for you: the imaginative username, “postgres”. Run sudo passwd postgres to set this yourself.

You can open up the psql command line, too: sudo -u postgres psql

During this process, I repeatedly got an error about the username I had selected months ago not being recognised by Postgres. The username “postgres” was doing all of the work, but the username I had selected for WSL was obviously not “postgres”. You might notice that you are able to start the database but unable to do much else from here.

Here is what to do if you have this issue.

1. Open up the psql command line (using the code in the paragraph above this one) and type \du.

Here, if you see “postgres” as the sole value under the “Role name” column, this is your problem.

2. Add your own WSL username as a superuser: CREATE USER [your WSL username] SUPERUSER.

3. Create a password for this superuser: ALTER USER [username] PASSWORD [password]

Finally, press Ctrl+Alt+P in VSCode and type “PostgreSQL: Connect”.

• You can see your existing databases by typing \l into the psql shell.
• If you are hosting locally, then the server name is localhost.
• If you want to connect to the default database, leave the database name field blank, but otherwise use a database name which you found from the first bullet point (or create one in the shell!)
• You can use either your WSL username or “postgres” at this point for the username. The next field asks for the password, which you set earlier.
• It finally requests the port. This is usually 5432.

From here, you should be done. A confirmation will show up in the lower-right of the VSCode instance detailing the host, username, and database. If you run into any issues, please leave a comment on this post; I know the pain of trying to configure this!