Windows Subsystem for Linux (WSL)

Writing here for sharing and also for my personal reference as I have very poor memory and tend to forget some commands.

For windows users, you can download WSL (Windows Subsystem for Linux) on powershell. WSL lets developers run a Linux environment directly on windows Read more here

After installing, in Command Prompt or PowerShell, type wsl and it will open the WSL terminal window:

wsl

To open a VS Code, type code . from the ubuntu terminal.

code .

Go back to root directory

cd /

Mounting

/mnt is a standard subdirectory of the root directory on Linux. The /mnt/c/ directory basically means mounting on Windows C drive. Therefore if u see this path, it means u are accessing a folder that resides in Windows. Read more here

/mnt/c/<file_path>

sudo means "super user do" - a Linux command that allows you to executedddd programs as a super user (aka root user)

sudo

Before we go into installing new packages, lets cover some of the essential commands to help with troubleshooting.
Key terminologies to know:

1. postgres is one of the default PostgreSQL database
2. postgres is also the default admin user in PostgreSQL
3. psql - interactive commandline tool

Basic Commands to Know

To check status of database:

sudo service postgresql status

To start running your database

sudo service postgresql start

To stop running your database

sudo service postgresql stop

Installation of Packages

To update & upgrade our Ubuntu to latest packages:

sudo apt-get update && sudo apt-get upgrade

To install package such as node, python or postgresql

sudo apt-get install <package>

To check version, e.g. postgreSQL, you type either one of this.

psql -V
psql --version

To see all the command documentations, you can either type one of the following commands:

-?
--help
man postgres

Show information about all PostgreSQL clusters

 pg_lsclusters

Default Databases of Postgres

Most Postgres servers have three databases defined by default: template0, template1 and postgres. (yes it is a little confusing for first-timer, default user is postgres and one of the default database name is also postgres)

The default admin user, postgres, needs a password assigned in order to connect to a database. By default, the postgres user has no password and can hence only connect if ran by the postgres system user. Therefore, we have to set a password for the postgres user:

sudo passwd postgres

You will see the command line change to postgres=#

To exit the postgres=#, enter \q or Ctrl+D:

\q

To switch from root user to the default admin user, postgres.

su - postgres

You will see something like this. You can now run commands as the PostgreSQL superuser.

postgres@DESKTOP-ORMQU9O:~$

To create a new user:

createuser --interactive --pwprompt

You will be prompt the following:

postgres@DESKTOP-ORMQU9O:~$ createuser --interactive --pwprompt
Enter name of role to add: kenji
Enter password for new role: 
Enter it again: 
Shall the new role be a superuser? (y/n) 

I (postgres user in this case) have created a new user called kenji.

To create a new database called simple_blog:

createdb simple_blog

To destroy a database:

dropdb simple_blog

Using psql Interactive Shell as default postgres user

To run Postgres with psql interactive shell (you can also just type psql), it will prompt you for a password:

sudo -u postgres psql

You will be greeted with the following:

psql (12.9 (Ubuntu 12.9-0ubuntu0.20.04.1))
Type "help" for help.

postgres=#

Press Ctrl+D to exit and we will return to this:

postgres@DESKTOP-ORMQU9O:~$

Here we can switch to the new user we have just created:

postgres@DESKTOP-ORMQU9O:~$ su - kenji

And you will then see this instead (after password prompt):

kenji@DESKTOP-ORMQU9O:~$ 

We can enter the psql command line by typing:

kenji@DESKTOP-ORMQU9O:~$ psql

And you will see this:

psql (12.9 (Ubuntu 12.9-0ubuntu0.20.04.1))
Type "help" for help.

kenji=#

psql is an interactive terminal program that allows you to interactively enter, edit and execute SQL commands. Remember to put ';' to end the SQL statement.

With psql shell open, to see what user accounts have been created:

kenji=# \du

To see all other available backslash commands:

kenji=# \?

To exit the help log, type '\q' and press Enter:

kenji=# \q

We can also check connection info to see port which database, which user and which port you are connected to:

kenji=# \conninfo

You will see this:

You are connected to database "kenji" as user "kenji" via socket in "/var/run/postgresql" at port "5432".

Start psql to specific database you have created

When not in psql shell, You can also startup psql to a specific database with the below commaned:

psql simple_blog

You will be greeted with the following:

psql (12.9 (Ubuntu 12.9-0ubuntu0.20.04.1))
Type "help" for help.

simple_blog=#

To see all database:

\l

You can write SQL statement directly, remember to end with semi-colon:

simple_blog=# CREATE TABLE my_table (
simple_blog(# first integer not null default 0,
simple_blog(# second text)
simple_blog-# ;
CREATE TABLE

Now if you type '\d my_table', you will see the created table

simple_blog=# \d my_table
              Table "public.my_table"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 first  | integer |           | not null | 0
 second | text    |           |          | 

As always, to learn more, it is best to read the documentation, as some solutions shared by others might be outdated.