Introduction 

This article shows how to import a database from a vmPRO support bundle. It consists of the following sections:

 

• Importing the Database
• Querying the Database
• Cleaning Up the Database View

---

Importing the Database  

The appliance database dumps important information into /var/ found in the support bundle. The file of interest is psqldump.output. To import this file:

 

1. Copy psqldump.output into /var/upgrade of the local vmPRO appliance. This is the Samba share upgrade. You can copy this using the UNC path in a Windows environment, for example, '\\10.20.230.151\upgrade'.

    

2. Use an SSH client to log into a vmPRO appliance, then drop to a bash shell.

    

3. Create a new database to import the dump into:

    

       bash-4.1# createdb tmp_db

    

4. List the databases to confirm:

    

       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)

    

5. Import the psqldump into the new db. Stout of the command has been truncated for simplicity.     

bash-4.1# psql -d tmp_db < /var/upgrade/psqldump.output
 

.....

 

(1 row)

CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
 setval
--------
 124113
(1 row)

CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
 setval
--------
    260
(1 row)

CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
 setval
......

 

---

Querying the Database

1. Connect to the new database: 

bash-4.1# psql -d tmp_db

psql (8.4.13)

Type "help" for help.

tmp_db=#

 

2. Continue to query the database from the support bundle, as discussed in the Using psql to Interpret the Appliance Database article.

As a working example, consider the 'command' datatable discussed in the Tracing vmPRO Controller Commands article.

 

###Command datatable###

 

---

Cleaning Up the Database View

When you use a text editor to view the text file from the bundle, the columns may be misaligned, leading to confusion. The above example illustrates this well. Everything surrounded by { } brackets really only belongs to the JSON column, effectively flushing everything to the right. This makes it seem like "retain_years": belongs to the 'target_uuid' column. It doesn't.

                                

You can clean things up by doing the following.

 

1. Use the select clause, as shown below. (See http://www.postgresql.org/docs/8.2/static/sql-select.html).

###psql select clause reference###

 

tmp_db=# select cmd_id, target_uuid from command;
 

         cmd_id         |           target_uuid
------------------------+----------------------------------
 registry_update        | 3d040d70c47f11e281080050569e0006
 hv_discover            | 3d040d70c47f11e281080050569e0006
 registry_update        | 3d040d70c47f11e281080050569e0006
 tomato_sync_check      | 3d040d70c47f11e281080050569e0006
 update_backup_policy   | 3d040d70c47f11e281080050569e0006
 vss_query_waiting_vms  | 3d040d70c47f11e281080050569e0006
 tomato_sync_check      | 3d040d70c47f11e281080050569e0006
 update_backup_policy   | 3d040d70c47f11e281080050569e0006
 registry_update        | 3d040d70c47f11e281080050569e0006
 registry_update        | 3d040d70c47f11e281080050569e0006

 

2. You can filter the results further to show even more specific results, using the where clause and its supported operands, as shown here. (See http://www.postgresqltutorial.com/postgresql-where/).

    

   ###psql from clause reference### 

tmp_db=# select cmd_id, target_uuid
tmp_db-# from command
tmp_db-# where cmd_id='gather_support_logs';
       cmd_id        |           target_uuid
---------------------+----------------------------------
 gather_support_logs | 3d040d70c47f11e281080050569e0006
 gather_support_logs | 3d040d70c47f11e281080050569e0006
 gather_support_logs | 3d040d70c47f11e281080050569e0006
 gather_support_logs | 3d040d70c47f11e281080050569e0006
 gather_support_logs | 3d040d70c47f11e281080050569e0006

 

Notes:

• You must use a semicolon to end all statements. Otherwise, the buffer will interpret the command entered all as one big statment.

• If you need to start over, flush the buffer with \r.

 

3. When you're done viewing the temporary database, delete it::

      sudo -u postgres psql -c "DROP DATABASE tmp_db"