What is this issue?
fsync controls whether PostgreSQL calls the operating system's fsync()
function to ensure data is physically written to disk.
When fsync = off:
- Writes may stay in OS cache indefinitely
- A power failure or crash can lose minutes of data
- Worse, partial writes can corrupt the database
- Recovery may be impossible
This setting should NEVER be off in production. The only valid use case
is rebuilding a database from backup or scratch where data loss is acceptable.
Why it matters
Data Corruption
Partial writes during a crash leave the database in an unrecoverable state
Data Loss
Minutes or hours of committed transactions may be lost
Unrecoverable
Point-in-time recovery and WAL replay will not work correctly
Silent Failure
You won't know there's a problem until disaster strikes
How PG Pilot detects it
```sql SELECT name, setting, boot_val FROM pg_settings WHERE name = 'fsync'; ```
How to fix it
Enable fsync immediately
This requires a configuration change and reload:
ALTER SYSTEM SET fsync = on;
SELECT pg_reload_conf();Or edit postgresql.conf:
fsync = onThen reload: pg_ctl reload
Verify the change
Confirm fsync is now enabled:
SHOW fsync;Should return on.
Consider a full backup
If fsync was off for any significant time, take a fresh
base backup immediately. Previous backups may contain
corrupted data.
Prevention
- Never set fsync = off in production
- Use configuration management to enforce this setting
- Add monitoring alerts for this critical setting
- If you need faster writes, use synchronous_commit = off instead