data_sync_retry

Definition PG 18 manual

When set to off, which is the default, PostgreSQL will raise a PANIC-level error on failure to flush modified data files to the file system. This causes the database server to crash. This parameter can only be set at server start.

On some operating systems, the status of data in the kernel's page cache is unknown after a write-back failure. In some cases it might have been entirely forgotten, making it unsafe to retry; the second attempt may be reported as successful, when in fact the data has been lost. In these circumstances, the only way to avoid data loss is to recover from the WAL after any failure is reported, preferably after investigating the root cause of the failure and replacing any faulty hardware.

If set to on, PostgreSQL will instead report an error but continue to run so that the data flushing operation can be retried in a later checkpoint. Only set it to on after investigating the operating system's treatment of buffered data in case of write-back failure.

How it works

Whether to continue running after a failure to sync data files. The value is fixed when the server starts, so changing it requires a restart.

After a data-file fsync failure, the default behavior treats shared buffers as potentially inconsistent and raises PANIC so crash recovery re-establishes state. Continuing can lose knowledge of dirty pages and is intended only for platforms whose kernel semantics make retry safe.

Monitor and change data_sync_retry together with restart_after_crash, recovery_init_sync_method, fsync. Validate on the relevant server role and real workload, then use its postmaster context to choose session change, reload, or restart; a historical boot default is not the current effective value.

Operational considerations

Enabling it on a platform where fsync failure loses dirty-page knowledge.

Treating an I/O error as transient without replacing or fencing bad storage.

Optimizing availability at the expense of silent corruption.

Confusing the boot default of data_sync_retry with its current effective value.

Ignoring its postmaster context when deciding when it takes effect.

Workload guidance

OLAP: Test separately under long queries, batch jobs, and peak concurrency rather than copying OLTP assumptions to analytical nodes.

OLTP: Change data_sync_retry only from an explicit failure model and measured evidence. Validate in a session/test environment, deploy according to its context, and retain a rollback value.

SMALL: Keep the default without a concrete problem; small systems should not trade global compatibility or failure semantics for a marginal gain.