Sample Exam Questions

From the objective of OSS-DB Exam Silver
S2.4 Backup and restore (Transaction log (WAL) and WAL archive)

Sample Question

2.28

Choose the three most appropriate descriptions for WAL archiving.

 
  1. To use point-in-time recovery (PITR), WAL archiving must be enabled.

  2. This function combines multiple WAL files into a single file.

  3. The command specified in the archive_command configuration parameter is executed.

  4. Attention must be paid as the command is not re-executed even if it fails.

  5. PostgreSQL keeps track of whether a command has been executed, but does not know what the command actually does or whether it is doing the right operations for WAL archiving.

※This sample exam is different from those that appear in the actual OSS-DB Exam.
2024/05/07

Answer and Explanation

WAL stands for Write-Ahead-Log and writes transaction information to a log file before updating the database file. Processing of COMMIT is designed so that the client is notified of the completion upon writing of WAL, and time-consuming updates of the database file itself are performed asynchronously.

All database update information is written to WAL, so even if a database file fails, a backup of the database at some point and all WAL files generated since then can restore the database to its current state. Point-in-time recovery (PITR) takes advantage of this mechanism.

However, if WAL is left unattended, it will grow and disk space quickly runs out. To avoid this, it recycles old WAL files so that WAL does not overwhelm the disk space by default; however, that also means point-in-time recovery is not available by default. A function for storing all WAL files is WAL archiving.

To use WAL archiving, set the configuration parameter wal_level to replica or higher and wal_archive to on, then set archive_command to the command for archiving WAL. Normally, you would set a command (such as a shell script) to copy WAL to a different disk from where the database is located, but the command should return 0 on success and non-zero on failure. PostgreSQL checks whether archive_command succeeds, and re-executes it if it fails. If archiving succeeds, the WAL is recycled.

Note that PostgreSQL has no knowledge of what archive_command actually does. It manages whether the command to copy the file returned a success, but it is up to the administrator to determine whether the file was actually copied, whether archive_command was doing the copying at all, and so on, so this should be done after careful testing.

WAL archiving should be understood in the meaning of the verb "archive," that is, "storing in an archive." Be careful not to misunderstand the term "archive" because it is also used in commands that combine multiple files into a single file (an archive), such as the Linux ar and tar commands.

So the correct answers are A, C, and E.