VPostGres database filling up VCSA 5.5 U2

pre cleanup vcenterRan into an interesting issue the other day my VPostgres database was at 100% capacity. Doing some quick investigation I realized that the postgres log was filled by a warning that would display multiple times a second causing a slow leak in available storage.

Warning causing database pileup

This would happen hundreds of times an hour and slowly kill the remaining storage on the VPostgres. Here is how i fixed it below.


1.Log into the VCSA and find the error above. You can find this error in the following directory in the VCSA if you are using SSH.

#~ cd /storage/db/vpostgres/pg_log

This will contain a lot of the files with the naming convention “Postgresql-year-date


2. Stopping the services on the VCSA and database is the next step before continuing on. If the Postgres is filled to capacity there is no need to stop the postgres services. These commands should be ran to prevent any problems in the shutdown process and will help to make sure other problems don’t arise from services not started properly.

Run the command: service vmware-vpxd stop

stopvcenter

Then run the command : Service vmware-vpostgres stop

Postgres stop

In the above example the services didn’t stop properly because my lab Vcenter was already filled to capacity and the services was already stopped.


3. While you figured out that the issue is this warning that keeps popping up the next step is to go into the postgres configuration file and change the logging levels. I used WINSCP to go into the VCSA to find the configuration file but you can use whatever utility you would like.

But make sure before you change this configuration file you make a backup on the VCSA to rollback if needed. You can also make a snapshot to make sure all changes can be rolled back (Only if this Vcenter is managed by another Vcenter, Obviously you cannot take a snapshot of the vcenter that wont turn on)

Use the command below in your SSH session to make a copy of the configuration file we will use in case of rollback.

cp /storage/db/vpostgres/postgresql.conf /storage/db/vpostgres/postgresql.conf.orig

 

Logging into Winscp to find the configuration file is easy, follow the same path as above and open it up in your favorite text editor.

There is a line of code in the configuration file on-line 312: #log_min_messages = warning

What we want to do is change this line to read like: log_min_messages = error

See below for an example. Ignore the | before the command.

Config file

Save the file and we can begin to start the services on the VCSA.


4. Now lets remove the old files from the database. This can be done from WinSCP of it can be done from the SSH console to the VCSA I can show you the site of both. I personally would keep the last 2-3 months worth of logs for when there was any valuable information in there.

Run the command: RM /storage/db/vpostgres/pg_log/postgres-DateYouWanttodelete

remove old postgres

A much easier way in my opinion is doing this through WinSCP go to the same file place above Ctrl-Select your choices you want to deleted then right-click and select delete.


5. Starting the services is the best part IMHO it means the problem is solved and the solution we just worked out hasn’t failed us.  Run the two commands to start the VCSA services and we should be on our way.

Start the Vpostgres service : service vmware-vpostgres start

Start the VPXD service on the VCSA: service vmware-vpxd start

You should receive a message like the one below.

Start services


6. Last but not least it is time to see the finished result. though SSH run the following command: df -h

This command should spit out the list of your drives and capacity of those drives.

Or if you want a prettier look log into the vcsa appliance webpage and check out the dashboard

Https://VCSA:5480after database cleanup

Vmware KB article 2092127

 

Hope you enjoyed. Comment below for any critiques.

Leave a Reply

Your email address will not be published. Required fields are marked *