Introduction

This article discusses psql, a terminal-based front-end to PostgreSQL, which lets you enter queries interactively, issue them to PostgreSQL, and see the query results. Alternatively, queries can be processed from a file. In addition, psql provides many meta-commands and shell-like features, to facilitate writing scripts and automating a wide variety of tasks.

 

See this documentation for general instructions and available options:

 

http://www.postgresql.org/docs/9.2/static/app-psql.html

 

This article has the following sections:

 

• Displaying Databases with psql
• Accessing a Database
• Listing the Database Data Tables
• Exploring a Data Table
• Using the select Command to Query a Data Table

---

Displaying Databases with psql

1. SSH into the Master Appliance, entering your credentials to gain access. The default username/password are sysadmin/sysadmin.

    

2. Drop from the Panshell (sysadmin user) into a Bash shell (root user) with the command 'shell-escape'.

    

3. To display the databases, enter 'psql -l'. The following listing shows the 'appliance' database.

bash-4.1# psql -l
                                  List of databases
   Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 appliance | root     | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 smr_1     | root     | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 smr_2     | root     | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
                                                             : postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
                                                             : postgres=CTc/postgres
 tmp_db    | root     | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(7 rows)

bash-4.1#

 

---

Accessing a Database

To access the database, enter 'psql -d appliance'. The prompt will change to the name of the database.

 

bash-4.1# psql -d appliance
psql (8.4.13)
Type "help" for help.

appliance=#

 

---

Listing the Database Data Tables

To list the data tables for the current database, enter '\dt'.

 

appliance=# \dt
                 List of relations
 Schema |           Name            | Type  | Owner
--------+---------------------------+-------+-------
 public | alert                     | table | root
 public | apilog                    | table | root
 public | backup_policy             | table | root
 public | backup_target             | table | root
 public | backup_target_smr         | table | root
 public | command                   | table | root
 public | config                    | table | root
 public | datastore                 | table | root
 public | folder                    | table | root
 public | folder_policy             | table | root
 public | hv_config                 | table | root
 public | hv_info                   | table | root
 public | license                   | table | root
 public | msm_alert                 | table | root
 public | msm_client                | table | root
 public | node                      | table | root
 public | perf                      | table | root
 public | smartmotion_stats         | table | root
 public | smartmotion_status        | table | root
 public | smartmotion_status_folder | table | root
 public | vdisk                     | table | root
 public | virtual_machine           | table | root
 public | vm_config                 | table | root
 public | vm_stats                  | table | root
(24 rows)

appliance=#

---

Exploring a Data Table

To explore a data table, enter the \d command followed by the 'Name' of the data table.

 

appliance=# \d alert
                                        Table "public.alert"
    Column     |          Type           |                        Modifiers
---------------+-------------------------+----------------------------------------------------------
 entry_id      | integer                 | not null default nextval('alert_entry_id_seq'::regclass)
 gen_id        | integer                 |
 alert_id      | character varying(64)   |
 object_id     | character varying(1024) |
 alert_time    | double precision        |
 hv_host       | character varying(256)  |
 vhost         | character varying(256)  |
 severity      | character varying(64)   |
 message       | character varying(1024) |
 cleared_by    | character varying(256)  |
 cleared_time  | double precision        | default (0)::double precision
 node_uuid     | character varying(64)   |
 managed_alert | integer                 | default 1
 posted        | integer                 | default 0
Indexes:
    "alert_pkey" PRIMARY KEY, btree (entry_id)
    "ix_alert_id" btree (alert_id, node_uuid, alert_time)

appliance=#

 

---

Using the select Command to Query a Data Table

Now that you have some idea of the columns availible in the data table, you can use the select commnd to query the table, as shown below.

 

Notes: 

• Always end your queiry with a semicolon (';') or the command will not be terminated.
• The aseterisk ('*') used below to specify the column(s) to display is a wildcard that says to display (select) all columns in the folder datatable.
  

If you only want to see ceertain columns, you can specify them. A command to do so, and its output, would look like this.

 

appliance=# select uuid, name, cbt_reset_schedule from folder;
                 uuid                 |     name     |                   cbt_reset_schedule
--------------------------------------+--------------+--------------------------------------------------------
 ae27ee15-6af1-4404-a10d-64ee8afe4941 | Monday Full  | {"hr":1,"days":[],"cycle":[0],"week":["mon"],"min":0}
 c6af8cc9-820c-46f1-bcad-8b8d7a4db405 | Friday Full  | {"hr":1,"days":[],"cycle":[0],"week":["fri"],"min":0}
 02802bf2-1bab-4b3f-b4be-d4e84aad03b6 | 10.20.230.15 | {"min":30,"hr":3,"week":["sun"],"cycle":[0],"days":[]}
 e787a216-0c68-42f6-b7cf-a7d75d4b5b10 | test         | {"min":30,"hr":3,"week":["sun"],"cycle":[0],"days":[]}
(4 rows)

appliance=#

 

---

What's Next?

Importing a Database from a vmPRO Support Bundle >