MCQs on Logical and Physical Replication | PostgreSQL Database
Learn the essentials of PostgreSQL replication, including streaming replication, logical replication setup, WAL (Write-Ahead Logging) for data consistency, and configuring hot standbys for high availability and disaster recovery.
Streaming Replication Basics
- What is the primary function of streaming replication in PostgreSQL?
a) Replicating database schemas
b) Synchronizing data in real time
c) Backing up the database
d) Compressing data for storage - Which process is responsible for sending WAL records in streaming replication?
a) WAL Writer
b) Replication Sender
c) Replication Receiver
d) Hot Standby Server - What type of replication is used in streaming replication?
a) Logical Replication
b) Physical Replication
c) Asynchronous Replication
d) Synchronous Replication - What must be configured for streaming replication to work in PostgreSQL?
a) A shared storage system
b) WAL archiving
c) Synchronous replication mode
d) A master-slave setup with replication slots - In streaming replication, which of the following is the “primary” role?
a) Standby server
b) Replication sender
c) Publisher
d) Primary server
Logical Replication Setup and Use Cases
- What is the main advantage of logical replication over physical replication?
a) It is faster
b) It supports replication of specific tables or databases
c) It requires fewer resources
d) It is more secure - Which PostgreSQL feature allows replication of individual tables using logical replication?
a) Publish-Subscribe
b) Replication Slot
c) Logical Replication Subscription
d) WAL Streaming - What must be configured on the publisher side to enable logical replication?
a) Replication Slots
b) Logical Replication Subscription
c) Replication Role
d) Replication Publisher - In logical replication, what is the role of a “subscription”?
a) To create new database replicas
b) To define the tables and data to be replicated
c) To manage the replication slots
d) To create a hot standby server - Which of the following is NOT a use case for logical replication?
a) Cross-version replication
b) Filtering data during replication
c) Real-time replication of large databases
d) Replicating specific tables or subsets of data
Understanding WAL (Write-Ahead Logging)
- What is the primary purpose of Write-Ahead Logging (WAL) in PostgreSQL?
a) To encrypt database data
b) To ensure data integrity during transactions
c) To create backups of the database
d) To optimize query performance - Where are WAL logs typically stored in PostgreSQL?
a) In the pg_log directory
b) In the database tables
c) In the shared_buffers
d) In the pg_wal directory - How does WAL contribute to database recovery?
a) By storing the entire database in memory
b) By replaying changes after a crash
c) By creating daily backups
d) By automatically archiving data - Which of the following is an advantage of using WAL for replication?
a) Provides replication of only schema changes
b) Ensures that replication is consistent and durable
c) Allows replication of non-transactional data
d) Reduces the need for indexing - What happens to WAL records when using streaming replication?
a) They are archived only after database shutdown
b) They are discarded after replication
c) They are continuously sent from the primary to the standby server
d) They are not used for replication
Setting Up Hot Standbys
- What is the purpose of a hot standby in PostgreSQL?
a) To act as a backup server for disaster recovery
b) To speed up database insert operations
c) To act as a read-only replica of the primary server
d) To automatically backup WAL logs - In PostgreSQL, what must be configured to enable a hot standby server?
a) Streaming replication
b) Logical replication subscription
c) Backup encryption
d) WAL archiving - Which configuration setting is crucial for allowing a hot standby server to receive WAL data?
a) hot_standby = on
b) synchronous_commit = off
c) wal_level = minimal
d) primary_conninfo = <primary-server-details> - How does a hot standby server differ from a warm standby in PostgreSQL?
a) Hot standby is always running and read-only, while warm standby is offline until needed
b) Hot standby can accept writes, while warm standby cannot
c) Warm standby uses logical replication, while hot standby uses physical replication
d) Warm standby provides automatic failover, while hot standby requires manual intervention - What happens to the hot standby server if the primary server goes down?
a) The hot standby automatically becomes the new primary server
b) The hot standby stops accepting connections
c) The hot standby triggers a failover operation manually
d) The hot standby replicates data to a new secondary server
Advanced Replication Concepts
- Which of the following is NOT required for setting up physical replication in PostgreSQL?
a) WAL archiving
b) Replication slots
c) Hot standby configuration
d) Publish-Subscribe mechanism - What is the main difference between physical replication and logical replication?
a) Physical replication copies the entire database, while logical replication only copies specific data
b) Logical replication does not use WAL
c) Physical replication requires more network resources
d) Logical replication is faster than physical replication - How can you ensure replication consistency in a PostgreSQL setup with hot standbys?
a) Use synchronous replication
b) Use asynchronous replication
c) Use logical replication
d) Use WAL logging without replication - In a replication setup, how are conflicts handled when using logical replication?
a) PostgreSQL automatically resolves all conflicts
b) Conflicts are not allowed in logical replication
c) Conflicts must be manually resolved by the administrator
d) Conflicts are reported as errors during replication - What feature does PostgreSQL provide for monitoring replication status?
a) Replication status view in pg_stat_replication
b) Replication error log
c) Automatic replication check during backups
d) Periodic consistency checks
Troubleshooting Replication Issues
- What might cause replication lag in a PostgreSQL streaming replication setup?
a) Insufficient disk space on the primary server
b) Slow network connection between primary and standby
c) Lack of indexing on the primary server
d) High transaction rates on the standby server - How can replication lag be minimized in a PostgreSQL setup?
a) Increase the frequency of WAL checkpoints
b) Enable synchronous replication
c) Disable WAL archiving
d) Disable hot standby mode - What happens if the primary server in a streaming replication setup becomes unreachable?
a) The replication connection is automatically redirected to another standby
b) Replication is paused until the primary server is back online
c) A new primary server is automatically elected
d) The standby server starts accepting write operations - How can you check the replication status in PostgreSQL?
a) By using the command SHOW REPLICATION STATUS
b) By querying the pg_stat_replication view
c) By checking the WAL logs directly
d) By querying the replication logs - Which PostgreSQL feature provides automatic failover in case of primary server failure?
a) Streaming Replication
b) Hot Standby
c) Logical Replication
d) Replication Manager (repmgr)
Answer Key
| Qno | Answer |
|---|---|
| 1 | b) Synchronizing data in real time |
| 2 | b) Replication Sender |
| 3 | b) Physical Replication |
| 4 | d) A master-slave setup with replication slots |
| 5 | d) Primary server |
| 6 | b) It supports replication of specific tables or databases |
| 7 | c) Logical Replication Subscription |
| 8 | a) Replication Slots |
| 9 | b) To define the tables and data to be replicated |
| 10 | c) Real-time replication of large databases |
| 11 | b) To ensure data integrity during transactions |
| 12 | d) In the pg_wal directory |
| 13 | b) By replaying changes after a crash |
| 14 | b) Ensures that replication is consistent and durable |
| 15 | c) They are continuously sent from the primary to the standby server |
| 16 | c) To act as a read-only replica of the primary server |
| 17 | a) Streaming replication |
| 18 | a) hot_standby = on |
| 19 | a) Hot standby is always running and read-only, while warm standby is offline until needed |
| 20 | a) The hot standby automatically becomes the new primary server |
| 21 | d) Publish-Subscribe mechanism |
| 22 | a) Physical replication copies the entire database, while logical replication only copies specific data |
| 23 | a) Use synchronous replication |
| 24 | c) Conflicts must be manually resolved by the administrator |
| 25 | a) Replication status view in pg_stat_replication |
| 26 | b) Slow network connection between primary and standby |
| 27 | b) Enable synchronous replication |
| 28 | b) Replication is paused until the primary server is back online |
| 29 | b) By querying the pg_stat_replication view |
| 30 | d) Replication Manager (repmgr) |