PostgreSQL : A Small Introduction

PostgreSQL is an advanced object-relational database management system
that supports an extended subset of the SQL standard, including
transactions, foreign keys, subqueries, triggers, user-defined types
and functions. It is also one of the most used databases in the industry for production and deployment.

Installation:

Here we’ll install PostgreSQL 9.5.11 on Ubuntu 16.04 system.

Ubuntu’s default repositories contain Postgres packages, so we can install these easily using the apt-get

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

It’ll create postgres service

➜  ~ sudo systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
   Active: active (exited) since Thu 2018-02-22 16:12:45 IST; 1 day 20h ago
  Process: 1325 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 1325 (code=exited, status=0/SUCCESS)
   CGroup: /system.slice/postgresql.service
Feb 22 16:12:44 anton systemd[1]: Starting PostgreSQL RDBMS...
Feb 22 16:12:45 anton systemd[1]: Started PostgreSQL RDBMS.

running on port 5432

➜  ~ sudo netstat -nlp | grep postgres
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      1203/postgres

Installed!

Using PostgreSQL:

1. The installation procedure created a user account called postgres that is associated with the default Postgres role. In order to use Postgres, log in using posgres user.

sudo -i -u postgres

2. prompt to postrges shell

psql
or to a particular db
psql -d <dbname>

3. Exit from postgres shell

postgres=# \q

4. Create new user

sudo -u postgres createuser <username>

5. Create a password for this user from postgres shell

$ sudo -u postgres psql
postgres=# alter user <username> with password '<password>';

6. Create database

sudo -u postgres createdb <dbname>

7. Grant privileges on database

postgres=# grant all privileges on database <dbname> to <username> ;

8. Log in using new user

To log in with new user, we’ll need a Linux user with the same name as your Postgres role and database. If there is no matching Linux user available, we need tocreate one

sudo adduser <username>

login

sudo -i -u <username>

prompt to the db

➜  ~ psql -d <dbname>
psql (9.5.11)
Type "help" for help.
<dbname>=>

9. See database connection info

<dbname>=> \conninfo
You are connected to database <dbname> as user <username> via socket in "/var/run/postgresql" at port "5432".

10. Well done we’ve created a postgres database and user and ready to create new tables.

Some of the helper commands :~

To access root :- sudo -i
Now to access as another user :- su - <username>/postgres
To access PostgreSQL db shell as that user :~ psql -d <db-name>
To see all the tables :~ \dt
SQL Queries to query the database
To END :~ \q
Then Simply :~ exit  #Till you reach back to orignal User.
Add extension to your PostgreSQL DB after entering it as postgres:~ CREATE EXTENSION citext;

Resources

A complete guide to PostgreSQL

PostgreSQL Tutorial