When set to "on", the commit is considered successful as soon as the WAL record is written to the standby's disk.
When set to "off", the commit is considered successful even before the WAL has been written to either the primary or the standby server.
When set to "local", the commit is considered successful when the WAL is written to the buffer, before it is written to the standby's disk.
When set to "remote_apply", the commit is considered successful not only when the WAL is written to disk on the standby server, but also when the changes described in the WAL have been applied to the database.
※This sample exam is different from those that appear in the actual OSS-DB Exam.
2025/05/29
The correct answer is C.
The synchronous_commit parameter controls the level of synchronization in a replication configuration. Below are the possible values for the synchronous_commit parameter and their description.
●remote_apply
This is the highest synchronization level setting. With this setting, the flow from the execution of the transaction commit on the primary machine to the successful completion of the commit is as follows:
STEP 1. The user commits the transaction on the primary machine.
STEP 2. The primary machine saves the WAL to the primary machine's disk.
STEP 3. The primary machine sends the WAL to the standby machine, and the standby machine receives the WAL.
STEP 4. The standby machine stores the WAL in the buffer cache of the operation system.
STEP 5. The standby machine saves the WAL stored in the buffer cache to disk.
STEP 6. The standby machine updates the database according to the description in the WAL (the database is updated when the SELECT query is executed here).
STEP 7. The primary machine receives the report from the standby machine.
STEP 8. The primary machine considers the commit to be successful.
●on
This is the default synchronization setting; compared to remote_apply, it does not wait for STEP 6 to be completed before proceeding to STEP 7. This setting guarantees that no transactions are lost, except when the primary machine and all standby machines suffer database storage failure.
●remote_write
Compared to remote_apply, this setting proceeds to STEP 7 without waiting for STEP 5 and STEP 6 to be completed.
This setting is characterized by a slight performance improvement over "on" because the waiting time for WAL write processing on the primary machine is reduced. It also guarantees data protection even if the PostgreSQL server on the standby machine crashes. However, if the standby machine crashes at the OS level, data protection cannot be guaranteed because the system buffers may be lost.
●local
This is an asynchronous configuration; compared to remote_apply, it proceeds to STEP 8 without waiting for STEP 4 to STEP 7 to be completed. This setting is not desirable in a synchronous replication configuration.
●off
Compared to remote_apply, it proceeds to STEP 8 without waiting for STEP 2 to STEP 7 to complete.
When committing a transaction on the primary machine, it does not wait to see if the WAL record has been written to the primary machine and the standby machine. On the other hand, transactions may be lost in the event of an OS or PostgreSQL server crash.
Note that in a non-replicated configuration, "remote_apply", "on", and "remote_write" have the same behavior as "local".
Let's look at the options one by one.
[A. When set to "on", the commit is considered successful as soon as the WAL record is written to the standby's disk.]
Correct. When set to "on", the standby machine reports to the primary machine when the WAL is written to disk. The primary machine then considers the transaction commit to be successful based on that report.
[B. When set to "off", the commit is considered successful even before the WAL has been written to either the primary or the standby server.]
Correct. When set to "off", the primary machine considers the commit successful without waiting for confirmation that the WAL has been written to the primary machine's disk, the WAL has been written by the standby machine, or the query execution has been completed.
[C. When set to "local", the commit is considered successful when the WAL is written to the buffer, before it is written to the standby's disk.]
Incorrect. This description is more accurate for the "remote_write" setting, not the "local" setting.
[D. When set to "remote_apply", the commit is considered successful not only when the WAL is written to disk on the standby server, but also when the changes described in the WAL have been applied to the database.]
Correct. When set to "remote_apply", the primary machine waits for the standby machine to report that the contents of the WAL have been applied to the database before considering the commit to be successful.