PostgreSQL Streaming Replication Quiz

1. In the recovery.conf file, what does the standby_mode parameter indicate?

Server should start as a standby and continuously apply WAL

Server should never promote to primary

Server should only accept read-only connections

Server should disable all replication features

2. What command is used to manually promote a standby server to primary?

Pg_ctl promote

SELECT pg_promote()

ALTER SYSTEM SET standby_mode = off

CREATE PRIMARY FROM STANDBY

3. In streaming replication, what does the wal_receiver_timeout parameter do?

Specifies timeout for WAL receiver to detect primary connection loss

Sets maximum time for WAL segment transfer

Controls how long standby waits before applying WAL

Limits total duration of replication session

4. What is the primary advantage of using logical replication over streaming replication?

Can replicate individual tables and filter changes

Faster WAL transmission speed

Lower CPU usage on primary server

Automatic failover without manual intervention

5. Which view displays information about all replication slots and their status?

Pg_replication_slots

Pg_stat_replication

Pg_wal_lsn_diff

Pg_replication_origin_status

6. What does promote_trigger_file accomplish on a standby server?

Triggers promotion to primary when file is created

Initiates WAL segment archiving

Enables read-only queries on standby

Resets replication slot information

7. Which parameter controls whether a standby server blocks during WAL apply?

Max_standby_streaming_delay

Wal_receiver_timeout

Synchronous_commit

Recovery_min_apply_delay

8. In cascading replication, what role does an intermediate server play?

Acts as both standby to primary and primary to downstream standby

Compresses WAL data before forwarding

Routes all queries to the main primary server

Manages failover decisions for the entire cluster

9. What is the purpose of pg_wal_lsn_diff() function?

Calculate byte difference between two WAL positions

Compare timestamps of WAL segments

Verify integrity of WAL files

Monitor replication slot consumption

10. Which parameter specifies the command to retrieve WAL files for streaming replication?

Primary_conninfo

Restore_command

Archive_command

Wal_retrieve_retry_interval

11. What is the primary purpose of WAL (Write-Ahead Logging) in PostgreSQL streaming replication?

To record all database changes before they are applied to ensure durability

To compress transaction logs for faster transmission

To encrypt sensitive data during replication

To manage connection pooling between primary and standby servers

12. What does the wal_keep_size parameter specify?

Minimum WAL size to retain for standby replication

Maximum size of a single WAL segment file

Total size of the WAL archive directory

Size threshold for triggering automatic WAL compression

13. Which tool is used to create a physical backup of the primary database for standby setup?

Pg_basebackup

Pg_dump

Pg_restore

Pg_repack

14. What parameter on the standby server enables it to accept read queries?

Hot_standby = on

Standby_mode = on

Recovery_target_timeline = 'latest'

Wal_receiver_status_interval = 10s

15. Which command creates a physical replication slot on the primary server?

SELECT * FROM pg_create_physical_replication_slot('slot_name')

CREATE REPLICATION SLOT slot_name

Pg_basebackup --slot=slot_name

ALTER SYSTEM SET primary_replication_slot

16. What is a replication slot's primary function?

Reserve WAL segments to prevent them from being recycled

Encrypt WAL data during transmission

Load balance queries between primary and standby

Schedule automatic failover events

17. Which view shows the current replication status and lag on the primary server?

Pg_stat_replication

Pg_replication_slots

Pg_stat_activity

Pg_wal_lsn_diff

18. In synchronous replication, what does synchronous_commit = remote_apply ensure?

Transactions wait until standby has replayed changes

Transactions commit immediately without waiting

Transactions are archived before standby receives them

Transactions are encrypted during transmission

19. What does the max_wal_senders parameter control?

Maximum number of concurrent WAL sender processes

Maximum size of WAL files in megabytes

Maximum distance a standby server can be located

Maximum time allowed for WAL archiving

20. Which parameter enables streaming replication on the primary PostgreSQL server?

Wal_level = replica

Max_wal_senders = 0

Synchronous_commit = off

Hot_standby = true

PostgreSQL Professional Streaming Replication Quiz