How to set the Postgres database log level to ALL in vRealize Operations 8.2

Products

• VMware Aria Suite

Issue/Introduction

By default, vRealize Operation's Postgres database log level value is set to NONE.
For security purposes the log level can be changed to ALL.

Environment

VMware vRealize Operations 8.2.x

Resolution

To change the log level to ALL, complete the steps below.

1. Log into the Primary node as root via SSH or Console, pressing ALT+F1 in a Console to log in.
2. Open /storage/db/vcops/vpostgres/data/postgresql.conf in a text editor.
3. Change #log_statement=NONE to log_statement=ALL.

Note: By default this option is commented, uncomment it by removing #..

4. Save and close the file.
5. Open /storage/db/vcops/vpostgres/repl/postgresql.conf in a text editor.
6. Change #log_statement=NONE to log_statement=ALL.

Note: By default this option is commented, uncomment it by removing #..

7. Save and close the file.
8. Reload the Postgres configuration by running the following commands, one at a time:

• su – postgress
• /opt/vmware/vpostgres/current/bin/pg_ctl reload -D /storage/db/vcops/vpostgres/data
• /opt/vmware/vpostgres/current/bin/pg_ctl reload -D /storage/db/vcops/vpostgres/repl

9. Repeat steps 1-8 on all other analytic nodes in the vRealize Operations Manager cluster.

Additional Information

To change the log level back to NONE, complete the steps below.

1. Log into the Primary node as root via SSH or Console, pressing ALT+F1 in a Console to log in.
2. Open /storage/db/vcops/vpostgres/data/postgresql.conf in a text editor.
3. Change #log_statement=ALL to log_statement=NONE.

Note: By default this option is commented, uncomment it by removing #..

4. Save and close the file.
5. Open /storage/db/vcops/vpostgres/repl/postgresql.conf in a text editor.
6. Change #log_statement=ALL to log_statement=NONE.

Note: By default this option is commented, uncomment it by removing #..

7. Save and close the file.
8. Reload the Postgres configuration by running the following commands, one at a time:

• su – postgress
• /opt/vmware/vpostgres/current/bin/pg_ctl reload -D /storage/db/vcops/vpostgres/data
• /opt/vmware/vpostgres/current/bin/pg_ctl reload -D /storage/db/vcops/vpostgres/repl

9. Repeat steps 1-8 on all other Analytic nodes in the vRealize Operations Manager cluster.

Impact/Risks:
Even if ALL is recommended from a security perspective, it can generate a huge number of logs and put setups under a large load, up to breaking the regular working processes.

Example: An extra-large 5 nodes cluster (1 master and 4 data nodes) with ~200K objects and ~150Milion metrics produces 0-260 KB logs every 10 minutes when log_statement=NONE and up to 4900-27000KBs every 10 minutes when log_statement=ALL, approximately generating 100 times more logs.

Setting the log level to ALL will increases risk of the /storage/log partition filling up.
See Safely cleaning up log files in vRealize Operations if you need to clear space.