Backend interview prep

Start

PostgreSQL Replication Interview Questions

Master PostgreSQL Replication with interview-focused questions covering Physical Replication, Logical Replication, Streaming Replication, WAL, Primary-Standby Architecture, Synchronous vs Asynchronous Replication, Replication Slots, Failover, Read Replicas, High Availability, and enterprise production best practices.

Introduction

Modern enterprise applications require databases that are

  • Highly Available
  • Fault Tolerant
  • Scalable
  • Disaster Recovery Ready

A single PostgreSQL server creates a Single Point of Failure (SPOF).

If the primary server crashes,

the application becomes unavailable.

PostgreSQL Replication solves this problem by maintaining one or more copies of the primary database.

Replication provides

  • High Availability (HA)
  • Disaster Recovery (DR)
  • Read Scaling
  • Backup Offloading
  • Data Redundancy

PostgreSQL Replication Architecture

flowchart LR

Application --> PrimaryServer["Primary Server"]

PrimaryServer["Primary Server"] --> WAL

WAL --> StreamingReplication["Streaming Replication"]

StreamingReplication["Streaming Replication"] --> StandbyServer1["Standby Server 1"]

StreamingReplication["Streaming Replication"] --> StandbyServer2["Standby Server 2"]

StandbyServer1["Standby Server 1"] --> ReadQueries["Read Queries"]

StandbyServer2["Standby Server 2"] --> ReadQueries["Read Queries"]

1. What is PostgreSQL Replication?

Answer

Replication is the process of copying data from one PostgreSQL server (Primary) to one or more Standby servers.

Benefits

  • High Availability
  • Read Scaling
  • Disaster Recovery
  • Fault Tolerance

2. Why is Replication required?

Without replication

Primary Server Failure

↓

Database Down

↓

Application Down

With replication

Primary Failure

↓

Standby Promoted

↓

Application Continues

3. What are the types of PostgreSQL Replication?

PostgreSQL supports

  • Physical Replication
  • Logical Replication

Replication Types

flowchart TD

Replication --> Physical

Replication --> Logical

4. What is Physical Replication?

Physical Replication copies

the entire database cluster

block by block

using WAL records.

The standby database is an exact binary copy of the primary.

5. What is Logical Replication?

Logical Replication copies

database objects

instead of physical storage blocks.

Can replicate

  • Tables
  • Selected Databases
  • Selected Columns (through application design)
  • Specific Publications

Useful for migrations and selective replication.

Physical vs Logical Replication

Physical Replication Logical Replication
Entire Cluster Selected Objects
Binary Copy Logical Data Changes
Read Replica Flexible Replication
Disaster Recovery Data Distribution

6. What is Streaming Replication?

Streaming Replication continuously streams

WAL records

from Primary

to Standby.

Standby stays almost synchronized.

Streaming Replication

flowchart LR

Primary --> WAL --> Network --> Standby --> Replay

7. What is WAL?

WAL

Write-Ahead Log

stores every database modification.

Replication uses WAL records

to synchronize standby servers.

WAL Flow

flowchart LR

Transaction --> WAL --> Replication --> Standby

8. What is a Primary Server?

Primary Server

handles

  • Reads
  • Writes
  • Transactions
  • WAL Generation

Only one Primary exists in a replication cluster.

9. What is a Standby Server?

Standby Server continuously applies WAL records received from the Primary.

Normally used for

  • Read Queries
  • Failover
  • Backup

Primary-Standby Architecture

flowchart LR

Application --> Primary

Primary --> Standby1

Primary --> Standby2

10. What is Hot Standby?

Hot Standby allows users

to execute

read-only queries

on standby servers

while WAL replay continues.

11. What is Synchronous Replication?

Primary waits

until the standby confirms

that WAL has been written (or flushed/applied depending on configuration)

before committing the transaction.

Benefits

  • Strong Data Consistency

Drawback

  • Higher Commit Latency

12. What is Asynchronous Replication?

Primary commits immediately.

Standby receives WAL later.

Benefits

  • Faster Performance

Risk

  • Small amount of data loss possible if the primary fails before WAL reaches the standby.

Synchronous vs Asynchronous

Synchronous Asynchronous
No Data Loss (Properly Configured) Possible Small Data Loss
Slower Commit Faster Commit
Higher Consistency Higher Performance

13. What is Replication Lag?

Replication Lag is the delay

between

Primary

and

Standby.

Measured in

  • Seconds
  • WAL Bytes
  • Transactions

14. Why does Replication Lag occur?

Reasons

  • Slow Network
  • High WAL Generation
  • Slow Disk
  • Heavy Workload
  • Large Transactions

Replication Lag

flowchart LR

Primary --> WAL --> Delay --> Standby

15. What is a Replication Slot?

Replication Slot ensures

the Primary retains WAL files

until every standby has consumed them.

Prevents WAL loss.

Replication Slot

flowchart LR

Primary --> ReplicationSlotStandby["Replication Slot --> Standby"]

16. Why are Replication Slots important?

Without Replication Slot

Old WAL files

may be deleted

before standby reads them.

Replication fails.

17. What is Failover?

Failover promotes

a standby server

to become

the new Primary

after failure.

Failover Workflow

flowchart LR

PrimaryFailure["Primary Failure"] --> StandbyPromotionNewPrimary["Standby Promotion --> New Primary --> Application"]

18. What is Switchover?

Switchover is a planned role reversal.

Primary

↓

Standby

Standby

↓

Primary

No failure is involved.

Used during maintenance.

Failover vs Switchover

Failover Switchover
Unplanned Planned
Server Failure Maintenance
Automatic/Manual Planned Operation

19. What are Read Replicas?

Standby servers

used only for

read-only queries.

Benefits

  • Load Balancing
  • Reporting
  • Analytics

Read Scaling

flowchart LR

Application --> Primary

Application --> ReadReplica1["Read Replica1"]

Application --> ReadReplica2["Read Replica2"]

20. Can writes occur on Standby?

No.

Physical standby servers

are read-only.

Writes happen only on the Primary.

(Logical replication targets can be writable depending on the architecture.)

21. What is Cascading Replication?

One standby

replicates

to another standby.

Useful when

many standby servers exist.

Cascading Replication

flowchart LR

Primary --> Standby1

Standby1 --> Standby2

Standby2 --> Standby3

22. How do you monitor replication?

Useful views

pg_stat_replication

pg_stat_wal_receiver

Useful metrics

  • Replication Lag
  • WAL Position
  • Sync State

23. Banking Example

Primary

↓

Two Standbys

↓

ATM Queries

↓

Read Replica

↓

Primary handles transactions

24. E-Commerce Example

Black Friday

↓

Primary handles orders

↓

Read Replicas serve

product searches

25. SaaS Example

Primary

↓

Regional Read Replicas

↓

Lower Query Latency

26. Disaster Recovery Example

Primary server crashes

↓

Automatic Failover

↓

Standby promoted

↓

Application continues

27. Common Replication Problems

  • Replication Lag
  • Network Failures
  • WAL Disk Full
  • Missing Replication Slots
  • Slow Standby Storage
  • Split-Brain (poor failover design)
  • Improper Failover Configuration

28. Replication vs Backup

Replication Backup
High Availability Data Recovery
Continuous Periodic
Fast Failover Restore Required
Live Copy Historical Copy

29. Replication Best Practices

  • Monitor replication lag continuously.
  • Use replication slots carefully.
  • Keep WAL storage adequately sized.
  • Test failover regularly.
  • Use synchronous replication only when required.
  • Separate replication traffic from client traffic.
  • Monitor standby replay status.
  • Keep standby servers patched.
  • Validate backups independently of replication.
  • Automate failover where appropriate.

Replication Workflow

flowchart LR

Primary --> WAL --> Streaming --> Standby --> Replay --> ReadQueries["Read Queries"]

Enterprise Best Practices

  • Use Streaming Replication for High Availability.
  • Use Read Replicas for reporting workloads.
  • Monitor pg_stat_replication.
  • Configure replication slots where appropriate.
  • Test disaster recovery procedures regularly.
  • Monitor WAL generation rate.
  • Maintain reliable network connectivity.
  • Automate failover using HA tools such as Patroni or repmgr when appropriate.
  • Keep regular backups even when replication is enabled.
  • Document failover and recovery procedures.

Quick Revision

Topic Key Point
Replication Copy Database Changes
Physical Replication Entire Cluster
Logical Replication Selected Objects
Streaming Replication Continuous WAL Transfer
Primary Read & Write Server
Standby Read Replica
WAL Write-Ahead Log
Replication Slot Preserve WAL Files
Hot Standby Read-Only Queries
Synchronous Strong Consistency
Asynchronous Better Performance
Replication Lag Delay Between Servers
Failover Standby Becomes Primary
Switchover Planned Role Change

Interview Tips

Interviewers frequently ask

  • What is PostgreSQL Replication?
  • Physical vs Logical Replication.
  • What is Streaming Replication?
  • What is WAL?
  • Primary vs Standby.
  • Synchronous vs Asynchronous Replication.
  • What is Replication Lag?
  • What is a Replication Slot?
  • Failover vs Switchover.
  • How do you monitor PostgreSQL Replication?

A strong interview explanation is:

"PostgreSQL replication uses WAL records to synchronize one or more standby servers with the primary database. Physical replication creates an exact binary copy of the database cluster, while logical replication replicates selected database objects. Streaming Replication continuously sends WAL changes to standby servers, enabling high availability, disaster recovery, and read scaling. Features such as replication slots, hot standby, failover, and read replicas make PostgreSQL suitable for enterprise production environments."

Summary

PostgreSQL Replication is a core technology for achieving high availability, fault tolerance, and read scalability. Features such as Streaming Replication, Physical Replication, Logical Replication, Replication Slots, Hot Standby, Read Replicas, Failover, and Switchover enable organizations to build resilient and scalable database architectures.

Understanding replication architectures, WAL-based synchronization, standby management, monitoring, and disaster recovery strategies is essential for PostgreSQL Developers, Database Engineers, DBAs, DevOps Engineers, and Solution Architects managing enterprise PostgreSQL deployments.