vCenter server 6.0 service crashes due to postgres database filling up OS Partition.

Today l encountered a very interesting issue with one of my customer who was quite not happy with his vCenter services being crashed frequently for last 24 hours. Customer was running on vCenter 6.0 environment with external PSC.

Logged in to the vCenter to troubleshoot and found C drive was running out of space.Disk was having 1 MB space left and using treesize pro we figured out postgres database was consuming 36 GB of data.

Not all the data can be purged only couple of tables can actually be truncated. So we should be very careful while truncating or executing instructions at DB level.

Note:I requested the customer to take a snapshot of the vCenter and take a healthy backup of the database.

Follow the below instructions to connect to the database on first place.

• To obtain the credentials to connect to the vCenters vPostgres database open the C:\ProgramData\VMware\vCenterServer\cfg\vmware-vpx\vcdb.properties file in a plain text editor.

         Note: To view this file, administrator level permissions are required.

• In this file look for the database name, for example:jdbsc:postgresql:/localhost:5432/VCDB
• The username is in the string username = username.Note: These entries may not have spaces between fields.For example:  username = usernamepassword =
• Look for the database password located in the string password = password.

• Click Start > Run type in cmd and press Enter.
• Change to the C:\Program Files\VMware\vCenter Server\vPostgres\bin\ directory.Note:  Drive letter may change based on installation options.
• To connect to the database by running the the psql command:psql  -U username database_name

In the current eg. psql -U vc VCDB

Enter the password that was gathered earlier when prompted.

• To verify connectivity to the database run the command \c database_name.For example:\c VCDBYou are now connected to database “VCDB” as user “vc”

Below query provided me the size being consumed by each table.

SELECT nspname || ‘.’ || relname AS “relation”, 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;

 

relation | total_size
——————————+————
vc.vpx_event_arg | 16848 MB
vc.vpx_event | 65 MB
vc.vpx_task | 1088 kB
vc.vpx_topn_past_day | 688 kB
vc.vpx_host | 632 kB

I verified that events table are consuming more than 50% space of the database. Executed the below query to truncate the task and events table.

VCDB=> TRUNCATE TABLE vpx_event cascade;

NOTICE:  truncate cascades to table “vpx_event_arg”

NOTICE:  truncate cascades to table “vpx_entity_last_event”

TRUNCATE TABLE

 

Re-ran the query to validate the current table sizes.

SELECT nspname || ‘.’ || relname AS “relation”, 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;

relation | total_size
——————————+————
vc.vpx_task | 1088 kB
vc.vpx_topn_past_day | 688 kB
vc.vpx_host | 632 kB
vc.vpx_topn_past_week | 624 kB
vc.vpx_host_vm_config_option | 584 kB
vc.vpx_vm | 544 kB
vc.vpx_topn_past_month | 496 kB

To exit the current database executed the below command

VCDB=>\q

We finally managed to clear 18 GB of data and went ahead and tried starting the vCenter services. Services started successfully and vCenter was up and stable. Finally I had one happy customer by the end of the call.

Hope this article was very helpful to you. Watch out for more.