You need to start postgres every time you start bash. Please use:
sudo service postgresql start
Expect to see something like:
Starting PostgreSQL 10 database server
, when succesfully started.
Run psql client:
psql
By default it tries to connect to db with the name of the user, so for user groozin it looks for db name groozin and fails if cannot find.
psql: FATAL: database "groozin" does not exist
To connect to enceladus db use the follwing:
psql enceladus
If correct you will see this:
psql (10.1)
Type "help" for help.
enceladus=#
Run \conninfo to check connection parameters:
enceladus=# \conninfo
You are connected to database "enceladus" as user "groozin" via socket in "/var/run/postgresql" at port "5432".
Ok we are there.
Start with listing tables and schemas using \dt for tables and \dn for shemas.
List of shemas:
enceladus=# \dn
List of schemas
Name | Owner
--------+----------
import | groozin
public | postgres
(2 rows)
List of tables:
enceladus=# \dt
List of relations
Schema | Name | Type | Owner
--------+--------------+-------+---------
public | event_types | table | groozin
public | events | table | groozin
public | mission_plan | table | groozin
public | requests | table | groozin
public | spass_types | table | groozin
public | targets | table | groozin
public | teams | table | groozin
(7 rows)
There are 2 schemas public (which is default) and import that is custom created. But only tables from public are listed. This is because of search_path runtime variable. It contains ordered list of schemas to look into when performing any query/operation without direct schema inclusion.
enceladus=# show search_path;
search_path
-----------------
"$user", public
(1 row)
Note: show displays contents of the runtime variable requested (to display all variables use show all).
Only 2 schemas are part of this lookup: username (will be groozin in this case) and public (default). We need to add import at the ens so it is included when subsequent queries are made.
enceladus=# set search_path to "$user",public,import;
SET
enceladus=# show search_path;
search_path
-------------------------
"$user", public, import
(1 row)
Let's re run \dt.
enceladus=# \dt
List of relations
Schema | Name | Type | Owner
--------+--------------+-------+---------
import | master_plan | table | groozin
public | event_types | table | groozin
public | events | table | groozin
public | mission_plan | table | groozin
public | requests | table | groozin
public | spass_types | table | groozin
public | targets | table | groozin
public | teams | table | groozin
(8 rows)
Now import schema is included with one additional db.
To list all realations, not only tables from db use \d.
enceladus=# \d
List of relations
Schema | Name | Type | Owner
--------+---------------------+----------+---------
import | master_plan | table | groozin
public | event_types | table | groozin
public | event_types_id_seq | sequence | groozin
public | events | table | groozin
public | events_id_seq | sequence | groozin
public | mission_plan | table | groozin
public | mission_plan_id_seq | sequence | groozin
public | requests | table | groozin
public | requests_id_seq | sequence | groozin
public | spass_types | table | groozin
public | spass_types_id_seq | sequence | groozin
public | targets | table | groozin
public | targets_id_seq | sequence | groozin
public | teams | table | groozin
public | teams_id_seq | sequence | groozin
(15 rows)
To list columns from db use \d dbname.
enceladus=# \d events
Table "public.events"
Column | Type | Collation | Nullable | Default
---------------+--------------------------+-----------+----------+------------------------------------
id | integer | | not null | nextval('events_id_seq'::regclass)
time_stamp | timestamp with time zone | | not null |
title | character varying(500) | | |
description | text | | |
event_type_id | integer | | |
spass_type_id | integer | | |
target_id | integer | | |
team_id | integer | | |
request_id | integer | | |
Indexes:
"events_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"events_event_type_id_fkey" FOREIGN KEY (event_type_id) REFERENCES event_types(id)
"events_request_id_fkey" FOREIGN KEY (request_id) REFERENCES requests(id)
"events_spass_type_id_fkey" FOREIGN KEY (spass_type_id) REFERENCES spass_types(id)
"events_target_id_fkey" FOREIGN KEY (target_id) REFERENCES targets(id)
"events_team_id_fkey" FOREIGN KEY (team_id) REFERENCES teams(id)