Postgres stops logging entries to /storage/db/pgdata/pg_log/postgresql.csv on the vRA appliance

Products

• VMware Aria Suite

Issue/Introduction

Symptoms:
Postgres stops logging entries to /storage/db/pgdata/pg_log/postgresql.csv on the vRA appliance.

Environment

VMware vRealize Automation 7.4.x

Cause

Logrotate settings cause /storage/db/pgdata/pg_log/postgresql.csv to be overwritten with a new file having the same name, but a different inode number, than the original.
The postgres logging facility writes to the postgresql.csv log file based on the inode number rather than the name, so it can no longer append to the original file.

Resolution

To resolve this issue:

Note: Execute the below steps on all vRA appliances in the cluster.

1. Edit the logrotate config file.
   1. Run the command to edit the file:
      
      vi /etc/logrotate.d/vcac.lr
   2. Locate entries:
      
      /var/log/vmware/vcac/vcac-config.log /var/vmware/vpostgres/current/pgdata/pg_log/*.csv
      rotate 8
      nodateext
      compress
      size 5M
      missingok
      nocreate
      notifempty
      sharedscripts
      postrotate
      /etc/init.d/syslog reload > /dev/null
      endscript
      su root root
      }
   3. Replace "nocreate" with "copytruncate".
   4. Save and close the file.

2. Reset postgres logging.
   
   Note: Does not require server restart.
   1. Run the command to Switch to the postgres user:
      su - postgres
   2. Start psql:
      
      psql vcac
   3. Execute SQL commands to reset logging:
      
      alter system set log_truncate_on_rotation = on;
      select pg_reload_conf();
      select pg_rotate_logfile();
      alter system reset log_truncate_on_rotation;
      select pg_reload_conf();
   4. Exit psql:
      \q