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:
- postgres is one of the default PostgreSQL database
- postgres is also the default admin user in PostgreSQL
- 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.
Top comments (0)