Sample Exam Questions

From the objective of OSS-DB Exam Silver
S2.4 Backup and restore (Concepts and procedures of Point-in-Time Recovery (PITR))

Sample Question


Choose the incorrect step of the procedure for taking a base backup for point-in-time recovery (PITR).

  1. Enable WAL archiving.

  2. Connect to the database and run pg_start_backup.

  3. Stop the database server.

  4. Use the tar command to obtain a copy of the entire database cluster.

  5. Connect to the database and run pg_stop_backup.

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

Answer and Explanation

Point-in-time recovery (PITR) is a method that uses the starting database state (base backup) and subsequent changes to the database (WAL) to continuously back up the database and, if necessary, restore the database to any point in time.

To use it, you must first enable the WAL archiving feature, which stores WAL files. A base backup can be taken easily using the pg_basebackup command, but you should understand what you are doing internally. If pg_basebackup is not used, the procedure is as follows:

First, connect to the database as an administrator user and run pg_start_backup. Specifically, execute a SELECT statement similar to SELECT pg_start_backup('label', false, false);.

Then make a copy of the entire database cluster using any command available to the OS, such as tar or cp. Note that you should not shut down the database server during copying, and that you should use commands that physically copy files rather than PostgreSQL backup commands such as pg_dump or pg_dumpall. Since the database is not shut down, it is possible that the database files will be rewritten during the copy, but it still functions as a base backup.

Once the copy is complete, reconnect to the database as the administrator user and run pg_stop_backup.

This is a question that you need to select the incorrect option, so the correct answer is C.

To use PITR, it is of course important to keep the base backup created in step D, but it is also equally important to continuously store the WAL archive set in step A. Note that it is meaningless without both of these.