Windows PostgreSQL configuration causes corruption

Products

• VMware

Issue/Introduction

In order to bring the Windows vCenter PostgreSQL database into ACID compliance we must change the wal_sync_method to fsync_writethrough.

This ensures that the transaction log (WAL) files are flushed to disk immediately.

Symptoms:
The default transaction log (WAL) sync method implemented in the Windows vCenter configuration for PostgreSQL uses write caching. This is not ACID compliant and could be a cause for data loss and/or corruption.

Cause

This issue is caused due to improper configuration of the Windows PostgreSQL database.

Specifically the method used to write the transaction log (WAL) files to disk used caching. With this configuration in the event of storage or power loss the "committed" transaction log would be lost. This will cause data loss or corruption in the database.

Resolution

Note: Take a backup of the PostgreSQL configuration before making any changes.

To resolve the issue, download and run the attached Windows .bat script to correct the issue in your configuration. The script will automatically reload your PostgreSQL configuration requiring no user intervention.

 

Note: During a PostgreSQL reload all active connections will remain but no new connections will be allowed.

 

Alternatively, manually edit the postgresql.conf file:

1. Edit <Install Drive>:\ProgramData\VMware\vCenterServer\data\vpostgres\postgresql.conf
2. Uncomment wal_sync_method
3. Set wal_sync_method=fsync_writethrough
4. Reload or restart PostgreSQL

Additional Information

Impact/Risks:
This issue has a major impact on the vCenter server as it can cause data loss and/or corruption.

 

Failure to address this issue may result in an inoperable vCenter database requiring a reset and total data loss.