Sample Exam Questions

From the objective of OSS-DB Exam Silver
S2.4 Backup and restore (Usage of various backup commands)

Sample Question


Choose the wrong description for the backup taken by the pg_dump command.

  1. You cannot back up users or roles in a database.

  2. By specifying the -a option, you can output a backup of just the data without including table definitions.

  3. By specifying the -s option, you can output a backup that does not contain data, but only a table definition.

  4. The -F option specifies the output format: p outputs a text backup, c, d or t outputs a binary backup.

  5. Of the binary formats, c outputs a physical backup, while the other formats output a logical backup.

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

Answer and Explanation

A PostgreSQL database cluster consists of two or more separate databases that store tables and data, and global objects that store information common to each database, such as users, roles, and tablespaces. However, pg_dump is a command that backs up individual databases, and cannot back up data for global objects such as users or roles. To back up global objects, use the pg_dumpall command.

pg_dump has a variety of options that allow you to take a backup in the appropriate format for your purposes or usages.

By default, it outputs both the table definition and the data, so you can reproduce the same table data in other databases by restoring that backup. The -s option outputs only the table definition, so restoring it will create an empty table with no data. This is useful, for example, when you want to migrate only table definitions from a development environment to a production environment. The -a option, on the other hand, outputs a data-only backup. It can be used to migrate data only when tables have already been created at the restore destination.
The format of the output file is specified with the -F option. p (default) means plain and is in text format. Let's actually run pg_dump and see the output file in a text editor. It is a sequence of SQL statements, such as CREATE TABLE for a table definition or COPY to populate a table. To restore it, use the psql command.

In addition to p, you can specify c, d, and t, all of which output binary files and can be restored using the pg_restore command. The binary format is compressed with tar or gzip, so you can unzip it to see what it looks like. These are also logical backups because they are actually collections of SQL that define tables and data. To obtain a physical backup of a database, use OS commands to perform copying and other operations on the directories that make up the database cluster.

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