Postgresql instructions

Postgresql-instructions

# Installing PostgreSql
|~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
| (bash)$ dnf install postgresql postgresql-server postgresql-contrib  |
| (bash)$ # Start PostgreSql database server                           |
| (bash)$ service postgresql initdb                                    |
| (bash)$ service postgresql start                                     |
| (bash)$ # Autostart PostgreSql on Boot                               |
| (bash)$ systemctl start postgresql service                           |
| (bash)$ systemctl enable postgresql service                          |
|~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|

# Accesing psql
|~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
| (bash)$ sudo su - postgres       | (bash)$ psql -h localhost -U postgres  |
| [sudo] password for <Username>:  | psql (9.6.1)                           |
| [postgres@<HostName> ~]$ psql    | postgres=#                             |
| psql (9.6.1)                     |~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
| postgres=#                       |
|~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|

# Creating database
|~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
| (bash)$ createdb -U postgres mydb   |
|~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|

# Connecting database
|~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
| (bash)$ psql -U postgres mydb       | (bash)$ psql -h localhost -d mydb -U postgres |
| psql (9.6.1)                        | psql(9.6.1)                                   |
| mydb=#                              | mydb=#                                        |
|~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|

# [One Way] Creating a new user and assign some db
  (bash)$ sudo adduser postgres_user  # OR `sudo useradd postgres_user` , then `sudo mkdir /home/postgres_user`
  (bash)$ sudo su - postgres
  [sudo] password for <UserName>:
  [postgres@<HostName> ~]$ psql
  psql (9.6.1)
  postgres#
  postgres# CREATE USER postgres_user WITH PASSWORD 'password';
  postgres# CREATE DATABASE my_postgres_db OWNER postgres_user;
  postgres# \q
  [postgres@<HostName> ~]$ exit

# Accessing new user
  (bash)$ sudo su - postgres_user
  [postgres_user@<HostName> ~]$ psql my_postgres_db
  psql (9.6.1)
  my_postgres_db=>
  
# [Other Way] Create a new user
  (bash)$ su - postgres
  [sudo] password for <UserName>:
  [postgres@<HostName> ~]$ createdb mytestdb
  [postgres@<HostName> ~]$ psql mytestdb
  psql (9.6.1)
  mytestdb=# CREATE TABLE employees (employee_id int, first_name varchar, last_name varchar);
  mytestdb=# INSERT INTO employees VALUES (1, 'Jack', 'Sprat');
  mytestdb=# \q
  [postgres@<HostName> ~]$ createuser alison --pwprompt
  Enter password for new role:
  Enter it again:
  [postgres@<HostName> ~]$ psql mytestdb
  psql (9.6.1)
  mytestdb=# GRANT ALL ON employees TO alison;
  mytestdb=# \q
  [postgres@<HostName> ~]$ exit
  (bash)$ psql -U alison -W mytestdb
  
# Create SuperUser
  postgres# CREATE USER pleycpl WITH SUPERUSER LOGIN PASSWORD 'pleycpl';
  
# Config Files
  (bash)$ cat /var/lib/pgsql/data/pg_hba.conf
  local all all           md5
  host  all all 192.      md5
  (bash)$ cat /var/lib/pgsql/data/postgresql.conf
  listen_addresses = '*'
  port = 5432
  
# PgAdmin3 - GUI
  Name: localhost
  Host: localhost
  Port: 5432
  Username: pleycpl
  Password: pleycpl
	        Enter
          
### Editing the prompt
  (bash)$ echo "\set PROMPT1 '%M:%> %n@%/%R%#%x '" > ~/.psqlrc
  (bash)$ # Document all 
	%M refers to the database server's hostname -- is "[local]" if the connection is over a Unix domain socket
	%> refers to the listening port
	%n refers to the session username
	%/ refers the current database
	%R refers to whether you're in single-line mode (^) or disconnected (!) but is normally =
	%# refers to whether you're a superuser (#) or a regular user (>)
	%x refers to the transaction status -- usually blank unless in a transaction block (*)
If logged into a machine with hostname "trident" as user "john" and accessing the database "orange" as a regular user, you would see
	[trident]:5432 john@orange=>

### Source
https://www.digitalocean.com/community/tutorials/how-to-customize-the-postgresql-prompt-with-psqlrc-on-ubuntu-14-04
Create User (https://geekpeek.net/postgresql-create-user/)
https://www.digitalocean.com/community/tutorials/how-to-create-remove-manage-tables-in-postgresql-on-a-cloud-server
https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-14-04