This article provides necessary commands to access TIBCO Data Virtualization PostgreSQL Repository which is bundled with TDV using the Command Prompt tool. This is a fast and convenient way for accessing all repository databases to know its table contents, check if the table size are bloating and for general information. 

This article outlines the necessary commands for accessing TIBCO Data Virtualization's Repository database contents

All supported Operating Systems

1.  Open command prompt or Terminal and navigate to the TDV Repository installation folder located at [TDV_Install_Dir]\repository\bin.
2.  To run the psql utility, execute the below command:
     TDV Server on Windows: psql.exe -h <host> -U <user> -p <port> -d <dbname>
     TDV Server on Linux: psql -h <host> -U <user> -p <port> -d <dbname>

Host: TDV Server Hostname
Type of Users (Owner): cisrepo, tutorial, root
Port: TDV Base port + 8
List of databases : cisrepo, inventory, orders, postgres, template0, template1 

List of database names with subsequent users (Owner): 


Example for reference:   psql -h localhost -U root -p 9408 -d postgres

When prompted for a password, refer to the below note:
a. User 'root' password is chosen during TDV installation.
b. User 'tutorial' password is tutorial.
c. User 'cisrepo' password is encrypted and stored in [TDV_Install_Dir]/conf/server/server_values.xml and in the postgres databases (data and data_cache). This password needs to be decrypted using a decryption tool. Easier way to access the 'cisrepo' database is with user 'root'.

3. Once connected to the database, run below commands or queries to review the table contents. For complete list of commands, go to help section by running \?.  

cisrepo=# \l                                                                                                                   Show all the databases in the repository
cisrepo=# SELECT nspname || '.' || relname AS "relation",                                          Find top 20 tables with maximum table size
pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND C.relkind <> 'i'
AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 20; 
cisrepo=# \c <database_name>                                                                                   Connect to a different database                               
postgres=# show search_path;                                                                                     Show the environment variable search_path           
postgres=# set search_path to cisrepo;                                                                        Set search_path to database cisrepo   
orders=# \dt[+]                                                                                                               List all the tables in the current search path(sign '+' is optional)  
orders=# select count(*) from [table_name];                                                                 Find the total numbers of rows for a particular table   
orders=# \?                                                                                                                    Show help 
orders=# \q                                                                                                                    Quit psql  

Screenshot for reference: