Replication
Use Cases
16
Copyright
© Postgres Professional, 2017–2025
Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Alexey Beresnev
Photo: Oleg Bartunov (Phu Monastery and Bhrikuti Peak, Nepal)
Use of Course Materials
Non-commercial use of course materials (presentations, demonstrations) is
allowed without restrictions. Commercial use is possible only with the written
permission of Postgres Professional. It is prohibited to make changes to the
course materials.
Feedback
Please send your feedback, comments and suggestions to:
edu@postgrespro.ru
Disclaimer
Postgres Professional assumes no responsibility for any damages and
losses, including loss of income, caused by direct or indirect, intentional or
accidental use of course materials. Postgres Professional company
specifically disclaims any warranties on course materials. Course materials
are provided “as is,” and Postgres Professional company has no obligations
to provide maintenance, support, updates, enhancements, or modifications.
2
Topics
Physical Replicaion Use Cases
Logical Replication Use Cases
3
Physical Replication
Hot Standby for High Availability
Load Balancing for OLTP Workloads
Replica for Reporting
Multiple Replicas and Cascading Replication
Delayed Replication
4
Hot Standby for High
Availability
synchronous replication: the replica stays as closely aligned with the leader as
possible
queries to replica are possible, but not a priority:
they will be terminated in case of any conflict
primary
(leader)
WAL segments
select, insert
update, delete
standby
(replica)
wal sender
wal receiver
startup
synchronous_commit = on
synchronous_standby_names
hot_standby_feedback = off
max_standby_streaming_delay = t
A replica can be set up to serve different purposes, and its configuration
depends on the intended use case.
One possible scenario is maintaining a hot standby for high availability
purposes. This means that if the main server fails, we need to switch over to
the replica as quickly as possible without losing any data.
Reliability is ensured by synchronous replication with synchronous_commit =
on mode (write option does not guarantee reliability, apply may cause
unnecessary delays).
For synchronous replication, the synchronous_standby_names configuration
parameter must specify a list of standby servers (replicas).
To minimize the replica's lag behind the primary server, WAL records should
be applied immediately. We can set the max_standby_streaming_delay
parameter to something small to achieve this.
To prevent queries on the replica from negatively impacting the primary
server, we can disable feedback (hot_standby_feedback = off).
With these settings, queries on the replica are allowed, but they'll be
terminated if conflicts occur. If queries to the replica aren't planned, you can
set it up in warm standby mode with hot_standby = off.
Note that replication is a fundamental mechanism for ensuring high
availability, but it's just one of many features of HA systems. This is covered
in more detail in the "Cluster Technologies" module.
5
OLTP Load Balancing
queries on the replica must complete successfully
long-running queries will impact the main server, but in an OLTP environment,
they shouldn't exist in the first place
data consistency must be handled separately
primary
(leader)
WAL segments
select, insert
update, delete
standby
(replica)
wal sender
wal receiver
startup
synchronous_commit = local
hot_standby_feedback = on
max_standby_streaming_delay = T
A replica can also be used to balance OLTP read load.
OLTP loads have a lot of short-running queries. This keeps the replica
closely synchronized with the main server to ensure queries won't be
interrupted by conflicts, while avoiding any negative impact on the main
server.
We can enable feedback (hot_standby_feedback = on) and set a sufficiently
large delay for applying conflicting records (max_standby_streaming_delay).
Synchronous replication likely isn't needed in this configuration. It won't
guarantee consistency anyway: a change committed on the leader will not
necessarily be noticed by the replica right away.
To ensure consistency, changes must commit on both servers
simultaneously. The synchronous_commit = apply mode allows waiting for
the WAL record to be applied on the replica (at the cost of significantly
degraded primary server performance), but then you could see the changes
on the replica before they appear on the leader.
A reliable solution that's transparent to applications can be achieved using
specialized distributed transaction protocols (see the "Cluster Technologies"
module).
6
Replica for Reporting
queries on replica must complete successfully, including long-
running ones (but the data may not be completely up-to-date)
the leader shouldn't be affected by feedback from the replica
to completely decouple the servers, remove the replication slot and rely on just the
WAL archive
primary
(leader)
WAL segments
select, insert
update, delete
standby
(replica)
wal sender
wal receiver
startup
synchronous_commit = local
hot_standby_feedback = off
max_standby_streaming_delay = −1
-
Another option is to distribute different types of workload across separate
servers. The main server can handle OLTP workloads, while long-running
read queries (reports) can be offloaded to a replica.
Beyond distributing workload across servers, this approach also prevents
long-running queries on the main server from delaying vacuum operations
and causing table bloat.
Based on these considerations, the reporting replica should be kept as
isolated as possible from the main server. Feedback should be disabled
(hot_standby_feedback = off), but the delay for conflicting WAL records
should be set very high, up to infinity (max_standby_streaming_delay = −1).
Since long-running reports don't need up-to-the-minute data, you can forgo
using a replication slot if an archive is available (just remember to adjust the
max_standby_archive_delay parameter the same way as
max_standby_streaming_delay). In that case, if the leader removes a WAL
file before the replica can retrieve it, the replica will fetch it from the archive
instead.
This replica can also be used for creating backups.
Of course, other viable parameter combinations exist. The key is to have a
clear understanding of what problem needs solving and how different
parameters affect the outcome.
7
Multiple replicas
primary
(leader)
WAL segments
select, insert
update, delete
standby
(replica A)
wal sender
standby
(replica B)
wal sender
wal receiver
startup
wal receiver
startup
pg_copy_physical_replication_slot()
Multiple replicas can be connected to the main server. No special
configuration is required for this, but keep in mind that each replica will have
its own dedicated wal sender process and its own replication slot.
During deployment, you can distribute a single base backup and clone the
replication slot using the pg_copy_physical_replication_slot function.
This setup is typically used for load distribution, but the actual distribution
itself must be handled by external tools (see the "Cluster Technologies"
module).
9
reduces load on the leader and redistributes network traffic
cascaded synchronous replication is not supported
feedback comes from all replicas
Cascading replication
primary
(leader)
WAL segments
select, insert
update, delete
standby
(replica B)
wal sender
standby
(replica A)
wal senderwal receiver
startup
wal receiver
startup
recovery_target_timeline = 'latest'
synchronous_standby_names = ''
or synchronous_commit = local
Multiple replicas connected to a single primary server will generate
additional load on it. Network load should also be considered when
transmitting multiple copies of the WAL stream.
To reduce this load, replicas can be arranged in a cascade configuration,
where servers relay WAL records to each other in a chain. The further
downstream from the leader, the greater potential lag may accumulate in the
replicated data. With this setup, the replication process has to be monitored
at multiple servers at once, making monitoring more complicated.
When a switchover to a replica occurs (to the one closest to the primary
server), its timeline ID increments. recovery_target_timeline = 'latest' will
direct the recovery to this new timeline (this is the default value).
Note that cascaded synchronous replication is not supported here – the
leader can only synchronize with replicas directly connected to it.
Therefore, on the first replica in the cascade, you need to set
synchronous_standby_names = "or synchronous_commit = 'local'.
However, the leader collects feedback from all replicas in the cascade.
10
Delayed Replication
a time machine with recovery options
to multiple points in time, no archive needed
primary
(leader)
WAL segments
select, insert
update, delete
standby
(replica)
wal sender
wal receiver
startup
recovery_min_apply_delay = T
hot_standby_feedback = off
Case: have the ability to view data at and recover to an arbitrary point in
time.
Point-in-time recovery from archive already does precisely this, but it takes a
while, and has to be set up in advance.
Alternatively, you can create a replica that applies WAL records with a delay
(recovery_min_apply_delay). This requires clock synchronization between
servers.
Only commit records are delayed, not all WAL records.
Records prior to commit may be applied "ahead of schedule," but this poses
no problem thanks to multiversion concurrency control. Feedback should be
disabled to prevent table bloat on the leader.
A typical scenario: something goes wrong on the leader (for instance, critical
data gets deleted). The replica still has the data because the corresponding
records haven't been applied yet. Further WAL replay is paused using the
pg_wal_replay_pause function while the issue is being investigated. The
pg_get_wal_replay_pause_state function allows you to check the pause
status.
To resume, use thepg_wal_replay_resume function to lift the pause. If you
decide to roll back to a point before the deletion occurred, set the recovery
target using one of the recovery_target_* parameters and restart the replica.
11
Logical Replication
Consolidation and Shared Reference Data
Major Version Upgrade
Leader-Leader
Logical Decoding at Physical Replica
12
collection and consolidation
of data from regional servers
Consolidation
main
server
select, insert
update, delete
regional
server
wal sender
regional
server
wal sender
select, insert
update, delete
WAL segments
select, insert
update, delete
WAL segments
logical repl.
worker
logical repl.
worker
WAL segments
Suppose there are several regional branches, each of which runs on its own
PostgreSQL server. The goal is to consolidate some of the data on the
central server.
First, publications for the necessary data are created on regional servers.
The central server subscribes to these publications. The received data can
be processed on the central server using triggers: for example, to
standardize it into a uniform format.
Since logical replication relies on streaming data through replication slots,
the servers need a reasonably stable connection: otherwise, during
connection outages, the regional servers will have to retain WAL files.
This approach has many nuances from a business logic perspective that
require thorough examination. In some cases, it may be easier to transmit
data in batches every now and again.
13
reference data replication
Shared reference data
main
server
regional
server
regional
server
WAL segments
WAL segments
WAL segments
select, insert
update, delete
wal sender
wal sender
select, insert
update, delete
logical repl.
worker
select, insert
update, delete
logical repl.
worker
Another use case: the main server maintains reference data that needs to
be kept current across regional servers.
In this case, the setup is reversed: the main server publishes changes, and
regional servers subscribe to receive those updates.
15
Server Upgrades
leader and physical replica: major version upgrade required
primary
server
standby
server
15.12
wal sender
wal receiver
startup
WAL segments
select, insert
update, delete
15.12
Logical replication can be used to upgrade the server's major version with
zero or minimal downtime.
Let's say we have a main server and a physical streaming replica.
16
Server Upgrades
primary
server
WAL segments
select, insert
update, delete
standby
server
wal sender
wal receiver
startup
wal sender
15.12 15.12
replication of all DB tables
clients not connected yet
standby
wal sender
wal receiver
startup
15.12
primary
server
WAL segments
logical repl.
worker
16.8
We set up a new server running the required PostgreSQL version and
migrate the table structure from the selected database to it.
On the primary server running the previous version, we publish changes for
all database tables. Since schema changes are not replicated, they must be
prohibited during the upgrade process.
17
Server Upgrades
primary
server
WAL segments
select, insert
update, delete
wal sender
15.12
primary
server
WAL segments
standby
server
logical repl.
worker
wal sender
16.8 16.8
clients not connected yet
new
physical
standby
wal receiver
startup
standby
server
wal sender
wal receiver
startup
15.12
We create a physical replica that mirrors the replica from the previous server
version.
We end up with a parallel server setup running the new PostgreSQL version.
This setup mirrors the one we have for the old version.
18
Server Upgrades
clients connect to the new server
primary
server
WAL segments
standby
server
wal sender
16.8 16.8
wal receiver
startup
select, insert
update, delete
After that, we switch clients over to the new servers and shut down the old
ones. The service interruption will depend on how smoothly this switchover
can be performed.
Keep in mind that this approach isn't universally applicable: it comes with
quite a few limitations and is significantly more complex than using the
pg_upgrade utility, which can perform the upgrade in a short amount of time.
19
a cluster where multiple servers can modify data
Leader-Leader
primary
primary
select, insert
update, delete
WAL segments
select, insert
update, delete
WAL segments
wal sender
logical repl.
worker
logical repl.
worker
wal sender
Case: provide reliable data storage on multiple servers with the ability to
write the data on any node (particularly useful for systems distributed across
multiple locations).
This can be achieved through bidirectional logical replication, synchronizing
changes for the same tables between servers in both directions (while
limiting allowed replication origins, as was discussed in the “Logical
Replication” module.
This requires that the applications working with the cluster are built with
certain considerations in mind in order to avoid conflicts when modifying
data in the same tables. For example, to use globally unique identifiers or to
ensure that different servers work with different ranges of keys.
You can also use the row filtering capabilities introduced in PostgreSQL 15.
Keep in mind that the leader-leader setup with logical replication will not
support global distributed transactions by itself. With synchronous
replication, reliability can be ensured, but consistency of data between
servers cannot. In addition, PostgreSQL does not offer any tools for
automatic failure processing, connecting or removing nodes from the cluster,
etc. These tasks must be solved by external means.
20
logical slot can be created on a physical replica
when creating a logical slot, you need a list of active transactions
WAL segments
select, insert
update, delete
wal sender
physical
standby
wal sender
wal receiver
startup
logical repl.
worker
publisher
server
subscriber
Decoding on Replica
wal_level = logical
hot_standby_feedback = on
pg_log_standby_snapshot()
Starting with PostgreSQL 16, the logical publication slot can be created on a
physical replica, which removes some of the load from the leader.
On the replica, enable hot_standby_feedback so that the leader does not
delete the tuples needed by the replica prematurely.
When creating a logical replication slot, you need to know the active
transaction IDs.
This information is collected from the bgwriter and checkpointer processes
on the leader as a WAL record. If there is no activity on the leader, slot
creation may be delayed. To avoid waiting, you can call the
pg_log_standby_snapshot function on the leader.
21
Takeaways
Physical replication is a fundamental mechanism for addressing
a wide range of challenges
ensuring high availability
load balancing for uniform workloads
separating different types of workloads
etc.
Logical replication extends these capabilities
22
Practice
1. Configure physical streaming replication between alpha and beta.
2. Configure cascade replication to gamma
with a WAL record application delay of ten seconds.
3. Verify that replication is working and confirm that data appears
on gamma with the configured delay.
4. Switch to beta after stopping the leader.
5. Confirm that gamma continues to operate as a replica.
After completing the practice, remove the created database, the replication
slot, and reset the configuration.
23
Practice+
1. Configure physical replication between alpha and beta with
feedback. Note the WAL level required to configure logical
replication, you will need it later.
2. Configure logical replication of a table from alpha to gamma by
creating a replication slot on beta – a physical replica. To speed
up slot creation on beta, use the pg_log_standby_snapshot
function.
2. Transfer the structure of the published table to gamma, for example, using
pg_dump. Do not transfer any data.
The publication must be created on alpha, subscription on gamma,
and beta set in the subscription connection string.
Reset the system: delete the new database and the replication slot and reset
the configuration.
