Sample Exam Questions

From the objective of OSS-DB Exam Silver
- Operation and Management - Backup method

Sample Question


Choose two appropriate descriptions for backup using pg_dump.

  1. Database superuser privilege is required to execute pg_dump.
  2. You can back up not only data of tables but also data of users and roles created with CREATE USER or CREATE ROLE.
  3. In addition to table data, you can also back up access privileges to tables granted / deprived by GRANT or REVOKE.
  4. You can back up only table definitions such as CREATE TABLE without dumping data.
  5. When backing up in binary format, restoring data is restricted to servers of the same architecture.

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

Answer and Explanation

pg_dump creates a logical backup of the database. "Logically" means that the data in the database is the same as the data in the backup, and in fact it makes backups in the form of SQL statements such as CREATE TABLE and INSERT. In contrast, a "physical" backup  creates copies of data files. If you restore from a physical backup, the structure in the data file and data block will be retained as is, but if you restore from a logical backup the contents of the data file will be totally different. Logical backups can be restored to database servers of different architectures and versions. This is not limited even if the backup is in binary format.

To run pg_dump, you need the appropriate authority for all objects in the database that you want to back up. Attempting to back up objects that do not have access privileges results in an error. However, you do not have to run it as superuser.

Since PostgreSQL maintains user and role information as global objects of the database cluster, it is not included in each database and can not be backed up with pg_dump. You need to use pg_dumpall to back up the entire database cluster. Because access rights by GRANT and REVOKE are in each database, this is backed up with pg_dump. If you run pg_dump with the -s or --schema-only option, you can back up only the table definition. Conversely, there is an option called - a or - data - only which does not output the table definition but dumps only the data.

Therefore, the correct answers are C and D.