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:
- Open your WSL terminal
- Update your Ubuntu packages:
sudo apt update
- Once the packages have updated, install PostgreSQL (and the -contrib package which has some helpful utilities) with:
sudo apt install postgresql postgresql-contrib
- 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:
- Enter the command:
sudo passwd postgres
- You will get a prompt to enter your new password.
- 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
- You have to change the file
postgresql.conf
. Just uncomment the line forlisten_address
and change it tolisten_address = '*'
. - 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)
@sfpear great article. You have a typo on
sudo -u postrges psql
that generates an error. It should besudo -u postgres psql
Thank you! And good catch, fixed it 😊
Thanks, that's what I needed. Clear explanation.
Glad it was useful :)
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
Great article !
Great Job, really helped me to get started 🫡