Smarts NCM: PostgreSQL query output is too large to capture; How do I redirect standard output of a PostgreSQL (Postgres) client shell window to a file?

Products

• VMware

Issue/Introduction

Symptoms:


How do I redirect standard output of a PostgreSQL (Postgres) client shell window to a file in a Smarts Network Configuration Manager (Smarts NCM) environment?
How do I output PostgreSQL (Postgres) query results to a file in a Smarts NCM environment?


PostgreSQL query output in Smarts NCM environment is too large to capture in PostgreSQL client window
Cannot capture PostgreSQL query results in Smarts NCM environment for queries that return a large amount of output

Environment

VMware Smart Assurance - NCM

Cause

All query and command results are echoed to the PostgreSQL client window by default, making it difficult to capture query results, especially for queries that return a large amount of output.

Resolution

You can redirect PostgreSQL standard output to a file and then restore standard output back to the terminal window. This is done as follows:

1. From inside a PostgreSQL client shell window, disable PostgreSQL interactive paging to prevent long query results from being truncated in the output file, This is done by running the following command:
   
   \pset pager
   
    
2. Redirect standard output from the PostgreSQL client to a file by running the following command:
   
   \o <absolute_path><file_name>
   
   Where:
   
   <absolute_path> is the path to which the file will be written
   <file_name> is the name of the file to which the results will be written
   
    
3. Execute a query or command for which the output must be captured to a file.
4. Restore standard output to the PostgreSQL client shell window by running the following command:
   
   \o
   
    
5. Re-enable PostgreSQL interactive paging to restore the PostgreSQL   s default paging scheme in the client shell window by running the following command:
   
   \pset pager

Additional Information

Example:

To obtain a file containing a list of all devices and their current status, do the following:

1. Run the following commands to disable pagination and redirect PostgreSQL output to a file on Linux:
   
   \pset pager
   
   \o /tmp/DeviceStatus_AllDevices.txt 
   
    
2. Output a list of all devices and their current status to the file by running the following query:
   
   SELECT device_name, device_idx, device_status, is_operational_device FROM cm_device ORDER BY device_status, device_id;
   
    
3. Run the following commands to restore standard output to the PostgreSQL client
   
   \o
   
   \pset pager

Operating system shell or command line environment variables containing path information are not available inside the PostgreSQL client.

The PostgreSQL client does not automatically have full 'admin' or 'root' disk write priviliges. Make sure appropriate write permissions are enabled on the target <absolute_path> (such as the /tmp directory in Linux).

If no <absolute_path> is included, the file will be created in the <product home>/db/controldb/ directory.