Technical Reference
Command Line Access (SSH Tunnel from laptop)
1) From a local terminal window open up an SSH tunnel through mote to our postgres server:
$ ssh -L 5555:postgres:5432 -p 443 username@mote.cs.vassar.edu
where username
is the name of your CS account.
Then from a second local terminal window access our postgres server directly from the command line:
$ psql -h localhost -p 5555 -U username -d dbname
where username
is (again) the name of your CS account, and dbname
is the name of the database (e.g., cap).
Command Line Access (from Linux workstation)
Our postgres server can be accessed from the command line via psql
(the PostgreSQL interactive terminal):
$ psql -h postgres -U username -d dbname
where username
is name of your CS account and dbname
is the name of your database.
You will be prompted to enter your password. You should enter your Linux CS account password, not your Vassar password.
For more information about the psql
command see: https://www.postgresql.org/docs/11/app-psql.html
For example:
mlsmith@mote:~$ psql -h postgres -U mlsmith -d university Password for user mlsmith: psql (10.10 (Ubuntu 10.10-0ubuntu0.18.04.1)) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type "help" for help. university=#
From within psql
there, a few useful commands are:
\dt -- to display metadata for the tables in the database \d tablename -- to display metadata for a particular table in the database \? -- to display help on psql command line options \h -- to display help on SQL commands \q -- to quit out of psql and return to the linux prompt ($)
For example:
university=# \dt List of relations Schema | Name | Type | Owner --------+------------+-------+--------- public | advisor | table | mlsmith public | classroom | table | mlsmith public | course | table | mlsmith public | department | table | mlsmith public | instructor | table | mlsmith public | prereq | table | mlsmith public | section | table | mlsmith public | student | table | mlsmith public | takes | table | mlsmith public | teaches | table | mlsmith public | time_slot | table | mlsmith (11 rows) university=# \d instructor Table "public.instructor" Column | Type | Collation | Nullable | Default -----------+-----------------------+-----------+----------+--------- id | character varying(5) | | not null | name | character varying(20) | | not null | dept_name | character varying(20) | | | salary | numeric(8,2) | | | Indexes: "instructor_pkey" PRIMARY KEY, btree (id) Check constraints: "instructor_salary_check" CHECK (salary > 29000::numeric) Foreign-key constraints: "instructor_dept_name_fkey" FOREIGN KEY (dept_name) REFERENCES department(dept_name) ON DELETE SET NULL Referenced by: TABLE "advisor" CONSTRAINT "advisor_i_id_fkey" FOREIGN KEY (i_id) REFERENCES instructor(id) ON DELETE SET NULL TABLE "teaches" CONSTRAINT "teaches_id_fkey" FOREIGN KEY (id) REFERENCES instructor(id) ON DELETE CASCADE university=#
The other useful–and cool–thing you can do from inside psql
is issue SQL commands. For example:
university=# select * from instructor; id | name | dept_name | salary -------+------------+------------+---------- 10101 | Srinivasan | Comp. Sci. | 65000.00 12121 | Wu | Finance | 90000.00 15151 | Mozart | Music | 40000.00 22222 | Einstein | Physics | 95000.00 32343 | El Said | History | 60000.00 33456 | Gold | Physics | 87000.00 45565 | Katz | Comp. Sci. | 75000.00 58583 | Califieri | History | 62000.00 76543 | Singh | Finance | 80000.00 76766 | Crick | Biology | 72000.00 83821 | Brandt | Comp. Sci. | 92000.00 98345 | Kim | Elec. Eng. | 80000.00 (12 rows) university=#