As applications grow, a single PostgreSQL server may eventually become a bottleneck or a single point of failure.
This is where PostgreSQL replication becomes useful.
Replication allows data from one PostgreSQL server to be copied to one or more other PostgreSQL servers.
A simple setup looks like this:
Primary PostgreSQL
│
├──────────→ Replica 1
│
└──────────→ Replica 2
The primary server handles writes, while replicas can be used for read workloads, backups, reporting, disaster recovery, or high availability depending on the architecture.
PostgreSQL replication copies database changes from a primary server to one or more standby servers.
In this guide, we’ll understand PostgreSQL replication from the basics and explore how it works, different replication types, streaming replication, replication slots, synchronous and asynchronous replication, failover, monitoring, and best practices.
What Is PostgreSQL Replication?
PostgreSQL replication is the process of maintaining copies of PostgreSQL data on other PostgreSQL servers.
The main server is commonly called the:
Primary
The receiving servers are commonly called:
Standbys
Replicas
A basic architecture looks like:
┌───────────────┐
│ Primary │
│ PostgreSQL │
└───────┬───────┘
│
WAL Changes
│
┌───────────┴───────────┐
↓ ↓
┌─────────────┐ ┌─────────────┐
│ Replica 1 │ │ Replica 2 │
│ PostgreSQL │ │ PostgreSQL │
└─────────────┘ └─────────────┘
The replicas maintain copies of the primary’s data.
Why Use PostgreSQL Replication?
Replication can solve several problems.
Common reasons include:
- High availability
- Disaster recovery
- Read scaling
- Reporting workloads
- Backup support
- Geographic redundancy
- Reducing load on the primary
For example, suppose your application receives:
100,000 read requests
10,000 write requests
You may want the primary database to handle writes while replicas handle some read traffic.
Application
│
├── Writes ──→ Primary
│
└── Reads ───→ Replicas
This can reduce pressure on the primary server.
PostgreSQL Replication Architecture
A typical production architecture might look like:
Application
/ \
/ \
Writes Reads
↓ ↓
┌───────────┐ ┌───────────┐
│ Primary │────→│ Replica │
└───────────┘ └───────────┘
For larger systems:
Application
│
Load Balancer
/ \
/ \
Primary Replicas
│ / \
│ / \
└──────────→ Replica 1
Replica 2
The exact architecture depends on whether you’re optimizing for read scaling, availability, disaster recovery, or all three.
Understanding WAL
To understand PostgreSQL replication, you need to understand WAL.
WAL stands for:
Write-Ahead Logging
PostgreSQL records changes in WAL before the corresponding data pages are considered safely written.
Conceptually:
SQL Operation
↓
WAL Record
↓
Data Changes
For example:
UPDATE users
SET name = 'Oliver'
WHERE id = 1;
PostgreSQL generates WAL describing the necessary changes.
Replication can use these WAL records to keep standby servers synchronized with the primary.
How Streaming Replication Works
PostgreSQL’s physical streaming replication uses WAL.
A simplified flow is:
Application
↓
Primary
↓
WAL
↓
WAL Sender
↓
Network
↓
WAL Receiver
↓
Standby
↓
Replay WAL
The standby continuously receives WAL records and replays them.
This allows the replica to maintain a physical copy of the primary database.
Primary and Standby
The primary is the server that accepts normal read/write operations.
A standby receives changes from the primary.
A standby can be configured as:
Hot Standby
which allows read-only queries while it is receiving and replaying WAL.
So:
Primary
→ Read + Write
Hot Standby
→ Read
This makes replicas useful for read-heavy workloads.
Asynchronous Replication
PostgreSQL streaming replication is commonly configured asynchronously.
In asynchronous replication:
Primary
↓
Commit
↓
Client receives response
Meanwhile:
↓
WAL sent to replica
↓
Replica replays WAL
The primary does not necessarily wait for the standby to confirm that the transaction has been safely received and replayed.
Advantage
Lower write latency.
Disadvantage
If the primary fails before a replica receives the latest WAL, some recently committed data may not yet exist on that replica.
This potential difference is commonly described as replication lag or, in a failure scenario, possible data loss depending on the recovery point.
Synchronous Replication
With synchronous replication, the primary can wait for confirmation from one or more synchronous standbys before considering a transaction committed according to the configured synchronous-commit behavior.
Conceptually:
Application
↓
Primary
↓
WAL
↓
Synchronous Standby
↓
Acknowledgment
↓
Commit
Advantage
Can reduce the amount of committed data that could be lost if the primary fails.
Disadvantage
Writes can become slower because the primary depends on standby acknowledgment.
There is therefore a trade-off:
Asynchronous
→ Lower latency
→ Potential replication lag
Synchronous
→ Stronger durability across servers
→ Potentially higher write latency
Replication Lag
Replication lag occurs when a replica hasn’t caught up with the primary.
For example:
Primary WAL position
↓
1000
Replica WAL position
↓
950
The replica is behind.
Lag can be caused by:
- Network problems
- Heavy write workload
- Slow storage
- High query load on the replica
- Configuration limitations
- Long-running transactions
- Resource constraints
Monitoring replication lag is critical in production.
Why Replication Lag Matters
Suppose a user creates an order:
POST /orders
↓
Primary
↓
Order created
Immediately afterward, the application reads from a replica:
GET /orders
↓
Replica
If replication hasn’t caught up, the newly created order might not appear yet.
This is called a read-after-write consistency problem.
A common solution is to route critical reads to the primary until the required consistency point is reached.
Read Scaling
One of the most common uses of PostgreSQL replicas is read scaling.
For example:
Application
│
┌───────┴───────┐
↓ ↓
Writes Reads
↓ ↓
Primary ┌────┴────┐
↓ ↓
Replica 1 Replica 2
If your application has many read operations, replicas can help distribute that workload.
However, replication does not automatically solve every performance problem.
Good indexing, query optimization, caching, and application architecture are still important.
Reporting and Analytics
Heavy reporting queries can put pressure on a production database.
Instead of running expensive reports on the primary:
Production Application
↓
Primary
you can sometimes direct reporting workloads to a replica:
Production Application
↓
Primary
│
↓
Replica
↓
Reports
This helps isolate some read-heavy workloads from transactional traffic.
The replica still consumes resources, so monitoring is necessary.
Physical Replication
PostgreSQL physical replication copies the underlying database changes through WAL.
Streaming replication is a major example.
The standby is essentially maintaining a physical representation of the primary’s database cluster.
Physical replication is commonly used for:
- High availability
- Standby servers
- Read replicas
- Disaster recovery
Logical Replication
PostgreSQL also supports logical replication.
Instead of replicating the physical storage changes, logical replication works with logical changes to data.
Conceptually:
Primary
↓
Logical Changes
↓
Publication
↓
Subscription
↓
Subscriber
Logical replication can be useful for:
- Selective table replication
- Data integration
- Migration scenarios
- Upgrading between PostgreSQL versions
- Replicating specific datasets
- Combining data from different systems
Physical vs Logical Replication
| Feature | Physical Replication | Logical Replication |
|---|---|---|
| Replication level | Physical/WAL | Logical changes |
| Typical use | HA and standby | Selective data replication |
| Entire cluster | Generally | Can replicate selected tables |
| Read-only standby | Yes | Not the same model |
| Cross-version flexibility | More limited | Often more flexible |
| Selective replication | No | Yes |
| Common use | Failover / DR | Integration / migration |
Choose based on the problem you’re trying to solve.
PostgreSQL Logical Replication Concepts
Logical replication uses:
Publication
Subscription
The publisher exposes changes through a publication.
The subscriber receives those changes through a subscription.
For example:
CREATE PUBLICATION app_publication
FOR TABLE users, orders;
Then on another PostgreSQL database:
CREATE SUBSCRIPTION app_subscription
CONNECTION '...'
PUBLICATION app_publication;
The exact connection string and security configuration should be handled carefully.
Replication Slots
A replication slot helps PostgreSQL retain WAL required by a consumer.
For example:
Primary
↓
WAL
↓
Replication Slot
↓
Replica / Consumer
A slot prevents PostgreSQL from removing WAL that a connected consumer still needs.
However, there is an important risk.
If a replication slot stops consuming WAL for a long time, WAL can accumulate on the primary and consume significant disk space.
Therefore:
Replication slots must be monitored carefully.
WAL Retention
PostgreSQL needs to retain enough WAL for replicas and other consumers to catch up.
If WAL is removed before a replica has received it, that replica may no longer be able to continue normally from the available WAL and may require additional recovery steps.
This is why monitoring replication health and WAL retention is important.
Streaming Replication Flow
A simplified physical replication flow is:
1. Client sends transaction
↓
2. Primary generates WAL
↓
3. WAL sender sends WAL
↓
4. Replica receives WAL
↓
5. Replica writes WAL
↓
6. Replica replays WAL
↓
7. Replica becomes current
This happens continuously while replication is operating normally.
Hot Standby
A PostgreSQL standby can be configured to accept read-only queries while recovery is in progress.
This is known as a hot standby.
It allows applications to use the replica for read workloads while it continues receiving and replaying changes.
However, some queries may conflict with recovery activity, especially long-running queries.
Replica Conflicts
A standby replaying WAL may need to apply changes that conflict with queries currently running on the standby.
For example:
Replica
↓
Long-running SELECT
↓
WAL replay needs to remove/update related rows
PostgreSQL has configuration options that influence how these conflicts are handled.
This is an important consideration when using replicas for reporting or analytics.
Failover
Failover is the process of moving application operations from a failed primary to another PostgreSQL server.
For example:
Normal:
Application
↓
Primary
↓
Replica
Failure:
Application
↓
Replica promoted
↓
New Primary
Failover can be:
Manual
or:
Automated
Automated failover typically requires additional infrastructure or orchestration.
PostgreSQL replication by itself doesn’t automatically provide a complete application failover system.
Promoting a Standby
A standby can be promoted to become a primary.
The exact command and procedure depend on the PostgreSQL version and operational setup.
Conceptually:
Standby
↓
Promotion
↓
Primary
After promotion, applications need to connect to the new primary.
This is why service discovery, DNS, proxies, load balancers, or database-aware infrastructure are often part of a complete high-availability architecture.
Replication Is Not the Same as Backup
This is one of the most important concepts.
Replication:
Primary
↓
Replica
Backup:
Database
↓
Independent Backup
↓
Object Storage / Backup System
If someone accidentally deletes data:
DROP TABLE users;
that change can also be replicated.
So the replica may contain the same deletion.
Replication improves availability, but it does not replace backups.
A production PostgreSQL environment should have both an appropriate replication strategy and a tested backup/recovery strategy.
Point-in-Time Recovery
PostgreSQL supports Point-in-Time Recovery (PITR) using base backups and WAL archiving.
This allows recovery to a particular point in time.
For example:
10:00 → Normal
10:30 → Bad deployment
10:35 → Data accidentally deleted
You may want to recover the database to:
10:29
PITR can be used for this type of recovery scenario when properly configured.
Replication for Disaster Recovery
A replica in another availability zone or geographic location can provide additional protection.
For example:
Region A
┌───────────────┐
│ Primary │
└───────┬───────┘
│
│ Replication
↓
Region B
┌───────────────┐
│ Standby │
└───────────────┘
If an entire infrastructure location becomes unavailable, the standby can potentially be used as part of the disaster recovery process.
The effectiveness depends heavily on network connectivity, replication lag, failover procedures, and recovery objectives.
RPO and RTO
Replication decisions are closely related to two important concepts.
RPO
Recovery Point Objective answers:
How much data can we afford to lose?
For example:
RPO = 1 minute
means the organization aims to limit potential data loss to approximately one minute of changes in the relevant failure scenario.
RTO
Recovery Time Objective answers:
How quickly must the system recover?
For example:
RTO = 5 minutes
means the target is to restore service within approximately five minutes.
Your replication and backup architecture should be designed around these requirements.
Monitoring Replication
PostgreSQL provides views for monitoring replication.
On a primary, you can inspect:
SELECT *
FROM pg_stat_replication;
This can provide information about connected standbys and their replication state.
On a standby, you can inspect:
SELECT *
FROM pg_stat_wal_receiver;
This provides information about the WAL receiver connection.
Monitoring should track things such as:
Replication state
WAL positions
Replay position
Replication lag
Connection status
WAL retention
Disk usage
Checking Replication Lag
PostgreSQL provides functions and statistics that can help estimate replication delay.
For example, on a primary you can inspect LSN differences using PostgreSQL’s WAL functions and pg_stat_replication.
The exact interpretation depends on whether you’re measuring:
Write lag
Flush lag
Replay lag
It’s important to distinguish between these metrics rather than treating all lag as the same thing.
Replication Monitoring Architecture
A production system might look like:
PostgreSQL
↓
Metrics
↓
Monitoring System
↓
Alerts
You may want alerts for:
- Replica disconnected
- Replication lag too high
- WAL growth
- Disk usage too high
- Replication slot retaining excessive WAL
- Standby recovery problems
Monitoring should be proactive rather than waiting for a replica to fail.
PostgreSQL Replication Slots: Important Warning
Suppose a replication slot is created:
Primary
↓
Slot
↓
Consumer stops
PostgreSQL may retain WAL needed by that slot.
If the consumer stays offline for too long:
WAL
↓
WAL
↓
WAL
↓
Disk usage increases
Eventually, the primary can run out of disk space.
Therefore:
Never create replication slots without monitoring their WAL retention.
Synchronous Standby Configuration
PostgreSQL allows administrators to configure synchronous standbys.
A conceptual configuration may look like:
Primary
↓
Synchronous Standby
The primary waits for the configured level of standby acknowledgment before completing the relevant commit behavior.
The exact settings depend on the durability requirements of the application.
Synchronous replication should be chosen carefully because it introduces additional dependency on network and standby performance.
Multiple Replicas
You can have multiple replicas:
Primary
/ | \
/ | \
↓ ↓ ↓
Replica1 Replica2 Replica3
This can support:
- Read scaling
- Geographic redundancy
- Reporting
- Failover options
- Different workload isolation
However, each additional replica adds infrastructure and operational complexity.
Cascading Replication
PostgreSQL can also support cascading replication.
Instead of every replica connecting directly to the primary:
Primary
├── Replica 1
└── Replica 2
you can have:
Primary
↓
Replica 1
↓
Replica 2
This can reduce the number of direct replication connections and can be useful in certain geographic or infrastructure designs.
But cascading replication introduces additional dependencies and potentially more replication delay.
Read and Write Routing
Applications often need to distinguish between:
Writes
and:
Reads
For example:
POST /orders
↓
Primary
GET /products
↓
Replica
This can be implemented at different layers using application logic, connection pools, proxies, or infrastructure.
Be careful with consistency requirements.
Not every read should automatically go to a replica.
When Should Reads Stay on the Primary?
Reads should generally stay on the primary when they require very fresh data and replica lag could cause incorrect application behavior.
Examples might include:
Immediately after creating a payment
Immediately after updating account settings
Critical inventory checks
Strong read-after-write requirements
A common strategy is:
Write
↓
Primary
Immediately related read
↓
Primary
Non-critical read
↓
Replica
The correct approach depends on the application’s consistency requirements.
PostgreSQL Replication and Load Balancing
A load balancer or database-aware proxy can distribute read traffic.
Conceptually:
Application
↓
Database Proxy
/ \
↓ ↓
Replica 1 Replica 2
Writes can be routed to the primary while read traffic is distributed among replicas.
The routing layer needs to understand which nodes are healthy and which are writable.
Replication During Database Upgrades
Logical replication can be useful in some PostgreSQL upgrade strategies.
A simplified approach is:
Old PostgreSQL
↓
Logical Replication
↓
New PostgreSQL
Once the new server has caught up, applications can be switched over.
The exact upgrade strategy depends on PostgreSQL versions, extensions, schema compatibility, downtime requirements, and application behavior.
Common PostgreSQL Replication Mistakes
1. Treating Replication as Backup
Replication does not protect against every type of data loss.
Always maintain backups.
2. Ignoring Replication Lag
A replica that is several hours behind isn’t useful for every workload.
Monitor lag continuously.
3. Creating Unmonitored Replication Slots
Slots can retain large amounts of WAL.
Monitor them carefully.
4. Sending Every Read to Replicas
Replicas may lag behind the primary.
Critical reads may require the primary.
5. Assuming Automatic Failover Exists
Replication and failover are related but different problems.
A complete HA solution needs:
Replication
+
Failure Detection
+
Promotion
+
Application Routing
+
Recovery Procedures
6. Running Heavy Queries on Replicas Without Monitoring
Reporting queries can consume CPU, memory, and storage resources.
A replica is still a database server and needs capacity planning.
PostgreSQL Replication Best Practices
Monitor Replication Lag
Set appropriate alerts.
Monitor Disk Usage
Especially monitor WAL growth.
Monitor Replication Slots
Ensure inactive slots don’t retain unlimited WAL.
Maintain Independent Backups
Replication is not a replacement for backup.
Test Failover
A failover procedure that has never been tested may fail when you need it most.
Define RPO and RTO
Use business requirements to guide your architecture.
Consider Read Consistency
Don’t route critical reads to lagging replicas blindly.
Keep Replicas Healthy
Replicas should have sufficient CPU, memory, network, and storage resources.
Document Recovery Procedures
Your team should know how to respond when the primary fails.
Simple PostgreSQL Replication Architecture
A basic high-availability setup might look like:
Application
│
↓
Database Proxy
/ \
/ \
↓ ↓
PostgreSQL PostgreSQL
Primary Standby
│ ↑
└── WAL ──────┘
If the primary fails:
Primary
✕
↓
Standby
↓
Promote
↓
New Primary
The proxy or application then needs to route writes to the new primary.
PostgreSQL Replication Checklist
Before deploying replication, consider:
☐ Primary configured correctly
☐ Standby configured correctly
☐ WAL settings reviewed
☐ Replication authentication configured
☐ Replication slots monitored
☐ Replication lag monitored
☐ Disk usage monitored
☐ Backups configured
☐ PITR strategy considered
☐ Failover procedure documented
☐ Failover tested
☐ Application routing configured
☐ RPO defined
☐ RTO defined
PostgreSQL Replication Cheat Sheet
| Concept | Purpose |
|---|---|
| Primary | Handles normal writes |
| Standby | Receives and replays changes |
| WAL | Records database changes |
| Streaming Replication | Continuously sends WAL |
| Async Replication | Primary doesn’t wait for standby confirmation |
| Sync Replication | Primary waits according to synchronous commit settings |
| Physical Replication | Replicates physical/WAL changes |
| Logical Replication | Replicates logical data changes |
| Replication Slot | Tracks WAL needed by a consumer |
| Hot Standby | Allows read-only queries during recovery |
| Failover | Moves service to another PostgreSQL server |
| PITR | Recovers to a selected point in time |
Final Thoughts
PostgreSQL replication is an important technology for applications that need higher availability, disaster recovery, read scaling, or database redundancy.
The basic concept is:
Primary
↓
WAL
↓
Standby
↓
Replay
From there, you can build more advanced architectures:
Primary
/ | \
↓ ↓ ↓
Replica Replica Replica
or:
Primary
↓
Replica
↓
Replica
The most important concepts to understand are:
WAL
Streaming Replication
Replication Lag
Synchronous Replication
Asynchronous Replication
Replication Slots
Logical Replication
Failover
PITR
But remember that replication is only one part of a reliable PostgreSQL architecture.
A production-ready system should combine:
Replication
+
Backups
+
Monitoring
+
Failover
+
Recovery Testing
PostgreSQL replication improves availability and scalability, but a reliable database architecture requires more than simply keeping a second copy of your data.
