MCQs on Backup and Disaster Recovery | PostgreSQL Database
Ensure your PostgreSQL database is safeguarded with effective backup and disaster recovery strategies. Learn essential concepts like pg_dump, pg_basebackup, PITR, and replication for high availability.
MCQs on Backup and Disaster Recovery in PostgreSQL
Section 1: Backups using pg_dump and pg_basebackup
- What is the main purpose of using pg_dump in PostgreSQL?
- A) To perform database replication
- B) To create a backup of the database
- C) To optimize database performance
- D) To analyze query execution plans
- Which PostgreSQL command is used to back up a specific database?
- A) pg_basebackup
- B) pg_dump
- C) pg_restore
- D) pg_export
- Which format is NOT supported by pg_dump for backups?
- A) Plain text
- B) Custom
- C) Tar
- D) Binary
- What is the function of pg_basebackup in PostgreSQL?
- A) To create a point-in-time backup
- B) To perform a logical backup
- C) To create a physical backup of the entire cluster
- D) To restore a backup
- Which option is used with pg_basebackup to create a backup of the entire database cluster?
- A) -D
- B) -F
- C) -Ft
- D) -P
- How does pg_dump handle large databases?
- A) It performs incremental backups
- B) It supports parallel backups
- C) It requires manual partitioning of large databases
- D) It does not handle large databases
- Can pg_dump back up the entire PostgreSQL database cluster?
- A) Yes, with the correct flags
- B) No, it can only back up individual databases
- C) Yes, it automatically backs up the cluster
- D) No, pg_dump is not used for backups
- Which of the following backup types does pg_dump provide?
- A) Full backup only
- B) Incremental backup
- C) Logical backup
- D) Physical backup
- What is the typical size of the output file when using pg_dump in plain text mode?
- A) Larger than other formats
- B) Smaller than other formats
- C) Same size as binary mode
- D) None of the above
- How is a pg_dump backup restored?
- A) Using pg_basebackup
- B) Using pg_restore
- C) Using psql
- D) Using copy command
Section 2: Point-In-Time Recovery (PITR)
- What is the main goal of Point-In-Time Recovery (PITR)?
- A) To restore the database to the state at a specific point in time
- B) To restore the database from the latest full backup
- C) To restore the database to the last checkpoint
- D) To create a replica of the primary database
- What is required to perform Point-In-Time Recovery (PITR) in PostgreSQL?
- A) A backup taken with pg_basebackup and WAL (Write-Ahead Logging) files
- B) Only a full database backup
- C) A copy of the database schema
- D) Only the WAL files
- In which scenario would you use Point-In-Time Recovery (PITR)?
- A) Recovering from accidental data deletion
- B) Restoring a database to its default configuration
- C) Performing an incremental backup
- D) Recovering from a server crash without backup
- Which configuration file is used to specify recovery settings during PITR in PostgreSQL?
- A) recovery.conf
- B) postgresql.conf
- C) archive.conf
- D) backup.conf
- What is the role of the archive_mode parameter during PITR?
- A) It enables replication
- B) It specifies where the backups are stored
- C) It ensures WAL files are archived for PITR
- D) It controls the backup frequency
- How do you enable WAL archiving for PITR in PostgreSQL?
- A) Set archive_mode to ‘on’ in postgresql.conf
- B) Use the pg_wal command
- C) Enable PITR via pg_dump
- D) Use pg_basebackup for WAL archiving
- What does the trigger file in PITR signify?
- A) The point at which recovery will stop
- B) A marker indicating when the PITR process should start
- C) The location where backups are stored
- D) The location of the WAL files
- Which of the following is a potential risk during PITR?
- A) Data corruption due to incorrect file system settings
- B) Overwriting valid WAL files
- C) Incomplete data recovery
- D) All of the above
- What is the main advantage of using PITR over traditional backups?
- A) Faster restoration of large databases
- B) The ability to restore to any specific time
- C) Simplified backup management
- D) It does not require WAL files
- How can you verify if PITR was successful in PostgreSQL?
- A) Check the PostgreSQL logs for errors
- B) Compare the recovered data with the backup
- C) Use the SELECT command to check the time of the restored data
- D) All of the above
Section 3: Managing Backup Strategies
- Which of the following is a best practice for PostgreSQL backups?
- A) Perform backups during peak hours for minimal disruption
- B) Use only physical backups for larger databases
- C) Regularly test your backup and recovery strategy
- D) Rely on incremental backups alone for disaster recovery
- What is an incremental backup?
- A) A backup that includes all data since the last full backup
- B) A backup that includes the entire database
- C) A backup that only includes transaction logs
- D) A backup that includes only schema changes
- Which of the following can be used to schedule backups in PostgreSQL?
- A) cron jobs on Linux systems
- B) pg_scheduler
- C) PostgreSQL Event Trigger
- D) All of the above
- How can you ensure backup consistency in PostgreSQL?
- A) Use a consistent snapshot during backup
- B) Disable writes to the database during backup
- C) Use pg_basebackup for all backups
- D) Ensure database replication is always active
- What is the best strategy for backing up a large PostgreSQL database?
- A) Only use pg_basebackup
- B) Perform backups during system downtime
- C) Use incremental backups and periodically take full backups
- D) Backup only the schema
- Which tool can be used to monitor the backup process in PostgreSQL?
- A) pg_stat_activity
- B) pg_stat_bgwriter
- C) pg_stat_archiver
- D) pg_stat_file_system
- What is the risk of not testing your backup and disaster recovery plan?
- A) Data corruption during restore
- B) Longer downtime in the event of failure
- C) Incomplete backups leading to data loss
- D) All of the above
- Which of the following is true about automated backups in PostgreSQL?
- A) They can be scheduled using tools like cron or pgAgent
- B) They are unnecessary for small databases
- C) They do not require monitoring once scheduled
- D) They cannot include WAL files
- How often should backups be taken in a PostgreSQL environment?
- A) Once every week
- B) Once every day
- C) Based on the frequency of database changes
- D) Only when a disaster recovery situation occurs
- What is the benefit of using a cloud-based backup solution for PostgreSQL?
- A) Automatic backup scheduling and management
- B) Reduced risk of local hardware failure
- C) Greater scalability for large databases
- D) All of the above
Answer Key
| Qno | Answer |
|---|---|
| 1 | B) To create a backup of the database |
| 2 | B) pg_dump |
| 3 | D) Binary |
| 4 | C) To create a physical backup of the entire cluster |
| 5 | A) -D |
| 6 | B) It supports parallel backups |
| 7 | B) No, it can only back up individual databases |
| 8 | C) Logical backup |
| 9 | A) Larger than other formats |
| 10 | B) Using pg_restore |
| 11 | A) To restore the database to the state at a specific point in time |
| 12 | A) A backup taken with pg_basebackup and WAL (Write-Ahead Logging) files |
| 13 | A) Recovering from accidental data deletion |
| 14 | A) recovery.conf |
| 15 | C) It ensures WAL files are archived for PITR |
| 16 | A) Set archive_mode to ‘on’ in postgresql.conf |
| 17 | B) A marker indicating when the PITR process should start |
| 18 | D) All of the above |
| 19 | B) The ability to restore to any specific time |
| 20 | D) All of the above |
| 21 | C) Regularly test your backup and recovery strategy |
| 22 | A) A backup that includes all data since the last full backup |
| 23 | D) All of the above |
| 24 | A) Use a consistent snapshot during backup |
| 25 | C) Use incremental backups and periodically take full backups |
| 26 | C) pg_stat_archiver |
| 27 | D) All of the above |
| 28 | A) They can be scheduled using tools like cron or pgAgent |
| 29 | C) Based on the frequency of database changes |
| 30 | D) All of the above |